
CREATE TABLE conductor (
	"Conductor_ID" INTEGER, 
	"Name" TEXT, 
	"Age" INTEGER, 
	"Nationality" TEXT, 
	"Year_of_Work" INTEGER, 
	PRIMARY KEY ("Conductor_ID")
)


CREATE TABLE orchestra (
	"Orchestra_ID" INTEGER, 
	"Orchestra" TEXT, 
	"Conductor_ID" INTEGER, 
	"Record_Company" TEXT, 
	"Year_of_Founded" REAL, 
	"Major_Record_Format" TEXT, 
	PRIMARY KEY ("Orchestra_ID"), 
	FOREIGN KEY("Conductor_ID") REFERENCES conductor ("Conductor_ID")
)


CREATE TABLE performance (
	"Performance_ID" INTEGER, 
	"Orchestra_ID" INTEGER, 
	"Type" TEXT, 
	"Date" TEXT, 
	"Official_ratings_(millions)" REAL, 
	"Weekly_rank" TEXT, 
	"Share" TEXT, 
	PRIMARY KEY ("Performance_ID"), 
	FOREIGN KEY("Orchestra_ID") REFERENCES orchestra ("Orchestra_ID")
)


CREATE TABLE show (
	"Show_ID" INTEGER, 
	"Performance_ID" INTEGER, 
	"If_first_show" BOOLEAN, 
	"Result" TEXT, 
	"Attendance" REAL, 
	FOREIGN KEY("Performance_ID") REFERENCES performance ("Performance_ID")
)

CREATE TABLE conductor (
	"Conductor_ID" INTEGER, 
	"Name" TEXT, 
	"Age" INTEGER, 
	"Nationality" TEXT, 
	"Year_of_Work" INTEGER, 
	PRIMARY KEY ("Conductor_ID")
)

/*
2 rows from conductor table:
Conductor_ID	Name	Age	Nationality	Year_of_Work
1	Antal Doráti	40	USA	10
2	Igor Stravinsky	41	UK	11
*/


CREATE TABLE orchestra (
	"Orchestra_ID" INTEGER, 
	"Orchestra" TEXT, 
	"Conductor_ID" INTEGER, 
	"Record_Company" TEXT, 
	"Year_of_Founded" REAL, 
	"Major_Record_Format" TEXT, 
	PRIMARY KEY ("Orchestra_ID"), 
	FOREIGN KEY("Conductor_ID") REFERENCES conductor ("Conductor_ID")
)

/*
2 rows from orchestra table:
Orchestra_ID	Orchestra	Conductor_ID	Record_Company	Year_of_Founded	Major_Record_Format
1	London Symphony Orchestra	1	Mercury Records	2003.0	CD
2	Columbia Symphony Orchestra	2	Columbia Masterworks	2009.0	CD / LP
*/


CREATE TABLE performance (
	"Performance_ID" INTEGER, 
	"Orchestra_ID" INTEGER, 
	"Type" TEXT, 
	"Date" TEXT, 
	"Official_ratings_(millions)" REAL, 
	"Weekly_rank" TEXT, 
	"Share" TEXT, 
	PRIMARY KEY ("Performance_ID"), 
	FOREIGN KEY("Orchestra_ID") REFERENCES orchestra ("Orchestra_ID")
)

/*
2 rows from performance table:
Performance_ID	Orchestra_ID	Type	Date	Official_ratings_(millions)	Weekly_rank	Share
1	1	Auditions 1	9 June	5.2	12	22.7%
2	2	Auditions 2	10 June	6.73	8	28.0%
*/


CREATE TABLE show (
	"Show_ID" INTEGER, 
	"Performance_ID" INTEGER, 
	"If_first_show" BOOLEAN, 
	"Result" TEXT, 
	"Attendance" REAL, 
	FOREIGN KEY("Performance_ID") REFERENCES performance ("Performance_ID")
)

/*
2 rows from show table:
Show_ID	Performance_ID	If_first_show	Result	Attendance
1	1	True	T	1026.0
2	2	True	T	695.0
*/
What are the record companies that are used by both orchestras founded before 2003 and those founded after 2003?
SELECT Record_Company FROM orchestra WHERE Year_of_Founded  <  2003 INTERSECT SELECT Record_Company FROM orchestra WHERE Year_of_Founded  >  2003
[{'Record_Company': 'Decca Records'}]