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 name of ships whose nationality is either United States or United Kingdom.
assistant
SELECT Name FROM ship WHERE Nationality = 'United States' OR Nationality = 'United Kingdom'
human
What are the type and nationality of ships?
assistant
SELECT TYPE , Nationality FROM ship
human
Please show the most common type of ships.
assistant
SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
human
List the name of ships whose nationality is not 'United States'.
assistant
SELECT Name FROM ship WHERE Nationality != 'United States'
human
How many ships are there?
assistant
SELECT count(*) FROM ship
human
List the nations that have more than two ships.
assistant
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT( * ) > 2 ;