

-- CS 236 Snoopy Database


CREATE TABLE SNAP (
S varchar,
N varchar,
A varchar,
P varchar
);

INSERT INTO "SNAP" VALUES('12345','C. Brown','12 Apple St.','555-1234');
INSERT INTO "SNAP" VALUES('67890','L. Van Pelt','34 Pear Ave.','555-5678');
INSERT INTO "SNAP" VALUES('22222','P. Patty','56 Grape Blvd.','555-9999');
INSERT INTO "SNAP" VALUES('33333','Snoopy','12 Apple St.','555-1234');

CREATE TABLE CR (
C varchar,
R varchar
);

INSERT INTO "CR" VALUES('CS101','Turing Aud.');
INSERT INTO "CR" VALUES('EE200','25 Ohm Hall');
INSERT INTO "CR" VALUES('PH100','Newton Lab.');

CREATE TABLE CDH (
C varchar,
D varchar,
H varchar
);

INSERT INTO "CDH" VALUES('CS101','M','9AM');
INSERT INTO "CDH" VALUES('CS101','W','9AM');
INSERT INTO "CDH" VALUES('CS101','F','9AM');
INSERT INTO "CDH" VALUES('EE200','Tu','10AM');
INSERT INTO "CDH" VALUES('EE200','W','1PM');
INSERT INTO "CDH" VALUES('EE200','Th','10AM');
INSERT INTO "CDH" VALUES('PH100','Tu','11AM');

CREATE TABLE CSG (
C varchar,
S varchar,
G varchar
);

INSERT INTO "CSG" VALUES('CS101','12345','A');
INSERT INTO "CSG" VALUES('CS101','67890','B');
INSERT INTO "CSG" VALUES('EE200','12345','C');
INSERT INTO "CSG" VALUES('EE200','22222','B+');
INSERT INTO "CSG" VALUES('EE200','33333','B');
INSERT INTO "CSG" VALUES('CS101','33333','A-');
INSERT INTO "CSG" VALUES('PH100','67890','C+');

CREATE TABLE CP (
C varchar,
P varchar
);

INSERT INTO "CP" VALUES('CS101','CS100');
INSERT INTO "CP" VALUES('EE200','EE005');
INSERT INTO "CP" VALUES('EE200','CS100');
INSERT INTO "CP" VALUES('CS120','CS101');
INSERT INTO "CP" VALUES('CS121','CS120');
INSERT INTO "CP" VALUES('CS205','CS101');
INSERT INTO "CP" VALUES('CS206','CS121');
INSERT INTO "CP" VALUES('CS206','CS205');


.header on
.mode column


select P from CP where C='EE200';

select D, H from CDH where C='CS101';

select D, H, R from CDH natural join CR where CDH.C='EE200';


