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
List the name of ships that are not involved in any mission
assistant
SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)
human
Show the distinct fate of missions that involve ships with nationality 'United States'
assistant
SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID WHERE T2.Nationality = 'United States'
human
What are the type and nationality of ships?
assistant
SELECT TYPE , Nationality FROM ship
human
List the nations that have more than two ships.
assistant
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*) > 2
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
List the name of ships whose nationality is not 'United States'.
assistant
SELECT Name FROM ship WHERE Nationality != "United States" ;