Database: shop_membership

CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
);
CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
);
CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
);
CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_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

Question: What is the sum of total pounds of purchase in year 2018 for all branches in London?
SQL: 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

Question: How many total pounds were purchased in the year 2018 at all London branches?
SQL: 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

Question: What is the total number of purchases for members with level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6

Question: What are the total purchases for members rated at level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6

Question: Find the name of branches where have some members whose hometown is in Louisville, Kentucky and some in Hiram, Georgia.
SQL: 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'

Question: What are the names of the branches that have some members with a hometown in Louisville, Kentucky and also those from Hiram, Goergia?
SQL: 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'

Question: list the card number of all members whose hometown address includes word "Kentucky".
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"

Question: What are the card numbers of members from Kentucky?
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"