
CREATE TABLE "Has_Pet" (
	"StuID" INTEGER, 
	"PetID" INTEGER, 
	FOREIGN KEY("PetID") REFERENCES "Pets" ("PetID"), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID")
)


CREATE TABLE "Pets" (
	"PetID" INTEGER, 
	"PetType" VARCHAR(20), 
	pet_age INTEGER, 
	weight REAL, 
	PRIMARY KEY ("PetID")
)


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

CREATE TABLE "Has_Pet" (
	"StuID" INTEGER, 
	"PetID" INTEGER, 
	FOREIGN KEY("PetID") REFERENCES "Pets" ("PetID"), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID")
)

/*
2 rows from Has_Pet table:
StuID	PetID
1001	2001
1002	2002
*/


CREATE TABLE "Pets" (
	"PetID" INTEGER, 
	"PetType" VARCHAR(20), 
	pet_age INTEGER, 
	weight REAL, 
	PRIMARY KEY ("PetID")
)

/*
2 rows from Pets table:
PetID	PetType	pet_age	weight
2001	cat	3	12.0
2002	dog	2	13.4
*/


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

/*
2 rows from Student table:
StuID	LName	Fname	Age	Sex	Major	Advisor	city_code
1001	Smith	Linda	18	F	600	1121	BAL
1002	Kim	Tracy	19	F	600	7712	HKG
*/
What is the average and maximum age for each pet type?
SELECT avg(pet_age) ,  max(pet_age) ,  pettype FROM pets GROUP BY pettype
[{'avg(pet_age)': 3.0, 'max(pet_age)': 3, 'PetType': 'cat'}, {'avg(pet_age)': 1.5, 'max(pet_age)': 2, 'PetType': 'dog'}]