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
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
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
How many different levels do members have?
assistant
SELECT count(DISTINCT LEVEL) FROM member
human
Find the name of branches where have some members whose hometown is in Louisville, Kentucky and some in Hiram, Georgia.
assistant
SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id JOIN member AS T3 ON T1.member_id = T3.member_id WHERE T3.Hometown = 'Louisville , Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id JOIN member AS T3 ON T1.member_id = T3.member_id WHERE T3.Hometown = 'Hiram , Georgia'
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
What are the card numbers of members from Kentucky?
assistant
SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%" ;