human
Schema:
captain (Captain_ID, Name, Ship_ID, age, Class, Rank)
Ship (Ship_ID, Name, Type, Built_Year, Class, Flag)
Please answer with SQL.
assistant
Sure, I understand.
human
how many ships are there?
assistant
SELECT count(*) FROM ship
human
Find the ship type that are used by both ships with Panama and Malta flags.
assistant
SELECT TYPE FROM ship WHERE flag = 'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag = 'Malta'
human
Which flag is most widely used among all ships?
assistant
SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1
human
In which year were most of ships built?
assistant
SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1
human
List all ship names in the order of built year and class.
assistant
SELECT name FROM ship ORDER BY built_year , CLASS
human
What is the name, type, and flag of the ship that was built in the most recent year?
assistant
SELECT name , TYPE , flag FROM ship ORDER BY built_year DESC LIMIT 1 ;