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
Show different types of ships and the average tonnage of ships of each type.
assistant
SELECT TYPE , avg(Tonnage) FROM ship GROUP BY TYPE
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
What is the name of the ship with the largest tonnage?
assistant
SELECT Name FROM ship ORDER BY Tonnage DESC LIMIT 1
human
What are the types of the ships that have both shiips with tonnage more than 6000 and those with tonnage less than 4000?
assistant
SELECT TYPE FROM ship WHERE Tonnage > 6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage < 4000 ;