human
Schema:
mission (Mission_ID, Ship_ID, Code, Launched_Year, Location, Speed_knots, Fate)
ship (Ship_ID, Name, Type, Nationality, Tonnage)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the types of ships that have both ships with tonnage larger than 6000 and ships with tonnage smaller than 4000.
assistant
SELECT TYPE FROM ship WHERE Tonnage > 6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage < 4000;
human
Show the types of ships with tonnage between 3000 and 5000.
assistant
SELECT distinct TYPE FROM ship WHERE Tonnage > 3000 AND Tonnage < 5000;
human
What is the nationality of the ship with maximum tonnage?
assistant
SELECT Nationality FROM ship ORDER by Tonnage desc LIMIT 1;
human
Can you give me the nationality of the ship with minimum tonnage.
assistant
SELECT Nationality FROM ship ORDER by Tonnage LIMIT 1;
human
What is the maximum and the minimum tonnage for a cargo ship?
assistant
SELECT MAX( Tonnage ) , MIN( Tonnage ) FROM ship WHERE Type = 'Cargo ship';
human
Hmmm. Can you also give me the maximum and the minimum tonnage for a battle ship?
assistant
SELECT MAX( Tonnage ) , MIN( Tonnage ) FROM ship WHERE Type = "Battle ship" ;