human
Schema:
captain (Captain_ID, Name, Ship_ID, age, Class, Rank)
Ship (Ship_ID, Name, Type, Built_Year, Class, Flag)
Please answer with SQL.
assistant
Sure, I understand.
human
what are the names and classes of the ships that do not have any captain yet?
assistant
SELECT name , CLASS FROM ship WHERE ship_id NOT IN ( SELECT ship_id FROM captain );
human
What are the names of the captains that have classes?
assistant
SELECT name FROM captain WHERE class ! = 'NULL';
human
What is the id for HMS Gorgon? | Did you mean ship id? | yes
assistant
SELECT Ship_ID FROM ship WHERE name = 'HMS Gorgon';
human
How many classes are there? | Did you mean the number of distinct type classes of ships? | yes
assistant
SELECT COUNT( distinct Class ) FROM Ship ;