
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 "Voting_record" (
	"StuID" INTEGER, 
	"Registration_Date" VARCHAR(12), 
	"Election_Cycle" VARCHAR(12), 
	"President_Vote" INTEGER, 
	"Vice_President_Vote" INTEGER, 
	"Secretary_Vote" INTEGER, 
	"Treasurer_Vote" INTEGER, 
	"Class_President_Vote" INTEGER, 
	"Class_Senator_Vote" INTEGER, 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Vice_President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Secretary_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Treasurer_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Class_President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Class_Senator_Vote") REFERENCES "Student" ("StuID")
)

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
*/


CREATE TABLE "Voting_record" (
	"StuID" INTEGER, 
	"Registration_Date" VARCHAR(12), 
	"Election_Cycle" VARCHAR(12), 
	"President_Vote" INTEGER, 
	"Vice_President_Vote" INTEGER, 
	"Secretary_Vote" INTEGER, 
	"Treasurer_Vote" INTEGER, 
	"Class_President_Vote" INTEGER, 
	"Class_Senator_Vote" INTEGER, 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Vice_President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Secretary_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Treasurer_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Class_President_Vote") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Class_Senator_Vote") REFERENCES "Student" ("StuID")
)

/*
2 rows from Voting_record table:
StuID	Registration_Date	Election_Cycle	President_Vote	Vice_President_Vote	Secretary_Vote	Treasurer_Vote	Class_President_Vote	Class_Senator_Vote
1001	08/30/2015	Spring	1004	1007	1010	1035	1001	1028
1002	08/30/2014	Spring	1014	1007	1020	1025	1021	1028
*/
Find the average age of students who live in the city with code "NYC" and have secretary votes in the spring election cycle.
SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  SECRETARY_Vote WHERE T1.city_code  =  "NYC" AND T2.Election_Cycle  =  "Spring"
[{'avg(T1.Age)': 22.0}]