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
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 cities without a branch having more than 100 memberships.
assistant
SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount > 100
human
Show the member name and hometown who registered a branch in 2016.
assistant
SELECT T2.name , T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id = T2.member_id WHERE T1.register_year = 2016
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
list the card number of all members whose hometown address includes word 'Kentucky'.
assistant
SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%" ;