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 name and flag of ships that are not steered by any captain with Midshipman rank.
assistant
SELECT name , flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank = 'Midshipman')
human
Find the name, type, and flag of the ship that is built in the most recent year.
assistant
SELECT name , TYPE , flag FROM ship ORDER BY built_year DESC LIMIT 1
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
Group by ships by flag, and return number of ships that have each flag.
assistant
SELECT count(*) , flag FROM ship GROUP BY flag
human
Which flag is most widely used among all ships?
assistant
SELECT flag FROM ship GROUP BY flag ORDER BY COUNT( * ) DESC LIMIT 1 ;