CREATE TABLE member (
    Member_ID NUMBER PRIMARY KEY,
    Card_Number TEXT,
    Name TEXT,
    Hometown TEXT,
    Level NUMBER
);
CREATE TABLE branch (
    Branch_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Open_year TEXT,
    Address_road TEXT,
    City TEXT,
    membership_amount TEXT
);
CREATE TABLE membership_register_branch (
    Member_ID NUMBER PRIMARY KEY,
    Branch_ID TEXT,
    Register_Year TEXT,
    FOREIGN KEY ("Branch_ID") REFERENCES branch("Branch_ID"),
    FOREIGN KEY ("Member_ID") REFERENCES member("Member_ID")
);
CREATE TABLE purchase (
    Member_ID NUMBER PRIMARY KEY,
    Branch_ID TEXT,
    Year TEXT,
    Total_pounds NUMBER,
    FOREIGN KEY ("Branch_ID") REFERENCES branch("Branch_ID"),
    FOREIGN KEY ("Member_ID") REFERENCES member("Member_ID")
);

Question: How many branches where have more than average number of memberships are there?
SQL: SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)

Question: What is the number of branches that have more than the average number of memberships?
SQL: SELECT count(*) FROM branch WHERE membership_amount  >  (SELECT avg(membership_amount) FROM branch)

Question: Show name, address road, and city for all branches sorted by open year.
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year

Question: What are the names, address roads, and cities of the branches ordered by opening year?
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year

Question: What are names for top three branches with most number of membership?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3

Question: What are the names for the 3 branches that have the most memberships?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3

Question: Show all distinct city where branches with at least 100 memberships are located.
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100

Question: What are the different cities that have more than 100 memberships?
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100

Question: List all open years when at least two shops are opened.
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2

Question: What are the opening years in which at least two shops opened?
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2

Question: Show minimum and maximum amount of memberships for all branches opened in 2011 or located at city London.
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'

Question: What are the minimum and maximum membership amounts for all branches that either opened in 2011 or are located in London?
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'

Question: Show the city and the number of branches opened before 2010 for each city.
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city

Question: For each city, how many branches opened before 2010?
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city

Question: How many different levels do members have?
SQL: SELECT count(DISTINCT LEVEL) FROM member

Question: What are the different membership levels?
SQL: SELECT count(DISTINCT LEVEL) FROM member

Question: Show card number, name, and hometown for all members in a descending order of level.
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC

Question: What are the card numbers, names, and hometowns of every member ordered by descending level?
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC

Question: Show the membership level with most number of members.
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1

Question: What is the membership level with the most people?
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1

Question: Show all member names and registered branch names sorted by register year.
SQL: 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

Question: What are the names of the members and branches at which they are registered sorted by year of registration?
SQL: 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

Question: Show all branch names with the number of members in each branch registered after 2015.
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id

Question: For each branch id, what are the names of the branches that were registered after 2015?
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id

Question: Show member names without any registered branch.
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)

Question: What are the names of the members that have never registered at any branch?
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)

Question: List the branch name and city without any registered members.
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)

Question: What are the names and cities of the branches that do not have any registered members?
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)

Question: What is the name and open year for the branch with most number of memberships registered in 2016?
SQL: 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

Question: What is the name and opening year for the branch that registered the most members in 2016?
SQL: 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

Question: Show the member name and hometown who registered a branch in 2016.
SQL: 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

Question: What are the member names and hometowns of those who registered at a branch in 2016?
SQL: 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

Question: Show all city with a branch opened in 2001 and a branch with more than 100 membership.
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100

Question: What are the cities that have a branch that opened in 2001 and a branch with more than 100 members?
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100

Question: Show all cities without a branch having more than 100 memberships.
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100

Question: What are the cities that do not have any branches with more than 100 members?
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100