Database: orchestra

CREATE TABLE "conductor" (
"Conductor_ID" int,
"Name" text,
"Age" int,
"Nationality" text,
"Year_of_Work" int,
PRIMARY KEY ("Conductor_ID")
);
CREATE TABLE "orchestra" (
"Orchestra_ID" int,
"Orchestra" text,
"Conductor_ID" int,
"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" int,
"Orchestra_ID" int,
"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" int,
"Performance_ID" int,
"If_first_show" bool,
"Result" text,
"Attendance" real,
FOREIGN KEY (`Performance_ID`) REFERENCES `performance`(`Performance_ID`)
);

Question: How many conductors are there?
SQL: SELECT count(*) FROM conductor

Question: Count the number of conductors.
SQL: SELECT count(*) FROM conductor

Question: List the names of conductors in ascending order of age.
SQL: SELECT Name FROM conductor ORDER BY Age ASC

Question: What are the names of conductors, ordered by age?
SQL: SELECT Name FROM conductor ORDER BY Age ASC

Question: What are the names of conductors whose nationalities are not "USA"?
SQL: SELECT Name FROM conductor WHERE Nationality != 'USA'

Question: Return the names of conductors that do not have the nationality "USA".
SQL: SELECT Name FROM conductor WHERE Nationality != 'USA'

Question: What are the record companies of orchestras in descending order of years in which they were founded?
SQL: SELECT Record_Company FROM orchestra ORDER BY Year_of_Founded DESC

Question: Return the record companies of orchestras, sorted descending by the years in which they were founded.
SQL: SELECT Record_Company FROM orchestra ORDER BY Year_of_Founded DESC

Question: What is the average attendance of shows?
SQL: SELECT avg(Attendance) FROM SHOW

Question: Return the average attendance across all shows.
SQL: SELECT avg(Attendance) FROM SHOW

Question: What are the maximum and minimum share of performances whose type is not "Live final".
SQL: SELECT max(SHARE) ,  min(SHARE) FROM performance WHERE TYPE != "Live final"

Question: Return the maximum and minimum shares for performances that do not have the type "Live final".
SQL: SELECT max(SHARE) ,  min(SHARE) FROM performance WHERE TYPE != "Live final"

Question: How many different nationalities do conductors have?
SQL: SELECT count(DISTINCT Nationality) FROM conductor

Question: Count the number of different nationalities of conductors.
SQL: SELECT count(DISTINCT Nationality) FROM conductor

Question: List names of conductors in descending order of years of work.
SQL: SELECT Name FROM conductor ORDER BY Year_of_Work DESC

Question: What are the names of conductors, sorted descending by the number of years they have worked?
SQL: SELECT Name FROM conductor ORDER BY Year_of_Work DESC

Question: List the name of the conductor with the most years of work.
SQL: SELECT Name FROM conductor ORDER BY Year_of_Work DESC LIMIT 1

Question: What is the name of the conductor who has worked the greatest number of years?
SQL: SELECT Name FROM conductor ORDER BY Year_of_Work DESC LIMIT 1

Question: Show the names of conductors and the orchestras they have conducted.
SQL: SELECT T1.Name ,  T2.Orchestra FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID

Question: What are the names of conductors as well as the corresonding orchestras that they have conducted?
SQL: SELECT T1.Name ,  T2.Orchestra FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID

Question: Show the names of conductors that have conducted more than one orchestras.
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID HAVING COUNT(*)  >  1

Question: What are the names of conductors who have conducted at more than one orchestra?
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID HAVING COUNT(*)  >  1

Question: Show the name of the conductor that has conducted the most number of orchestras.
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the name of the conductor who has conducted the most orchestras?
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID GROUP BY T2.Conductor_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: Please show the name of the conductor that has conducted orchestras founded after 2008.
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID WHERE Year_of_Founded  >  2008

Question: What are the names of conductors who have conducted orchestras founded after the year 2008?
SQL: SELECT T1.Name FROM conductor AS T1 JOIN orchestra AS T2 ON T1.Conductor_ID  =  T2.Conductor_ID WHERE Year_of_Founded  >  2008

Question: Please show the different record companies and the corresponding number of orchestras.
SQL: SELECT Record_Company ,  COUNT(*) FROM orchestra GROUP BY Record_Company

Question: How many orchestras does each record company manage?
SQL: SELECT Record_Company ,  COUNT(*) FROM orchestra GROUP BY Record_Company

Question: Please show the record formats of orchestras in ascending order of count.
SQL: SELECT Major_Record_Format FROM orchestra GROUP BY Major_Record_Format ORDER BY COUNT(*) ASC

Question: What are the major record formats of orchestras, sorted by their frequency?
SQL: SELECT Major_Record_Format FROM orchestra GROUP BY Major_Record_Format ORDER BY COUNT(*) ASC

Question: List the record company shared by the most number of orchestras.
SQL: SELECT Record_Company FROM orchestra GROUP BY Record_Company ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the record company used by the greatest number of orchestras?
SQL: SELECT Record_Company FROM orchestra GROUP BY Record_Company ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of orchestras that have no performance.
SQL: SELECT Orchestra FROM orchestra WHERE Orchestra_ID NOT IN (SELECT Orchestra_ID FROM performance)

Question: What are the orchestras that do not have any performances?
SQL: SELECT Orchestra FROM orchestra WHERE Orchestra_ID NOT IN (SELECT Orchestra_ID FROM performance)

Question: Show the record companies shared by orchestras founded before 2003 and after 2003.
SQL: SELECT Record_Company FROM orchestra WHERE Year_of_Founded  <  2003 INTERSECT SELECT Record_Company FROM orchestra WHERE Year_of_Founded  >  2003

Question: What are the record companies that are used by both orchestras founded before 2003 and those founded after 2003?
SQL: SELECT Record_Company FROM orchestra WHERE Year_of_Founded  <  2003 INTERSECT SELECT Record_Company FROM orchestra WHERE Year_of_Founded  >  2003

Question: Find the number of orchestras whose record format is "CD" or "DVD".
SQL: SELECT COUNT(*) FROM orchestra WHERE Major_Record_Format  =  "CD" OR Major_Record_Format  =  "DVD"

Question: Count the number of orchestras that have CD or DVD as their record format.
SQL: SELECT COUNT(*) FROM orchestra WHERE Major_Record_Format  =  "CD" OR Major_Record_Format  =  "DVD"

Question: Show the years in which orchestras that have given more than one performance are founded.
SQL: SELECT Year_of_Founded FROM orchestra AS T1 JOIN performance AS T2 ON T1.Orchestra_ID  =  T2.Orchestra_ID GROUP BY T2.Orchestra_ID HAVING COUNT(*)  >  1

Question: What are years of founding for orchestras that have had more than a single performance?
SQL: SELECT Year_of_Founded FROM orchestra AS T1 JOIN performance AS T2 ON T1.Orchestra_ID  =  T2.Orchestra_ID GROUP BY T2.Orchestra_ID HAVING COUNT(*)  >  1