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
What are the type and nationality of ships?
assistant
SELECT TYPE , Nationality FROM ship
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 types of ships that have both ships with tonnage larger than 6000 and ships with tonnage smaller than 4000.
assistant
SELECT TYPE FROM ship WHERE Tonnage > 6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage < 4000
human
Show different types of ships and the number of ships of each type.
assistant
SELECT TYPE , COUNT(*) FROM ship GROUP BY TYPE
human
Show different types of ships and the average tonnage of ships of each type.
assistant
SELECT TYPE , avg ( Tonnage ) FROM ship GROUP BY TYPE ;