human
Schema:
mission (Mission_ID, Ship_ID, Code, Launched_Year, Location, Speed_knots, Fate)
ship (Ship_ID, Name, Type, Nationality, Tonnage)
Please answer with SQL.
assistant
Sure, I understand.
human
List the name of ships in ascending order of tonnage.
assistant
SELECT Name FROM ship ORDER BY Tonnage ASC
human
Please show the most common type of ships.
assistant
SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
human
Show the distinct fate of missions that involve ships with nationality 'United States'
assistant
SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID WHERE T2.Nationality = 'United States'
human
List the name of ships that are not involved in any mission
assistant
SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)
human
Show names of ships involved in a mission launched after 1928.
assistant
SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID WHERE T1.Launched_Year > 1928
human
Show codes and fates of missions, and names of ships involved.
assistant
SELECT T1.Code , T1.Fate , T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID ;