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
List the nations that have more than two ships.
assistant
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*) > 2
human
How many ships are there?
assistant
SELECT count(*) FROM ship
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
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
What are the mission codes, fates, and names of the 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 ;