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
Which rank is the most common among captains?
assistant
SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
human
What is the name of the youngest captain?
assistant
SELECT name FROM captain ORDER BY age LIMIT 1
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
What are the average and minimum age of captains in different class?
assistant
SELECT avg ( age ) , MIN( age ) , CLASS FROM captain GROUP BY CLASS ;