Database: concert_singer
Schema: CREATE TABLE "stadium" ( "Stadium_ID" int, "Location" text, "Name" text, "Capacity" int, "Highest" int, "Lowest" int, "Average" int, PRIMARY KEY ("Stadium_ID") ); CREATE TABLE "singer" ( "Singer_ID" int, "Name" text, "Country" text, "Song_Name" text, "Song_release_year" text, "Age" int, "Is_male" bool, PRIMARY KEY ("Singer_ID") ); CREATE TABLE "concert" ( "concert_ID" int, "concert_Name" text, "Theme" text, "Stadium_ID" text, "Year" text, PRIMARY KEY ("concert_ID"), FOREIGN KEY ("Stadium_ID") REFERENCES "stadium"("Stadium_ID") ); CREATE TABLE "singer_in_concert" ( "concert_ID" int, "Singer_ID" text, PRIMARY KEY ("concert_ID","Singer_ID"), FOREIGN KEY ("concert_ID") REFERENCES "concert"("concert_ID"), FOREIGN KEY ("Singer_ID") REFERENCES "singer"("Singer_ID") );

Question (easy): How many singers do we have?
SQL: SELECT count(*) FROM singer

Question (easy): What is the total number of singers?
SQL: SELECT count(*) FROM singer

Question (medium): Show name, country, age for all singers ordered by age from the oldest to the youngest.
SQL: SELECT name ,  country ,  age FROM singer ORDER BY age DESC

Question (medium): What are the names, countries, and ages for every singer in descending order of age?
SQL: SELECT name ,  country ,  age FROM singer ORDER BY age DESC

Question (medium): What is the average, minimum, and maximum age of all singers from France?
SQL: SELECT avg(age) ,  min(age) ,  max(age) FROM singer WHERE country  =  'France'

Question (medium): What is the average, minimum, and maximum age for all French singers?
SQL: SELECT avg(age) ,  min(age) ,  max(age) FROM singer WHERE country  =  'France'

Question (medium): Show the name and the release year of the song by the youngest singer.
SQL: SELECT song_name ,  song_release_year FROM singer ORDER BY age LIMIT 1

Question (medium): What are the names and release years for all the songs of the youngest singer?
SQL: SELECT song_name ,  song_release_year FROM singer ORDER BY age LIMIT 1

Question (easy): What are all distinct countries where singers above age 20 are from?
SQL: SELECT DISTINCT country FROM singer WHERE age  >  20

Question (easy): What are  the different countries with singers above age 20?
SQL: SELECT DISTINCT country FROM singer WHERE age  >  20

Question (medium): Show all countries and the number of singers in each country.
SQL: SELECT country ,  count(*) FROM singer GROUP BY country

Question (medium): How many singers are from each country?
SQL: SELECT country ,  count(*) FROM singer GROUP BY country

Question (hard): List all song names by singers above the average age.
SQL: SELECT song_name FROM singer WHERE age  >  (SELECT avg(age) FROM singer)

Question (hard): What are all the song names by singers who are older than average?
SQL: SELECT song_name FROM singer WHERE age  >  (SELECT avg(age) FROM singer)

Question (medium): Show location and name for all stadiums with a capacity between 5000 and 10000.
SQL: SELECT LOCATION ,  name FROM stadium WHERE capacity BETWEEN 5000 AND 10000

Question (medium): What are the locations and names of all stations with capacity between 5000 and 10000?
SQL: SELECT LOCATION ,  name FROM stadium WHERE capacity BETWEEN 5000 AND 10000

Question (medium): What is the maximum capacity and the average of all stadiums ?
SQL: select max(capacity), average from stadium

Question (medium): What is the average and maximum capacities for all stadiums ?
SQL: select avg(capacity) ,  max(capacity) from stadium

Question (medium): What is the name and capacity for the stadium with highest average attendance?
SQL: SELECT name ,  capacity FROM stadium ORDER BY average DESC LIMIT 1

Question (medium): What is the name and capacity for the stadium with the highest average attendance?
SQL: SELECT name ,  capacity FROM stadium ORDER BY average DESC LIMIT 1

Question (medium): How many concerts are there in year 2014 or 2015?
SQL: SELECT count(*) FROM concert WHERE YEAR  =  2014 OR YEAR  =  2015

Question (medium): How many concerts occurred in 2014 or 2015?
SQL: SELECT count(*) FROM concert WHERE YEAR  =  2014 OR YEAR  =  2015

Question (medium): Show the stadium name and the number of concerts in each stadium.
SQL: SELECT T2.name ,  count(*) FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id GROUP BY T1.stadium_id

Question (medium): For each stadium, how many concerts play there?
SQL: SELECT T2.name ,  count(*) FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id GROUP BY T1.stadium_id

Question (extra): Show the stadium name and capacity with most number of concerts in year 2014 or after.
SQL: SELECT T2.name ,  T2.capacity FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.year  >=  2014 GROUP BY T2.stadium_id ORDER BY count(*) DESC LIMIT 1

Question (extra): What is the name and capacity of the stadium with the most concerts after 2013 ?
SQL: select t2.name ,  t2.capacity from concert as t1 join stadium as t2 on t1.stadium_id  =  t2.stadium_id where t1.year  >  2013 group by t2.stadium_id order by count(*) desc limit 1

Question (hard): Which year has most number of concerts?
SQL: SELECT YEAR FROM concert GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question (hard): What is the year that had the most concerts?
SQL: SELECT YEAR FROM concert GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question (hard): Show the stadium names without any concert.
SQL: SELECT name FROM stadium WHERE stadium_id NOT IN (SELECT stadium_id FROM concert)

Question (hard): What are the names of the stadiums without any concerts?
SQL: SELECT name FROM stadium WHERE stadium_id NOT IN (SELECT stadium_id FROM concert)

Question (hard): Show countries where a singer above age 40 and a singer below 30 are from.
SQL: SELECT country FROM singer WHERE age  >  40 INTERSECT SELECT country FROM singer WHERE age  <  30

Question (hard): Show names for all stadiums except for stadiums having a concert in year 2014.
SQL: SELECT name FROM stadium EXCEPT SELECT T2.name FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.year  =  2014

Question (hard): What are the names of all stadiums that did not have a concert in 2014?
SQL: SELECT name FROM stadium EXCEPT SELECT T2.name FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.year  =  2014

Question (medium): Show the name and theme for all concerts and the number of singers in each concert.
SQL: SELECT T2.concert_name ,  T2.theme ,  count(*) FROM singer_in_concert AS T1 JOIN concert AS T2 ON T1.concert_id  =  T2.concert_id GROUP BY T2.concert_id

Question (medium): What are the names , themes , and number of singers for every concert ?
SQL: select t2.concert_name ,  t2.theme ,  count(*) from singer_in_concert as t1 join concert as t2 on t1.concert_id  =  t2.concert_id group by t2.concert_id

Question (medium): List singer names and number of concerts for each singer.
SQL: SELECT T2.name ,  count(*) FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id GROUP BY T2.singer_id

Question (medium): What are the names of the singers and number of concerts for each person?
SQL: SELECT T2.name ,  count(*) FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id GROUP BY T2.singer_id

Question (hard): List all singer names in concerts in year 2014.
SQL: SELECT T2.name FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id JOIN concert AS T3 ON T1.concert_id  =  T3.concert_id WHERE T3.year  =  2014

Question (hard): What are the names of the singers who performed in a concert in 2014?
SQL: SELECT T2.name FROM singer_in_concert AS T1 JOIN singer AS T2 ON T1.singer_id  =  T2.singer_id JOIN concert AS T3 ON T1.concert_id  =  T3.concert_id WHERE T3.year  =  2014

Question (medium): what is the name and nation of the singer who have a song having 'Hey' in its name?
SQL: SELECT name ,  country FROM singer WHERE song_name LIKE '%Hey%'

Question (medium): What is the name and country of origin of every singer who has a song with the word 'Hey' in its title?
SQL: SELECT name ,  country FROM singer WHERE song_name LIKE '%Hey%'

Question (extra): Find the name and location of the stadiums which some concerts happened in the years of both 2014 and 2015.
SQL: SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015

Question (extra): What are the names and locations of the stadiums that had concerts that occurred in both 2014 and 2015?
SQL: SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015

Question (hard): Find the number of concerts happened in the stadium with the highest capacity .
SQL: select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1)

Question (hard): What are the number of concerts that occurred in the stadium with the largest capacity ?
SQL: select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1)