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 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
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)
human
Show card number, name, and hometown for all members in a descending order of level.
assistant
SELECT card_number , name , hometown FROM member ORDER BY LEVEL DESC
human
How many different levels do members have?
assistant
SELECT count(DISTINCT LEVEL) FROM member
human
Show the membership level with most number of members.
assistant
SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) DESC LIMIT 1 ;