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
Sort all captain names by their ages from old to young.
assistant
SELECT name FROM captain ORDER BY age DESC
human
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
Which classes have more than two captains?
assistant
SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*) > 2
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
Find the name, class and rank of all captains.
assistant
SELECT name , CLASS , rank FROM captain
human
Return the average and minimum age of captains in each class.
assistant
SELECT avg ( age ) , MIN( age ) , CLASS FROM captain GROUP BY CLASS ;