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 have more than one captain?
assistant
SELECT COUNT( * ) FROM ( SELECT * 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 ships have a single captain?
assistant
SELECT COUNT( * ) FROM ( SELECT * FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id = t2.ship_id GROUP BY t2.ship_id HAVING COUNT( * ) = 1 );
human
what is the age of the oldest captain?
assistant
SELECT age FROM captain ORDER by age desc LIMIT 1;
human
what about the youngest?
assistant
SELECT age FROM captain ORDER by age LIMIT 1 ;