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 sum of total pounds of purchase in year 2018 for all branches in London?
assistant
SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T2.city = 'London' AND T1.year = 2018
human
Show all city with a branch opened in 2001 and a branch with more than 100 membership.
assistant
SELECT city FROM branch WHERE open_year = 2001 AND membership_amount > 100
human
Show name, address road, and city for all branches sorted by open year.
assistant
SELECT name , address_road , city FROM branch ORDER BY open_year
human
Show the city and the number of branches opened before 2010 for each city.
assistant
SELECT city , count(*) FROM branch WHERE open_year < 2010 GROUP BY city
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 the opening years in which at least two shops opened?
assistant
SELECT open_year FROM branch GROUP BY open_year HAVING COUNT( * ) >= 2 ;