
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 have more than one captain?
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1
[{'Name': 'HMS Gorgon'}, {'Name': 'HM Cutter Avenger'}]