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 are there?
assistant
SELECT count(*) FROM ship
human
Show names of ships involved in a mission launched after 1928.
assistant
SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID WHERE T1.Launched_Year > 1928
human
List the name of ships whose nationality is not 'United States'.
assistant
SELECT Name FROM ship WHERE Nationality != 'United States'
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 codes and fates of missions, and names of ships involved.
assistant
SELECT T1.Code , T1.Fate , T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID = T2.Ship_ID
human
What are the different fates of the mission that involved ships from the 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" ;