Database: museum_visit
Schema: CREATE TABLE "museum" ( "Museum_ID" int, "Name" text, "Num_of_Staff" int, "Open_Year" text, PRIMARY KEY ("Museum_ID") ); CREATE TABLE "visitor" ( "ID" int, "Name" text, "Level_of_membership" int, "Age" int, PRIMARY KEY ("ID") ); CREATE TABLE "visit" ( "Museum_ID" int, "visitor_ID" text, "Num_of_Ticket" int, "Total_spent" real, PRIMARY KEY ("Museum_ID","visitor_ID"), FOREIGN KEY ("Museum_ID") REFERENCES `museum`("Museum_ID"), FOREIGN KEY ("visitor_ID") REFERENCES `visitor`("ID") );

Question (easy): How many visitors below age 30 are there?
SQL: SELECT count(*) FROM visitor WHERE age  <  30

Question (medium): Find the names of the visitors whose membership level is higher than 4, and order the results by the level from high to low.
SQL: SELECT name FROM visitor WHERE Level_of_membership  >  4 ORDER BY Level_of_membership DESC

Question (easy): What is the average age of the visitors whose membership level is not higher than 4?
SQL: SELECT avg(age) FROM visitor WHERE Level_of_membership  <=  4

Question (medium): Find the name and membership level of the visitors whose membership level is higher than 4, and sort by their age from old to young.
SQL: SELECT name ,  Level_of_membership FROM visitor WHERE Level_of_membership  >  4 ORDER BY age DESC

Question (medium): Find the id and name of the museum that has the most staff members?
SQL: SELECT museum_id ,  name FROM museum ORDER BY num_of_staff DESC LIMIT 1

Question (easy): Find the average number of staff working for the museums that were open before 2009.
SQL: SELECT avg(num_of_staff) FROM museum WHERE open_year  <  2009

Question (medium): What are the opening year and staff number of the museum named Plaza Museum?
SQL: SELECT Num_of_Staff ,  Open_Year FROM museum WHERE name  =  'Plaza Museum'

Question (hard): find the names of museums which have more staff than the minimum staff number of all museums opened after 2010.
SQL: SELECT name FROM museum WHERE num_of_staff  >  (SELECT min(num_of_staff) FROM museum WHERE open_year  >  2010)

Question (medium): find the id, name and age for visitors who visited some museums more than once.
SQL: SELECT t1.id ,  t1.name ,  t1.age FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id GROUP BY t1.id HAVING count(*)  >  1

Question (extra): What are the id, name and membership level of visitors who have spent the largest amount of money in total in all museum tickets?
SQL: SELECT t2.visitor_id ,  t1.name ,  t1.Level_of_membership FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id GROUP BY t2.visitor_id ORDER BY sum(t2.Total_spent) DESC LIMIT 1

Question (extra): What are the id and name of the museum visited most times?
SQL: SELECT t2.Museum_ID ,  t1.name FROM museum AS t1 JOIN visit AS t2 ON t1.Museum_ID  =  t2.Museum_ID GROUP BY t2.Museum_ID ORDER BY count(*) DESC LIMIT 1

Question (hard): What is the name of the museum that had no visitor yet?
SQL: SELECT name FROM museum WHERE Museum_ID NOT IN (SELECT museum_id FROM visit)

Question (hard): Find the name and age of the visitor who bought the most tickets at once.
SQL: SELECT t1.name ,  t1.age FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id ORDER BY t2.num_of_ticket DESC LIMIT 1

Question (medium): What are the average and maximum number of tickets bought in all visits?
SQL: SELECT avg(num_of_ticket) ,  max(num_of_ticket) FROM visit

Question (medium): What is the total ticket expense of the visitors whose membership level is 1?
SQL: SELECT sum(t2.Total_spent) FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id WHERE t1.Level_of_membership  =  1

Question (extra): What is the name of the visitor who visited both a museum opened before 2009 and a museum opened after 2011?
SQL: SELECT t1.name FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id JOIN museum AS t3 ON t3.Museum_ID  =  t2.Museum_ID WHERE t3.open_year  <  2009 INTERSECT SELECT t1.name FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id JOIN museum AS t3 ON t3.Museum_ID  =  t2.Museum_ID WHERE t3.open_year  >  2011

Question (extra): Find the number of visitors who did not visit any museum opened after 2010.
SQL: SELECT count(*) FROM visitor WHERE id NOT IN (SELECT t2.visitor_id FROM museum AS t1 JOIN visit AS t2 ON t1.Museum_ID  =  t2.Museum_ID WHERE t1.open_year  >  2010)

Question (medium): How many museums were opened after 2013 or before 2008?
SQL: SELECT count(*) FROM museum WHERE open_year  >  2013 OR open_year  <  2008