
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 all home cities except for those having a driver older than 40.
SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age  >  40
[{'Home_city': 'Bethel'}, {'Home_city': 'Coventry'}, {'Home_city': 'East Hartford'}]