CREATE TABLE ship (ship_id NUMBER, name CLOB, type CLOB, built_year NUMBER, class CLOB, flag CLOB)
CREATE TABLE captain (captain_id NUMBER, name CLOB, ship_id NUMBER, age CLOB, class CLOB, rank CLOB)

What are the names and flags of ships that do not have a captain with the rank of Midshipman?
SELECT name, flag FROM ship WHERE NOT ship_id IN (SELECT ship_id FROM captain WHERE rank = 'Midshipman')
SELECT "name", "flag" FROM "ship" WHERE NOT "ship_id" IN (SELECT "ship_id" FROM "captain" WHERE "rank" = 'Midshipman')