-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College -------------- -------------- create table Student ( sID int, sName text, GPA float, sizeHS int) -------------- -------------- create table Apply ( sID int, cName text, major text, decision boolean) -------------- -------------- create table College ( cName text, state char(2), enrollment int) -------------- -------------- insert into Student values (123, 'Amy', 3.9, 1000) -------------- -------------- insert into Student values (234, 'Bob', 3.6, 1500) -------------- -------------- insert into Student values (320, 'Lori', null, 2500) -------------- -------------- insert into Student values (345, 'Craig', 3.5, 500) -------------- -------------- insert into Student values (432, 'Kevin', null, 1500) -------------- -------------- insert into Student values (456, 'Doris', 3.9, 1000) -------------- -------------- insert into Student values (543, 'Craig', 3.4, 2000) -------------- -------------- insert into Student values (567, 'Edward', 2.9, 2000) -------------- -------------- insert into Student values (654, 'Amy', 3.9, 1000) -------------- -------------- insert into Student values (678, 'Fay', 3.8, 200) -------------- -------------- insert into Student values (765, 'Jay', 2.9, 1500) -------------- -------------- insert into Student values (789, 'Gary', 3.4, 800) -------------- -------------- insert into Student values (876, 'Irene', 3.9, 400) -------------- -------------- insert into Student values (987, 'Helen', 3.7, 800) -------------- -------------- insert into Apply values (123, 'Berkeley', 'CS', true) -------------- -------------- insert into Apply values (123, 'Cornell', 'EE', true) -------------- -------------- insert into Apply values (123, 'Stanford', 'CS', true) -------------- -------------- insert into Apply values (123, 'Stanford', 'EE', false) -------------- -------------- insert into Apply values (234, 'Berkeley', 'biology', false) -------------- -------------- insert into Apply values (321, 'MIT', 'history', false) -------------- -------------- insert into Apply values (321, 'MIT', 'psychology', true) -------------- -------------- insert into Apply values (345, 'Cornell', 'bioengineering', false) -------------- -------------- insert into Apply values (345, 'Cornell', 'CS', true) -------------- -------------- insert into Apply values (345, 'Cornell', 'EE', false) -------------- -------------- insert into Apply values (345, 'MIT', 'bioengineering', true) -------------- -------------- insert into Apply values (543, 'MIT', 'CS', false) -------------- -------------- insert into Apply values (678, 'Stanford', 'history', true) -------------- -------------- insert into Apply values (765, 'Cornell', 'history', false) -------------- -------------- insert into Apply values (765, 'Cornell', 'psychology', true) -------------- -------------- insert into Apply values (765, 'Stanford', 'history', true) -------------- -------------- insert into Apply values (876, 'MIT', 'biology', true) -------------- -------------- insert into Apply values (876, 'MIT', 'marine biology', false) -------------- -------------- insert into Apply values (876, 'Stanford', 'CS', false) -------------- -------------- insert into Apply values (987, 'Berkeley', 'CS', true) -------------- -------------- insert into Apply values (987, 'Stanford', 'CS', true) -------------- -------------- insert into College values ('Berkeley', 'CA', 36000) -------------- -------------- insert into College values ('Cornell', 'NY', 21000) -------------- -------------- insert into College values ('Irene', 'TX', 25000) -------------- -------------- insert into College values ('MIT', 'MA', 10000) -------------- -------------- insert into College values ('Stanford', 'CA', 15000) -------------- -------------- select * from Student where (GPA > 3.7) --------------
sIDsNameGPAsizeHS
123Amy3.91000
456Doris3.91000
654Amy3.91000
678Fay3.8200
876Irene3.9400
987Helen3.7800
-------------- select * from Student where (GPA > 3.7) and (sizeHS < 1000) --------------
sIDsNameGPAsizeHS
678Fay3.8200
876Irene3.9400
987Helen3.7800
-------------- select * from Apply where (cName = 'Stanford') and (major = 'CS') --------------
sIDcNamemajordecision
123StanfordCS1
876StanfordCS0
987StanfordCS1
-------------- select sID, decision from Apply --------------
sIDdecision
1231
1231
1231
1230
2340
3210
3211
3450
3451
3450
3451
5430
6781
7650
7651
7651
8761
8760
8760
9871
9871
-------------- select sID, sName from Student where (GPA > 3.7) --------------
sIDsName
123Amy
456Doris
654Amy
678Fay
876Irene
987Helen
-------------- select major, decision from Apply --------------
majordecision
CS1
EE1
CS1
EE0
biology0
history0
psychology1
bioengineering0
CS1
EE0
bioengineering1
CS0
history1
history0
psychology1
history1
biology1
marine biology0
CS0
CS1
CS1
-------------- select distinct major, decision from Apply --------------
majordecision
CS1
EE1
EE0
biology0
history0
psychology1
bioengineering0
bioengineering1
CS0
history1
biology1
marine biology0
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------