-------------- 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 applied to two or more majors ***" --------------
*** students who applied to two or more majors ***
*** students who applied to two or more majors ***
-------------- select sID, count(distinct major) from Apply group by sID having count(distinct major) > 2 --------------
sIDcount(distinct major)
3453
8763
-------------- select "*** delete those students from Student ***" --------------
*** delete those students from Student ***
*** delete those students from Student ***
-------------- select count(*) from Student --------------
count(*)
14
-------------- 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
-------------- delete from Student where sID in (select sID from Apply group by sID having count(distinct major) > 2) -------------- -------------- select count(*) from Student --------------
count(*)
12
-------------- select * from Student --------------
sIDsNameGPAsizeHS
123Amy3.91000
234Bob3.61500
320LoriNULL2500
432KevinNULL1500
456Doris3.91000
543Craig3.42000
567Edward2.92000
654Amy3.91000
678Fay3.8200
765Jay2.91500
789Gary3.4800
987Helen3.7800
-------------- select "*** delete those students from Apply ***" --------------
*** delete those students from Apply ***
*** delete those students from Apply ***
-------------- select "create temporary table first" --------------
create temporary table first
create temporary table first
-------------- create temporary table T as select sID from Apply group by sID having count(distinct major) > 2 -------------- -------------- select count(*) from T --------------
count(*)
2
-------------- select * from T --------------
sID
345
876
-------------- select count(*) from Apply where sID in (select * from T) --------------
count(*)
7
-------------- select * from Apply where sID in (select * from T) --------------
sIDcNamemajordecision
345Cornellbioengineering0
345CornellCS1
345CornellEE0
345MITbioengineering1
876MITbiology1
876MITmarine biology0
876StanfordCS0
-------------- 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
-------------- delete from Apply where sID in (select * from T) -------------- -------------- select count(*) from Apply --------------
count(*)
14
-------------- select * from Apply --------------
sIDcNamemajordecision
123BerkeleyCS1
123CornellEE1
123StanfordCS1
123StanfordEE0
234Berkeleybiology0
321MIThistory0
321MITpsychology1
543MITCS0
678Stanfordhistory1
765Cornellhistory0
765Cornellpsychology1
765Stanfordhistory1
987BerkeleyCS1
987StanfordCS1
-------------- select "*** colleges without CS applications ***" --------------
*** colleges without CS applications ***
*** colleges without CS applications ***
-------------- select count(*) from College where cName not in (select cName from Apply where major = 'CS') --------------
count(*)
2
-------------- select cName from College where cName not in (select cName from Apply where major = 'CS') --------------
cName
Cornell
Irene
-------------- select "*** delete those colleges from College ***" --------------
*** delete those colleges from College ***
*** delete those colleges from College ***
-------------- select count(*) from College --------------
count(*)
5
-------------- select * from College --------------
cNamestateenrollment
BerkeleyCA36000
CornellNY21000
IreneTX25000
MITMA10000
StanfordCA15000
-------------- delete from College where cName not in (select cName from Apply where major = 'CS') -------------- -------------- select count(*) from College --------------
count(*)
3
-------------- select * from College --------------
cNamestateenrollment
BerkeleyCA36000
MITMA10000
StanfordCA15000
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------