
CREATE TABLE museum (
	"Museum_ID" INTEGER, 
	"Name" TEXT, 
	"Num_of_Staff" INTEGER, 
	"Open_Year" TEXT, 
	PRIMARY KEY ("Museum_ID")
)


CREATE TABLE visit (
	"Museum_ID" INTEGER, 
	"visitor_ID" TEXT, 
	"Num_of_Ticket" INTEGER, 
	"Total_spent" REAL, 
	PRIMARY KEY ("Museum_ID", "visitor_ID"), 
	FOREIGN KEY("visitor_ID") REFERENCES visitor ("ID"), 
	FOREIGN KEY("Museum_ID") REFERENCES museum ("Museum_ID")
)


CREATE TABLE visitor (
	"ID" INTEGER, 
	"Name" TEXT, 
	"Level_of_membership" INTEGER, 
	"Age" INTEGER, 
	PRIMARY KEY ("ID")
)

CREATE TABLE museum (
	"Museum_ID" INTEGER, 
	"Name" TEXT, 
	"Num_of_Staff" INTEGER, 
	"Open_Year" TEXT, 
	PRIMARY KEY ("Museum_ID")
)

/*
2 rows from museum table:
Museum_ID	Name	Num_of_Staff	Open_Year
1	Plaza Museum	62	2000
2	Capital Plaza Museum	25	2012
*/


CREATE TABLE visit (
	"Museum_ID" INTEGER, 
	"visitor_ID" TEXT, 
	"Num_of_Ticket" INTEGER, 
	"Total_spent" REAL, 
	PRIMARY KEY ("Museum_ID", "visitor_ID"), 
	FOREIGN KEY("visitor_ID") REFERENCES visitor ("ID"), 
	FOREIGN KEY("Museum_ID") REFERENCES museum ("Museum_ID")
)

/*
2 rows from visit table:
Museum_ID	visitor_ID	Num_of_Ticket	Total_spent
1	5	20	320.14
2	5	4	89.98
*/


CREATE TABLE visitor (
	"ID" INTEGER, 
	"Name" TEXT, 
	"Level_of_membership" INTEGER, 
	"Age" INTEGER, 
	PRIMARY KEY ("ID")
)

/*
2 rows from visitor table:
ID	Name	Level_of_membership	Age
1	Gonzalo Higuaín 	8	35
2	Guti Midfielder	5	28
*/
What is the name of the visitor who visited both a museum opened before 2009 and a museum opened after 2011?
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
[{'Name': 'Fernando Gago'}]