-------------- 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 "applications of students who applied to Cornell with a GPA < 3.6" --------------
applications of students who applied to Cornell with a GPA < 3.6
applications of students who applied to Cornell with a GPA < 3.6
-------------- select count(*) from Student inner join Apply using (sID) where (cName = 'Cornell') and (GPA < 3.6) --------------
count(*)
5
-------------- select * from Student inner join Apply using (sID) where (cName = 'Cornell') and (GPA < 3.6) --------------
sIDsNameGPAsizeHScNamemajordecision
345Craig3.5500Cornellbioengineering0
345Craig3.5500CornellCS1
345Craig3.5500CornellEE0
765Jay2.91500Cornellhistory0
765Jay2.91500Cornellpsychology1
-------------- select count(*) from Apply where (cName = 'Cornell') and sID in (select sID from Student where GPA < 3.6) --------------
count(*)
5
-------------- select * from Apply where (cName = 'Cornell') and sID in (select sID from Student where GPA < 3.6) --------------
sIDcNamemajordecision
345Cornellbioengineering0
345CornellCS1
345CornellEE0
765Cornellhistory0
765Cornellpsychology1
-------------- select "" --------------
 
-------------- select "change those applications from Cornell to UT" --------------
change those applications from Cornell to UT
change those applications from Cornell to UT
-------------- select "and have them accepted" --------------
and have them accepted
and have them accepted
-------------- update Apply set cName = 'UT', decision = true where (cName = 'Cornell') and sID in (select sID from Student where GPA < 3.6) -------------- -------------- select count(*) from Student inner join Apply using (sID) where (cName = 'UT') and (GPA < 3.6) --------------
count(*)
5
-------------- select * from Student inner join Apply using (sID) where (cName = 'UT') and (GPA < 3.6) --------------
sIDsNameGPAsizeHScNamemajordecision
345Craig3.5500UTbioengineering1
345Craig3.5500UTCS1
345Craig3.5500UTEE1
765Jay2.91500UThistory1
765Jay2.91500UTpsychology1
-------------- select * from Apply where (cName = 'UT') and sID in (select sID from Student where GPA < 3.6) --------------
sIDcNamemajordecision
345UTbioengineering1
345UTCS1
345UTEE1
765UThistory1
765UTpsychology1
-------------- select * from Apply where (cName = 'UT') and sID in (select sID from Student where GPA < 3.6) --------------
sIDcNamemajordecision
345UTbioengineering1
345UTCS1
345UTEE1
765UThistory1
765UTpsychology1
-------------- select "" --------------
 
-------------- select "applications of students with the highest GPA who applied to CS" --------------
applications of students with the highest GPA who applied to CS
applications of students with the highest GPA who applied to CS
-------------- select count(*) from Student inner join Apply using (sID) where major = 'CS' order by GPA desc --------------
count(*)
7
-------------- select * from Student inner join Apply using (sID) where major = 'CS' order by GPA desc --------------
sIDsNameGPAsizeHScNamemajordecision
123Amy3.91000BerkeleyCS1
123Amy3.91000StanfordCS1
876Irene3.9400StanfordCS0
987Helen3.7800BerkeleyCS1
987Helen3.7800StanfordCS1
345Craig3.5500UTCS1
543Craig3.42000MITCS0
-------------- select * from Student inner join Apply using (sID) where (major = 'CS') and sID in (select sID from Student where GPA >= all (select GPA from Student where sID in (select sID from Apply where major = 'CS'))) --------------
sIDsNameGPAsizeHScNamemajordecision
123Amy3.91000BerkeleyCS1
123Amy3.91000StanfordCS1
876Irene3.9400StanfordCS0
-------------- select "" --------------
 
-------------- select "change those applications from CS to CSE" --------------
change those applications from CS to CSE
change those applications from CS to CSE
-------------- create temporary table T as (select sID from Student where GPA >= all (select GPA from Student where sID in (select sID from Apply where major = 'CS'))) -------------- -------------- update Apply set major = 'CSE' where (major = 'CS') and sID in (select * from T) -------------- -------------- select count(*) from Student inner join Apply using (sID) where major = 'CSE' order by GPA desc --------------
count(*)
3
-------------- select * from Student inner join Apply using (sID) where major = 'CSE' order by GPA desc --------------
sIDsNameGPAsizeHScNamemajordecision
123Amy3.91000BerkeleyCSE1
123Amy3.91000StanfordCSE1
876Irene3.9400StanfordCSE0
-------------- select "" --------------
 
-------------- select "change every student to have the highest GPA" --------------
change every student to have the highest GPA
change every student to have the highest GPA
-------------- select "and smallest school size" --------------
and smallest school size
and smallest school size
-------------- 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
-------------- create temporary table R as select max(GPA) from Student -------------- -------------- create temporary table S as select min(sizeHS) from Student -------------- -------------- update Student set GPA = (select * from R), sizeHS = (select * from S) -------------- -------------- select count(*) from Student --------------
count(*)
14
-------------- select * from Student --------------
sIDsNameGPAsizeHS
123Amy3.9200
234Bob3.9200
320Lori3.9200
345Craig3.9200
432Kevin3.9200
456Doris3.9200
543Craig3.9200
567Edward3.9200
654Amy3.9200
678Fay3.9200
765Jay3.9200
789Gary3.9200
876Irene3.9200
987Helen3.9200
-------------- select "" --------------
 
-------------- select "accept all students" --------------
accept all students
accept all students
-------------- select count(*) from Apply --------------
count(*)
21
-------------- select * from Apply --------------
sIDcNamemajordecision
123BerkeleyCSE1
123CornellEE1
123StanfordCSE1
123StanfordEE0
234Berkeleybiology0
321MIThistory0
321MITpsychology1
345UTbioengineering1
345UTCS1
345UTEE1
345MITbioengineering1
543MITCS0
678Stanfordhistory1
765UThistory1
765UTpsychology1
765Stanfordhistory1
876MITbiology1
876MITmarine biology0
876StanfordCSE0
987BerkeleyCS1
987StanfordCS1
-------------- update Apply set decision = true -------------- -------------- select count(*) from Apply --------------
count(*)
21
-------------- select * from Apply --------------
sIDcNamemajordecision
123BerkeleyCSE1
123CornellEE1
123StanfordCSE1
123StanfordEE1
234Berkeleybiology1
321MIThistory1
321MITpsychology1
345UTbioengineering1
345UTCS1
345UTEE1
345MITbioengineering1
543MITCS1
678Stanfordhistory1
765UThistory1
765UTpsychology1
765Stanfordhistory1
876MITbiology1
876MITmarine biology1
876StanfordCSE1
987BerkeleyCS1
987StanfordCS1
-------------- select "" --------------
 
-------------- select "Drop" --------------
Drop
Drop
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------