
CREATE TABLE driver (
	"Driver_ID" INTEGER, 
	"Name" TEXT, 
	"Party" TEXT, 
	"Home_city" TEXT, 
	"Age" INTEGER, 
	PRIMARY KEY ("Driver_ID")
)


CREATE TABLE school (
	"School_ID" INTEGER, 
	"Grade" TEXT, 
	"School" TEXT, 
	"Location" TEXT, 
	"Type" TEXT, 
	PRIMARY KEY ("School_ID")
)


CREATE TABLE school_bus (
	"School_ID" INTEGER, 
	"Driver_ID" INTEGER, 
	"Years_Working" INTEGER, 
	"If_full_time" BOOLEAN, 
	PRIMARY KEY ("School_ID", "Driver_ID"), 
	FOREIGN KEY("Driver_ID") REFERENCES driver ("Driver_ID"), 
	FOREIGN KEY("School_ID") REFERENCES school ("School_ID")
)

CREATE TABLE driver (
	"Driver_ID" INTEGER, 
	"Name" TEXT, 
	"Party" TEXT, 
	"Home_city" TEXT, 
	"Age" INTEGER, 
	PRIMARY KEY ("Driver_ID")
)

/*
2 rows from driver table:
Driver_ID	Name	Party	Home_city	Age
1	Matthew Ritter	Dem	Hartford	40
2	Dan Carter	Rep	Bethel	30
*/


CREATE TABLE school (
	"School_ID" INTEGER, 
	"Grade" TEXT, 
	"School" TEXT, 
	"Location" TEXT, 
	"Type" TEXT, 
	PRIMARY KEY ("School_ID")
)

/*
2 rows from school table:
School_ID	Grade	School	Location	Type
1	Kindergarten	Noelani Elementary School	Honolulu, Hawaii	Public
2	1st-3rd grade	St. Francis Assisi	Jakarta, Indonesia	Private Catholic
*/


CREATE TABLE school_bus (
	"School_ID" INTEGER, 
	"Driver_ID" INTEGER, 
	"Years_Working" INTEGER, 
	"If_full_time" BOOLEAN, 
	PRIMARY KEY ("School_ID", "Driver_ID"), 
	FOREIGN KEY("Driver_ID") REFERENCES driver ("Driver_ID"), 
	FOREIGN KEY("School_ID") REFERENCES school ("School_ID")
)

/*
2 rows from school_bus table:
School_ID	Driver_ID	Years_Working	If_full_time
1	10	10	True
5	7	8	True
*/
Show the school name and driver name for all school buses.
SELECT T2.school ,  T3.name FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN driver AS T3 ON T1.driver_id  =  T3.driver_id
[{'School': 'Noelani Elementary School', 'Name': 'Henry Genga'}, {'School': 'State Elementary School Menteng 01', 'Name': 'Angel Acre'}, {'School': 'Punahou School', 'Name': 'Minnie Gonzalez'}, {'School': 'Occidental College', 'Name': 'Douglas McCrory'}, {'School': 'Harvard Law School', 'Name': 'Jason Rojas'}]