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
Show all distinct city where branches with at least 100 memberships are located.
assistant
SELECT DISTINCT city FROM branch WHERE membership_amount >= 100
human
Show minimum and maximum amount of memberships for all branches opened in 2011 or located at city London.
assistant
SELECT min(membership_amount) , max(membership_amount) FROM branch WHERE open_year = 2011 OR city = 'London'
human
How many different levels do members have?
assistant
SELECT count(DISTINCT LEVEL) FROM member
human
What is the name and open year for the branch with most number of memberships registered in 2016?
assistant
SELECT T2.name , T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T1.register_year = 2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
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
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 ) ;