Database: ship_mission

CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
);
CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
);

Question: How many ships are there?
SQL: SELECT count(*) FROM ship

Question: What is the number of ships?
SQL: SELECT count(*) FROM ship

Question: List the name of ships in ascending order of tonnage.
SQL: SELECT Name FROM ship ORDER BY Tonnage ASC

Question: what are the names of the ships ordered by ascending tonnage?
SQL: SELECT Name FROM ship ORDER BY Tonnage ASC

Question: What are the type and nationality of ships?
SQL: SELECT TYPE ,  Nationality FROM ship

Question: What are the types and nationalities of every ship?
SQL: SELECT TYPE ,  Nationality FROM ship

Question: List the name of ships whose nationality is not "United States".
SQL: SELECT Name FROM ship WHERE Nationality != "United States"

Question: What are the names of the ships that are not from the United States?
SQL: SELECT Name FROM ship WHERE Nationality != "United States"

Question: Show the name of ships whose nationality is either United States or United Kingdom.
SQL: SELECT Name FROM ship WHERE Nationality  =  "United States" OR Nationality  =  "United Kingdom"

Question: What are the names of the ships  that are from either the US or the UK?
SQL: SELECT Name FROM ship WHERE Nationality  =  "United States" OR Nationality  =  "United Kingdom"

Question: What is the name of the ship with the largest tonnage?
SQL: SELECT Name FROM ship ORDER BY Tonnage DESC LIMIT 1

Question: What is the ship with the largest amount of tonnage called?
SQL: SELECT Name FROM ship ORDER BY Tonnage DESC LIMIT 1

Question: Show different types of ships and the number of ships of each type.
SQL: SELECT TYPE ,  COUNT(*) FROM ship GROUP BY TYPE

Question: For each type, how many ships are there?
SQL: SELECT TYPE ,  COUNT(*) FROM ship GROUP BY TYPE

Question: Please show the most common type of ships.
SQL: SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the most common type of ships?
SQL: SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1

Question: List the nations that have more than two ships.
SQL: SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*)  >  2

Question: What are the nations that have more than two ships?
SQL: SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*)  >  2

Question: Show different types of ships and the average tonnage of ships of each type.
SQL: SELECT TYPE ,  avg(Tonnage) FROM ship GROUP BY TYPE

Question: For each type, what is the average tonnage?
SQL: SELECT TYPE ,  avg(Tonnage) FROM ship GROUP BY TYPE

Question: Show codes and fates of missions, and names of ships involved.
SQL: SELECT T1.Code ,  T1.Fate ,  T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID

Question: What are the mission codes, fates, and names of the ships involved?
SQL: SELECT T1.Code ,  T1.Fate ,  T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID

Question: Show names of ships involved in a mission launched after 1928.
SQL: SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T1.Launched_Year  >  1928

Question: What are the names of ships that were involved in a mission launched after 1928?
SQL: SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T1.Launched_Year  >  1928

Question: Show the distinct fate of missions that involve ships with nationality "United States"
SQL: SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T2.Nationality  =  "United States"

Question: What are the different fates of the mission that involved ships from the United States?
SQL: SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T2.Nationality  =  "United States"

Question: List the name of ships that are not involved in any mission
SQL: SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)

Question: What are the names of the ships that are not involved in any missions?
SQL: SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)

Question: Show the types of ships that have both ships with tonnage larger than 6000 and ships with tonnage smaller than 4000.
SQL: SELECT TYPE FROM ship WHERE Tonnage  >  6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage  <  4000

Question: What are the types of the ships that have both shiips with tonnage more than 6000 and those with tonnage less than 4000?
SQL: SELECT TYPE FROM ship WHERE Tonnage  >  6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage  <  4000