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, class and rank of all captains.
assistant
SELECT name , CLASS , rank FROM captain
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
How many captains with younger than 50 are in each rank?
assistant
SELECT count(*) , rank FROM captain WHERE age < 50 GROUP BY rank
human
Sort all captain names by their ages from old to young.
assistant
SELECT name FROM captain ORDER BY age DESC
human
Find the name of the ship that is steered by the youngest captain.
assistant
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id = t2.ship_id ORDER BY t2.age LIMIT 1
human
Return the name of the youngest captain.
assistant
SELECT name FROM captain ORDER BY age LIMIT 1 ;