Database: museum_visit

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: How many visitors below age 30 are there?
SQL: SELECT count(*) FROM visitor WHERE age  <  30

Question: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: How many museums were opened after 2013 or before 2008?
SQL: SELECT count(*) FROM museum WHERE open_year  >  2013 OR open_year  <  2008