-------------- select "Drop" --------------
Drop
Drop
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College -------------- -------------- select "" --------------
 
-------------- select "Create" --------------
Create
Create
-------------- 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) -------------- -------------- select "" --------------
 
-------------- select "Insert" --------------
Insert
Insert
-------------- 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 "" --------------
 
-------------- select "Select" --------------
Select
Select
-------------- 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 "" --------------
 
-------------- 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 "" --------------
 
-------------- 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 "" --------------
 
-------------- 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 "" --------------
 
-------------- 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 * from College where cName not in (select cName from Apply where major = 'CS') --------------
cNamestateenrollment
CornellNY21000
IreneTX25000
-------------- select "" --------------
 
-------------- 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
-------------- select "" --------------
 
-------------- select "Drop" --------------
Drop
Drop
-------------- drop table if exists College -------------- -------------- drop table if exists Student -------------- -------------- drop table if exists Apply --------------