
CREATE TABLE "Ship" (
	"Ship_ID" INTEGER, 
	"Name" TEXT, 
	"Type" TEXT, 
	"Built_Year" REAL, 
	"Class" TEXT, 
	"Flag" TEXT, 
	PRIMARY KEY ("Ship_ID")
)


CREATE TABLE captain (
	"Captain_ID" INTEGER, 
	"Name" TEXT, 
	"Ship_ID" INTEGER, 
	age TEXT, 
	"Class" TEXT, 
	"Rank" TEXT, 
	PRIMARY KEY ("Captain_ID"), 
	FOREIGN KEY("Ship_ID") REFERENCES "Ship" ("Ship_ID")
)

CREATE TABLE "Ship" (
	"Ship_ID" INTEGER, 
	"Name" TEXT, 
	"Type" TEXT, 
	"Built_Year" REAL, 
	"Class" TEXT, 
	"Flag" TEXT, 
	PRIMARY KEY ("Ship_ID")
)

/*
2 rows from Ship table:
Ship_ID	Name	Type	Built_Year	Class	Flag
1	HMS Manxman	Panamax	1997.0	KR	Panama
2	HMS Gorgon	Panamax	1998.0	KR	Panama
*/


CREATE TABLE captain (
	"Captain_ID" INTEGER, 
	"Name" TEXT, 
	"Ship_ID" INTEGER, 
	age TEXT, 
	"Class" TEXT, 
	"Rank" TEXT, 
	PRIMARY KEY ("Captain_ID"), 
	FOREIGN KEY("Ship_ID") REFERENCES "Ship" ("Ship_ID")
)

/*
2 rows from captain table:
Captain_ID	Name	Ship_ID	age	Class	Rank
1	Captain Sir Henry Langford	1	40	Third-rate ship of the line	Midshipman
2	Captain Beves Conway	2	54	Third-rate ship of the line	Midshipman
*/
What are the names of ships that are commanded by both captains with the rank of Midshipman and captains with the rank of Lieutenant?
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'
[{'Name': 'HMS Gorgon'}]