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
How many ships does United Kingdom have?
assistant
SELECT Nationality , COUNT( * ) FROM ship WHERE Nationality = 'United Kingdom' GROUP BY Nationality;
human
Which other country has more than 2 ships?
assistant
SELECT Nationality FROM ship WHERE Nationality ! = "United Kingdom" GROUP BY Nationality HAVING COUNT( * ) > 2 ;