-------------- 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 --------------
sIDsNameGPAsizeHS
123Amy3.91000
234Bob3.61500
320LoriNULL2500
345Craig3.5500
432KevinNULL1500
456Doris3.91000
543Craig3.42000
567Edward2.92000
654Amy3.91000
678Fay3.8200
765Jay2.91500
789Gary3.4800
876Irene3.9400
987Helen3.7800
-------------- select * from Apply --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
234Berkeleybiology0
321MIThistory0
321MITpsychology1
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
543MITCS0
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
876MITbiology1
876MITmarine biology0
876StanfordCS0
987BerkeleyCS1
987StanfordCS1
-------------- select * from College --------------
cNamestateenrollment
BerkeleyCA36000
CornellNY21000
IreneTX25000
MITMA10000
StanfordCA15000
-------------- select "*** students who did not apply anywhere ***" --------------
*** students who did not apply anywhere ***
*** students who did not apply anywhere ***
-------------- select count(*) from Student where sID not in (select sID from Apply) --------------
count(*)
6
-------------- select * from Student where sID not in (select sID from Apply) --------------
sIDsNameGPAsizeHS
320LoriNULL2500
432KevinNULL1500
456Doris3.91000
567Edward2.92000
654Amy3.91000
789Gary3.4800
-------------- select "*** have those students apply to Carnegie Mellon in CS ***" --------------
*** have those students apply to Carnegie Mellon in CS ***
*** have those students apply to Carnegie Mellon in CS ***
-------------- select count(*) from Apply --------------
count(*)
21
-------------- select * from Apply --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
234Berkeleybiology0
321MIThistory0
321MITpsychology1
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
543MITCS0
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
876MITbiology1
876MITmarine biology0
876StanfordCS0
987BerkeleyCS1
987StanfordCS1
-------------- insert into Apply select sID, 'Carnegie Mellon', 'CS', null from Student where sID not in (select sID from Apply) -------------- -------------- select count(*) from Apply --------------
count(*)
27
-------------- select * from Apply order by sID --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
234Berkeleybiology0
320Carnegie MellonCSNULL
321MIThistory0
321MITpsychology1
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
432Carnegie MellonCSNULL
456Carnegie MellonCSNULL
543MITCS0
567Carnegie MellonCSNULL
654Carnegie MellonCSNULL
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
789Carnegie MellonCSNULL
876MITbiology1
876MITmarine biology0
876StanfordCS0
987BerkeleyCS1
987StanfordCS1
-------------- select "*** students who applied to EE and were rejected ***" --------------
*** students who applied to EE and were rejected ***
*** students who applied to EE and were rejected ***
-------------- select count(*) from Student where sID in (select sID from Apply where (major = 'EE') and (decision = false)) --------------
count(*)
2
-------------- select sID from Student where sID in (select sID from Apply where (major = 'EE') and (decision = false)) --------------
sID
123
345
-------------- select distinct sID from Apply where (major = 'EE') and (decision = false) --------------
sID
123
345
-------------- select "*** have those students apply to Carnegie Mellon in EE ***" --------------
*** have those students apply to Carnegie Mellon in EE ***
*** have those students apply to Carnegie Mellon in EE ***
-------------- select "*** and be accepted ***" --------------
*** and be accepted ***
*** and be accepted ***
-------------- select count(*) from Apply --------------
count(*)
27
-------------- select * from Apply order by sID --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
234Berkeleybiology0
320Carnegie MellonCSNULL
321MIThistory0
321MITpsychology1
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
432Carnegie MellonCSNULL
456Carnegie MellonCSNULL
543MITCS0
567Carnegie MellonCSNULL
654Carnegie MellonCSNULL
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
789Carnegie MellonCSNULL
876MITbiology1
876MITmarine biology0
876StanfordCS0
987BerkeleyCS1
987StanfordCS1
-------------- insert into Apply select sID, 'Carnegie Mellon', 'EE', true from Student where sID in (select sID from Apply where (major = 'EE') and (decision = false)) -------------- -------------- insert into Apply select sID, 'Carnegie Mellon', 'EE', true from (select distinct sID from Apply where (major = 'EE') and (decision = false)) as T -------------- -------------- select count(*) from Apply --------------
count(*)
31
-------------- select * from Apply order by sID --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
123Carnegie MellonEE1
123Carnegie MellonEE1
234Berkeleybiology0
320Carnegie MellonCSNULL
321MIThistory0
321MITpsychology1
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
345Carnegie MellonEE1
345Carnegie MellonEE1
432Carnegie MellonCSNULL
456Carnegie MellonCSNULL
543MITCS0
567Carnegie MellonCSNULL
654Carnegie MellonCSNULL
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
789Carnegie MellonCSNULL
876MITbiology1
876MITmarine biology0
876StanfordCS0
987BerkeleyCS1
987StanfordCS1
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------