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 member names and registered branch names sorted by register year.
assistant
SELECT T3.name , 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 ORDER BY T1.register_year
human
list the card number of all members whose hometown address includes word 'Kentucky'.
assistant
SELECT card_number FROM member WHERE Hometown LIKE '%Kentucky%'
human
Show the membership level with most number of members.
assistant
SELECT LEVEL FROM member GROUP BY LEVEL 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 different levels do members have?
assistant
SELECT count(DISTINCT LEVEL) FROM member
human
What are the card numbers, names, and hometowns of every member ordered by descending level?
assistant
SELECT card_number , name , hometown FROM member ORDER BY LEVEL DESC ;