1 -- The `Person` table is used to explain the most basic queries.
2 -- Note that `danforth` has no measurements.
9 insert into Person values('dyer', 'William', 'Dyer');
10 insert into Person values('pb', 'Frank', 'Pabodie');
11 insert into Person values('lake', 'Anderson', 'Lake');
12 insert into Person values('roe', 'Valentina', 'Roerich');
13 insert into Person values('danforth', 'Frank', 'Danforth');
15 -- The `Site` table is equally simple. Use it to explain the
16 -- difference between databases and spreadsheets: in a spreadsheet,
17 -- the lat/long of measurements would probably be duplicated.
24 insert into Site values('DR-1', -49.85, -128.57);
25 insert into Site values('DR-3', -47.15, -126.72);
26 insert into Site values('MSK-4', -48.87, -123.40);
28 -- `Visited` is an enhanced `join` table: it connects to the lat/long
29 -- of specific measurements, and also provides their dates.
30 -- Note that #752 is missing a date; we use this to talk about NULL.
37 insert into Visited values(619, 'DR-1', '1927-02-08');
38 insert into Visited values(622, 'DR-1', '1927-02-10');
39 insert into Visited values(734, 'DR-3', '1939-01-07');
40 insert into Visited values(735, 'DR-3', '1930-01-12');
41 insert into Visited values(751, 'DR-3', '1930-02-26');
42 insert into Visited values(752, 'DR-3', null);
43 insert into Visited values(837, 'MSK-4', '1932-01-14');
44 insert into Visited values(844, 'DR-1', '1932-03-22');
46 -- The `Survey` table is the actual readings. Join it with `Site` to
47 -- get lat/long, and with `Visited` to get dates (except for #752).
48 -- Note that Roerich's salinity measurements are an order of magnitude
49 -- too large (use this to talk about data cleanup). Note also that
50 -- there are two cases where we don't know who took the measurement,
51 -- and that in most cases we don't have an entry (null or not) for the
60 insert into Survey values(619, 'dyer', 'rad', 9.82);
61 insert into Survey values(619, 'dyer', 'sal', 0.13);
62 insert into Survey values(622, 'dyer', 'rad', 7.80);
63 insert into Survey values(622, 'dyer', 'sal', 0.09);
64 insert into Survey values(734, 'pb', 'rad', 8.41);
65 insert into Survey values(734, 'lake', 'sal', 0.05);
66 insert into Survey values(734, 'pb', 'temp', -21.50);
67 insert into Survey values(735, 'pb', 'rad', 7.22);
68 insert into Survey values(735, null, 'sal', 0.06);
69 insert into Survey values(735, null, 'temp', -26.00);
70 insert into Survey values(751, 'pb', 'rad', 4.35);
71 insert into Survey values(751, 'pb', 'temp', -18.50);
72 insert into Survey values(751, 'lake', 'sal', 0.10);
73 insert into Survey values(752, 'lake', 'rad', 2.19);
74 insert into Survey values(752, 'lake', 'sal', 0.09);
75 insert into Survey values(752, 'lake', 'temp', -16.00);
76 insert into Survey values(752, 'roe', 'sal', 41.60);
77 insert into Survey values(837, 'lake', 'rad', 1.46);
78 insert into Survey values(837, 'lake', 'sal', 0.21);
79 insert into Survey values(837, 'roe', 'sal', 22.50);
80 insert into Survey values(844, 'roe', 'rad', 11.25);