
CREATE TABLE "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)


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 "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)

/*
2 rows from Allergy_Type table:
Allergy	AllergyType
Eggs	food
Nuts	food
*/


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)

/*
2 rows from Has_Allergy table:
StuID	Allergy
1001	Cat
1002	Shellfish
*/


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
*/
How many students does each advisor have?
SELECT advisor ,  count(*) FROM Student GROUP BY advisor
[{'Advisor': 1121, 'count(*)': 3}, {'Advisor': 1148, 'count(*)': 3}, {'Advisor': 2192, 'count(*)': 4}, {'Advisor': 2311, 'count(*)': 3}, {'Advisor': 5718, 'count(*)': 2}, {'Advisor': 7134, 'count(*)': 2}, {'Advisor': 7271, 'count(*)': 2}, {'Advisor': 7712, 'count(*)': 1}, {'Advisor': 7723, 'count(*)': 1}, {'Advisor': 7792, 'count(*)': 1}, {'Advisor': 8423, 'count(*)': 1}, {'Advisor': 8721, 'count(*)': 1}, {'Advisor': 8722, 'count(*)': 3}, {'Advisor': 8723, 'count(*)': 1}, {'Advisor': 8741, 'count(*)': 1}, {'Advisor': 8772, 'count(*)': 3}, {'Advisor': 8918, 'count(*)': 1}, {'Advisor': 9172, 'count(*)': 1}]