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
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 different captain ranks are there?
assistant
SELECT count(DISTINCT rank) FROM captain
human
How many captains are in each rank?
assistant
SELECT count(*) , rank FROM captain GROUP BY rank
human
What are the ranks of captains that have no captain that are in the Third-rate ship of the line class?
assistant
SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS = "Third-rate ship of the line" ;