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 different types of ships and the number of ships of each type.
assistant
SELECT TYPE , COUNT(*) FROM ship GROUP BY TYPE
human
What are the type and nationality of ships?
assistant
SELECT TYPE , Nationality 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 ;