Database: orchestra
Schema: 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 (easy): How many conductors are there?
SQL: SELECT count(*) FROM conductor

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

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

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

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

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

Question (easy): 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 (easy): 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 (easy): What is the average attendance of shows?
SQL: SELECT avg(Attendance) FROM SHOW

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

Question (medium): 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 (medium): 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 (easy): How many different nationalities do conductors have?
SQL: SELECT count(DISTINCT Nationality) FROM conductor

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

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

Question (easy): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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 (extra): 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 (extra): 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 (medium): 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 (medium): 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 (medium): Please show the different record companies and the corresponding number of orchestras.
SQL: SELECT Record_Company ,  COUNT(*) FROM orchestra GROUP BY Record_Company

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

Question (medium): 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 (medium): 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 (hard): 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 (hard): 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 (hard): 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 (hard): 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 (hard): 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 (hard): 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 (medium): 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 (medium): 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 (medium): 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 (medium): 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