
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 female (sex is F) students who 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.Sex  =  "F" AND T2.Election_Cycle  =  "Spring"
[{'avg(T1.Age)': 21.0}]