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 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
How many different captain ranks are there?
assistant
SELECT count(DISTINCT rank) FROM captain
human
Which rank is the most common among captains?
assistant
SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
human
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
What are the names of captains that have either the rank Midshipman or Lieutenant?
assistant
SELECT name FROM captain WHERE rank = "Midshipman" OR rank = "Lieutenant" ;