
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 card number, name, and hometown for all members in a descending order of level.
SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
[{'Card_Number': 'LE99', 'Name': 'Jeremy Jarmon', 'Hometown': 'Collierville, Tennessee'}, {'Card_Number': 'LT98', 'Name': 'Myron Pryor', 'Hometown': 'Louisville, Kentucky'}, {'Card_Number': 'RT91', 'Name': 'Corey Peters', 'Hometown': 'Louisville, Kentucky'}, {'Card_Number': 'RE95', 'Name': 'Ventrell Jenkins', 'Hometown': 'Columbia, South Carolina'}, {'Card_Number': 'OLB56', 'Name': 'Braxton Kelley', 'Hometown': 'LaGrange, Georgia'}, {'Card_Number': 'SLB51', 'Name': 'Johnny Williams', 'Hometown': 'Jacksonville, Florida'}, {'Card_Number': 'RCB32', 'Name': 'Trevard Lindley', 'Hometown': 'Hiram, Georgia'}, {'Card_Number': 'LCB7', 'Name': 'David Jones', 'Hometown': 'Red Jacket, West Virginia'}, {'Card_Number': 'MLB4', 'Name': 'Micah Johnson', 'Hometown': 'Fort Campbell, Kentucky'}, {'Card_Number': 'FS10', 'Name': 'Matt Lentz', 'Hometown': 'Simpsonville, South Carolina'}]