
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
*/
What campus started in year 1956, has more than 200 full time students, and more than 400 students enrolled?
SELECT T1.campus FROM campuses AS t1 JOIN enrollments AS t2 ON t1.id  =  t2.campus WHERE t2.year  =  1956 AND totalenrollment_ay  >  400 AND FTE_AY  >  200
[{'Campus': 'California State University-Chico'}, {'Campus': 'Fresno State University'}, {'Campus': 'Humboldt State University'}, {'Campus': 'Long Beach State University'}, {'Campus': 'California State University-Los Angeles'}, {'Campus': 'California State University-Northridge'}, {'Campus': 'California State Polytechnic University-Pomona'}, {'Campus': 'California State University-Sacramento'}, {'Campus': 'San Diego State University'}, {'Campus': 'San Francisco State University'}, {'Campus': 'San Jose State University'}, {'Campus': 'California Polytechnic State University-San Luis Obispo'}]