
CREATE TABLE "Campuses" (
	"Id" INTEGER, 
	"Campus" TEXT, 
	"Location" TEXT, 
	"County" TEXT, 
	"Year" INTEGER, 
	PRIMARY KEY ("Id")
)


CREATE TABLE csu_fees (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"CampusFee" INTEGER, 
	PRIMARY KEY ("Campus"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)


CREATE TABLE degrees (
	"Year" INTEGER, 
	"Campus" INTEGER, 
	"Degrees" INTEGER, 
	PRIMARY KEY ("Year", "Campus"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)


CREATE TABLE discipline_enrollments (
	"Campus" INTEGER, 
	"Discipline" INTEGER, 
	"Year" INTEGER, 
	"Undergraduate" INTEGER, 
	"Graduate" INTEGER, 
	PRIMARY KEY ("Campus", "Discipline"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)


CREATE TABLE enrollments (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"TotalEnrollment_AY" INTEGER, 
	"FTE_AY" INTEGER, 
	PRIMARY KEY ("Campus", "Year"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)


CREATE TABLE faculty (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"Faculty" REAL, 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

CREATE TABLE "Campuses" (
	"Id" INTEGER, 
	"Campus" TEXT, 
	"Location" TEXT, 
	"County" TEXT, 
	"Year" INTEGER, 
	PRIMARY KEY ("Id")
)

/*
2 rows from Campuses table:
Id	Campus	Location	County	Year
1	California State University-Bakersfield	Bakersfield	Kern	1965
2	California State University-Channel Islands	Camarillo	Ventura	2002
*/


CREATE TABLE csu_fees (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"CampusFee" INTEGER, 
	PRIMARY KEY ("Campus"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

/*
2 rows from csu_fees table:
Campus	Year	CampusFee
1	1996	1951
2	2003	1868
*/


CREATE TABLE degrees (
	"Year" INTEGER, 
	"Campus" INTEGER, 
	"Degrees" INTEGER, 
	PRIMARY KEY ("Year", "Campus"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

/*
2 rows from degrees table:
Year	Campus	Degrees
1990	1	701
1991	1	681
*/


CREATE TABLE discipline_enrollments (
	"Campus" INTEGER, 
	"Discipline" INTEGER, 
	"Year" INTEGER, 
	"Undergraduate" INTEGER, 
	"Graduate" INTEGER, 
	PRIMARY KEY ("Campus", "Discipline"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

/*
2 rows from discipline_enrollments table:
Campus	Discipline	Year	Undergraduate	Graduate
1	4	2004	248	0
1	5	2004	811	73
*/


CREATE TABLE enrollments (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"TotalEnrollment_AY" INTEGER, 
	"FTE_AY" INTEGER, 
	PRIMARY KEY ("Campus", "Year"), 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

/*
2 rows from enrollments table:
Campus	Year	TotalEnrollment_AY	FTE_AY
1	1956	384	123
1	1957	432	151
*/


CREATE TABLE faculty (
	"Campus" INTEGER, 
	"Year" INTEGER, 
	"Faculty" REAL, 
	FOREIGN KEY("Campus") REFERENCES "Campuses" ("Id")
)

/*
2 rows from faculty table:
Campus	Year	Faculty
1	2002	357.1
2	2002	48.4
*/
How many faculty lines are there in the university that conferred the most number of degrees in year 2002?
SELECT T2.faculty FROM campuses AS T1 JOIN faculty AS T2 ON T1.id  =  t2.campus JOIN degrees AS T3 ON T1.id  =  t3.campus AND t2.year  =  t3.year WHERE t2.year  =  2002 ORDER BY t3.degrees DESC LIMIT 1
[{'Faculty': 1555.7}]