-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMsaSQLDataAnalytics.html
More file actions
1353 lines (1250 loc) · 81.8 KB
/
Copy pathMsaSQLDataAnalytics.html
File metadata and controls
1353 lines (1250 loc) · 81.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html lang="en"><head>
<script src="site_libs/clipboard/clipboard.min.js"></script>
<script src="site_libs/quarto-html/tabby.min.js"></script>
<script src="site_libs/quarto-html/popper.min.js"></script>
<script src="site_libs/quarto-html/tippy.umd.min.js"></script>
<link href="site_libs/quarto-html/tippy.css" rel="stylesheet">
<link href="site_libs/quarto-html/light-border.css" rel="stylesheet">
<link href="site_libs/quarto-html/quarto-html.min.css" rel="stylesheet" data-mode="light">
<link href="site_libs/quarto-html/quarto-syntax-highlighting.css" rel="stylesheet" id="quarto-text-highlighting-styles"><meta charset="utf-8">
<meta name="generator" content="quarto-1.3.353">
<title>MsaSQLDataAnalytics - Data Analytics Introduction Using SQL Training</title>
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, minimal-ui">
<link rel="stylesheet" href="site_libs/revealjs/dist/reset.css">
<link rel="stylesheet" href="site_libs/revealjs/dist/reveal.css">
<style>
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
div.columns{display: flex; gap: min(4vw, 1.5em);}
div.column{flex: auto; overflow-x: auto;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
ul.task-list li input[type="checkbox"] {
width: 0.8em;
margin: 0 0.8em 0.2em -1em; /* quarto-specific, see https://github.com/quarto-dev/quarto-cli/issues/4556 */
vertical-align: middle;
}
/* CSS for syntax highlighting */
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { display: inline-block; line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ color: #003b4f; background-color: #f1f3f5; }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span { color: #003b4f; } /* Normal */
code span.al { color: #ad0000; } /* Alert */
code span.an { color: #5e5e5e; } /* Annotation */
code span.at { color: #657422; } /* Attribute */
code span.bn { color: #ad0000; } /* BaseN */
code span.bu { } /* BuiltIn */
code span.cf { color: #003b4f; } /* ControlFlow */
code span.ch { color: #20794d; } /* Char */
code span.cn { color: #8f5902; } /* Constant */
code span.co { color: #5e5e5e; } /* Comment */
code span.cv { color: #5e5e5e; font-style: italic; } /* CommentVar */
code span.do { color: #5e5e5e; font-style: italic; } /* Documentation */
code span.dt { color: #ad0000; } /* DataType */
code span.dv { color: #ad0000; } /* DecVal */
code span.er { color: #ad0000; } /* Error */
code span.ex { } /* Extension */
code span.fl { color: #ad0000; } /* Float */
code span.fu { color: #4758ab; } /* Function */
code span.im { color: #00769e; } /* Import */
code span.in { color: #5e5e5e; } /* Information */
code span.kw { color: #003b4f; } /* Keyword */
code span.op { color: #5e5e5e; } /* Operator */
code span.ot { color: #003b4f; } /* Other */
code span.pp { color: #ad0000; } /* Preprocessor */
code span.sc { color: #5e5e5e; } /* SpecialChar */
code span.ss { color: #20794d; } /* SpecialString */
code span.st { color: #20794d; } /* String */
code span.va { color: #111111; } /* Variable */
code span.vs { color: #20794d; } /* VerbatimString */
code span.wa { color: #5e5e5e; font-style: italic; } /* Warning */
</style>
<link rel="stylesheet" href="site_libs/revealjs/dist/theme/quarto.css">
<link href="site_libs/revealjs/plugin/quarto-line-highlight/line-highlight.css" rel="stylesheet">
<link href="site_libs/revealjs/plugin/reveal-menu/menu.css" rel="stylesheet">
<link href="site_libs/revealjs/plugin/reveal-menu/quarto-menu.css" rel="stylesheet">
<link href="site_libs/revealjs/plugin/quarto-support/footer.css" rel="stylesheet">
<style type="text/css">
.callout {
margin-top: 1em;
margin-bottom: 1em;
border-radius: .25rem;
}
.callout.callout-style-simple {
padding: 0em 0.5em;
border-left: solid #acacac .3rem;
border-right: solid 1px silver;
border-top: solid 1px silver;
border-bottom: solid 1px silver;
display: flex;
}
.callout.callout-style-default {
border-left: solid #acacac .3rem;
border-right: solid 1px silver;
border-top: solid 1px silver;
border-bottom: solid 1px silver;
}
.callout .callout-body-container {
flex-grow: 1;
}
.callout.callout-style-simple .callout-body {
font-size: 1rem;
font-weight: 400;
}
.callout.callout-style-default .callout-body {
font-size: 0.9rem;
font-weight: 400;
}
.callout.callout-titled.callout-style-simple .callout-body {
margin-top: 0.2em;
}
.callout:not(.callout-titled) .callout-body {
display: flex;
}
.callout:not(.no-icon).callout-titled.callout-style-simple .callout-content {
padding-left: 1.6em;
}
.callout.callout-titled .callout-header {
padding-top: 0.2em;
margin-bottom: -0.2em;
}
.callout.callout-titled .callout-title p {
margin-top: 0.5em;
margin-bottom: 0.5em;
}
.callout.callout-titled.callout-style-simple .callout-content p {
margin-top: 0;
}
.callout.callout-titled.callout-style-default .callout-content p {
margin-top: 0.7em;
}
.callout.callout-style-simple div.callout-title {
border-bottom: none;
font-size: .9rem;
font-weight: 600;
opacity: 75%;
}
.callout.callout-style-default div.callout-title {
border-bottom: none;
font-weight: 600;
opacity: 85%;
font-size: 0.9rem;
padding-left: 0.5em;
padding-right: 0.5em;
}
.callout.callout-style-default div.callout-content {
padding-left: 0.5em;
padding-right: 0.5em;
}
.callout.callout-style-simple .callout-icon::before {
height: 1rem;
width: 1rem;
display: inline-block;
content: "";
background-repeat: no-repeat;
background-size: 1rem 1rem;
}
.callout.callout-style-default .callout-icon::before {
height: 0.9rem;
width: 0.9rem;
display: inline-block;
content: "";
background-repeat: no-repeat;
background-size: 0.9rem 0.9rem;
}
.callout-title {
display: flex
}
.callout-icon::before {
margin-top: 1rem;
padding-right: .5rem;
}
.callout.no-icon::before {
display: none !important;
}
.callout.callout-titled .callout-body > .callout-content > :last-child {
margin-bottom: 0.5rem;
}
.callout.callout-titled .callout-icon::before {
margin-top: .5rem;
padding-right: .5rem;
}
.callout:not(.callout-titled) .callout-icon::before {
margin-top: 1rem;
padding-right: .5rem;
}
/* Callout Types */
div.callout-note {
border-left-color: #4582ec !important;
}
div.callout-note .callout-icon::before {
background-image: url('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAAEU0lEQVRYCcVXTWhcVRQ+586kSUMMxkyaElstCto2SIhitS5Ek8xUKV2poatCcVHtUlFQk8mbaaziwpWgglJwVaquitBOfhQXFlqlzSJpFSpIYyXNjBNiTCck7x2/8/LeNDOZxDuEkgOXe++553zfefee+/OYLOXFk3+1LLrRdiO81yNqZ6K9cG0P3MeFaMIQjXssE8Z1JzLO9ls20MBZX7oG8w9GxB0goaPrW5aNMp1yOZIa7Wv6o2ykpLtmAPs/vrG14Z+6d4jpbSKuhdcSyq9wGMPXjonwmESXrriLzFGOdDBLB8Y6MNYBu0dRokSygMA/mrun8MGFN3behm6VVAwg4WR3i6FvYK1T7MHo9BK7ydH+1uurECoouk5MPRyVSBrBHMYwVobG2aOXM07sWrn5qgB60rc6mcwIDJtQrnrEr44kmy+UO9r0u9O5/YbkS9juQckLed3DyW2XV/qWBBB3ptvI8EUY3I9p/67OW+g967TNr3Sotn3IuVlfMLVnsBwH4fsnebJvyGm5GeIUA3jljERmrv49SizPYuq+z7c2H/jlGC+Ghhupn/hcapqmcudB9jwJ/3jvnvu6vu5lVzF1fXyZuZZ7U8nRmVzytvT+H3kilYvH09mLWrQdwFSsFEsxFVs5fK7A0g8gMZjbif4ACpKbjv7gNGaD8bUrlk8x+KRflttr22JEMRUbTUwwDQScyzPgedQHZT0xnx7ujw2jfVfExwYHwOsDTjLdJ2ebmeQIlJ7neo41s/DrsL3kl+W2lWvAga0tR3zueGr6GL78M3ifH0rGXrBC2aAR8uYcIA5gwV8zIE8onoh8u0Fca/ciF7j1uOzEnqcIm59sEXoGc0+z6+H45V1CvAvHcD7THztu669cnp+L0okAeIc6zjbM/24LgGM1gZk7jnRu1aQWoU9sfUOuhrmtaPIO3YY1KLLWZaEO5TKUbMY5zx8W9UJ6elpLwKXbsaZ4EFl7B4bMtDv0iRipKoDQT2sNQI9b1utXFdYisi+wzZ/ri/1m7QfDgEuvgUUEIJPq3DhX/5DWNqIXDOweC2wvIR90Oq3lDpdMIgD2r0dXvGdsEW5H6x6HLRJYU7C69VefO1x8Gde1ZFSJLfWS1jbCnhtOPxmpfv2LXOA2Xk2tvnwKKPFuZ/oRmwBwqRQDcKNeVQkYcOjtWVBuM/JuYw5b6isojIkYxyYAFn5K7ZBF10fea52y8QltAg6jnMqNHFBmGkQ1j+U43HMi2xMar1Nv0zGsf1s8nUsmUtPOOrbFIR8bHFDMB5zL13Gmr/kGlCkUzedTzzmzsaJXhYawnA3UmARpiYj5ooJZiUoxFRtK3X6pgNPv+IZVPcnwbOl6f+aBaO1CNvPW9n9LmCp01nuSaTRF2YxHqZ8DYQT6WsXT+RD6eUztwYLZ8rM+rcPxamv1VQzFUkzFXvkiVrySGQgJNvXHJAxiU3/NwiC03rSf05VBaPtu/Z7/B8Yn/w7eguloAAAAAElFTkSuQmCC');
}
div.callout-note.callout-style-default .callout-title {
background-color: #dae6fb
}
div.callout-important {
border-left-color: #d9534f !important;
}
div.callout-important .callout-icon::before {
background-image: url('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAAEKklEQVRYCcVXTWhcVRS+575MJym48A+hSRFr00ySRQhURRfd2HYjk2SSTokuBCkU2o0LoSKKraKIBTcuFCoidGFD08nkBzdREbpQ1EDNIv8qSGMFUboImMSZd4/f9zJv8ibJMC8xJQfO3HPPPef7zrvvvnvviIkpC9nsw0UttFunbUhpFzFtarSd6WJkStVMw5xyVqYTvkwfzuf/5FgtkVoB0729j1rjXwThS7Vio+Mo6DNnvLfahoZ+i/o32lULuJ3NNiz7q6+pyAUkJaFF6JwaM2lUJlV0MlnQn5aTRbEu0SEqHUa0A4AdiGuB1kFXRfVyg5d87+Dg4DL6m2TLAub60ilj7A1Ec4odSAc8X95sHh7+ZRPCFo6Fnp7HfU/fBng/hi10CjCnWnJjsxvDNxWw0NfV6Rv5GgP3I3jGWXumdTD/3cbEOP2ZbOZp69yniG3FQ9z1jD7bnBu9Fc2tKGC2q+uAJOQHBDRiZX1x36o7fWBs7J9ownbtO+n0/qWkvW7UPIfc37WgT6ZGR++EOJyeQDSb9UB+DZ1G6DdLDzyS+b/kBCYGsYgJbSQHuThGKRcw5xdeQf8YdNHsc6ePXrlSYMBuSIAFTGAtQo+VuALo4BX83N190NWZWbynBjhOHsmNfFWLeL6v+ynsA58zDvvAC8j5PkbOcXCMg2PZFk3q8MjI7WAG/Dp9AwP7jdGBOOQkAvlFUB+irtm16I1Zw9YBcpGTGXYmk3kQIC/Cds55l+iMI3jqhjAuaoe+am2Jw5GT3Nbz3CkE12NavmzN5+erJW7046n/CH1RO/RVa8lBLozXk9uqykkGAyRXLWlLv5jyp4RFsG5vGVzpDLnIjTWgnRy2Rr+tDKvRc7Y8AyZq10jj8DqXdnIRNtFZb+t/ZRtXcDiVnzpqx8mPcDWxgARUqx0W1QB9MeUZiNrV4qP+Ehc+BpNgATsTX8ozYKL2NtFYAHc84fG7ndxUPr+AR/iQSns7uSUufAymwDOb2+NjK27lEFocm/EE2WpyIy/Hi66MWuMKJn8RvxIcj87IM5Vh9663ziW36kR0HNenXuxmfaD8JC7tfKbrhFr7LiZCrMjrzTeGx+PmkosrkNzW94ObzwocJ7A1HokLolY+AvkTiD/q1H0cN48c5EL8Crkttsa/AXQVDmutfyku0E7jShx49XqV3MFK8IryDhYVbj7Sj2P2eBxwcXoe8T8idsKKPRcnZw1b+slFTubwUwhktrfnAt7J++jwQtLZcm3sr9LQrjRzz6cfMv9aLvgmnAGvpoaGLxM4mAEaLV7iAzQ3oU0IvD5x9ix3yF2RAAuYAOO2f7PEFWCXZ4C9Pb2UsgDeVnFSpbFK7/IWu7TPTvBqzbGdCHOJQSxiEjt6IyZmxQyEJHv6xyQsYk//moVFsN2zP6fRImjfq7/n/wFDguUQFNEwugAAAABJRU5ErkJggg==');
}
div.callout-important.callout-style-default .callout-title {
background-color: #f7dddc
}
div.callout-warning {
border-left-color: #f0ad4e !important;
}
div.callout-warning .callout-icon::before {
background-image: url('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAAETklEQVRYCeVWW2gcVRg+58yaTUnizqbipZeX4uWhBEniBaoUX1Ioze52t7sRq6APio9V9MEaoWlVsFasRq0gltaAPuxms8lu0gcviE/FFOstVbSIxgcv6SU7EZqmdc7v9+9mJtNks51NTUH84ed889/PP+cmxP+d5FIbMJmNbpREu4WUkiTtCicKny0l1pIKmBzovF2S+hIJHX8iEu3hZJ5lNZGqyRrGSIQpq15AzF28jgpeY6yk6GVdrfFqdrD6Iw+QlB8g0YS2g7dyQmXM/IDhBhT0UCiRf59lfqmmDvzRt6kByV/m4JjtzuaujMUM2c5Z2d6JdKrRb3K2q6mA+oYVz8JnDdKPmmNthzkAk/lN63sYPgevrguc72aZX/L9C6x09GYyxBgCX4NlvyGUHOKELlm5rXeR1kchuChJt4SSwyddZRXgvwMGvYo4QSlk3/zkHD8UHxwVJA6zjZZqP8v8kK8OWLnIZtLyCAJagYC4rTGW/9Pqj92N/c+LUaAj27movwbi19tk/whRCIE7Q9vyI6yvRpftAKVTdUjOW40X3h5OXsKCdmFcx0xlLJoSuQngnrJe7Kcjm4OMq9FlC7CMmScQANuNvjfP3PjGXDBaUQmbp296S5L4DrpbrHN1T87ZVEZVCzg1FF0Ft+dKrlLukI+/c9ENo+TvlTDbYFvuKPtQ9+l052rXrgKoWkDAFnvh0wTOmYn8R5f4k/jN/fZiCM1tQx9jQQ4ANhqG4hiL0qIFTGViG9DKB7GYzgubnpofgYRwO+DFjh0Zin2m4b/97EDkXkc+f6xYAPX0KK2I/7fUQuwzuwo/L3AkcjugPNixC8cHf0FyPjWlItmLxWw4Ou9YsQCr5fijMGoD/zpdRy95HRysyXA74MWOnscpO4j2y3HAVisw85hX5+AFBRSHt4ShfLFkIMXTqyKFc46xdzQM6XbAi702a7sy04J0+feReMFKp5q9esYLCqAZYw/k14E/xcLLsFElaornTuJB0svMuJINy8xkIYuL+xPAlWRceH6+HX7THJ0djLUom46zREu7tTkxwmf/FdOZ/sh6Q8qvEAiHpm4PJ4a/doJe0gH1t+aHRgCzOvBvJedEK5OFE5jpm4AGP2a8Dxe3gGJ/pAutug9Gp6he92CsSsWBaEcxGx0FHytmIpuqGkOpldqNYQK8cSoXvd+xLxXADw0kf6UkJNFtdo5MOgaLjiQOQHcn+A6h5NuL2s0qsC2LOM75PcF3yr5STuBSAcGG+meA14K/CI21HcS4LBT6tv0QAh8Dr5l93AhZzG5ZJ4VxAqdZUEl9z7WJ4aN+svMvwHHL21UKTd1mqvChH7/Za5xzXBBKrUcB0TQ+Ulgkfbi/H/YT5EptrGzsEK7tR1B7ln9BBwckYfMiuSqklSznIuoIIOM42MQO+QnduCoFCI0bpkzjCjddHPN/F+2Yu+sd9bKNpVwHhbS3LluK/0zgfwD0xYI5dXuzlQAAAABJRU5ErkJggg==');
}
div.callout-warning.callout-style-default .callout-title {
background-color: #fcefdc
}
div.callout-tip {
border-left-color: #02b875 !important;
}
div.callout-tip .callout-icon::before {
background-image: url('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAADr0lEQVRYCe1XTWgTQRj9ZjZV8a9SPIkKgj8I1bMHsUWrqYLVg4Ue6v9BwZOxSYsIerFao7UiUryIqJcqgtpimhbBXoSCVxUFe9CTiogUrUp2Pt+3aUI2u5vdNh4dmMzOzHvvezuz8xNFM0mjnbXaNu1MvFWRXkXEyE6aYOYJpdW4IXuA4r0fo8qqSMDBU0v1HJUgVieAXxzCsdE/YJTdFcVIZQNMyhruOMJKXYFoLfIfIvVIMWdsrd+Rpd86ZmyzzjJmLStqRn0v8lzkb4rVIXvnpScOJuAn2ACC65FkPzEdEy4TPWRLJ2h7z4cArXzzaOdKlbOvKKX25Wl00jSnrwVxAg3o4dRxhO13RBSdNvH0xSARv3adTXbBdTf64IWO2vH0LT+cv4GR1DJt+DUItaQogeBX/chhbTBxEiZ6gftlDNXTrvT7co4ub5A6gp9HIcHvzTa46OS5fBeP87Qm0fQkr4FsYgVQ7Qg+ZayaDg9jhg1GkWj8RG6lkeSacrrHgDaxdoBiZPg+NXV/KifMuB6//JmYH4CntVEHy/keA6x4h4CU5oFy8GzrBS18cLJMXcljAKB6INjWsRcuZBWVaS3GDrqB7rdapVIeA+isQ57Eev9eCqzqOa81CY05VLd6SamW2wA2H3SiTbnbSxmzfp7WtKZkqy4mdyAlGx7ennghYf8voqp9cLSgKdqNfa6RdRsAAkPwRuJZNbpByn+RrJi1RXTwdi8RQF6ymDwGMAtZ6TVE+4uoKh+MYkcLsT0Hk8eAienbiGdjJHZTpmNjlbFJNKDVAp2fJlYju6IreQxQ08UJDNYdoLSl6AadO+fFuCQqVMB1NJwPm69T04Wv5WhfcWyfXQB+wXRs1pt+nCknRa0LVzSA/2B+a9+zQJadb7IyyV24YAxKp2Jqs3emZTuNnKxsah+uabKbMk7CbTgJx/zIgQYErIeTKRQ9yD9wxVof5YolPHqaWo7TD6tJlh7jQnK5z2n3+fGdggIOx2kaa2YI9QWarc5Ce1ipNWMKeSG4DysFF52KBmTNMmn5HqCFkwy34rDg05gDwgH3bBi+sgFhN/e8QvRn8kbamCOhgrZ9GJhFDgfcMHzFb6BAtjKpFhzTjwv1KCVuxHvCbsSiEz4CANnj84cwHdFXAbAOJ4LTSAawGWFn5tDhLMYz6nWeU2wJfIhmIJBefcd/A5FWQWGgrWzyORZ3Q6HuV+Jf0Bj+BTX69fm1zWgK7By1YTXchFDORywnfQ7GpzOo6S+qECrsx2ifVQAAAABJRU5ErkJggg==');
}
div.callout-tip.callout-style-default .callout-title {
background-color: #ccf1e3
}
div.callout-caution {
border-left-color: #fd7e14 !important;
}
div.callout-caution .callout-icon::before {
background-image: url('data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAACV0lEQVRYCdVWzWoUQRCuqp2ICBLJXgITZL1EfQDBW/bkzUMUD7klD+ATSHBEfAIfQO+iXsWDxJsHL96EHAwhgzlkg8nBg25XWb0zIb0zs9muYYWkoKeru+vn664fBqElyZNuyh167NXJ8Ut8McjbmEraKHkd7uAnAFku+VWdb3reSmRV8PKSLfZ0Gjn3a6Xlcq9YGb6tADjn+lUfTXtVmaZ1KwBIvFI11rRXlWlatwIAAv2asaa9mlB9wwygiDX26qaw1yYPzFXg2N1GgG0FMF8Oj+VIx7E/03lHx8UhvYyNZLN7BwSPgekXXLribw7w5/c8EF+DBK5idvDVYtEEwMeYefjjLAdEyQ3M9nfOkgnPTEkYU+sxMq0BxNR6jExrAI31H1rzvLEfRIdgcv1XEdj6QTQAS2wtstEALLG1yEZ3QhH6oDX7ExBSFEkFINXH98NTrme5IOaaA7kIfiu2L8A3qhH9zRbukdCqdsA98TdElyeMe5BI8Rs2xHRIsoTSSVFfCFCWGPn9XHb4cdobRIWABNf0add9jakDjQJpJ1bTXOJXnnRXHRf+dNL1ZV1MBRCXhMbaHqGI1JkKIL7+i8uffuP6wVQAzO7+qVEbF6NbS0LJureYcWXUUhH66nLR5rYmva+2tjRFtojkM2aD76HEGAD3tPtKM309FJg5j/K682ywcWJ3PASCcycH/22u+Bh7Aa0ehM2Fu4z0SAE81HF9RkB21c5bEn4Dzw+/qNOyXr3DCTQDMBOdhi4nAgiFDGCinIa2owCEChUwD8qzd03PG+qdW/4fDzjUMcE1ZpIAAAAASUVORK5CYII=');
}
div.callout-caution.callout-style-default .callout-title {
background-color: #ffe5d0
}
</style>
<style type="text/css">
.reveal div.sourceCode {
margin: 0;
overflow: auto;
}
.reveal div.hanging-indent {
margin-left: 1em;
text-indent: -1em;
}
.reveal .slide:not(.center) {
height: 100%;
overflow-y: auto;
}
.reveal .slide.scrollable {
overflow-y: auto;
}
.reveal .footnotes {
height: 100%;
overflow-y: auto;
}
.reveal .slide .absolute {
position: absolute;
display: block;
}
.reveal .footnotes ol {
counter-reset: ol;
list-style-type: none;
margin-left: 0;
}
.reveal .footnotes ol li:before {
counter-increment: ol;
content: counter(ol) ". ";
}
.reveal .footnotes ol li > p:first-child {
display: inline-block;
}
.reveal .slide ul,
.reveal .slide ol {
margin-bottom: 0.5em;
}
.reveal .slide ul li,
.reveal .slide ol li {
margin-top: 0.4em;
margin-bottom: 0.2em;
}
.reveal .slide ul[role="tablist"] li {
margin-bottom: 0;
}
.reveal .slide ul li > *:first-child,
.reveal .slide ol li > *:first-child {
margin-block-start: 0;
}
.reveal .slide ul li > *:last-child,
.reveal .slide ol li > *:last-child {
margin-block-end: 0;
}
.reveal .slide .columns:nth-child(3) {
margin-block-start: 0.8em;
}
.reveal blockquote {
box-shadow: none;
}
.reveal .tippy-content>* {
margin-top: 0.2em;
margin-bottom: 0.7em;
}
.reveal .tippy-content>*:last-child {
margin-bottom: 0.2em;
}
.reveal .slide > img.stretch.quarto-figure-center,
.reveal .slide > img.r-stretch.quarto-figure-center {
display: block;
margin-left: auto;
margin-right: auto;
}
.reveal .slide > img.stretch.quarto-figure-left,
.reveal .slide > img.r-stretch.quarto-figure-left {
display: block;
margin-left: 0;
margin-right: auto;
}
.reveal .slide > img.stretch.quarto-figure-right,
.reveal .slide > img.r-stretch.quarto-figure-right {
display: block;
margin-left: auto;
margin-right: 0;
}
</style>
</head>
<body class="quarto-light">
<div class="reveal">
<div class="slides">
<section id="title-slide" class="quarto-title-block center">
<h1 class="title">Data Analytics Introduction Using SQL Training</h1>
<div class="quarto-title-authors">
</div>
</section>
<section>
<section id="introduction" class="title-slide slide level1 center">
<h1>Introduction</h1>
<p>SQL or Structured Query Language lets you do this in a very efficient way. SQL not only enables you to you ask meaningful questions to the data but also allows you to you play with the data in many different ways. Without databases, practically no real-world application is possible. So, the knowledge of databases and being able to handle them are crucial parts of a data scientist’s toolbox.</p>
<p><strong>SQL</strong> is also called <strong><em>SE-QU-EL</em></strong>.</p>
<p>SQL relies on relational nature or tables. Tables (datasets with rows and columns) in SQL can be equated dataframes in <strong>Python</strong> and tables in <strong>Excel</strong></p>
</section>
<section id="relationship-database-management-system-rdbms" class="slide level2">
<h2>Relationship Database Management System (RDBMS)</h2>
<p>To be able to fully appreciate the use of databases and the relational nature, we access this through <strong>RDBMS</strong>. There are numerous RDBMS just to mention but a few;</p>
<ul>
<li>MS SQL</li>
<li>MySQL</li>
<li>PostgreSQL</li>
<li>SQLite</li>
<li>e.t.c.</li>
</ul>
</section>
<section id="sql-crud-create-read-update-delete" class="slide level2">
<h2>SQL CRUD (<strong>C</strong>reate <strong>R</strong>ead <strong>U</strong>pdate <strong>D</strong>elete)</h2>
<p><strong>SQL</strong> main ideology is to be able to: - <strong><em>Create</em></strong> tables/Databases - <strong>Read</strong> tables/Databases - <strong>Update</strong> tables/Databases - <strong>Delete</strong> tables/Databases</p>
<p><strong><em>Create</em></strong></p>
<div class="sourceCode" id="cb1"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb1-1"><a href="#cb1-1"></a></span>
<span id="cb1-2"><a href="#cb1-2"></a>CREATE TABLE table_name (</span>
<span id="cb1-3"><a href="#cb1-3"></a> column_name TYPE column_constraint,</span>
<span id="cb1-4"><a href="#cb1-4"></a> table_constraint table_constraint</span>
<span id="cb1-5"><a href="#cb1-5"></a>)</span>
<span id="cb1-6"><a href="#cb1-6"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong><em>Insert</em></strong></p>
<p>Inserting records into a table</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb2-1"><a href="#cb2-1"></a></span>
<span id="cb2-2"><a href="#cb2-2"></a>INSERT INTO table(column1, column2, …)</span>
<span id="cb2-3"><a href="#cb2-3"></a>VALUES</span>
<span id="cb2-4"><a href="#cb2-4"></a> (value1, value2, …);</span>
<span id="cb2-5"><a href="#cb2-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong><em>Update</em></strong></p>
<p>Updating a record in the table - The general structure of an update query in SQL looks like the following:</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb3-1"><a href="#cb3-1"></a></span>
<span id="cb3-2"><a href="#cb3-2"></a>UPDATE table</span>
<span id="cb3-3"><a href="#cb3-3"></a>SET column1 = value1,</span>
<span id="cb3-4"><a href="#cb3-4"></a> column2 = value2 ,...</span>
<span id="cb3-5"><a href="#cb3-5"></a>WHERE</span>
<span id="cb3-6"><a href="#cb3-6"></a> condition;</span>
<span id="cb3-7"><a href="#cb3-7"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong><em>Delete</em></strong></p>
<p>Deleting a record in the table - The general structure of a delete query in SQL looks like following:</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb4-1"><a href="#cb4-1"></a></span>
<span id="cb4-2"><a href="#cb4-2"></a>DELETE FROM table</span>
<span id="cb4-3"><a href="#cb4-3"></a>WHERE condition;</span>
<span id="cb4-4"><a href="#cb4-4"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong><em>Sample Create</em></strong></p>
<div class="sourceCode" id="cb5"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb5-1"><a href="#cb5-1"></a></span>
<span id="cb5-2"><a href="#cb5-2"></a>CREATE TABLE cars (</span>
<span id="cb5-3"><a href="#cb5-3"></a></span>
<span id="cb5-4"><a href="#cb5-4"></a>"make" varchar(255),</span>
<span id="cb5-5"><a href="#cb5-5"></a>"model" varchar(255),</span>
<span id="cb5-6"><a href="#cb5-6"></a>"year" numeric(4,0),</span>
<span id="cb5-7"><a href="#cb5-7"></a>"propulsion_type" varchar(255),</span>
<span id="cb5-8"><a href="#cb5-8"></a>"time_to_60_mph_s" numeric(4,2),</span>
<span id="cb5-9"><a href="#cb5-9"></a>"limited_production_count" numeric(10,0)</span>
<span id="cb5-10"><a href="#cb5-10"></a></span>
<span id="cb5-11"><a href="#cb5-11"></a></span>
<span id="cb5-12"><a href="#cb5-12"></a>);</span>
<span id="cb5-13"><a href="#cb5-13"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="first-sql-syntax" class="slide level2">
<h2>First SQL syntax</h2>
<p><strong>SELECT</strong></p>
<p>Pick all the columns</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb6-1"><a href="#cb6-1"></a></span>
<span id="cb6-2"><a href="#cb6-2"></a>SELECT *</span>
<span id="cb6-3"><a href="#cb6-3"></a> FROM cars</span>
<span id="cb6-4"><a href="#cb6-4"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Select one single column</p>
<div class="sourceCode" id="cb7"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb7-1"><a href="#cb7-1"></a></span>
<span id="cb7-2"><a href="#cb7-2"></a>SELECT model</span>
<span id="cb7-3"><a href="#cb7-3"></a> FROM cars</span>
<span id="cb7-4"><a href="#cb7-4"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>select multiple columns</p>
<div class="sourceCode" id="cb8"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb8-1"><a href="#cb8-1"></a></span>
<span id="cb8-2"><a href="#cb8-2"></a>SELECT make, model</span>
<span id="cb8-3"><a href="#cb8-3"></a> FROM cars</span>
<span id="cb8-4"><a href="#cb8-4"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Override column names with SELECT col AS new_name</p>
<div class="sourceCode" id="cb9"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb9-1"><a href="#cb9-1"></a></span>
<span id="cb9-2"><a href="#cb9-2"></a>SELECT make, model, propulsion_type AS engine_type</span>
<span id="cb9-3"><a href="#cb9-3"></a> FROM cars</span>
<span id="cb9-4"><a href="#cb9-4"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Arrange the rows in ascending order of values in a column with ORDER BY col</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb10-1"><a href="#cb10-1"></a></span>
<span id="cb10-2"><a href="#cb10-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb10-3"><a href="#cb10-3"></a> FROM cars</span>
<span id="cb10-4"><a href="#cb10-4"></a> ORDER BY time_to_60_mph_s</span>
<span id="cb10-5"><a href="#cb10-5"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Limit the number of rows returned with LIMIT n</p>
<div class="sourceCode" id="cb11"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb11-1"><a href="#cb11-1"></a></span>
<span id="cb11-2"><a href="#cb11-2"></a>SELECT * </span>
<span id="cb11-3"><a href="#cb11-3"></a> FROM cars</span>
<span id="cb11-4"><a href="#cb11-4"></a>LIMIT 2</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get unique values with SELECT DISTINCT</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb12-1"><a href="#cb12-1"></a></span>
<span id="cb12-2"><a href="#cb12-2"></a>SELECT DISTINCT propulsion_type</span>
<span id="cb12-3"><a href="#cb12-3"></a> FROM cars</span>
<span id="cb12-4"><a href="#cb12-4"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="filtering-data" class="slide level2">
<h2>Filtering Data</h2>
<p>Get rows where a number is greater than a value with WHERE col > n</p>
<div class="sourceCode" id="cb13"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb13-1"><a href="#cb13-1"></a></span>
<span id="cb13-2"><a href="#cb13-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb13-3"><a href="#cb13-3"></a> FROM cars</span>
<span id="cb13-4"><a href="#cb13-4"></a> WHERE time_to_60_mph_s > 2.1</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is greater than or equal to a value with WHERE col >= n</p>
<div class="sourceCode" id="cb14"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb14-1"><a href="#cb14-1"></a></span>
<span id="cb14-2"><a href="#cb14-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb14-3"><a href="#cb14-3"></a> FROM cars</span>
<span id="cb14-4"><a href="#cb14-4"></a> WHERE time_to_60_mph_s >= 2.1</span>
<span id="cb14-5"><a href="#cb14-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is less than a value with WHERE col < n</p>
<div class="sourceCode" id="cb15"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb15-1"><a href="#cb15-1"></a></span>
<span id="cb15-2"><a href="#cb15-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb15-3"><a href="#cb15-3"></a> FROM cars</span>
<span id="cb15-4"><a href="#cb15-4"></a> WHERE time_to_60_mph_s < 2.1</span>
<span id="cb15-5"><a href="#cb15-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is less than or equal to a value with WHERE col <= n</p>
<div class="sourceCode" id="cb16"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb16-1"><a href="#cb16-1"></a></span>
<span id="cb16-2"><a href="#cb16-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb16-3"><a href="#cb16-3"></a> FROM cars</span>
<span id="cb16-4"><a href="#cb16-4"></a> WHERE time_to_60_mph_s <= 2.1</span>
<span id="cb16-5"><a href="#cb16-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is equal to a value with WHERE col = n</p>
<div class="sourceCode" id="cb17"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb17-1"><a href="#cb17-1"></a></span>
<span id="cb17-2"><a href="#cb17-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb17-3"><a href="#cb17-3"></a> FROM cars</span>
<span id="cb17-4"><a href="#cb17-4"></a> WHERE time_to_60_mph_s = 2.1</span>
<span id="cb17-5"><a href="#cb17-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is not equal to a value with WHERE col <> n or WHERE col != n</p>
<div class="sourceCode" id="cb18"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb18-1"><a href="#cb18-1"></a></span>
<span id="cb18-2"><a href="#cb18-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb18-3"><a href="#cb18-3"></a> FROM cars</span>
<span id="cb18-4"><a href="#cb18-4"></a> WHERE time_to_60_mph_s <> 2.1</span>
<span id="cb18-5"><a href="#cb18-5"></a></span>
<span id="cb18-6"><a href="#cb18-6"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where a number is between two values (inclusive) with WHERE col BETWEEN m AND n</p>
<div class="sourceCode" id="cb19"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb19-1"><a href="#cb19-1"></a></span>
<span id="cb19-2"><a href="#cb19-2"></a>SELECT make, model, time_to_60_mph_s</span>
<span id="cb19-3"><a href="#cb19-3"></a> FROM cars</span>
<span id="cb19-4"><a href="#cb19-4"></a> WHERE time_to_60_mph_s BETWEEN 1.9 AND 2.1</span>
<span id="cb19-5"><a href="#cb19-5"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section class="slide level2">
<p><strong>Filtering on text columns</strong></p>
<p>Get rows where text is equal to a value with WHERE col = ‘x’</p>
<div class="sourceCode" id="cb20"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb20-1"><a href="#cb20-1"></a></span>
<span id="cb20-2"><a href="#cb20-2"></a>SELECT make, model, propulsion_type</span>
<span id="cb20-3"><a href="#cb20-3"></a> FROM cars</span>
<span id="cb20-4"><a href="#cb20-4"></a> WHERE propulsion_type = 'Hybrid'</span>
<span id="cb20-5"><a href="#cb20-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where text is one of several values with WHERE col IN (‘x’, ‘y’)</p>
<div class="sourceCode" id="cb21"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb21-1"><a href="#cb21-1"></a></span>
<span id="cb21-2"><a href="#cb21-2"></a>SELECT make, model, propulsion_type</span>
<span id="cb21-3"><a href="#cb21-3"></a> FROM cars</span>
<span id="cb21-4"><a href="#cb21-4"></a> WHERE propulsion_type IN ('Electric', 'Hybrid')</span>
<span id="cb21-5"><a href="#cb21-5"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where text contains specific letters with WHERE col LIKE ‘%abc%’ (% represents any characters)</p>
<div class="sourceCode" id="cb22"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb22-1"><a href="#cb22-1"></a></span>
<span id="cb22-2"><a href="#cb22-2"></a>SELECT make, model, propulsion_type</span>
<span id="cb22-3"><a href="#cb22-3"></a> FROM cars</span>
<span id="cb22-4"><a href="#cb22-4"></a> WHERE propulsion_type LIKE '%ic%'</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>For case insensitive matching, use WHERE col ILIKE ‘%abc%’</p>
<div class="sourceCode" id="cb23"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb23-1"><a href="#cb23-1"></a></span>
<span id="cb23-2"><a href="#cb23-2"></a>SELECT make, model, propulsion_type</span>
<span id="cb23-3"><a href="#cb23-3"></a> FROM cars</span>
<span id="cb23-4"><a href="#cb23-4"></a> WHERE propulsion_type ILIKE '%ic%'</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section class="slide level2">
<p><strong>Filtering on multiple columns</strong></p>
<p>Get the rows where one condition and another condition holds with WHERE condn1 AND condn2</p>
<div class="sourceCode" id="cb24"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb24-1"><a href="#cb24-1"></a></span>
<span id="cb24-2"><a href="#cb24-2"></a>SELECT make, model, propulsion_type, model_year</span>
<span id="cb24-3"><a href="#cb24-3"></a> FROM cars</span>
<span id="cb24-4"><a href="#cb24-4"></a> WHERE propulsion_type = 'Hybrid'</span>
<span id="cb24-5"><a href="#cb24-5"></a> AND model_year < 2020</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the rows where one condition or another condition holds with WHERE condn1 OR condn2</p>
<div class="sourceCode" id="cb25"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb25-1"><a href="#cb25-1"></a></span>
<span id="cb25-2"><a href="#cb25-2"></a>SELECT make, model, propulsion_type, model_year</span>
<span id="cb25-3"><a href="#cb25-3"></a> FROM cars</span>
<span id="cb25-4"><a href="#cb25-4"></a> WHERE propulsion_type = 'Hybrid'</span>
<span id="cb25-5"><a href="#cb25-5"></a> OR model_year < 2020</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>Filtering on missing data</strong></p>
<p>Get rows where values are missing with WHERE col IS NULL</p>
<div class="sourceCode" id="cb26"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb26-1"><a href="#cb26-1"></a></span>
<span id="cb26-2"><a href="#cb26-2"></a>SELECT make, model, limited_production_count</span>
<span id="cb26-3"><a href="#cb26-3"></a> FROM cars</span>
<span id="cb26-4"><a href="#cb26-4"></a> WHERE limited_production_count IS NULL</span>
<span id="cb26-5"><a href="#cb26-5"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where values are not missing with WHERE col IS NOT NULL</p>
<div class="sourceCode" id="cb27"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb27-1"><a href="#cb27-1"></a></span>
<span id="cb27-2"><a href="#cb27-2"></a>SELECT make, model, limited_production_count</span>
<span id="cb27-3"><a href="#cb27-3"></a> FROM cars</span>
<span id="cb27-4"><a href="#cb27-4"></a> WHERE limited_production_count IS NOT NULL</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="aggregating-data" class="slide level2">
<h2>Aggregating Data</h2>
<p><strong>Simple aggregations</strong></p>
<p>Get the total number of rows SELECT COUNT(*)</p>
<div class="sourceCode" id="cb28"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb28-1"><a href="#cb28-1"></a></span>
<span id="cb28-2"><a href="#cb28-2"></a>SELECT COUNT(*)</span>
<span id="cb28-3"><a href="#cb28-3"></a> FROM cars</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the total value of a column with SELECT SUM(col)</p>
<div class="sourceCode" id="cb29"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb29-1"><a href="#cb29-1"></a></span>
<span id="cb29-2"><a href="#cb29-2"></a>SELECT SUM(limited_production_count)</span>
<span id="cb29-3"><a href="#cb29-3"></a> FROM cars</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the mean value of a column with SELECT AVG(col)</p>
<div class="sourceCode" id="cb30"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb30-1"><a href="#cb30-1"></a></span>
<span id="cb30-2"><a href="#cb30-2"></a>SELECT AVG(time_to_60_mph_s)</span>
<span id="cb30-3"><a href="#cb30-3"></a> FROM cars</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the minimum value of a column with SELECT MIN(col)</p>
<div class="sourceCode" id="cb31"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb31-1"><a href="#cb31-1"></a></span>
<span id="cb31-2"><a href="#cb31-2"></a>SELECT MIN(time_to_60_mph_s)</span>
<span id="cb31-3"><a href="#cb31-3"></a> FROM cars</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the maximum value of a column with SELECT MAX(col)</p>
<div class="sourceCode" id="cb32"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb32-1"><a href="#cb32-1"></a></span>
<span id="cb32-2"><a href="#cb32-2"></a>SELECT MAX(time_to_60_mph_s)</span>
<span id="cb32-3"><a href="#cb32-3"></a> FROM cars</span>
<span id="cb32-4"><a href="#cb32-4"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="grouping-filtering-and-sorting" class="slide level2">
<h2>Grouping, filtering, and sorting</h2>
<p>Get summaries grouped by values with GROUP BY col</p>
<div class="sourceCode" id="cb33"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb33-1"><a href="#cb33-1"></a></span>
<span id="cb33-2"><a href="#cb33-2"></a>SELECT propulsion_type, COUNT(*)</span>
<span id="cb33-3"><a href="#cb33-3"></a> FROM cars</span>
<span id="cb33-4"><a href="#cb33-4"></a> GROUP BY propulsion_type</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get summaries grouped by values, in order of summaries with GROUP BY col ORDER BY smmry</p>
<div class="sourceCode" id="cb34"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb34-1"><a href="#cb34-1"></a></span>
<span id="cb34-2"><a href="#cb34-2"></a>SELECT propulsion_type, AVG(time_to_60_mph_s) AS mean_time_to_60_mph_s</span>
<span id="cb34-3"><a href="#cb34-3"></a> FROM cars</span>
<span id="cb34-4"><a href="#cb34-4"></a> GROUP BY propulsion_type</span>
<span id="cb34-5"><a href="#cb34-5"></a> ORDER BY mean_time_to_60_mph_s</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get rows where values in a group meet a criterion with GROUP BY col HAVING condn</p>
<div class="sourceCode" id="cb35"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb35-1"><a href="#cb35-1"></a></span>
<span id="cb35-2"><a href="#cb35-2"></a>SELECT propulsion_type, AVG(time_to_60_mph_s) AS mean_time_to_60_mph_s</span>
<span id="cb35-3"><a href="#cb35-3"></a> FROM cars</span>
<span id="cb35-4"><a href="#cb35-4"></a> GROUP BY propulsion_type</span>
<span id="cb35-5"><a href="#cb35-5"></a> HAVING mean_time_to_60_mph_s > 2</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Filter before and after grouping with WHERE condn_before GROUP BY col HAVING condn_after</p>
<div class="sourceCode" id="cb36"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb36-1"><a href="#cb36-1"></a></span>
<span id="cb36-2"><a href="#cb36-2"></a>SELECT propulsion_type, AVG(time_to_60_mph_s) AS mean_time_to_60_mph_s</span>
<span id="cb36-3"><a href="#cb36-3"></a> FROM cars</span>
<span id="cb36-4"><a href="#cb36-4"></a>WHERE limited_production_count IS NOT NULL</span>
<span id="cb36-5"><a href="#cb36-5"></a> GROUP BY propulsion_type</span>
<span id="cb36-6"><a href="#cb36-6"></a> HAVING mean_time_to_60_mph_s > 2</span>
<span id="cb36-7"><a href="#cb36-7"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="postgresql-specific-syntax" class="slide level2">
<h2>PostgreSQL-Specific Syntax</h2>
<p>Not all code works in every dialect of SQL. The following examples work in PostgreSQL, but are not guaranteed to work in other dialects.</p>
<p>Limit the number of rows returned, offset from the top with LIMIT m OFFSET n</p>
<div class="sourceCode" id="cb37"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb37-1"><a href="#cb37-1"></a></span>
<span id="cb37-2"><a href="#cb37-2"></a>SELECT *</span>
<span id="cb37-3"><a href="#cb37-3"></a> FROM cars</span>
<span id="cb37-4"><a href="#cb37-4"></a>LIMIT 2 OFFSET 3</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>PostgreSQL allows text concatenation with the || operator</p>
<div class="sourceCode" id="cb38"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb38-1"><a href="#cb38-1"></a></span>
<span id="cb38-2"><a href="#cb38-2"></a>SELECT make || ' ' || model AS make_and_model</span>
<span id="cb38-3"><a href="#cb38-3"></a> FROM cars</span>
<span id="cb38-4"><a href="#cb38-4"></a> </span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>Get the current date with CURRENT_DATE and the current datetime with NOW() or CURRENT_TIME</p>
<div class="sourceCode" id="cb39"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb39-1"><a href="#cb39-1"></a></span>
<span id="cb39-2"><a href="#cb39-2"></a>SELECT NOW(), CURRENT_DATE, CURRENT_TIME</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p>List available tables by selecting from pg_catalog.pg_tables</p>
<div class="sourceCode" id="cb40"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb40-1"><a href="#cb40-1"></a></span>
<span id="cb40-2"><a href="#cb40-2"></a>SELECT * FROM pg_catalog.pg_tables</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="sql-eda-samples" class="slide level2">
<h2>SQL EDA Samples</h2>
<div class="sourceCode" id="cb41"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb41-1"><a href="#cb41-1"></a>-- CONVERT Syntax:</span>
<span id="cb41-2"><a href="#cb41-2"></a>CONVERT ( data_type [ ( length ) ] , expression [ , style ] )</span>
<span id="cb41-3"><a href="#cb41-3"></a>-- Returns expression based on data_type</span>
<span id="cb41-4"><a href="#cb41-4"></a></span>
<span id="cb41-5"><a href="#cb41-5"></a>-- DATEPART Syntax</span>
<span id="cb41-6"><a href="#cb41-6"></a>DATEPART ( datepart , date )</span>
<span id="cb41-7"><a href="#cb41-7"></a>-- Returns int</span>
<span id="cb41-8"><a href="#cb41-8"></a></span>
<span id="cb41-9"><a href="#cb41-9"></a>-- DATENAME Syntax</span>
<span id="cb41-10"><a href="#cb41-10"></a>DATENAME ( datepart , date )</span>
<span id="cb41-11"><a href="#cb41-11"></a>-- Returns nvarchar</span>
<span id="cb41-12"><a href="#cb41-12"></a></span>
<span id="cb41-13"><a href="#cb41-13"></a>-- DATEDIFF Syntax</span>
<span id="cb41-14"><a href="#cb41-14"></a>DATEDIFF ( datepart , startdate , enddate )</span>
<span id="cb41-15"><a href="#cb41-15"></a>-- Returns int; can't use datepart weekday value</span>
<span id="cb41-16"><a href="#cb41-16"></a>-- datepart values = year, quarter, month, dayofyear, day, week, weekday, hour,</span>
<span id="cb41-17"><a href="#cb41-17"></a>-- minute, second, microsecond, nanosecond</span>
<span id="cb41-18"><a href="#cb41-18"></a></span>
<span id="cb41-19"><a href="#cb41-19"></a>---Sample---</span>
<span id="cb41-20"><a href="#cb41-20"></a>-- CONVERT</span>
<span id="cb41-21"><a href="#cb41-21"></a>SELECT</span>
<span id="cb41-22"><a href="#cb41-22"></a>TOP 1 PickupDate,</span>
<span id="cb41-23"><a href="#cb41-23"></a>CONVERT (DATE, PickupDate) AS DateOnly</span>
<span id="cb41-24"><a href="#cb41-24"></a>FROM YellowTripData</span>
<span id="cb41-25"><a href="#cb41-25"></a></span>
<span id="cb41-26"><a href="#cb41-26"></a></span>
<span id="cb41-27"><a href="#cb41-27"></a>-- CAST syntax</span>
<span id="cb41-28"><a href="#cb41-28"></a>CAST ( expression AS data_type [ ( length ) ] )</span>
<span id="cb41-29"><a href="#cb41-29"></a>-- Returns expression based on data_type</span>
<span id="cb41-30"><a href="#cb41-30"></a></span>
<span id="cb41-31"><a href="#cb41-31"></a></span>
<span id="cb41-32"><a href="#cb41-32"></a>SELECT DISTINCT</span>
<span id="cb41-33"><a href="#cb41-33"></a>CAST(PickupDate as date),</span>
<span id="cb41-34"><a href="#cb41-34"></a>CAST(DropOffDate as date)</span>
<span id="cb41-35"><a href="#cb41-35"></a>FROM YellowTripData;</span>
<span id="cb41-36"><a href="#cb41-36"></a></span>
<span id="cb41-37"><a href="#cb41-37"></a></span>
<span id="cb41-38"><a href="#cb41-38"></a>SELECT GETDATE()</span>
<span id="cb41-39"><a href="#cb41-39"></a></span>
<span id="cb41-40"><a href="#cb41-40"></a></span>
<span id="cb41-41"><a href="#cb41-41"></a>-- Yesterday</span>
<span id="cb41-42"><a href="#cb41-42"></a>SELECT DATEADD(d, -1, GETDATE())</span>
<span id="cb41-43"><a href="#cb41-43"></a></span>
<span id="cb41-44"><a href="#cb41-44"></a></span>
<span id="cb41-45"><a href="#cb41-45"></a>-- Yesterday's Taxi Passenger Count</span>
<span id="cb41-46"><a href="#cb41-46"></a>SELECT SUM(PassengerCount)</span>
<span id="cb41-47"><a href="#cb41-47"></a>FROM YellowTripData</span>
<span id="cb41-48"><a href="#cb41-48"></a>WHERE CAST(PickupDate as date) = DATEADD(d, -1, GETDATE())</span>
<span id="cb41-49"><a href="#cb41-49"></a></span>
<span id="cb41-50"><a href="#cb41-50"></a></span>
<span id="cb41-51"><a href="#cb41-51"></a>SELECT DATEDIFF(day, '2/27/2019', '2/28/2019')</span>
<span id="cb41-52"><a href="#cb41-52"></a>SELECT DATEDIFF(year, '12/31/2017', '1/1/2019')</span>
<span id="cb41-53"><a href="#cb41-53"></a></span>
<span id="cb41-54"><a href="#cb41-54"></a>SELECT DATEADD(DD, 30, '2020-06-21')</span>
<span id="cb41-55"><a href="#cb41-55"></a></span>
<span id="cb41-56"><a href="#cb41-56"></a>SELECT DATEDIFF(DD, '2020-05-22', '2020-06-21') AS Difference1,</span>
<span id="cb41-57"><a href="#cb41-57"></a>DATEDIFF(DD, '2020-07-21', '2020-06-21') AS Difference2</span>
<span id="cb41-58"><a href="#cb41-58"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="sql-eda-continuation" class="slide level2">
<h2>SQL EDA CONTINUATION</h2>
<div class="sourceCode" id="cb42"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb42-1"><a href="#cb42-1"></a></span>
<span id="cb42-2"><a href="#cb42-2"></a>--IF ELSE Equivalent</span>
<span id="cb42-3"><a href="#cb42-3"></a></span>
<span id="cb42-4"><a href="#cb42-4"></a>SELECT name, continent, indep_year,</span>
<span id="cb42-5"><a href="#cb42-5"></a> CASE WHEN indep_year < 1900 THEN 'before 1900'</span>
<span id="cb42-6"><a href="#cb42-6"></a> WHEN indep_year <= 1930 THEN 'between 1900 and 1930'</span>
<span id="cb42-7"><a href="#cb42-7"></a> ELSE 'after 1930' END</span>
<span id="cb42-8"><a href="#cb42-8"></a> AS indep_year_group</span>
<span id="cb42-9"><a href="#cb42-9"></a>FROM states</span>
<span id="cb42-10"><a href="#cb42-10"></a>ORDER BY indep_year_group;</span>
<span id="cb42-11"><a href="#cb42-11"></a></span>
<span id="cb42-12"><a href="#cb42-12"></a></span>
<span id="cb42-13"><a href="#cb42-13"></a></span>
<span id="cb42-14"><a href="#cb42-14"></a>SELECT DurationSeconds,</span>
<span id="cb42-15"><a href="#cb42-15"></a> ROUND(DurationSeconds, 0) AS RoundToZero,</span>
<span id="cb42-16"><a href="#cb42-16"></a> ROUND(DurationSeconds, 1) AS RoundToOne</span>
<span id="cb42-17"><a href="#cb42-17"></a>FROM Incidents</span>
<span id="cb42-18"><a href="#cb42-18"></a></span>
<span id="cb42-19"><a href="#cb42-19"></a></span>
<span id="cb42-20"><a href="#cb42-20"></a>SELECT DurationSeconds,</span>
<span id="cb42-21"><a href="#cb42-21"></a>ROUND(DurationSeconds, -1) AS RoundToTen,</span>
<span id="cb42-22"><a href="#cb42-22"></a>ROUND(DurationSeconds, -2) AS RoundToHundred</span>
<span id="cb42-23"><a href="#cb42-23"></a>FROM Incidents</span>
<span id="cb42-24"><a href="#cb42-24"></a></span>
<span id="cb42-25"><a href="#cb42-25"></a></span>
<span id="cb42-26"><a href="#cb42-26"></a>SELECT Profit,</span>
<span id="cb42-27"><a href="#cb42-27"></a>ROUND(DurationSeconds, 0) AS RoundingtoWhole,</span>
<span id="cb42-28"><a href="#cb42-28"></a>ROUND(DurationSeconds, 0, 1) AS Truncating</span>
<span id="cb42-29"><a href="#cb42-29"></a>FROM Incidents</span>
<span id="cb42-30"><a href="#cb42-30"></a></span>
<span id="cb42-31"><a href="#cb42-31"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="joins" class="slide level2">
<h2>JOINS</h2>
<div class="sourceCode" id="cb43"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb43-1"><a href="#cb43-1"></a></span>
<span id="cb43-2"><a href="#cb43-2"></a></span>
<span id="cb43-3"><a href="#cb43-3"></a>SELECT p1.country, prime_minister, president</span>
<span id="cb43-4"><a href="#cb43-4"></a>FROM prime_ministers AS p1</span>
<span id="cb43-5"><a href="#cb43-5"></a>LEFT JOIN presidents AS p2</span>
<span id="cb43-6"><a href="#cb43-6"></a>ON p1.country = p2.country;</span>
<span id="cb43-7"><a href="#cb43-7"></a></span>
<span id="cb43-8"><a href="#cb43-8"></a></span>
<span id="cb43-9"><a href="#cb43-9"></a>SELECT p1.country AS pm_co, p2.country AS pres_co,</span>
<span id="cb43-10"><a href="#cb43-10"></a> prime_minister, president</span>
<span id="cb43-11"><a href="#cb43-11"></a>FROM prime_ministers AS p1</span>
<span id="cb43-12"><a href="#cb43-12"></a>FULL JOIN presidents AS p2</span>
<span id="cb43-13"><a href="#cb43-13"></a>ON p1.country = p2.country;</span>
<span id="cb43-14"><a href="#cb43-14"></a></span>
<span id="cb43-15"><a href="#cb43-15"></a></span>
<span id="cb43-16"><a href="#cb43-16"></a>SELECT prime_minister, president</span>
<span id="cb43-17"><a href="#cb43-17"></a>FROM prime_ministers AS p1</span>
<span id="cb43-18"><a href="#cb43-18"></a>CROSS JOIN presidents AS p2</span>
<span id="cb43-19"><a href="#cb43-19"></a>WHERE p1.continent IN ('North America', 'Oceania');</span>
<span id="cb43-20"><a href="#cb43-20"></a></span>
<span id="cb43-21"><a href="#cb43-21"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="unions" class="slide level2">
<h2>UNIONS</h2>
<div class="sourceCode" id="cb44"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb44-1"><a href="#cb44-1"></a></span>
<span id="cb44-2"><a href="#cb44-2"></a></span>
<span id="cb44-3"><a href="#cb44-3"></a>SELECT prime_minister AS leader, country</span>
<span id="cb44-4"><a href="#cb44-4"></a>FROM prime_ministers</span>
<span id="cb44-5"><a href="#cb44-5"></a>UNION</span>
<span id="cb44-6"><a href="#cb44-6"></a>SELECT monarch, country</span>
<span id="cb44-7"><a href="#cb44-7"></a>FROM monarchs</span>
<span id="cb44-8"><a href="#cb44-8"></a>ORDER BY country;</span>
<span id="cb44-9"><a href="#cb44-9"></a></span>
<span id="cb44-10"><a href="#cb44-10"></a></span>
<span id="cb44-11"><a href="#cb44-11"></a>SELECT prime_minister AS leader, country</span>
<span id="cb44-12"><a href="#cb44-12"></a>FROM prime_ministers</span>
<span id="cb44-13"><a href="#cb44-13"></a>UNION ALL</span>
<span id="cb44-14"><a href="#cb44-14"></a>SELECT monarch, country</span>
<span id="cb44-15"><a href="#cb44-15"></a>FROM monarchs</span>
<span id="cb44-16"><a href="#cb44-16"></a>ORDER BY country</span>
<span id="cb44-17"><a href="#cb44-17"></a>LIMIT 10;</span>
<span id="cb44-18"><a href="#cb44-18"></a></span>
<span id="cb44-19"><a href="#cb44-19"></a></span>
<span id="cb44-20"><a href="#cb44-20"></a>SELECT country</span>
<span id="cb44-21"><a href="#cb44-21"></a>FROM prime_ministers</span>
<span id="cb44-22"><a href="#cb44-22"></a>INTERSECT</span>
<span id="cb44-23"><a href="#cb44-23"></a>SELECT country</span>
<span id="cb44-24"><a href="#cb44-24"></a>FROM presidents;</span>
<span id="cb44-25"><a href="#cb44-25"></a></span>
<span id="cb44-26"><a href="#cb44-26"></a></span>
<span id="cb44-27"><a href="#cb44-27"></a>--Monarchs that aren't prime ministers</span>
<span id="cb44-28"><a href="#cb44-28"></a></span>
<span id="cb44-29"><a href="#cb44-29"></a>SELECT monarch, country</span>
<span id="cb44-30"><a href="#cb44-30"></a>FROM monarchs</span>
<span id="cb44-31"><a href="#cb44-31"></a>EXCEPT</span>
<span id="cb44-32"><a href="#cb44-32"></a>SELECT prime_minister, country</span>
<span id="cb44-33"><a href="#cb44-33"></a>FROM prime_ministers;</span>
<span id="cb44-34"><a href="#cb44-34"></a></span>
<span id="cb44-35"><a href="#cb44-35"></a></span>
<span id="cb44-36"><a href="#cb44-36"></a>-- Sub Queries</span>
<span id="cb44-37"><a href="#cb44-37"></a></span>
<span id="cb44-38"><a href="#cb44-38"></a>-- Semi JOin</span>
<span id="cb44-39"><a href="#cb44-39"></a></span>
<span id="cb44-40"><a href="#cb44-40"></a>SELECT president, country, continent</span>
<span id="cb44-41"><a href="#cb44-41"></a>FROM presidents</span>
<span id="cb44-42"><a href="#cb44-42"></a>WHERE country IN</span>
<span id="cb44-43"><a href="#cb44-43"></a>(SELECT name</span>
<span id="cb44-44"><a href="#cb44-44"></a> FROM states</span>
<span id="cb44-45"><a href="#cb44-45"></a> WHERE indep_year < 1800);</span>
<span id="cb44-46"><a href="#cb44-46"></a></span>
<span id="cb44-47"><a href="#cb44-47"></a>-- Anti Join</span>
<span id="cb44-48"><a href="#cb44-48"></a></span>
<span id="cb44-49"><a href="#cb44-49"></a>SELECT president, country, continent</span>
<span id="cb44-50"><a href="#cb44-50"></a>FROM presidents</span>
<span id="cb44-51"><a href="#cb44-51"></a>WHERE continent LIKE '%America'</span>
<span id="cb44-52"><a href="#cb44-52"></a>AND country NOT IN</span>
<span id="cb44-53"><a href="#cb44-53"></a>(SELECT name</span>
<span id="cb44-54"><a href="#cb44-54"></a> FROM states</span>
<span id="cb44-55"><a href="#cb44-55"></a> WHERE indep_year < 1800);</span>
<span id="cb44-56"><a href="#cb44-56"></a> </span>
<span id="cb44-57"><a href="#cb44-57"></a> </span>
<span id="cb44-58"><a href="#cb44-58"></a>SELECT DISTINCT monarchs.continent, subquery.max_perc</span>
<span id="cb44-59"><a href="#cb44-59"></a>FROM monarchs,</span>
<span id="cb44-60"><a href="#cb44-60"></a>(SELECT continent, MAX(women_parli_perc) AS max_perc</span>
<span id="cb44-61"><a href="#cb44-61"></a> FROM states</span>
<span id="cb44-62"><a href="#cb44-62"></a> GROUP BY continent) AS subquery</span>
<span id="cb44-63"><a href="#cb44-63"></a>WHERE monarchs.continent = subquery.continent</span>
<span id="cb44-64"><a href="#cb44-64"></a>ORDER BY continent;</span>
<span id="cb44-65"><a href="#cb44-65"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="missing" class="slide level2">
<h2>MISSING</h2>
<div class="sourceCode" id="cb45"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb45-1"><a href="#cb45-1"></a></span>
<span id="cb45-2"><a href="#cb45-2"></a>-- Returning No NULL</span>
<span id="cb45-3"><a href="#cb45-3"></a></span>
<span id="cb45-4"><a href="#cb45-4"></a>SELECT Country, InternetUse, Year</span>
<span id="cb45-5"><a href="#cb45-5"></a>FROM EconomicIndicators</span>
<span id="cb45-6"><a href="#cb45-6"></a>WHERE InternetUse IS NOT NULL</span>
<span id="cb45-7"><a href="#cb45-7"></a></span>
<span id="cb45-8"><a href="#cb45-8"></a>-- Detecting NULLs</span>
<span id="cb45-9"><a href="#cb45-9"></a></span>
<span id="cb45-10"><a href="#cb45-10"></a>SELECT Country, InternetUse, Year</span>
<span id="cb45-11"><a href="#cb45-11"></a>FROM EconomicIndicators</span>
<span id="cb45-12"><a href="#cb45-12"></a>WHERE InternetUse IS NULL</span>
<span id="cb45-13"><a href="#cb45-13"></a></span>
<span id="cb45-14"><a href="#cb45-14"></a></span>
<span id="cb45-15"><a href="#cb45-15"></a>-- Substituting missing data with a specific using ISNULL</span>
<span id="cb45-16"><a href="#cb45-16"></a></span>
<span id="cb45-17"><a href="#cb45-17"></a>SELECT GDP, Country,</span>
<span id="cb45-18"><a href="#cb45-18"></a>ISNULL(Country, 'Unknown') AS NewCountry</span>
<span id="cb45-19"><a href="#cb45-19"></a>FROM EconomicIndicators</span>
<span id="cb45-20"><a href="#cb45-20"></a></span>
<span id="cb45-21"><a href="#cb45-21"></a></span>
<span id="cb45-22"><a href="#cb45-22"></a>/*Substituting values from one column for another with ISNULL*/</span>
<span id="cb45-23"><a href="#cb45-23"></a>SELECT TradeGDPPercent, ImportGoodPercent,</span>
<span id="cb45-24"><a href="#cb45-24"></a>ISNULL(TradeGDPPercent, ImportGoodPercent) AS NewPercent</span>
<span id="cb45-25"><a href="#cb45-25"></a>FROM EconomicIndicators</span>
<span id="cb45-26"><a href="#cb45-26"></a></span>
<span id="cb45-27"><a href="#cb45-27"></a></span>
<span id="cb45-28"><a href="#cb45-28"></a>-- We are binning the data here into discrete groups</span>
<span id="cb45-29"><a href="#cb45-29"></a>SELECT Country, LifeExp,</span>
<span id="cb45-30"><a href="#cb45-30"></a>CASE WHEN LifeExp < 30 THEN 1</span>
<span id="cb45-31"><a href="#cb45-31"></a> WHEN LifeExp > 29 AND LifeExp < 40 THEN 2</span>
<span id="cb45-32"><a href="#cb45-32"></a> WHEN LifeExp > 39 AND LifeExp < 50 THEN 3</span>
<span id="cb45-33"><a href="#cb45-33"></a> WHEN LifeExp > 49 AND LifeExp < 60 THEN 4</span>
<span id="cb45-34"><a href="#cb45-34"></a> ELSE 5</span>
<span id="cb45-35"><a href="#cb45-35"></a> END AS LifeExpGroup</span>
<span id="cb45-36"><a href="#cb45-36"></a>FROM EconomicIndicators</span>
<span id="cb45-37"><a href="#cb45-37"></a>WHERE Year = 2007</span>
<span id="cb45-38"><a href="#cb45-38"></a></span>
<span id="cb45-39"><a href="#cb45-39"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="derived-tables-cte" class="slide level2">
<h2>DERIVED TABLES & CTE</h2>
<p>What are Derived tables? - Query which is treated like a temporary table - Always contained within the main query - They are specified in the <code>FROM</code> clause - Can contain intermediate calculations to be used the main query or different joins than in the main query</p>
<div class="sourceCode" id="cb46"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb46-1"><a href="#cb46-1"></a>SELECT a.* FROM Kidney a</span>
<span id="cb46-2"><a href="#cb46-2"></a>-- This derived table computes the Average age joined to the actual table</span>
<span id="cb46-3"><a href="#cb46-3"></a>JOIN (SELECT AVG(Age) AS AverageAge</span>
<span id="cb46-4"><a href="#cb46-4"></a>FROM Kidney) b</span>
<span id="cb46-5"><a href="#cb46-5"></a>ON a.Age = b.AverageAge</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>Common Table Expressions (CTE)</strong></p>
<div class="sourceCode" id="cb47"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb47-1"><a href="#cb47-1"></a></span>
<span id="cb47-2"><a href="#cb47-2"></a>-- CTE definitions start with the keyword WITH</span>
<span id="cb47-3"><a href="#cb47-3"></a>-- Followed by the CTE names and the columns it contains</span>
<span id="cb47-4"><a href="#cb47-4"></a>WITH CTEName (Col1, Col2)</span>
<span id="cb47-5"><a href="#cb47-5"></a>AS</span>
<span id="cb47-6"><a href="#cb47-6"></a>-- Define the CTE query</span>
<span id="cb47-7"><a href="#cb47-7"></a>(</span>
<span id="cb47-8"><a href="#cb47-8"></a>-- The two columns from the definition above</span>
<span id="cb47-9"><a href="#cb47-9"></a> SELECT Col1, Col2</span>
<span id="cb47-10"><a href="#cb47-10"></a> FROM TableName</span>
<span id="cb47-11"><a href="#cb47-11"></a>)</span>
<span id="cb47-12"><a href="#cb47-12"></a></span>
<span id="cb47-13"><a href="#cb47-13"></a></span>
<span id="cb47-14"><a href="#cb47-14"></a></span>
<span id="cb47-15"><a href="#cb47-15"></a>-- Create a CTE to get the Maximum BloodPressure by Age</span>
<span id="cb47-16"><a href="#cb47-16"></a>WITH BloodPressureAge(Age, MaxBloodPressure)</span>
<span id="cb47-17"><a href="#cb47-17"></a>AS</span>
<span id="cb47-18"><a href="#cb47-18"></a> (SELECT Age, MAX(BloodPressure) AS MaxBloodPressure</span>
<span id="cb47-19"><a href="#cb47-19"></a> FROM Kidney</span>
<span id="cb47-20"><a href="#cb47-20"></a> GROUP BY Age)</span>
<span id="cb47-21"><a href="#cb47-21"></a> </span>
<span id="cb47-22"><a href="#cb47-22"></a>-- Create a query to use the CTE as a table</span>
<span id="cb47-23"><a href="#cb47-23"></a>SELECT a.Age, MIN(a.BloodPressure), b.MaxBloodPressure</span>
<span id="cb47-24"><a href="#cb47-24"></a>FROM Kidney a</span>
<span id="cb47-25"><a href="#cb47-25"></a>-- Join the CTE with the table</span>
<span id="cb47-26"><a href="#cb47-26"></a>JOIN BloodpressureAge b</span>
<span id="cb47-27"><a href="#cb47-27"></a> ON a.Age = b.Age</span>
<span id="cb47-28"><a href="#cb47-28"></a>GROUP BY a.Age, b.MaxBloodPressure</span>
<span id="cb47-29"><a href="#cb47-29"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
<section id="windows-function" class="slide level2">
<h2>Windows Function</h2>
<p><strong>SUM</strong></p>
<div class="sourceCode" id="cb48"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb48-1"><a href="#cb48-1"></a></span>
<span id="cb48-2"><a href="#cb48-2"></a></span>
<span id="cb48-3"><a href="#cb48-3"></a>SELECT SalesPerson, SalesYear, CurrentQuota,</span>
<span id="cb48-4"><a href="#cb48-4"></a> SUM(CurrentQuota)</span>
<span id="cb48-5"><a href="#cb48-5"></a> OVER (PARTITION BY SalesYear) AS YearlyTotal,</span>
<span id="cb48-6"><a href="#cb48-6"></a> ModifiedDate AS ModDate</span>
<span id="cb48-7"><a href="#cb48-7"></a>FROM SaleGoal</span>
<span id="cb48-8"><a href="#cb48-8"></a></span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>COUNT</strong></p>
<div class="sourceCode" id="cb49"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb49-1"><a href="#cb49-1"></a></span>
<span id="cb49-2"><a href="#cb49-2"></a>SELECT SalesPerson, SalesYear, CurrentQuota,</span>
<span id="cb49-3"><a href="#cb49-3"></a> COUNT(CurrentQuota)</span>
<span id="cb49-4"><a href="#cb49-4"></a> OVER (PARTITION BY SalesYear) AS QuotaPerYear,</span>
<span id="cb49-5"><a href="#cb49-5"></a> ModifiedDate AS ModDate</span>
<span id="cb49-6"><a href="#cb49-6"></a>FROM SaleGoal</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>FIRST_VALUE() and LAST_VALUE()</strong></p>
<div class="sourceCode" id="cb50"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb50-1"><a href="#cb50-1"></a></span>
<span id="cb50-2"><a href="#cb50-2"></a>-- Select the columns</span>
<span id="cb50-3"><a href="#cb50-3"></a>SELECT SalesPerson, SalesYear, CurrentQuota,</span>
<span id="cb50-4"><a href="#cb50-4"></a> -- First value from every window</span>
<span id="cb50-5"><a href="#cb50-5"></a> FIRST_VALUE(CurrentQuota)</span>
<span id="cb50-6"><a href="#cb50-6"></a> OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS StartQuota,</span>
<span id="cb50-7"><a href="#cb50-7"></a>-- Last value from every window</span>
<span id="cb50-8"><a href="#cb50-8"></a> LAST_VALUE(CurrentQuota)</span>
<span id="cb50-9"><a href="#cb50-9"></a> OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS EndQuota,</span>
<span id="cb50-10"><a href="#cb50-10"></a> ModifiedDate as ModDate</span>
<span id="cb50-11"><a href="#cb50-11"></a>FROM SaleGoal</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>ROW_NUMBER()</strong></p>
<div class="sourceCode" id="cb51"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb51-1"><a href="#cb51-1"></a>SELECT SalesPerson, SalesYear, CurrentQuota,</span>
<span id="cb51-2"><a href="#cb51-2"></a> ROW_NUMBER()</span>
<span id="cb51-3"><a href="#cb51-3"></a> OVER (PARTITION BY SalesPerson ORDER BY SalesYear) AS QuotabySalesPerson</span>
<span id="cb51-4"><a href="#cb51-4"></a>FROM SaleGoal</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
<p><strong>Calculating the mode</strong></p>
<div class="sourceCode" id="cb52"><pre class="sourceCode numberSource {sql} number-lines code-with-copy"><code class="sourceCode"><span id="cb52-1"><a href="#cb52-1"></a></span>
<span id="cb52-2"><a href="#cb52-2"></a>WITH QuotaCount AS (</span>
<span id="cb52-3"><a href="#cb52-3"></a>SELECT SalesPerson, SalesYear, CurrentQuota,</span>
<span id="cb52-4"><a href="#cb52-4"></a> ROW_NUMBER()</span>
<span id="cb52-5"><a href="#cb52-5"></a> OVER (PARTITION BY CurrentQuota ORDER BY CurrentQuota) AS QuotaList</span>
<span id="cb52-6"><a href="#cb52-6"></a>FROM SaleGoal</span>
<span id="cb52-7"><a href="#cb52-7"></a>)</span>
<span id="cb52-8"><a href="#cb52-8"></a></span>
<span id="cb52-9"><a href="#cb52-9"></a>SELECT CurrentQuota, QuotaList AS Mode</span>
<span id="cb52-10"><a href="#cb52-10"></a>FROM QuotaCount</span>
<span id="cb52-11"><a href="#cb52-11"></a>WHERE QuotaList IN (SELECT MAX(QuotaList) FROM QuotaCount)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</section>
</section>
</div>
</div>
<script>window.backupDefine = window.define; window.define = undefined;</script>
<script src="site_libs/revealjs/dist/reveal.js"></script>
<!-- reveal.js plugins -->
<script src="site_libs/revealjs/plugin/quarto-line-highlight/line-highlight.js"></script>