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 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
List the name of ships whose nationality is not 'United States'.
assistant
SELECT Name FROM ship WHERE Nationality != 'United States'
human
Show different types of ships and the number of ships of each type.
assistant
SELECT TYPE , COUNT(*) FROM ship GROUP BY TYPE
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
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 ;