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
Which classes have more than two captains?
assistant
SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*) > 2
human
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
Find the name of the ships that are steered by both a captain with Midshipman rank and a captain with Lieutenant rank.
assistant
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id = t2.ship_id WHERE t2.rank = 'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id = t2.ship_id WHERE t2.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 of 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 ;