-------------- 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
-------------- explain select * from Student --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14
-------------- 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
-------------- explain select * from Apply --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEApplyALLNULLNULLNULLNULL21
-------------- 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
-------------- explain select * from College --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLECollegeALLNULLNULLNULLNULL5
-------------- select * from College --------------
cNamestateenrollment
BerkeleyCA36000
CornellNY21000
IreneTX25000
MITMA10000
StanfordCA15000
-------------- select "" --------------
 
-------------- select "students with GPA > 3.7" --------------
students with GPA > 3.7
students with GPA > 3.7
-------------- explain select * from Student where (GPA > 3.7) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using where
-------------- select * from Student where (GPA > 3.7) --------------
sIDsNameGPAsizeHS
123Amy3.91000
456Doris3.91000
654Amy3.91000
678Fay3.8200
876Irene3.9400
987Helen3.7800
-------------- select "students with GPA > 3.7 and high school size < 1000" --------------
students with GPA > 3.7 and high school size < 1000
students with GPA > 3.7 and high school size < 1000
-------------- explain select * from Student where (GPA > 3.7) and (sizeHS < 1000) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using where
-------------- select * from Student where (GPA > 3.7) and (sizeHS < 1000) --------------
sIDsNameGPAsizeHS
678Fay3.8200
876Irene3.9400
987Helen3.7800
-------------- select "applications to Stanford and major = CS" --------------
applications to Stanford and major = CS
applications to Stanford and major = CS
-------------- explain select * from Apply where (cName = 'Stanford') and (major = 'CS') --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEApplyALLNULLNULLNULLNULL21Using where
-------------- select * from Apply where (cName = 'Stanford') and (major = 'CS') --------------
sIDcNamemajordecision
123StanfordCS1
876StanfordCS0
987StanfordCS1
-------------- select "" --------------
 
-------------- select "student ID and decision of applications" --------------
student ID and decision of applications
student ID and decision of applications
-------------- explain select sID, decision from Apply --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEApplyALLNULLNULLNULLNULL21
-------------- 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 "" --------------
 
-------------- select "ID and name of students with GPA > 3.7" --------------
ID and name of students with GPA > 3.7
ID and name of students with GPA > 3.7
-------------- explain select sID, sName from Student where (GPA > 3.7) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using where
-------------- select sID, sName from Student where (GPA > 3.7) --------------
sIDsName
123Amy
456Doris
654Amy
678Fay
876Irene
987Helen
-------------- select "" --------------
 
-------------- select "major and decision of applications" --------------
major and decision of applications
major and decision of applications
-------------- explain select major, decision from Apply order by major --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEApplyALLNULLNULLNULLNULL21Using filesort
-------------- select major, decision from Apply order by major --------------
majordecision
bioengineering0
bioengineering1
biology0
biology1
CS1
CS1
CS1
CS0
CS0
CS1
CS1
EE1
EE0
EE0
history0
history1
history0
history1
marine biology0
psychology1
psychology1
-------------- explain select distinct major, decision from Apply order by major --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEApplyALLNULLNULLNULLNULL21Using temporary; Using filesort
-------------- select distinct major, decision from Apply order by major --------------
majordecision
bioengineering0
bioengineering1
biology0
biology1
CS0
CS1
EE0
EE1
history0
history1
marine biology0
psychology1
-------------- select "" --------------
 
-------------- select "Drop" --------------
Drop
Drop
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------