Database: coffee_shop

CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
);
CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
);
CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
);
CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
);

Question: How many members have the black membership card?
SQL: SELECT count(*) FROM member WHERE Membership_card  =  'Black'

Question: Find the number of members living in each address.
SQL: SELECT count(*) ,  address FROM member GROUP BY address

Question: Give me the names of members whose address is in Harford or Waterbury.
SQL: SELECT name FROM member WHERE address  =  'Harford' OR address  =  'Waterbury'

Question: Find the ids and names of members who are under age 30 or with black membership card.
SQL: SELECT name ,  member_id FROM member WHERE Membership_card  =  'Black' OR age  <  30

Question: Find the purchase time, age and address of each member, and show the results in the order of purchase time.
SQL: SELECT Time_of_purchase ,  age ,  address FROM member ORDER BY Time_of_purchase

Question: Which membership card has more than 5 members?
SQL: SELECT Membership_card FROM member GROUP BY Membership_card HAVING count(*)  >  5

Question: Which address has both members younger than 30 and members older than 40?
SQL: SELECT address FROM member WHERE age  <  30 INTERSECT SELECT address FROM member WHERE age  >  40

Question: What is the membership card held by both members living in Hartford and ones living in Waterbury address?
SQL: SELECT membership_card FROM member WHERE address  =  'Hartford' INTERSECT SELECT membership_card FROM member WHERE address  =  'Waterbury'

Question: How many members are not living in Hartford?
SQL: SELECT count(*) FROM member WHERE address != 'Hartford'

Question: Which address do not have any member with the black membership card?
SQL: SELECT address FROM member EXCEPT SELECT address FROM member WHERE Membership_card  =  'Black'

Question: Show the shop addresses ordered by their opening year.
SQL: SELECT address FROM shop ORDER BY open_year

Question: What are the average score and average staff number of all shops?
SQL: SELECT avg(num_of_staff) ,  avg(score) FROM shop

Question: Find the id and address of the shops whose score is below the average score.
SQL: SELECT shop_id ,  address FROM shop WHERE score  <  (SELECT avg(score) FROM shop)

Question: Find the address and staff number of the shops that do not have any happy hour.
SQL: SELECT address ,  num_of_staff FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM happy_hour)

Question: What are the id and address of the shops which have a happy hour in May?
SQL: SELECT t1.address ,  t1.shop_id FROM shop AS t1 JOIN happy_hour AS t2 ON t1.shop_id  =  t2.shop_id WHERE MONTH  =  'May'

Question: which shop has happy hour most frequently? List its id and number of happy hours.
SQL: SELECT shop_id ,  count(*) FROM happy_hour GROUP BY shop_id ORDER BY count(*) DESC LIMIT 1

Question: Which month has the most happy hours?
SQL: SELECT MONTH FROM happy_hour GROUP BY MONTH ORDER BY count(*) DESC LIMIT 1

Question: Which months have more than 2 happy hours?
SQL: SELECT MONTH FROM happy_hour GROUP BY MONTH HAVING count(*)  >  2