CREATE TABLE conductor (
    Conductor_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Age NUMBER,
    Nationality TEXT,
    Year_of_Work NUMBER
);
CREATE TABLE orchestra (
    Orchestra_ID NUMBER PRIMARY KEY,
    Orchestra TEXT,
    Conductor_ID NUMBER,
    Record_Company TEXT,
    Year_of_Founded NUMBER,
    Major_Record_Format TEXT,
    FOREIGN KEY ("Conductor_ID") REFERENCES conductor("Conductor_ID")
);
CREATE TABLE performance (
    Performance_ID NUMBER PRIMARY KEY,
    Orchestra_ID NUMBER,
    Type TEXT,
    Date TEXT,
    Official_ratings_(millions) NUMBER,
    Weekly_rank TEXT,
    Share TEXT,
    FOREIGN KEY ("Orchestra_ID") REFERENCES orchestra("Orchestra_ID")
);
CREATE TABLE show (
    Show_ID NUMBER,
    Performance_ID NUMBER,
    If_first_show OTHERS,
    Result TEXT,
    Attendance NUMBER,
    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