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
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
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 captain rank that has some captains in both Cutter and Armed schooner classes.
assistant
SELECT rank FROM captain WHERE CLASS = 'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS = 'Armed schooner'
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
Find the name, class and rank of all captains.
assistant
SELECT name , CLASS , rank FROM captain
human
Find the captain rank that has no captain in Third-rate ship of the line class.
assistant
SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS = "Third-rate ship of the line" ;