human
Schema:
shop (Shop_ID, Address, Num_of_staff, Score, Open_Year)
member (Member_ID, Name, Membership_card, Age, Time_of_purchase, Level_of_membership, Address)
happy_hour (HH_ID, Shop_ID, Month, Num_of_shaff_in_charge)
happy_hour_member (HH_ID, Member_ID, Total_amount)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the average score and average staff number of all shops?
assistant
SELECT avg(num_of_staff) , avg(score) FROM shop
human
Find the address and staff number of the shops that do not have any happy hour.
assistant
SELECT address , num_of_staff FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM happy_hour)
human
What are the id and address of the shops which have a happy hour in May?
assistant
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'
human
which shop has happy hour most frequently? List its id and number of happy hours.
assistant
SELECT shop_id , count(*) FROM happy_hour GROUP BY shop_id ORDER BY count(*) DESC LIMIT 1
human
Which months have more than 2 happy hours?
assistant
SELECT MONTH FROM happy_hour GROUP BY MONTH HAVING count(*) > 2
human
Which month has the most happy hours?
assistant
SELECT MONTH FROM happy_hour GROUP BY MONTH ORDER BY COUNT( * ) DESC LIMIT 1 ;