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 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
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
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
Find the name, class and rank of all captains.
assistant
SELECT name , CLASS , rank FROM captain
human
Give the classes that have more than two captains.
assistant
SELECT CLASS FROM captain GROUP BY CLASS HAVING COUNT( * ) > 2 ;