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
Give me the names of members whose address is in Harford or Waterbury.
assistant
SELECT name FROM member WHERE address = 'Harford' OR address = 'Waterbury'
human
What is the membership card held by both members living in Hartford and ones living in Waterbury address?
assistant
SELECT membership_card FROM member WHERE address = 'Hartford' INTERSECT SELECT membership_card FROM member WHERE address = 'Waterbury'
human
How many members are not living in Hartford?
assistant
SELECT count(*) FROM member WHERE address != 'Hartford'
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
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 number of members living in each address.
assistant
SELECT COUNT( * ) , address FROM member GROUP BY address ;