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
Which address has both members younger than 30 and members older than 40?
assistant
SELECT address FROM member WHERE age < 30 INTERSECT SELECT address FROM member WHERE age > 40
human
Find the ids and names of members who are under age 30 or with black membership card.
assistant
SELECT name , member_id FROM member WHERE Membership_card = 'Black' OR age < 30
human
Find the id and address of the shops whose score is below the average score.
assistant
SELECT shop_id , address FROM shop WHERE score < (SELECT avg(score) FROM shop)
human
Find the number of members living in each address.
assistant
SELECT count(*) , address FROM member GROUP BY address
human
Show the shop addresses ordered by their opening year.
assistant
SELECT address FROM shop ORDER BY open_year
human
Find the purchase time, age and address of each member, and show the results in the order of purchase time.
assistant
SELECT Time_of_purchase , age , address FROM member ORDER BY Time_of_purchase ;