Database: singer
Schema: CREATE TABLE "singer" ( "Singer_ID" int, "Name" text, "Birth_Year" real, "Net_Worth_Millions" real, "Citizenship" text, PRIMARY KEY ("Singer_ID") ); CREATE TABLE "song" ( "Song_ID" int, "Title" text, "Singer_ID" int, "Sales" real, "Highest_Position" real, PRIMARY KEY ("Song_ID"), FOREIGN KEY ("Singer_ID") REFERENCES `singer`("Singer_ID") );

Question (easy): How many singers are there?
SQL: SELECT count(*) FROM singer

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

Question (easy): List the name of singers in ascending order of net worth.
SQL: SELECT Name FROM singer ORDER BY Net_Worth_Millions ASC

Question (easy): What are the names of singers ordered by ascending net worth?
SQL: SELECT Name FROM singer ORDER BY Net_Worth_Millions ASC

Question (medium): What are the birth year and citizenship of singers?
SQL: SELECT Birth_Year ,  Citizenship FROM singer

Question (medium): What are the birth years and citizenships of the singers?
SQL: SELECT Birth_Year ,  Citizenship FROM singer

Question (easy): List the name of singers whose citizenship is not "France".
SQL: SELECT Name FROM singer WHERE Citizenship != "France"

Question (easy): What are the names of the singers who are not French citizens?
SQL: SELECT Name FROM singer WHERE Citizenship != "France"

Question (medium): Show the name of singers whose birth year is either 1948 or 1949?
SQL: SELECT Name FROM singer WHERE Birth_Year  =  1948 OR Birth_Year  =  1949

Question (medium): What are the names of the singers whose birth years are either 1948 or 1949?
SQL: SELECT Name FROM singer WHERE Birth_Year  =  1948 OR Birth_Year  =  1949

Question (medium): What is the name of the singer with the largest net worth?
SQL: SELECT Name FROM singer ORDER BY Net_Worth_Millions DESC LIMIT 1

Question (medium): What is the name of the singer who is worth the most?
SQL: SELECT Name FROM singer ORDER BY Net_Worth_Millions DESC LIMIT 1

Question (medium): Show different citizenship of singers and the number of singers of each citizenship.
SQL: SELECT Citizenship ,  COUNT(*) FROM singer GROUP BY Citizenship

Question (medium): For each citizenship, how many singers are from that country?
SQL: SELECT Citizenship ,  COUNT(*) FROM singer GROUP BY Citizenship

Question (hard): Please show the most common citizenship of singers.
SQL: SELECT Citizenship FROM singer GROUP BY Citizenship ORDER BY COUNT(*) DESC LIMIT 1

Question (hard): What is the most common singer citizenship ?
SQL: select citizenship from singer group by citizenship order by count(*) desc limit 1

Question (medium): Show different citizenships and the maximum net worth of singers of each citizenship.
SQL: SELECT Citizenship ,  max(Net_Worth_Millions) FROM singer GROUP BY Citizenship

Question (medium): For each citizenship, what is the maximum net worth?
SQL: SELECT Citizenship ,  max(Net_Worth_Millions) FROM singer GROUP BY Citizenship

Question (medium): Show titles of songs and names of singers.
SQL: SELECT T2.Title ,  T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID

Question (medium): What are the song titles and singer names?
SQL: SELECT T2.Title ,  T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID

Question (medium): Show distinct names of singers that have songs with sales more than 300000.
SQL: SELECT DISTINCT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID WHERE T2.Sales  >  300000

Question (medium): what are the different names of the singers that have sales more than 300000?
SQL: SELECT DISTINCT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID WHERE T2.Sales  >  300000

Question (medium): Show the names of singers that have more than one song.
SQL: SELECT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name HAVING COUNT(*)  >  1

Question (medium): What are the names of the singers that have more than one songs?
SQL: SELECT T1.Name FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name HAVING COUNT(*)  >  1

Question (medium): Show the names of singers and the total sales of their songs.
SQL: SELECT T1.Name ,  sum(T2.Sales) FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name

Question (medium): For each singer name, what is the total sales for their songs?
SQL: SELECT T1.Name ,  sum(T2.Sales) FROM singer AS T1 JOIN song AS T2 ON T1.Singer_ID  =  T2.Singer_ID GROUP BY T1.Name

Question (hard): List the name of singers that do not have any song.
SQL: SELECT Name FROM singer WHERE Singer_ID NOT IN (SELECT Singer_ID FROM song)

Question (hard): What is the sname of every sing that does not have any song?
SQL: SELECT Name FROM singer WHERE Singer_ID NOT IN (SELECT Singer_ID FROM song)

Question (hard): Show the citizenship shared by singers with birth year before 1945 and after 1955.
SQL: SELECT Citizenship FROM singer WHERE Birth_Year  <  1945 INTERSECT SELECT Citizenship FROM singer WHERE Birth_Year  >  1955

Question (hard): What are the citizenships that are shared by singers with a birth year before 1945 and after 1955?
SQL: SELECT Citizenship FROM singer WHERE Birth_Year  <  1945 INTERSECT SELECT Citizenship FROM singer WHERE Birth_Year  >  1955