Skip to content

Commit

Permalink
Adde drop table if exist before each create to avoid having the data …
Browse files Browse the repository at this point in the history 
…loaded repeatedly
  • Loading branch information
@mcollinsacis
mcollinsacis committed May 6, 2015
1 parent 5dd6ac3 commit 507a382
Showing 1 changed file with 58 additions and 54 deletions.
112 changes: 58 additions & 54 deletions code/gen-survey-database.sql
View file
Original file line number Diff line number Diff line change
@@ -1,47 +1,50 @@
-- The `Person` table is used to explain the most basic queries.
-- Note that `danforth` has no measurements.
CREATE TABLE Person(
ident TEXT,
personal TEXT,
family TEXT
drop table if exists Person;
create table Person(
ident text,
personal text,
family text
);

INSERT INTO Person VALUES('dyer', 'William', 'Dyer');
INSERT INTO Person VALUES('pb', 'Frank', 'Pabodie');
INSERT INTO Person VALUES('lake', 'Anderson', 'Lake');
INSERT INTO Person VALUES('roe', 'Valentina', 'Roerich');
INSERT INTO Person VALUES('danforth', 'Frank', 'Danforth');
insert into Person values('dyer', 'William', 'Dyer');
insert into Person values('pb', 'Frank', 'Pabodie');
insert into Person values('lake', 'Anderson', 'Lake');
insert into Person values('roe', 'Valentina', 'Roerich');
insert into Person values('danforth', 'Frank', 'Danforth');

-- The `Site` table is equally simple. Use it to explain the
-- difference between databases and spreadsheets: in a spreadsheet,
-- the lat/long of measurements would probably be duplicated.
CREATE TABLE Site(
name TEXT,
lat REAL,
long REAL
drop table if exists Site;
create table Site(
name text,
lat real,
long real
);

INSERT INTO Site VALUES('DR-1', -49.85, -128.57);
INSERT INTO Site VALUES('DR-3', -47.15, -126.72);
INSERT INTO Site VALUES('MSK-4', -48.87, -123.40);
insert into Site values('DR-1', -49.85, -128.57);
insert into Site values('DR-3', -47.15, -126.72);
insert into Site values('MSK-4', -48.87, -123.40);

-- `Visited` is an enhanced `join` table: it connects to the lat/long
-- of specific measurements, and also provides their dates.
-- Note that #752 is missing a date; we use this to talk about NULL.
CREATE TABLE Visited(
ident INTEGER,
site TEXT,
dated TEXT
drop table if exists Visited;
create table Visited(
ident integer,
site text,
dated text
);

INSERT INTO Visited VALUES(619, 'DR-1', '1927-02-08');
INSERT INTO Visited VALUES(622, 'DR-1', '1927-02-10');
INSERT INTO Visited VALUES(734, 'DR-3', '1930-01-07');
INSERT INTO Visited VALUES(735, 'DR-3', '1930-01-12');
INSERT INTO Visited VALUES(751, 'DR-3', '1930-02-26');
INSERT INTO Visited VALUES(752, 'DR-3', null);
INSERT INTO Visited VALUES(837, 'MSK-4', '1932-01-14');
INSERT INTO Visited VALUES(844, 'DR-1', '1932-03-22');
insert into Visited values(619, 'DR-1', '1927-02-08');
insert into Visited values(622, 'DR-1', '1927-02-10');
insert into Visited values(734, 'DR-3', '1939-01-07');
insert into Visited values(735, 'DR-3', '1930-01-12');
insert into Visited values(751, 'DR-3', '1930-02-26');
insert into Visited values(752, 'DR-3', null);
insert into Visited values(837, 'MSK-4', '1932-01-14');
insert into Visited values(844, 'DR-1', '1932-03-22');

-- The `Survey` table is the actual readings. Join it with `Site` to
-- get lat/long, and with `Visited` to get dates (except for #752).
Expand All @@ -50,31 +53,32 @@ INSERT INTO Visited VALUES(844, 'DR-1', '1932-03-22');
-- there are two cases where we don't know who took the measurement,
-- and that in most cases we don't have an entry (null or not) for the
-- temperature.
CREATE TABLE Survey(
taken INTEGER,
person TEXT,
quant TEXT,
reading REAL
drop table if exists Survey;
create table Survey(
taken integer,
person text,
quant text,
reading real
);

INSERT INTO Survey VALUES(619, 'dyer', 'rad', 9.82);
INSERT INTO Survey VALUES(619, 'dyer', 'sal', 0.13);
INSERT INTO Survey VALUES(622, 'dyer', 'rad', 7.80);
INSERT INTO Survey VALUES(622, 'dyer', 'sal', 0.09);
INSERT INTO Survey VALUES(734, 'pb', 'rad', 8.41);
INSERT INTO Survey VALUES(734, 'lake', 'sal', 0.05);
INSERT INTO Survey VALUES(734, 'pb', 'temp', -21.50);
INSERT INTO Survey VALUES(735, 'pb', 'rad', 7.22);
INSERT INTO Survey VALUES(735, null, 'sal', 0.06);
INSERT INTO Survey VALUES(735, null, 'temp', -26.00);
INSERT INTO Survey VALUES(751, 'pb', 'rad', 4.35);
INSERT INTO Survey VALUES(751, 'pb', 'temp', -18.50);
INSERT INTO Survey VALUES(751, 'lake', 'sal', 0.10);
INSERT INTO Survey VALUES(752, 'lake', 'rad', 2.19);
INSERT INTO Survey VALUES(752, 'lake', 'sal', 0.09);
INSERT INTO Survey VALUES(752, 'lake', 'temp', -16.00);
INSERT INTO Survey VALUES(752, 'roe', 'sal', 41.60);
INSERT INTO Survey VALUES(837, 'lake', 'rad', 1.46);
INSERT INTO Survey VALUES(837, 'lake', 'sal', 0.21);
INSERT INTO Survey VALUES(837, 'roe', 'sal', 22.50);
INSERT INTO Survey VALUES(844, 'roe', 'rad', 11.25);
insert into Survey values(619, 'dyer', 'rad', 9.82);
insert into Survey values(619, 'dyer', 'sal', 0.13);
insert into Survey values(622, 'dyer', 'rad', 7.80);
insert into Survey values(622, 'dyer', 'sal', 0.09);
insert into Survey values(734, 'pb', 'rad', 8.41);
insert into Survey values(734, 'lake', 'sal', 0.05);
insert into Survey values(734, 'pb', 'temp', -21.50);
insert into Survey values(735, 'pb', 'rad', 7.22);
insert into Survey values(735, null, 'sal', 0.06);
insert into Survey values(735, null, 'temp', -26.00);
insert into Survey values(751, 'pb', 'rad', 4.35);
insert into Survey values(751, 'pb', 'temp', -18.50);
insert into Survey values(751, 'lake', 'sal', 0.10);
insert into Survey values(752, 'lake', 'rad', 2.19);
insert into Survey values(752, 'lake', 'sal', 0.09);
insert into Survey values(752, 'lake', 'temp', -16.00);
insert into Survey values(752, 'roe', 'sal', 41.60);
insert into Survey values(837, 'lake', 'rad', 1.46);
insert into Survey values(837, 'lake', 'sal', 0.21);
insert into Survey values(837, 'roe', 'sal', 22.50);
insert into Survey values(844, 'roe', 'rad', 11.25);

0 comments on commit 507a382

Please sign in to comment.