
CREATE TABLE happy_hour (
	"HH_ID" INTEGER, 
	"Shop_ID" INTEGER, 
	"Month" TEXT, 
	"Num_of_shaff_in_charge" INTEGER, 
	PRIMARY KEY ("HH_ID", "Shop_ID", "Month"), 
	FOREIGN KEY("Shop_ID") REFERENCES shop ("Shop_ID")
)


CREATE TABLE happy_hour_member (
	"HH_ID" INTEGER, 
	"Member_ID" INTEGER, 
	"Total_amount" REAL, 
	PRIMARY KEY ("HH_ID", "Member_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID")
)


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Name" TEXT, 
	"Membership_card" TEXT, 
	"Age" INTEGER, 
	"Time_of_purchase" INTEGER, 
	"Level_of_membership" INTEGER, 
	"Address" TEXT, 
	PRIMARY KEY ("Member_ID")
)


CREATE TABLE shop (
	"Shop_ID" INTEGER, 
	"Address" TEXT, 
	"Num_of_staff" TEXT, 
	"Score" REAL, 
	"Open_Year" TEXT, 
	PRIMARY KEY ("Shop_ID")
)

CREATE TABLE happy_hour (
	"HH_ID" INTEGER, 
	"Shop_ID" INTEGER, 
	"Month" TEXT, 
	"Num_of_shaff_in_charge" INTEGER, 
	PRIMARY KEY ("HH_ID", "Shop_ID", "Month"), 
	FOREIGN KEY("Shop_ID") REFERENCES shop ("Shop_ID")
)

/*
2 rows from happy_hour table:
HH_ID	Shop_ID	Month	Num_of_shaff_in_charge
1	1	May	10
2	1	April	12
*/


CREATE TABLE happy_hour_member (
	"HH_ID" INTEGER, 
	"Member_ID" INTEGER, 
	"Total_amount" REAL, 
	PRIMARY KEY ("HH_ID", "Member_ID"), 
	FOREIGN KEY("Member_ID") REFERENCES member ("Member_ID")
)

/*
2 rows from happy_hour_member table:
HH_ID	Member_ID	Total_amount
1	3	20.9
4	3	20.92
*/


CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Name" TEXT, 
	"Membership_card" TEXT, 
	"Age" INTEGER, 
	"Time_of_purchase" INTEGER, 
	"Level_of_membership" INTEGER, 
	"Address" TEXT, 
	PRIMARY KEY ("Member_ID")
)

/*
2 rows from member table:
Member_ID	Name	Membership_card	Age	Time_of_purchase	Level_of_membership	Address
1	Ashby, Lazale	Black	29	18	5	Hartford
2	Breton, Robert	White	67	41	4	Waterbury
*/


CREATE TABLE shop (
	"Shop_ID" INTEGER, 
	"Address" TEXT, 
	"Num_of_staff" TEXT, 
	"Score" REAL, 
	"Open_Year" TEXT, 
	PRIMARY KEY ("Shop_ID")
)

/*
2 rows from shop table:
Shop_ID	Address	Num_of_staff	Score	Open_Year
1	1200 Main Street	13	42.0	2010
2	1111 Main Street	19	38.0	2008
*/
Which address do not have any member with the black membership card?
SELECT address FROM member EXCEPT SELECT address FROM member WHERE Membership_card  =  'Black'
[{'Address': 'Cheshire'}]