
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 degrees were conferred between 1998 and 2002?
SELECT T1.campus ,  sum(T2.degrees) FROM campuses AS T1 JOIN degrees AS T2 ON T1.id  =  T2.campus WHERE T2.year  >=  1998 AND T2.year  <=  2002 GROUP BY T1.campus
[{'Campus': 'California  Maritime Academy', 'sum(T2.degrees)': 405}, {'Campus': 'California Polytechnic State University-San Luis Obispo', 'sum(T2.degrees)': 16402}, {'Campus': 'California State Polytechnic University-Pomona', 'sum(T2.degrees)': 13493}, {'Campus': 'California State University-Bakersfield', 'sum(T2.degrees)': 4967}, {'Campus': 'California State University-Channel Islands', 'sum(T2.degrees)': 2}, {'Campus': 'California State University-Chico', 'sum(T2.degrees)': 13762}, {'Campus': 'California State University-Dominguez Hills', 'sum(T2.degrees)': 8681}, {'Campus': 'California State University-East Bay', 'sum(T2.degrees)': 11622}, {'Campus': 'California State University-Fullerton', 'sum(T2.degrees)': 23457}, {'Campus': 'California State University-Los Angeles', 'sum(T2.degrees)': 13032}, {'Campus': 'California State University-Monterey Bay', 'sum(T2.degrees)': 1626}, {'Campus': 'California State University-Northridge', 'sum(T2.degrees)': 22141}, {'Campus': 'California State University-Sacramento', 'sum(T2.degrees)': 20155}, {'Campus': 'California State University-San Bernardino', 'sum(T2.degrees)': 10959}, {'Campus': 'California State University-San Marcos', 'sum(T2.degrees)': 5275}, {'Campus': 'California State University-Stanislaus', 'sum(T2.degrees)': 6181}, {'Campus': 'Fresno State University', 'sum(T2.degrees)': 14060}, {'Campus': 'Humboldt State University', 'sum(T2.degrees)': 6963}, {'Campus': 'Long Beach State University', 'sum(T2.degrees)': 22502}, {'Campus': 'San Diego State University', 'sum(T2.degrees)': 26418}, {'Campus': 'San Francisco State University', 'sum(T2.degrees)': 21323}, {'Campus': 'San Jose State University', 'sum(T2.degrees)': 19945}, {'Campus': 'Sonoma State University', 'sum(T2.degrees)': 7204}]