Database: ship_1

CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
);
CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
);

Question: How many different captain ranks are there?
SQL: SELECT count(DISTINCT rank) FROM captain

Question: Count the number of different ranks of captain.
SQL: SELECT count(DISTINCT rank) FROM captain

Question: How many captains are in each rank?
SQL: SELECT count(*) ,  rank FROM captain GROUP BY rank

Question: Count the number of captains that have each rank.
SQL: SELECT count(*) ,  rank FROM captain GROUP BY rank

Question: How many captains with younger than 50 are in each rank?
SQL: SELECT count(*) ,  rank FROM captain WHERE age  <  50 GROUP BY rank

Question: Count the number of captains younger than 50 of each rank.
SQL: SELECT count(*) ,  rank FROM captain WHERE age  <  50 GROUP BY rank

Question: Sort all captain names by their ages from old to young.
SQL: SELECT name FROM captain ORDER BY age DESC

Question: What are the names of captains, sorted by age descending?
SQL: SELECT name FROM captain ORDER BY age DESC

Question: Find the name, class and rank of all captains.
SQL: SELECT name ,  CLASS ,  rank FROM captain

Question: What are the names, classes, and ranks of all captains?
SQL: SELECT name ,  CLASS ,  rank FROM captain

Question: Which rank is the most common among captains?
SQL: SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1

Question: Return the rank for which there are the fewest captains.
SQL: SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1

Question: Which classes have more than two captains?
SQL: SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2

Question: Give the classes that have more than two captains.
SQL: SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2

Question: Find the name of captains whose rank are either Midshipman or Lieutenant.
SQL: SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'

Question: What are the names of captains that have either the rank Midshipman or Lieutenant?
SQL: SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'

Question: What are the average and minimum age of captains in different class?
SQL: SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS

Question: Return the average and minimum age of captains in each class.
SQL: SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS

Question: Find the captain rank that has some captains in both Cutter and Armed schooner classes.
SQL: SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'

Question: What are the ranks of captains that are both in the Cutter and Armed schooner classes?
SQL: SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'

Question: Find the captain rank that has no captain in Third-rate ship of the line class.
SQL: SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'

Question: What are the ranks of captains that have no captain that are in the Third-rate ship of the line class?
SQL: SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'

Question: What is the name of the youngest captain?
SQL: SELECT name FROM captain ORDER BY age LIMIT 1

Question: Return the name of the youngest captain.
SQL: SELECT name FROM captain ORDER BY age LIMIT 1

Question: how many ships are there?
SQL: SELECT count(*) FROM ship

Question: Count the number of ships.
SQL: SELECT count(*) FROM ship

Question: Find the name, type, and flag of the ship that is built in the most recent year.
SQL: SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1

Question: What is the name, type, and flag of the ship that was built in the most recent year?
SQL: SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1

Question: Group by ships by flag, and return number of ships that have each flag.
SQL: SELECT count(*) ,  flag FROM ship GROUP BY flag

Question: What are the different ship flags, and how many ships have each?
SQL: SELECT count(*) ,  flag FROM ship GROUP BY flag

Question: Which flag is most widely used among all ships?
SQL: SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1

Question: Return the flag that is most common among all ships.
SQL: SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1

Question: List all ship names in the order of built year and class.
SQL: SELECT name FROM ship ORDER BY built_year ,  CLASS

Question: What are the names of ships, ordered by year they were built and their class?
SQL: SELECT name FROM ship ORDER BY built_year ,  CLASS

Question: Find the ship type that are used by both ships with Panama and Malta flags.
SQL: SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'

Question: What types of ships have both ships that have Panama Flags and Malta flags?
SQL: SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'

Question: In which year were most of ships built?
SQL: SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1

Question: What is the year in which most ships were built?
SQL: SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the ships that have more than one captain.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1

Question: What are the names of ships that have more than one captain?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1

Question: what are the names and classes of the ships that do not have any captain yet?
SQL: SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)

Question: Return the names and classes of ships that do not have a captain?
SQL: SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)

Question: Find the name of the ship that is steered by the youngest captain.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1

Question: What is the name of the ship that is commanded by the youngest captain?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1

Question: Find the name and flag of ships that are not steered by any captain with Midshipman rank.
SQL: SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')

Question: What are the names and flags of ships that do not have a captain with the rank of Midshipman?
SQL: SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')

Question: Find the name of the ships that are steered by both a captain with Midshipman rank and a captain with Lieutenant rank.
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'

Question: What are the names of ships that are commanded by both captains with the rank of Midshipman and captains with the rank of Lieutenant?
SQL: SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'