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
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
Which address do not have any member with the black membership card?
assistant
SELECT address FROM member EXCEPT SELECT address FROM member WHERE Membership_card = 'Black'
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
How many members have the black membership card?
assistant
SELECT count(*) FROM member WHERE Membership_card = 'Black'
human
Which membership card has more than 5 members?
assistant
SELECT Membership_card FROM member GROUP BY Membership_card HAVING COUNT( * ) > 5 ;