human
Schema:
member (Member_ID, Card_Number, Name, Hometown, Level)
branch (Branch_ID, Name, Open_year, Address_road, City, membership_amount)
membership_register_branch (Member_ID, Branch_ID, Register_Year)
purchase (Member_ID, Branch_ID, Year, Total_pounds)
Please answer with SQL.
assistant
Sure, I understand.
human
How many branches where have more than average number of memberships are there?
assistant
SELECT count(*) FROM branch WHERE membership_amount > (SELECT avg(membership_amount) FROM branch)
human
Show all city with a branch opened in 2001 and a branch with more than 100 membership.
assistant
SELECT city FROM branch WHERE open_year = 2001 AND membership_amount > 100
human
What are names for top three branches with most number of membership?
assistant
SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
human
List the branch name and city without any registered members.
assistant
SELECT name , city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
human
Show all distinct city where branches with at least 100 memberships are located.
assistant
SELECT DISTINCT city FROM branch WHERE membership_amount >= 100
human
What are the cities that do not have any branches with more than 100 members?
assistant
SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount > 100 ;