-------------- 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 "Student cross join Apply" --------------
Student cross join Apply
Student cross join Apply
-------------- explain select * from Student cross join Apply order by Student.sID --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using temporary; Using filesort
1SIMPLEApplyALLNULLNULLNULLNULL21Using join buffer
-------------- select * from Student cross join Apply order by Student.sID --------------
sIDsNameGPAsizeHSsIDcNamemajordecision
123Amy3.91000123BerkeleyCS1
123Amy3.91000123CornellEE1
123Amy3.91000123StanfordCS1
123Amy3.91000123StanfordEE0
123Amy3.91000234Berkeleybiology0
123Amy3.91000321MIThistory0
123Amy3.91000321MITpsychology1
123Amy3.91000345Cornellbioengineering0
123Amy3.91000345CornellCS1
123Amy3.91000345CornellEE0
123Amy3.91000345MITbioengineering1
123Amy3.91000543MITCS0
123Amy3.91000678Stanfordhistory1
123Amy3.91000765Cornellhistory0
123Amy3.91000765Cornellpsychology1
123Amy3.91000765Stanfordhistory1
123Amy3.91000876MITbiology1
123Amy3.91000876MITmarine biology0
123Amy3.91000876StanfordCS0
123Amy3.91000987BerkeleyCS1
123Amy3.91000987StanfordCS1
234Bob3.61500123BerkeleyCS1
234Bob3.61500123CornellEE1
234Bob3.61500123StanfordCS1
234Bob3.61500123StanfordEE0
234Bob3.61500234Berkeleybiology0
234Bob3.61500321MIThistory0
234Bob3.61500321MITpsychology1
234Bob3.61500345Cornellbioengineering0
234Bob3.61500345CornellCS1
234Bob3.61500345CornellEE0
234Bob3.61500345MITbioengineering1
234Bob3.61500543MITCS0
234Bob3.61500678Stanfordhistory1
234Bob3.61500765Cornellhistory0
234Bob3.61500765Cornellpsychology1
234Bob3.61500765Stanfordhistory1
234Bob3.61500876MITbiology1
234Bob3.61500876MITmarine biology0
234Bob3.61500876StanfordCS0
234Bob3.61500987BerkeleyCS1
234Bob3.61500987StanfordCS1
320LoriNULL2500123BerkeleyCS1
320LoriNULL2500123CornellEE1
320LoriNULL2500123StanfordCS1
320LoriNULL2500123StanfordEE0
320LoriNULL2500234Berkeleybiology0
320LoriNULL2500321MIThistory0
320LoriNULL2500321MITpsychology1
320LoriNULL2500345Cornellbioengineering0
320LoriNULL2500345CornellCS1
320LoriNULL2500345CornellEE0
320LoriNULL2500345MITbioengineering1
320LoriNULL2500543MITCS0
320LoriNULL2500678Stanfordhistory1
320LoriNULL2500765Cornellhistory0
320LoriNULL2500765Cornellpsychology1
320LoriNULL2500765Stanfordhistory1
320LoriNULL2500876MITbiology1
320LoriNULL2500876MITmarine biology0
320LoriNULL2500876StanfordCS0
320LoriNULL2500987BerkeleyCS1
320LoriNULL2500987StanfordCS1
345Craig3.5500123BerkeleyCS1
345Craig3.5500123CornellEE1
345Craig3.5500123StanfordCS1
345Craig3.5500123StanfordEE0
345Craig3.5500234Berkeleybiology0
345Craig3.5500321MIThistory0
345Craig3.5500321MITpsychology1
345Craig3.5500345Cornellbioengineering0
345Craig3.5500345CornellCS1
345Craig3.5500345CornellEE0
345Craig3.5500345MITbioengineering1
345Craig3.5500543MITCS0
345Craig3.5500678Stanfordhistory1
345Craig3.5500765Cornellhistory0
345Craig3.5500765Cornellpsychology1
345Craig3.5500765Stanfordhistory1
345Craig3.5500876MITbiology1
345Craig3.5500876MITmarine biology0
345Craig3.5500876StanfordCS0
345Craig3.5500987BerkeleyCS1
345Craig3.5500987StanfordCS1
432KevinNULL1500123BerkeleyCS1
432KevinNULL1500123CornellEE1
432KevinNULL1500123StanfordCS1
432KevinNULL1500123StanfordEE0
432KevinNULL1500234Berkeleybiology0
432KevinNULL1500321MIThistory0
432KevinNULL1500321MITpsychology1
432KevinNULL1500345Cornellbioengineering0
432KevinNULL1500345CornellCS1
432KevinNULL1500345CornellEE0
432KevinNULL1500345MITbioengineering1
432KevinNULL1500543MITCS0
432KevinNULL1500678Stanfordhistory1
432KevinNULL1500765Cornellhistory0
432KevinNULL1500765Cornellpsychology1
432KevinNULL1500765Stanfordhistory1
432KevinNULL1500876MITbiology1
432KevinNULL1500876MITmarine biology0
432KevinNULL1500876StanfordCS0
432KevinNULL1500987BerkeleyCS1
432KevinNULL1500987StanfordCS1
456Doris3.91000123BerkeleyCS1
456Doris3.91000123CornellEE1
456Doris3.91000123StanfordCS1
456Doris3.91000123StanfordEE0
456Doris3.91000234Berkeleybiology0
456Doris3.91000321MIThistory0
456Doris3.91000321MITpsychology1
456Doris3.91000345Cornellbioengineering0
456Doris3.91000345CornellCS1
456Doris3.91000345CornellEE0
456Doris3.91000345MITbioengineering1
456Doris3.91000543MITCS0
456Doris3.91000678Stanfordhistory1
456Doris3.91000765Cornellhistory0
456Doris3.91000765Cornellpsychology1
456Doris3.91000765Stanfordhistory1
456Doris3.91000876MITbiology1
456Doris3.91000876MITmarine biology0
456Doris3.91000876StanfordCS0
456Doris3.91000987BerkeleyCS1
456Doris3.91000987StanfordCS1
543Craig3.42000123BerkeleyCS1
543Craig3.42000123CornellEE1
543Craig3.42000123StanfordCS1
543Craig3.42000123StanfordEE0
543Craig3.42000234Berkeleybiology0
543Craig3.42000321MIThistory0
543Craig3.42000321MITpsychology1
543Craig3.42000345Cornellbioengineering0
543Craig3.42000345CornellCS1
543Craig3.42000345CornellEE0
543Craig3.42000345MITbioengineering1
543Craig3.42000543MITCS0
543Craig3.42000678Stanfordhistory1
543Craig3.42000765Cornellhistory0
543Craig3.42000765Cornellpsychology1
543Craig3.42000765Stanfordhistory1
543Craig3.42000876MITbiology1
543Craig3.42000876MITmarine biology0
543Craig3.42000876StanfordCS0
543Craig3.42000987BerkeleyCS1
543Craig3.42000987StanfordCS1
567Edward2.92000123BerkeleyCS1
567Edward2.92000123CornellEE1
567Edward2.92000123StanfordCS1
567Edward2.92000123StanfordEE0
567Edward2.92000234Berkeleybiology0
567Edward2.92000321MIThistory0
567Edward2.92000321MITpsychology1
567Edward2.92000345Cornellbioengineering0
567Edward2.92000345CornellCS1
567Edward2.92000345CornellEE0
567Edward2.92000345MITbioengineering1
567Edward2.92000543MITCS0
567Edward2.92000678Stanfordhistory1
567Edward2.92000765Cornellhistory0
567Edward2.92000765Cornellpsychology1
567Edward2.92000765Stanfordhistory1
567Edward2.92000876MITbiology1
567Edward2.92000876MITmarine biology0
567Edward2.92000876StanfordCS0
567Edward2.92000987BerkeleyCS1
567Edward2.92000987StanfordCS1
654Amy3.91000123BerkeleyCS1
654Amy3.91000123CornellEE1
654Amy3.91000123StanfordCS1
654Amy3.91000123StanfordEE0
654Amy3.91000234Berkeleybiology0
654Amy3.91000321MIThistory0
654Amy3.91000321MITpsychology1
654Amy3.91000345Cornellbioengineering0
654Amy3.91000345CornellCS1
654Amy3.91000345CornellEE0
654Amy3.91000345MITbioengineering1
654Amy3.91000543MITCS0
654Amy3.91000678Stanfordhistory1
654Amy3.91000765Cornellhistory0
654Amy3.91000765Cornellpsychology1
654Amy3.91000765Stanfordhistory1
654Amy3.91000876MITbiology1
654Amy3.91000876MITmarine biology0
654Amy3.91000876StanfordCS0
654Amy3.91000987BerkeleyCS1
654Amy3.91000987StanfordCS1
678Fay3.8200123BerkeleyCS1
678Fay3.8200123CornellEE1
678Fay3.8200123StanfordCS1
678Fay3.8200123StanfordEE0
678Fay3.8200234Berkeleybiology0
678Fay3.8200321MIThistory0
678Fay3.8200321MITpsychology1
678Fay3.8200345Cornellbioengineering0
678Fay3.8200345CornellCS1
678Fay3.8200345CornellEE0
678Fay3.8200345MITbioengineering1
678Fay3.8200543MITCS0
678Fay3.8200678Stanfordhistory1
678Fay3.8200765Cornellhistory0
678Fay3.8200765Cornellpsychology1
678Fay3.8200765Stanfordhistory1
678Fay3.8200876MITbiology1
678Fay3.8200876MITmarine biology0
678Fay3.8200876StanfordCS0
678Fay3.8200987BerkeleyCS1
678Fay3.8200987StanfordCS1
765Jay2.91500123BerkeleyCS1
765Jay2.91500123CornellEE1
765Jay2.91500123StanfordCS1
765Jay2.91500123StanfordEE0
765Jay2.91500234Berkeleybiology0
765Jay2.91500321MIThistory0
765Jay2.91500321MITpsychology1
765Jay2.91500345Cornellbioengineering0
765Jay2.91500345CornellCS1
765Jay2.91500345CornellEE0
765Jay2.91500345MITbioengineering1
765Jay2.91500543MITCS0
765Jay2.91500678Stanfordhistory1
765Jay2.91500765Cornellhistory0
765Jay2.91500765Cornellpsychology1
765Jay2.91500765Stanfordhistory1
765Jay2.91500876MITbiology1
765Jay2.91500876MITmarine biology0
765Jay2.91500876StanfordCS0
765Jay2.91500987BerkeleyCS1
765Jay2.91500987StanfordCS1
789Gary3.4800123BerkeleyCS1
789Gary3.4800123CornellEE1
789Gary3.4800123StanfordCS1
789Gary3.4800123StanfordEE0
789Gary3.4800234Berkeleybiology0
789Gary3.4800321MIThistory0
789Gary3.4800321MITpsychology1
789Gary3.4800345Cornellbioengineering0
789Gary3.4800345CornellCS1
789Gary3.4800345CornellEE0
789Gary3.4800345MITbioengineering1
789Gary3.4800543MITCS0
789Gary3.4800678Stanfordhistory1
789Gary3.4800765Cornellhistory0
789Gary3.4800765Cornellpsychology1
789Gary3.4800765Stanfordhistory1
789Gary3.4800876MITbiology1
789Gary3.4800876MITmarine biology0
789Gary3.4800876StanfordCS0
789Gary3.4800987BerkeleyCS1
789Gary3.4800987StanfordCS1
876Irene3.9400123BerkeleyCS1
876Irene3.9400123CornellEE1
876Irene3.9400123StanfordCS1
876Irene3.9400123StanfordEE0
876Irene3.9400234Berkeleybiology0
876Irene3.9400321MIThistory0
876Irene3.9400321MITpsychology1
876Irene3.9400345Cornellbioengineering0
876Irene3.9400345CornellCS1
876Irene3.9400345CornellEE0
876Irene3.9400345MITbioengineering1
876Irene3.9400543MITCS0
876Irene3.9400678Stanfordhistory1
876Irene3.9400765Cornellhistory0
876Irene3.9400765Cornellpsychology1
876Irene3.9400765Stanfordhistory1
876Irene3.9400876MITbiology1
876Irene3.9400876MITmarine biology0
876Irene3.9400876StanfordCS0
876Irene3.9400987BerkeleyCS1
876Irene3.9400987StanfordCS1
987Helen3.7800123BerkeleyCS1
987Helen3.7800123CornellEE1
987Helen3.7800123StanfordCS1
987Helen3.7800123StanfordEE0
987Helen3.7800234Berkeleybiology0
987Helen3.7800321MIThistory0
987Helen3.7800321MITpsychology1
987Helen3.7800345Cornellbioengineering0
987Helen3.7800345CornellCS1
987Helen3.7800345CornellEE0
987Helen3.7800345MITbioengineering1
987Helen3.7800543MITCS0
987Helen3.7800678Stanfordhistory1
987Helen3.7800765Cornellhistory0
987Helen3.7800765Cornellpsychology1
987Helen3.7800765Stanfordhistory1
987Helen3.7800876MITbiology1
987Helen3.7800876MITmarine biology0
987Helen3.7800876StanfordCS0
987Helen3.7800987BerkeleyCS1
987Helen3.7800987StanfordCS1
-------------- select "" --------------
 
-------------- select "Student theta join[Student.sID = Apply.sID] Apply" --------------
Student theta join[Student.sID = Apply.sID] Apply
Student theta join[Student.sID = Apply.sID] Apply
-------------- explain select * from Student inner join Apply using (sID) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select * from Student inner join Apply using (sID) --------------
sIDsNameGPAsizeHScNamemajordecision
123Amy3.91000BerkeleyCS1
123Amy3.91000CornellEE1
123Amy3.91000StanfordCS1
123Amy3.91000StanfordEE0
234Bob3.61500Berkeleybiology0
345Craig3.5500Cornellbioengineering0
345Craig3.5500CornellCS1
345Craig3.5500CornellEE0
345Craig3.5500MITbioengineering1
543Craig3.42000MITCS0
678Fay3.8200Stanfordhistory1
765Jay2.91500Cornellhistory0
765Jay2.91500Cornellpsychology1
765Jay2.91500Stanfordhistory1
876Irene3.9400MITbiology1
876Irene3.9400MITmarine biology0
876Irene3.9400StanfordCS0
987Helen3.7800BerkeleyCS1
987Helen3.7800StanfordCS1
-------------- select "" --------------
 
-------------- select "Student natural join Apply" --------------
Student natural join Apply
Student natural join Apply
-------------- explain select * from Student natural join Apply --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select * from Student natural join Apply --------------
sIDsNameGPAsizeHScNamemajordecision
123Amy3.91000BerkeleyCS1
123Amy3.91000CornellEE1
123Amy3.91000StanfordCS1
123Amy3.91000StanfordEE0
234Bob3.61500Berkeleybiology0
345Craig3.5500Cornellbioengineering0
345Craig3.5500CornellCS1
345Craig3.5500CornellEE0
345Craig3.5500MITbioengineering1
543Craig3.42000MITCS0
678Fay3.8200Stanfordhistory1
765Jay2.91500Cornellhistory0
765Jay2.91500Cornellpsychology1
765Jay2.91500Stanfordhistory1
876Irene3.9400MITbiology1
876Irene3.9400MITmarine biology0
876Irene3.9400StanfordCS0
987Helen3.7800BerkeleyCS1
987Helen3.7800StanfordCS1
-------------- select "" --------------
 
-------------- select "name and GPA of students" --------------
name and GPA of students
name and GPA of students
-------------- select " with high school size > 1000," --------------
with high school size > 1000,
with high school size > 1000,
-------------- select " with major = CS," --------------
with major = CS,
with major = CS,
-------------- select " with decision = false" --------------
with decision = false
with decision = false
-------------- explain select * from Student inner join Apply using (sID) where (sizeHS > 1000) and (major = 'CS') and (decision = false) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using where
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select * from Student inner join Apply using (sID) where (sizeHS > 1000) and (major = 'CS') and (decision = false) --------------
sIDsNameGPAsizeHScNamemajordecision
543Craig3.42000MITCS0
-------------- explain select sName, GPA from Student inner join Apply using (sID) where (sizeHS > 1000) and (major = 'CS') and (decision = false) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEStudentALLNULLNULLNULLNULL14Using where
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select sName, GPA from Student inner join Apply using (sID) where (sizeHS > 1000) and (major = 'CS') and (decision = false) --------------
sNameGPA
Craig3.4
-------------- select "" --------------
 
-------------- select "name and GPA of students with" --------------
name and GPA of students with
name and GPA of students with
-------------- select " with high school size > 500," --------------
with high school size > 500,
with high school size > 500,
-------------- select " with major = CS," --------------
with major = CS,
with major = CS,
-------------- select " with decision = false," --------------
with decision = false,
with decision = false,
-------------- select " with enrollment > 20000" --------------
with enrollment > 20000
with enrollment > 20000
-------------- explain select * from Student inner join Apply using (sID) inner join College using (cName) where (sizeHS > 500) and (major = 'CS') and (decision = true) and (enrollment > 20000) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLECollegeALLNULLNULLNULLNULL5Using where
1SIMPLEStudentALLNULLNULLNULLNULL14Using where; Using join buffer
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select * from Student inner join Apply using (sID) inner join College using (cName) where (sizeHS > 500) and (major = 'CS') and (decision = true) and (enrollment > 20000) --------------
cNamesIDsNameGPAsizeHSmajordecisionstateenrollment
Berkeley123Amy3.91000CS1CA36000
Berkeley987Helen3.7800CS1CA36000
-------------- explain select sName, GPA from Student inner join Apply using (sID) inner join College using (cName) where (sizeHS > 500) and (major = 'CS') and (decision = true) and (enrollment > 20000) --------------
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLECollegeALLNULLNULLNULLNULL5Using where
1SIMPLEStudentALLNULLNULLNULLNULL14Using where; Using join buffer
1SIMPLEApplyALLNULLNULLNULLNULL21Using where; Using join buffer
-------------- select sName, GPA from Student inner join Apply using (sID) inner join College using (cName) where (sizeHS > 500) and (major = 'CS') and (decision = true) and (enrollment > 20000) --------------
sNameGPA
Amy3.9
Helen3.7
-------------- select "" --------------
 
-------------- select "Drop" --------------
Drop
Drop
-------------- drop table if exists Student -------------- -------------- drop table if exists Apply -------------- -------------- drop table if exists College --------------