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
How many ships are there?
assistant
SELECT count(*) FROM ship
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 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 nations that have more than two ships.
assistant
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*) > 2
human
List the name of ships whose nationality is not 'United States'.
assistant
SELECT Name FROM ship WHERE 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 ) ;