
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
*/
Return the average and minimum age of captains in each class.
SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS
[{'avg(age)': 45.0, 'min(age)': '45', 'Class': 'Armed schooner'}, {'avg(age)': 43.0, 'min(age)': '43', 'Class': 'Cutter'}, {'avg(age)': 38.0, 'min(age)': '38', 'Class': 'Frigate'}, {'avg(age)': 38.0, 'min(age)': '38', 'Class': 'Sloop-of-war'}, {'avg(age)': 51.333333333333336, 'min(age)': '40', 'Class': 'Third-rate ship of the line'}]