-
Notifications
You must be signed in to change notification settings - Fork 25
/
index.html
6934 lines (6920 loc) · 279 KB
/
index.html
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-us">
<head>
<meta charset="UTF-8">
<title>PostgreSQL Exercises</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" type="text/css" href="stylesheets/normalize.css" media="screen">
<link href='https://fonts.googleapis.com/css?family=Open+Sans:400,700' rel='stylesheet' type='text/css'>
<link rel="stylesheet" type="text/css" href="stylesheets/stylesheet.css" media="screen">
<link rel="stylesheet" type="text/css" href="stylesheets/github-light.css" media="screen">
</head>
<body>
<section class="page-header">
<h1 class="project-name">PostgreSQL Exercises</h1>
<h2 class="project-tagline">A series of questions and answers on a single dataset </h2>
<a href="https://github.com/Ozencb/postgresql-exercises" class="btn">View on GitHub</a>
<a href="https://github.com/Ozencb/postgresql-exercises/raw/master/PDF.pdf" class="btn">Download PDF</a>
</section>
<section class="main-content">
<h1>
<a id="postgresql-exercises" class="anchor" href="#postgresql-exercises" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>PostgreSQL Exercises</h1>
<p>This is a compilation of all the questions and answers on <a href="https://github.com/AlisdairO">Alisdair Owen's</a> <a href="https://pgexercises.com">PostgreSQL Exercises</a>. Keep in mind that actually solving these problems will make you go further than just skimming through this guide, so make sure to pay <a href="https://pgexercises.com">PostgreSQL Exercises</a> a visit.</p>
<h2>
<a id="table-of-contents" class="anchor" href="#table-of-contents" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Table of Contents</h2>
<ul>
<li>
<a href="#getting-started">Getting Started</a>
<ul>
<li><a href="#i-want-to-use-my-own-postgres-system">I want to use my own Postgres system</a></li>
<li><a href="#schema">Schema</a></li>
</ul>
</li>
<li>
<a href="#simple-sql-queries">Simple SQL Queries</a>
<ul>
<li><a href="#retrieve-everything-from-a-table">Retrieve everything from a table</a></li>
<li><a href="#retrieve-specific-columns-from-a-table">Retrieve specific columns from a table</a></li>
<li><a href="#control-which-rows-are-retrieved">Control which rows are retrieved</a></li>
<li><a href="#control-which-rows-are-retrieved-part-2">Control which rows are retrieved, Part 2</a></li>
<li><a href="#basic-string-searches">Basic string searches</a></li>
<li><a href="#matching-against-multiple-possible-values">Matching against multiple possible values</a></li>
<li><a href="#classify-results-into-bucket">Classify results into bucket</a></li>
<li><a href="#working-with-dates">Working with dates</a></li>
<li><a href="#removing-duplicates-and-ordering-results">Removing duplicates, and ordering results</a></li>
<li><a href="#combining-results-from-multiple-queries">Combining results from multiple queries</a></li>
<li><a href="#simple-aggregation">Simple aggregation</a></li>
<li><a href="#more-aggregation">More aggregation</a></li>
</ul>
</li>
<li>
<a href="#joins-and-subqueries">Joins and Subqueries</a>
<ul>
<li><a href="#retrieve-the-start-times-of-members-bookings">Retrieve the start times of members' bookings</a></li>
<li><a href="#work-out-the-start-times-of-bookings-for-tennis-courts">Work out the start times of bookings for tennis courts</a></li>
<li><a href="#produce-a-list-of-all-members-who-have-recommended-another-member">Produce a list of all members who have recommended another member</a></li>
<li><a href="#produce-a-list-of-all-members-along-with-their-recommender">Produce a list of all members, along with their recommender</a></li>
<li><a href="#produce-a-list-of-all-members-who-have-used-a-tennis-court">Produce a list of all members who have used a tennis court</a></li>
<li><a href="#produce-a-list-of-costly-bookings">Produce a list of costly bookings</a></li>
<li><a href="#produce-a-list-of-all-members-along-with-their-recommender-using-no-joins">Produce a list of all members, along with their recommender, using no joins</a></li>
<li><a href="#produce-a-list-of-costly-bookings-using-a-subquery">Produce a list of costly bookings, using a subquery</a></li>
</ul>
</li>
<li>
<a href="#modifying-data">Modifying Data</a>
<ul>
<li><a href="#insert-some-data-into-a-table">Insert some data into a table</a></li>
<li><a href="#insert-multiple-rows-of-data-into-a-table">Insert multiple rows of data into a table</a></li>
<li><a href="#insert-calculated-data-into-a-table">Insert calculated data into a table</a></li>
<li><a href="#update-some-existing-data">Update some existing data</a></li>
<li><a href="#update-multiple-rows-and-columns-at-the-same-time">Update multiple rows and columns at the same time</a></li>
<li><a href="#update-a-row-based-on-the-contents-of-another-row">Update a row based on the contents of another row</a></li>
<li><a href="#delete-all-bookings">Delete all bookings</a></li>
<li><a href="#delete-a-member-from-the-cdmembers-table">Delete a member from the c-members table</a></li>
<li><a href="#delete-based-on-a-subquery">Delete based on a subquery</a></li>
</ul>
</li>
<li>
<a href="#aggregation">Aggregation</a>
<ul>
<li><a href="#count-the-number-of-facilities">Count the number of facilities</a></li>
<li><a href="#count-the-number-of-expensive-facilities">Count the number of expensive facilities</a></li>
<li><a href="#count-the-number-of-recommendations-each-member-makes">Count the number of recommendations each member makes</a></li>
<li><a href="#list-the-total-slots-booked-per-facility">List the total slots booked per facility</a></li>
<li><a href="#list-the-total-slots-booked-per-facility-in-a-given-month">List the total slots booked per facility in a given month</a></li>
<li><a href="#list-the-total-slots-booked-per-facility-per-month">List the total slots booked per facility per month</a></li>
<li><a href="#find-the-count-of-members-who-have-made-at-least-one-booking">Find the count of members who have made at least one booking</a></li>
<li><a href="#list-facilities-with-more-than-1000-slots-booked">List facilities with more than 1000 slots booked</a></li>
<li><a href="#find-the-total-revenue-of-each-facility">Find the total revenue of each facility</a></li>
<li><a href="#find-facilities-with-a-total-revenue-less-than-1000">Find facilities with a total revenue less than 1000</a></li>
<li><a href="#output-the-facility-id-that-has-the-highest-number-of-slots-booked">Output the facility id that has the highest number of slots booked</a></li>
<li><a href="#list-the-total-slots-booked-per-facility-per-month-part-2">List the total slots booked per facility per month, Part 2</a></li>
<li><a href="#list-the-total-hours-booked-per-named-facility">List the total hours booked per named facility</a></li>
<li><a href="#list-each-members-first-booking-after-september-1st-2012">List each member's first booking after September 1st 2012</a></li>
<li><a href="#produce-a-list-of-member-names-with-each-row-containing-the-total-member-count">Produce a list of member names, with each row containing the total member count</a></li>
<li><a href="#produce-a-numbered-list-of-members">Produce a numbered list of members</a></li>
<li><a href="#output-the-facility-id-that-has-the-highest-number-of-slots-booked-again">Output the facility id that has the highest number of slots booked, again</a></li>
<li><a href="#rank-members-by-rounded-hours-used">Rank members by (rounded) hours used</a></li>
<li><a href="#find-the-top-three-revenue-generating-facilities">Find the top three revenue generating facilities</a></li>
<li><a href="#classify-facilities-by-value">Classify facilities by value</a></li>
<li><a href="#calculate-the-payback-time-for-each-facility">Calculate the payback time for each facility</a></li>
<li><a href="#calculate-a-rolling-average-of-total-revenue">Calculate a rolling average of total revenue</a></li>
</ul>
</li>
<li>
<a href="#working-with-timestamps">Working with Timestamps</a>
<ul>
<li><a href="#produce-a-timestamp-for-1-am-on-the-31st-of-august-2012">Produce a timestamp for 1 a.m. on the 31st of August 2012</a></li>
<li><a href="#subtract-timestamps-from-each-other">Subtract timestamps from each other</a></li>
<li><a href="#generate-a-list-of-all-the-dates-in-october-2012">Generate a list of all the dates in October 2012</a></li>
<li><a href="#get-the-day-of-the-month-from-a-timestamp">Get the day of the month from a timestamp</a></li>
<li><a href="#work-out-the-number-of-seconds-between-timestamps">Work out the number of seconds between timestamps</a></li>
<li><a href="#work-out-the-number-of-days-in-each-month-of-2012">Work out the number of days in each month of 2012</a></li>
<li><a href="#work-out-the-number-of-days-remaining-in-the-month">Work out the number of days remaining in the month</a></li>
<li><a href="#work-out-the-end-time-of-bookings">Work out the end time of bookings</a></li>
<li><a href="#return-a-count-of-bookings-for-each-month">Return a count of bookings for each month</a></li>
<li><a href="#work-out-the-utilisation-percentage-for-each-facility-by-month">Work out the utilisation percentage for each facility by month</a></li>
</ul>
</li>
<li>
<a href="#string-operations">String Operations</a>
<ul>
<li><a href="#format-the-names-of-members">Format the names of members</a></li>
<li><a href="#find-facilities-by-a-name-prefix">Find facilities by a name prefix</a></li>
<li><a href="#perform-a-case-insensitive-search">Perform a case-insensitive search</a></li>
<li><a href="#find-telephone-numbers-with-parentheses">Find telephone numbers with parentheses</a></li>
<li><a href="#pad-zip-codes-with-leading-zeroes">Pad zip codes with leading zeroes</a></li>
<li><a href="#count-the-number-of-members-whose-surname-starts-with-each-letter-of-the-alphabet">Count the number of members whose surname starts with each letter of the alphabet</a></li>
<li><a href="#clean-up-telephone-numbers">Clean up telephone numbers</a></li>
</ul>
</li>
<li>
<a href="#recursive-queries">Recursive Queries</a>
<ul>
<li><a href="#find-the-upward-recommendation-chain-for-member-id-27">Find the upward recommendation chain for member ID 27</a></li>
<li><a href="#find-the-downward-recommendation-chain-for-member-id-1">Find the downward recommendation chain for member ID 1</a></li>
<li><a href="#produce-a-cte-that-can-return-the-upward-recommendation-chain-for-any-member">Produce a CTE that can return the upward recommendation chain for any member</a></li>
</ul>
</li>
</ul>
<hr>
<h2>
<a id="getting-started" class="anchor" href="#getting-started" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Getting Started</h2>
<p>It's pretty simple to get going with the exercises: all you have to do is <a href="https://pgexercises.com/questions/basic/">open the exercises</a>, take a look at the questions, and try to answer them!</p>
<p>The dataset for these exercises is for a newly created country club, with a set of members, facilities such as tennis courts, and booking history for those facilities. Amongst other things, the club wants to understand how they can use their information to analyse facility usage/demand. <strong>Please note:</strong> this dataset is designed purely for supporting an interesting array of exercises, and the database schema is flawed in several aspects - please don't take it as an example of good design. We'll start off with a look at the Members table:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">cd</span>.members
(
memid <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
surname <span class="pl-k">character varying</span>(<span class="pl-c1">200</span>) <span class="pl-k">NOT NULL</span>,
firstname <span class="pl-k">character varying</span>(<span class="pl-c1">200</span>) <span class="pl-k">NOT NULL</span>,
address <span class="pl-k">character varying</span>(<span class="pl-c1">300</span>) <span class="pl-k">NOT NULL</span>,
zipcode <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
telephone <span class="pl-k">character varying</span>(<span class="pl-c1">20</span>) <span class="pl-k">NOT NULL</span>,
recommendedby <span class="pl-k">integer</span>,
joindate <span class="pl-k">timestamp</span> <span class="pl-k">not null</span>,
<span class="pl-k">CONSTRAINT</span> members_pk <span class="pl-k">PRIMARY KEY</span> (memid),
<span class="pl-k">CONSTRAINT</span> fk_members_recommendedby <span class="pl-k">FOREIGN KEY</span> (recommendedby)
<span class="pl-k">REFERENCES</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>(memid) <span class="pl-k">ON DELETE</span> <span class="pl-k">SET</span> <span class="pl-k">NULL</span>
);</pre></div>
<p>Each member has an ID (not guaranteed to be sequential), basic address information, a reference to the member that recommended them (if any), and a timestamp for when they joined. The addresses in the dataset are entirely (and unrealistically) fabricated.</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">cd</span>.facilities
(
facid <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
name <span class="pl-k">character varying</span>(<span class="pl-c1">100</span>) <span class="pl-k">NOT NULL</span>,
membercost <span class="pl-k">numeric</span> <span class="pl-k">NOT NULL</span>,
guestcost <span class="pl-k">numeric</span> <span class="pl-k">NOT NULL</span>,
initialoutlay <span class="pl-k">numeric</span> <span class="pl-k">NOT NULL</span>,
monthlymaintenance <span class="pl-k">numeric</span> <span class="pl-k">NOT NULL</span>,
<span class="pl-k">CONSTRAINT</span> facilities_pk <span class="pl-k">PRIMARY KEY</span> (facid)
);</pre></div>
<p>The facilities table lists all the bookable facilities that the country club possesses. The club stores id/name information, the cost to book both members and guests, the initial cost to build the facility, and estimated monthly upkeep costs. They hope to use this information to track how financially worthwhile each facility is.</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">cd</span>.bookings
(
bookid <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
facid <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
memid <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
starttime <span class="pl-k">timestamp</span> <span class="pl-k">NOT NULL</span>,
slots <span class="pl-k">integer</span> <span class="pl-k">NOT NULL</span>,
<span class="pl-k">CONSTRAINT</span> bookings_pk <span class="pl-k">PRIMARY KEY</span> (bookid),
<span class="pl-k">CONSTRAINT</span> fk_bookings_facid <span class="pl-k">FOREIGN KEY</span> (facid) <span class="pl-k">REFERENCES</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>(facid),
<span class="pl-k">CONSTRAINT</span> fk_bookings_memid <span class="pl-k">FOREIGN KEY</span> (memid) <span class="pl-k">REFERENCES</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>(memid)
);</pre></div>
<p>Finally, there's a table tracking bookings of facilities. This stores the facility id, the member who made the booking, the start of the booking, and how many half hour 'slots' the booking was made for. This idiosyncratic design will make certain queries more difficult, but should provide you with some interesting challenges - as well as prepare you for the horror of working with some real-world databases :-).</p>
<p>Okay, that should be all the information you need. You can select a category of query to try from the menu above, or alternatively <a href="https://pgexercises.com/questions/basic/">start from the beginning</a>.</p>
<h4>
<a id="i-want-to-use-my-own-postgres-system" class="anchor" href="#i-want-to-use-my-own-postgres-system" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>I want to use my own Postgres system</h4>
<p>No problem! Getting up and running isn't too hard. First, you'll need an install of PostgreSQL, which you can get from <a href="http://www.postgresql.org/download/">here</a>. Once you have it started, <a href="https://pgexercises.com/dbfiles/clubdata.sql">download the SQL</a>.</p>
<p>Finally, run <code>psql -U <username> -f clubdata.sql -d postgres -x -q</code> to create the 'exercises' database, the Postgres 'pgexercises' user, the tables, and to load the data in. Note that you may find that the sort order of your results differs from those shown on the web site: that's probably because your Postgres is set up using a different locale to that used by PGExercises (which uses the C locale)</p>
<p>When you're running queries, you may find psql a little clunky. If so, I recommend trying out pgAdmin or the Eclipse database development tools.</p>
<h4>
<a id="schema" class="anchor" href="#schema" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Schema</h4>
<p><img src="Images/schema-horizontal.png" alt=""></p>
<hr>
<h2>
<a id="simple-sql-queries" class="anchor" href="#simple-sql-queries" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Simple SQL Queries</h2>
<p>This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends. If you're already educated in SQL you will probably find these exercises fairly easy. If not, you should find them a good point to start learning for the more difficult categories ahead!</p>
<p>If you struggle with these questions, I strongly recommend <a href="http://shop.oreilly.com/product/9780596007270.do">Learning SQL</a>, by Alan Beaulieu, as a concise and well-written book on the subject. If you're interested in the fundamentals of database systems (as opposed to just how to use them), you should also investigate An Introduction to Database Systems by C.J. Date.</p>
<h3>
<a id="retrieve-everything-from-a-table" class="anchor" href="#retrieve-everything-from-a-table" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Retrieve everything from a table</h3>
<p>How can you retrieve all the information from the cd.facilities table?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>facid</th>
<th>name</th>
<th>membercost</th>
<th>guestcost</th>
<th>initialoutlay</th>
<th>monthlymaintenance</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Tennis Court 1</td>
<td>5</td>
<td>25</td>
<td>10000</td>
<td>200</td>
</tr>
<tr>
<td>1</td>
<td>Tennis Court 2</td>
<td>5</td>
<td>25</td>
<td>8000</td>
<td>200</td>
</tr>
<tr>
<td>2</td>
<td>Badminton Court</td>
<td>0</td>
<td>15.5</td>
<td>4000</td>
<td>50</td>
</tr>
<tr>
<td>3</td>
<td>Table Tennis</td>
<td>0</td>
<td>5</td>
<td>320</td>
<td>10</td>
</tr>
<tr>
<td>4</td>
<td>Massage Room 1</td>
<td>35</td>
<td>80</td>
<td>4000</td>
<td>3000</td>
</tr>
<tr>
<td>5</td>
<td>Massage Room 2</td>
<td>35</td>
<td>80</td>
<td>4000</td>
<td>3000</td>
</tr>
<tr>
<td>6</td>
<td>Squash Court</td>
<td>3.5</td>
<td>17.5</td>
<td>5000</td>
<td>80</td>
</tr>
<tr>
<td>7</td>
<td>Snooker Table</td>
<td>0</td>
<td>5</td>
<td>450</td>
<td>15</td>
</tr>
<tr>
<td>8</td>
<td>Pool Table</td>
<td>0</td>
<td>5</td>
<td>400</td>
<td>15</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span> <span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>; </pre></div>
<p>The <code>SELECT</code> statement is the basic starting block for queries that read information out of the database. A minimal select statement is generally comprised of <code>select [some set of columns] from [some table or group of tables]</code>.</p>
<p>In this case, we want all of the information from the facilities table. The from section is easy - we just need to specify the <code>cd.facilities</code> table. 'cd' is the table's schema - a term used for a logical grouping of related information in the database.</p>
<p>Next, we need to specify that we want all the columns. Conveniently, there's a shorthand for 'all columns' - *. We can use this instead of laboriously specifying all the column names.</p>
<h3>
<a id="retrieve-specific-columns-from-a-table" class="anchor" href="#retrieve-specific-columns-from-a-table" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Retrieve specific columns from a table</h3>
<p>You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>name</th>
<th>membercost</th>
</tr>
</thead>
<tbody>
<tr>
<td>Tennis Court 1</td>
<td>5</td>
</tr>
<tr>
<td>Tennis Court 2</td>
<td>5</td>
</tr>
<tr>
<td>Badminton Court</td>
<td>0</td>
</tr>
<tr>
<td>Table Tennis</td>
<td>0</td>
</tr>
<tr>
<td>Massage Room 1</td>
<td>35</td>
</tr>
<tr>
<td>Massage Room 2</td>
<td>35</td>
</tr>
<tr>
<td>Squash Court</td>
<td>3.5</td>
</tr>
<tr>
<td>Snooker Table</td>
<td>0</td>
</tr>
<tr>
<td>Pool Table</td>
<td>0</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> name, membercost <span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>; </pre></div>
<p>For this question, we need to specify the columns that we want. We can do that with a simple comma-delimited list of column names specified to the select statement. All the database does is look at the columns available in the FROM clause, and return the ones we asked for, as illustrated below</p>
<p><img src="Images/select.png" alt=""></p>
<p>Generally speaking, for non-throwaway queries it's considered desirable to specify the names of the columns you want in your queries rather than using *. This is because your application might not be able to cope if more columns get added into the table.</p>
<h3>
<a id="control-which-rows-are-retrieved" class="anchor" href="#control-which-rows-are-retrieved" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Control which rows are retrieved</h3>
<p>How can you produce a list of facilities that charge a fee to members?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>facid</th>
<th>name</th>
<th>membercost</th>
<th>guestcost</th>
<th>initialoutlay</th>
<th>monthlymaintenance</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Tennis Court 1</td>
<td>5</td>
<td>25</td>
<td>10000</td>
<td>200</td>
</tr>
<tr>
<td>1</td>
<td>Tennis Court 2</td>
<td>5</td>
<td>25</td>
<td>8000</td>
<td>200</td>
</tr>
<tr>
<td>4</td>
<td>Massage Room 1</td>
<td>35</td>
<td>80</td>
<td>4000</td>
<td>3000</td>
</tr>
<tr>
<td>5</td>
<td>Massage Room 2</td>
<td>35</td>
<td>80</td>
<td>4000</td>
<td>3000</td>
</tr>
<tr>
<td>6</td>
<td>Squash Court</td>
<td>3.5</td>
<td>17.5</td>
<td>5000</td>
<td>80</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span> <span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span> <span class="pl-k">where</span> membercost <span class="pl-k">></span> <span class="pl-c1">0</span>; </pre></div>
<p>The <code>FROM</code> clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In future we will explore joining, which allows us to create much more interesting candidates.</p>
<p>Once we've built up our set of candidate rows, the <code>WHERE</code> clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero. As you will see in later exercises, <code>WHERE</code> clauses can have multiple components combined with boolean logic - it's possible to, for instance, search for facilities with a cost greater than 0 and less than 10. The filtering action of the <code>WHERE</code> clause on the facilities table is illustrated below:</p>
<p><img src="Images/whereclause.png" alt=""></p>
<h3>
<a id="control-which-rows-are-retrieved-part-2" class="anchor" href="#control-which-rows-are-retrieved-part-2" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Control which rows are retrieved, Part 2</h3>
<p>How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>facid</th>
<th>name</th>
<th>membercost</th>
<th>monthlymaintenance</th>
</tr>
</thead>
<tbody>
<tr>
<td>4</td>
<td>Massage Room 1</td>
<td>35</td>
<td>3000</td>
</tr>
<tr>
<td>5</td>
<td>Massage Room 2</td>
<td>35</td>
<td>3000</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> facid, name, membercost, monthlymaintenance
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>
<span class="pl-k">where</span>
membercost <span class="pl-k">></span> <span class="pl-c1">0</span> <span class="pl-k">and</span>
(membercost <span class="pl-k"><</span> monthlymaintenance<span class="pl-k">/</span><span class="pl-c1">50</span>.<span class="pl-c1">0</span>); </pre></div>
<p>The <code>WHERE</code> clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs!</p>
<p>When we want to test for two or more conditions, we use <code>AND</code> to combine them. We can, as you might expect, use <code>OR</code> to test whether either of a pair of conditions is true.</p>
<p>You might have noticed that this is our first query that combines a <code>WHERE</code> clause with selecting specific columns. You can see in the image below the effect of this: the intersection of the selected columns and the selected rows gives us the data to return. This may not seem too interesting now, but as we add in more complex operations like joins later, you'll see the simple elegance of this behaviour.</p>
<p><img src="Images/whereandselect.png" alt=""></p>
<h3>
<a id="basic-string-searches" class="anchor" href="#basic-string-searches" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Basic string searches</h3>
<p>How can you produce a list of all facilities with the word 'Tennis' in their name?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>facid</th>
<th>name</th>
<th>membercost</th>
<th>guestcost</th>
<th>initialoutlay</th>
<th>monthlymaintenance</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Tennis Court 1</td>
<td>5</td>
<td>25</td>
<td>10000</td>
<td>200</td>
</tr>
<tr>
<td>1</td>
<td>Tennis Court 2</td>
<td>5</td>
<td>25</td>
<td>8000</td>
<td>200</td>
</tr>
<tr>
<td>3</td>
<td>Table Tennis</td>
<td>0</td>
<td>5</td>
<td>320</td>
<td>10</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span>
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>
<span class="pl-k">where</span>
name <span class="pl-k">like</span> <span class="pl-s"><span class="pl-pds">'</span>%Tennis%<span class="pl-pds">'</span></span>; </pre></div>
<p>SQL's <code>LIKE</code> operator provides simple pattern matching on strings. It's pretty much universally implemented, and is nice and simple to use - it just takes a string with the % character matching any string, and _ matching any single character. In this case, we're looking for names containing the word 'Tennis', so putting a % on either side fits the bill.</p>
<p>There's other ways to accomplish this task: Postgres supports regular expressions with the ~ operator, for example. Use whatever makes you feel comfortable, but do be aware that the <code>LIKE</code> operator is much more portable between systems.</p>
<h3>
<a id="matching-against-multiple-possible-values" class="anchor" href="#matching-against-multiple-possible-values" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Matching against multiple possible values</h3>
<p>How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>facid</th>
<th>name</th>
<th>membercost</th>
<th>guestcost</th>
<th>initialoutlay</th>
<th>monthlymaintenance</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Tennis Court 2</td>
<td>5</td>
<td>25</td>
<td>8000</td>
<td>200</td>
</tr>
<tr>
<td>5</td>
<td>Massage Room 2</td>
<td>35</td>
<td>80</td>
<td>4000</td>
<td>3000</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span>
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>
<span class="pl-k">where</span>
facid <span class="pl-k">in</span> (<span class="pl-c1">1</span>,<span class="pl-c1">5</span>); </pre></div>
<p>The obvious answer to this question is to use a <code>WHERE</code> clause that looks like <code>where facid = 1 or facid = 5</code>. An alternative that is easier with large numbers of possible matches is the <code>IN</code> operator. The <code>IN</code> operator takes a list of possible values, and matches them against (in this case) the facid. If one of the values matches, the where clause is true for that row, and the row is returned.</p>
<p>The <code>IN</code> operator is a good early demonstrator of the elegance of the relational model. The argument it takes is not just a list of values - it's actually a table with a single column. Since queries also return tables, if you create a query that returns a single column, you can feed those results into an <code>IN</code> operator. To give a toy example:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-k">*</span>
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>
<span class="pl-k">where</span>
facid <span class="pl-k">in</span> (
<span class="pl-k">select</span> facid <span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>
);</pre></div>
<p>This example is functionally equivalent to just selecting all the facilities, but shows you how to feed the results of one query into another. The inner query is called a <em>subquery</em>.</p>
<h3>
<a id="classify-results-into-bucket" class="anchor" href="#classify-results-into-bucket" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Classify results into bucket</h3>
<p>How can you produce a list of facilities, with each labelled as 'cheap' or 'expensive' depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>name</th>
<th>cost</th>
</tr>
</thead>
<tbody>
<tr>
<td>Tennis Court 1</td>
<td>expensive</td>
</tr>
<tr>
<td>Tennis Court 2</td>
<td>expensive</td>
</tr>
<tr>
<td>Badminton Court</td>
<td>cheap</td>
</tr>
<tr>
<td>Table Tennis</td>
<td>cheap</td>
</tr>
<tr>
<td>Massage Room 1</td>
<td>expensive</td>
</tr>
<tr>
<td>Massage Room 2</td>
<td>expensive</td>
</tr>
<tr>
<td>Squash Court</td>
<td>cheap</td>
</tr>
<tr>
<td>Snooker Table</td>
<td>cheap</td>
</tr>
<tr>
<td>Pool Table</td>
<td>cheap</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> name,
case when (monthlymaintenance <span class="pl-k">></span> <span class="pl-c1">100</span>) then
<span class="pl-s"><span class="pl-pds">'</span>expensive<span class="pl-pds">'</span></span>
else
<span class="pl-s"><span class="pl-pds">'</span>cheap<span class="pl-pds">'</span></span>
end <span class="pl-k">as</span> cost
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>; </pre></div>
<p>This exercise contains a few new concepts. The first is the fact that we're doing computation in the area of the query between <code>SELECT</code> and <code>FROM</code>. Previously we've only used this to select columns that we want to return, but you can put anything in here that will produce a single result per returned row - including subqueries.</p>
<p>The second new concept is the <code>CASE</code> statement itself. <code>CASE</code> is effectively like if/switch statements in other languages, with a form as shown in the query. To add a 'middling' option, we would simply insert another <code>when...then</code> section.</p>
<p>Finally, there's the <code>AS</code> operator. This is simply used to label columns or expressions, to make them display more nicely or to make them easier to reference when used as part of a subquery.</p>
<h3>
<a id="working-with-dates" class="anchor" href="#working-with-dates" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Working with dates</h3>
<p>How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>memid</th>
<th>surname</th>
<th>firstname</th>
<th>joindate</th>
</tr>
</thead>
<tbody>
<tr>
<td>24</td>
<td>Sarwin</td>
<td>Ramnaresh</td>
<td>2012-09-01 08:44:42</td>
</tr>
<tr>
<td>26</td>
<td>Jones</td>
<td>Douglas</td>
<td>2012-09-02 18:43:05</td>
</tr>
<tr>
<td>27</td>
<td>Rumney</td>
<td>Henrietta</td>
<td>2012-09-05 08:42:35</td>
</tr>
<tr>
<td>28</td>
<td>Farrell</td>
<td>David</td>
<td>2012-09-15 08:22:05</td>
</tr>
<tr>
<td>29</td>
<td>Worthington-Smyth</td>
<td>Henry</td>
<td>2012-09-17 12:27:15</td>
</tr>
<tr>
<td>30</td>
<td>Purview</td>
<td>Millicent</td>
<td>2012-09-18 19:04:01</td>
</tr>
<tr>
<td>33</td>
<td>Tupperware</td>
<td>Hyacinth</td>
<td>2012-09-18 19:32:05</td>
</tr>
<tr>
<td>35</td>
<td>Hunt</td>
<td>John</td>
<td>2012-09-19 11:32:45</td>
</tr>
<tr>
<td>36</td>
<td>Crumpet</td>
<td>Erica</td>
<td>2012-09-22 08:36:38</td>
</tr>
<tr>
<td>37</td>
<td>Smith</td>
<td>Darren</td>
<td>2012-09-26 18:08:45</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> memid, surname, firstname, joindate
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>
<span class="pl-k">where</span> joindate <span class="pl-k">>=</span> <span class="pl-s"><span class="pl-pds">'</span>2012-09-01<span class="pl-pds">'</span></span>; </pre></div>
<p>This is our first look at SQL timestamps. They're formatted in descending order of magnitude: <code>YYYY-MM-DD HH:MM:SS.nnnnnn</code>. We can compare them just like we might a unix timestamp, although getting the differences between dates is a little more involved (and powerful!). In this case, we've just specified the date portion of the timestamp. This gets automatically cast by postgres into the full timestamp <code>2012-09-01 00:00:00</code>.</p>
<h3>
<a id="removing-duplicates-and-ordering-results" class="anchor" href="#removing-duplicates-and-ordering-results" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Removing duplicates, and ordering results</h3>
<p>How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>surname</th>
</tr>
</thead>
<tbody>
<tr>
<td>Bader</td>
</tr>
<tr>
<td>Baker</td>
</tr>
<tr>
<td>Boothe</td>
</tr>
<tr>
<td>Butters</td>
</tr>
<tr>
<td>Coplin</td>
</tr>
<tr>
<td>Crumpet</td>
</tr>
<tr>
<td>Dare</td>
</tr>
<tr>
<td>Farrell</td>
</tr>
<tr>
<td>GUEST</td>
</tr>
<tr>
<td>Genting</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select distinct</span> surname
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>
<span class="pl-k">order by</span> surname
<span class="pl-k">limit</span> <span class="pl-c1">10</span>; </pre></div>
<p>There's three new concepts here, but they're all pretty simple.</p>
<ul>
<li>Specifying <code>DISTINCT</code> after <code>SELECT</code> removes duplicate rows from the result set. Note that this applies to <em>rows</em>: if row A has multiple columns, row B is only equal to it if the values in all columns are the same. As a general rule, don't use <code>DISTINCT</code> in a willy-nilly fashion - it's not free to remove duplicates from large query result sets, so do it as-needed.</li>
<li>Specifying <code>ORDER BY</code> (after the <code>FROM</code> and <code>WHERE</code> clauses, near the end of the query) allows results to be ordered by a column or set of columns (comma separated).</li>
<li>The <code>LIMIT</code> keyword allows you to limit the number of results retrieved. This is useful for getting results a page at a time, and can be combined with the <code>OFFSET</code> keyword to get following pages. This is the same approach used by MySQL and is very convenient - you may, unfortunately, find that this process is a little more complicated in other DBs.</li>
</ul>
<h3>
<a id="combining-results-from-multiple-queries" class="anchor" href="#combining-results-from-multiple-queries" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Combining results from multiple queries</h3>
<p>You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list!</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>surname</th>
</tr>
</thead>
<tbody>
<tr>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>Worthington-Smyth</td>
</tr>
<tr>
<td>Badminton Court</td>
</tr>
<tr>
<td>Pinker</td>
</tr>
<tr>
<td>Dare</td>
</tr>
<tr>
<td>Bader</td>
</tr>
<tr>
<td>Mackenzie</td>
</tr>
<tr>
<td>Crumpet</td>
</tr>
<tr>
<td>Massage Room 1</td>
</tr>
<tr>
<td>Squash Court</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> surname
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>
<span class="pl-k">union</span>
<span class="pl-k">select</span> name
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span>; </pre></div>
<p>The <code>UNION</code> operator does what you might expect: combines the results of two SQL queries into a single table. The caveat is that both results from the two queries must have the same number of columns and compatible data types.</p>
<p><code>UNION</code> removes duplicate rows, while <code>UNION ALL</code> does not. Use <code>UNION ALL</code> by default, unless you care about duplicate results.</p>
<h3>
<a id="simple-aggregation" class="anchor" href="#simple-aggregation" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Simple aggregation</h3>
<p>You'd like to get the signup date of your last member. How can you retrieve this information?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>latest</th>
</tr>
</thead>
<tbody>
<tr>
<td>2012-09-26 18:08:45</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-c1">max</span>(joindate) <span class="pl-k">as</span> latest
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>; </pre></div>
<p>This is our first foray into SQL's aggregate functions. They're used to extract information about whole groups of rows, and allow us to easily ask questions like:</p>
<ul>
<li>What's the most expensive facility to maintain on a monthly basis?</li>
<li>Who has recommended the most new members?</li>
<li>How much time has each member spent at our facilities?</li>
</ul>
<p>The MAX aggregate function here is very simple: it receives all the possible values for joindate, and outputs the one that's biggest. There's a lot more power to aggregate functions, which you will come across in future exercises.</p>
<h3>
<a id="more-aggregation" class="anchor" href="#more-aggregation" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>More aggregation</h3>
<p>You'd like to get the first and last name of the last member(s) who signed up - not just the date. How can you do that?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>firstname</th>
<th>surname</th>
<th>joindate</th>
</tr>
</thead>
<tbody>
<tr>
<td>Darren</td>
<td>Smith</td>
<td>2012-09-26 18:08:45</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> firstname, surname, joindate
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>
<span class="pl-k">where</span> joindate <span class="pl-k">=</span>
(<span class="pl-k">select</span> <span class="pl-c1">max</span>(joindate)
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>); </pre></div>
<p>In the suggested approach above, you use a <em>subquery</em> to find out what the most recent joindate is. This subquery returns a <em>scalar</em> table - that is, a table with a single column and a single row. Since we have just a single value, we can substitute the subquery anywhere we might put a single constant value. In this case, we use it to complete the <code>WHERE</code> clause of a query to find a given member.</p>
<p>You might hope that you'd be able to do something like below:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> firstname, surname, <span class="pl-c1">max</span>(joindate)
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span></pre></div>
<p>Unfortunately, this doesn't work. The <code>MAX</code> function doesn't restrict rows like the <code>WHERE</code> clause does - it simply takes in a bunch of values and returns the biggest one. The database is then left wondering how to pair up a long list of names with the single join date that's come out of the max function, and fails. Instead, you're left having to say 'find me the row(s) which have a join date that's the same as the maximum join date'.</p>
<p>As mentioned by the hint, there's other ways to get this job done - one example is below. In this approach, rather than explicitly finding out what the last joined date is, we simply order our members table in descending order of join date, and pick off the first one. Note that this approach does not cover the extremely unlikely eventuality of two people joining at the exact same time :-).</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> firstname, surname, joindate
<span class="pl-k">from</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span>
<span class="pl-k">order by</span> joindate <span class="pl-k">desc</span>
<span class="pl-k">limit</span> <span class="pl-c1">1</span>;</pre></div>
<hr>
<h2>
<a id="joins-and-subqueries" class="anchor" href="#joins-and-subqueries" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Joins and Subqueries</h2>
<p>This category deals primarily with a foundational concept in relational database systems: joining. Joining allows you to combine related information from multiple tables to answer a question. This isn't just beneficial for ease of querying: a lack of join capability encourages denormalisation of data, which increases the complexity of keeping your data internally consistent.</p>
<p>This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend <a href="http://shop.oreilly.com/product/9780596007270.do">Learning SQL</a>, by Alan Beaulieu, as a concise and well-written book on the subject.</p>
<h3>
<a id="retrieve-the-start-times-of-members-bookings" class="anchor" href="#retrieve-the-start-times-of-members-bookings" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Retrieve the start times of members' bookings</h3>
<p>How can you produce a list of the start times for bookings by members named 'David Farrell'?</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>starttime</th>
</tr>
</thead>
<tbody>
<tr>
<td>2012-09-18 09:00:00</td>
</tr>
<tr>
<td>2012-09-18 17:30:00</td>
</tr>
<tr>
<td>2012-09-18 13:30:00</td>
</tr>
<tr>
<td>2012-09-18 20:00:00</td>
</tr>
<tr>
<td>2012-09-19 09:30:00</td>
</tr>
<tr>
<td>2012-09-19 15:00:00</td>
</tr>
<tr>
<td>2012-09-19 12:00:00</td>
</tr>
<tr>
<td>2012-09-20 15:30:00</td>
</tr>
<tr>
<td>2012-09-20 11:30:00</td>
</tr>
<tr>
<td>2012-09-20 14:00:00</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-c1">bks</span>.<span class="pl-c1">starttime</span>
<span class="pl-k">from</span>
<span class="pl-c1">cd</span>.<span class="pl-c1">bookings</span> bks
<span class="pl-k">inner join</span> <span class="pl-c1">cd</span>.<span class="pl-c1">members</span> mems
<span class="pl-k">on</span> <span class="pl-c1">mems</span>.<span class="pl-c1">memid</span> <span class="pl-k">=</span> <span class="pl-c1">bks</span>.<span class="pl-c1">memid</span>
<span class="pl-k">where</span>
<span class="pl-c1">mems</span>.<span class="pl-c1">firstname</span><span class="pl-k">=</span><span class="pl-s"><span class="pl-pds">'</span>David<span class="pl-pds">'</span></span>
<span class="pl-k">and</span> <span class="pl-c1">mems</span>.<span class="pl-c1">surname</span><span class="pl-k">=</span><span class="pl-s"><span class="pl-pds">'</span>Farrell<span class="pl-pds">'</span></span>; </pre></div>
<p>The most commonly used kind of join is the <code>INNER JOIN</code>. What this does is combine two tables based on a join expression - in this case, for each member id in the members table, we're looking for matching values in the bookings table. Where we find a match, a row combining the values for each table is returned. Note that we've given each table an <em>alias</em> (bks and mems). This is used for two reasons: firstly, it's convenient, and secondly we might join to the same table several times, requiring us to distinguish between columns from each different time the table was joined in.</p>
<p>Let's ignore our select and where clauses for now, and focus on what the <code>FROM</code> statement produces. In all our previous examples, <code>FROM</code> has just been a simple table. What is it now? Another table! This time, it's produced as a composite of bookings and members. You can see a subset of the output of the join below:</p>
<p><img src="Images/joinbefore.gif" alt=""></p>
<p>For each member in the members table, the join has found all the matching member ids in the bookings table. For each match, it's then produced a row combining the row from the members table, and the row from the bookings table.</p>
<p>Obviously, this is too much information on its own, and any useful question will want to filter it down. In our query, we use the start of the <code>SELECT</code> clause to pick columns, and the <code>WHERE</code> clause to pick rows, as illustrated below:</p>
<p><img src="Images/join1.png" alt=""></p>
<p>That's all we need to find David's bookings! In general, I encourage you to remember that the output of the <code>FROM</code> clause is essentially one big table that you then filter information out of. This may sound inefficient - but don't worry, under the covers the DB will be behaving much more intelligently :-).</p>
<p>One final note: there's two different syntaxes for inner joins. I've shown you the one I prefer, that I find more consistent with other join types. You'll commonly see a different syntax, shown below:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-c1">bks</span>.<span class="pl-c1">starttime</span>
<span class="pl-k">from</span>
<span class="pl-c1">cd</span>.<span class="pl-c1">bookings</span> bks,
<span class="pl-c1">cd</span>.<span class="pl-c1">members</span> mems
<span class="pl-k">where</span>
<span class="pl-c1">mems</span>.<span class="pl-c1">firstname</span><span class="pl-k">=</span><span class="pl-s"><span class="pl-pds">'</span>David<span class="pl-pds">'</span></span>
<span class="pl-k">and</span> <span class="pl-c1">mems</span>.<span class="pl-c1">surname</span><span class="pl-k">=</span><span class="pl-s"><span class="pl-pds">'</span>Farrell<span class="pl-pds">'</span></span>
<span class="pl-k">and</span> <span class="pl-c1">mems</span>.<span class="pl-c1">memid</span> <span class="pl-k">=</span> <span class="pl-c1">bks</span>.<span class="pl-c1">memid</span>;</pre></div>
<p>This is functionally exactly the same as the approved answer. If you feel more comfortable with this syntax, feel free to use it!</p>
<h3>
<a id="work-out-the-start-times-of-bookings-for-tennis-courts" class="anchor" href="#work-out-the-start-times-of-bookings-for-tennis-courts" aria-hidden="true"><span aria-hidden="true" class="octicon octicon-link"></span></a>Work out the start times of bookings for tennis courts</h3>
<p>How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time.</p>
<p>Expected results:</p>
<table>
<thead>
<tr>
<th>start</th>
<th>name</th>
</tr>
</thead>
<tbody>
<tr>
<td>2012-09-21 08:00:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 08:00:00</td>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>2012-09-21 09:30:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 10:00:00</td>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>2012-09-21 11:30:00</td>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>2012-09-21 12:00:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 13:30:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 14:00:00</td>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>2012-09-21 15:30:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 16:00:00</td>
<td>Tennis Court 2</td>
</tr>
<tr>
<td>2012-09-21 17:00:00</td>
<td>Tennis Court 1</td>
</tr>
<tr>
<td>2012-09-21 18:00:00</td>
<td>Tennis Court 2</td>
</tr>
</tbody>
</table>
<p>Answer:</p>
<div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> <span class="pl-c1">bks</span>.<span class="pl-c1">starttime</span> <span class="pl-k">as</span> start, <span class="pl-c1">facs</span>.<span class="pl-c1">name</span> <span class="pl-k">as</span> name
<span class="pl-k">from</span>
<span class="pl-c1">cd</span>.<span class="pl-c1">facilities</span> facs
<span class="pl-k">inner join</span> <span class="pl-c1">cd</span>.<span class="pl-c1">bookings</span> bks
<span class="pl-k">on</span> <span class="pl-c1">facs</span>.<span class="pl-c1">facid</span> <span class="pl-k">=</span> <span class="pl-c1">bks</span>.<span class="pl-c1">facid</span>
<span class="pl-k">where</span>
<span class="pl-c1">facs</span>.<span class="pl-c1">facid</span> <span class="pl-k">in</span> (<span class="pl-c1">0</span>,<span class="pl-c1">1</span>) <span class="pl-k">and</span>
<span class="pl-c1">bks</span>.<span class="pl-c1">starttime</span> <span class="pl-k">>=</span> <span class="pl-s"><span class="pl-pds">'</span>2012-09-21<span class="pl-pds">'</span></span> <span class="pl-k">and</span>
<span class="pl-c1">bks</span>.<span class="pl-c1">starttime</span> <span class="pl-k"><</span> <span class="pl-s"><span class="pl-pds">'</span>2012-09-22<span class="pl-pds">'</span></span>