
CREATE TABLE branch (
	"Branch_ID" INTEGER, 
	"Name" TEXT, 
	"Open_year" TEXT, 
	"Address_road" TEXT, 
	"City" TEXT, 
	membership_amount TEXT, 
	PRIMARY KEY ("Branch_ID")
)


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Card_Number" TEXT, 
	"Name" TEXT, 
	"Hometown" TEXT, 
	"Level" INTEGER, 
	PRIMARY KEY ("Member_ID")
)


CREATE TABLE membership_register_branch (
	"Member_ID" INTEGER, 
	"Branch_ID" TEXT, 
	"Register_Year" TEXT, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Branch_ID") REFERENCES branch ("Branch_ID")
)


CREATE TABLE purchase (
	"Member_ID" INTEGER, 
	"Branch_ID" TEXT, 
	"Year" TEXT, 
	"Total_pounds" REAL, 
	PRIMARY KEY ("Member_ID", "Branch_ID", "Year"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Branch_ID") REFERENCES branch ("Branch_ID")
)

CREATE TABLE branch (
	"Branch_ID" INTEGER, 
	"Name" TEXT, 
	"Open_year" TEXT, 
	"Address_road" TEXT, 
	"City" TEXT, 
	membership_amount TEXT, 
	PRIMARY KEY ("Branch_ID")
)

/*
2 rows from branch table:
Branch_ID	Name	Open_year	Address_road	City	membership_amount
1	Alexandre	2001	Valleys Place	London	112
2	Popert	2009	Oaks Crescent	London	11
*/


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Card_Number" TEXT, 
	"Name" TEXT, 
	"Hometown" TEXT, 
	"Level" INTEGER, 
	PRIMARY KEY ("Member_ID")
)

/*
2 rows from member table:
Member_ID	Card_Number	Name	Hometown	Level
1	LE99	Jeremy Jarmon	Collierville, Tennessee	6
2	LT98	Myron Pryor	Louisville, Kentucky	6
*/


CREATE TABLE membership_register_branch (
	"Member_ID" INTEGER, 
	"Branch_ID" TEXT, 
	"Register_Year" TEXT, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Branch_ID") REFERENCES branch ("Branch_ID")
)

/*
2 rows from membership_register_branch table:
Member_ID	Branch_ID	Register_Year
1	1	2016
2	4	2016
*/


CREATE TABLE purchase (
	"Member_ID" INTEGER, 
	"Branch_ID" TEXT, 
	"Year" TEXT, 
	"Total_pounds" REAL, 
	PRIMARY KEY ("Member_ID", "Branch_ID", "Year"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Branch_ID") REFERENCES branch ("Branch_ID")
)

/*
2 rows from purchase table:
Member_ID	Branch_ID	Year	Total_pounds
1	3	2018	2013.32
2	5	2018	321.13
*/
Show all cities without a branch having more than 100 memberships.
SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
[{'Nenhum': 'Nenhum resultado encontrado'}]