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 different captain ranks are there?
assistant
SELECT count(DISTINCT rank) FROM captain
human
Find the name of the ships that have more than one captain.
assistant
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id = t2.ship_id GROUP BY t2.ship_id HAVING count(*) > 1
human
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
Find the name of captains whose rank are either Midshipman or Lieutenant.
assistant
SELECT name FROM captain WHERE rank = 'Midshipman' OR rank = 'Lieutenant'
human
what are the names and classes of the ships that do not have any captain yet?
assistant
SELECT name , CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)
human
What are the names and flags of ships that do not have a captain with the rank of Midshipman?
assistant
SELECT name , flag FROM ship WHERE ship_id NOT IN ( SELECT ship_id FROM captain WHERE rank = "Midshipman" ) ;