human
Schema:
Campuses (Id, Campus, Location, County, Year)
csu_fees (Campus, Year, CampusFee)
degrees (Year, Campus, Degrees)
discipline_enrollments (Campus, Discipline, Year, Undergraduate, Graduate)
enrollments (Campus, Year, TotalEnrollment_AY, FTE_AY)
faculty (Campus, Year, Faculty)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the campus fee of 'San Francisco State University' in year 2000?
assistant
SELECT t1.campusfee FROM csu_fees AS t1 JOIN campuses AS t2 ON t1.campus = t2.id WHERE t2.campus = 'San Francisco State University' AND t1.year = 2000
human
Find the names of the campus which has more faculties in 2002 than every campus in Orange county.
assistant
SELECT T1.campus FROM campuses AS T1 JOIN faculty AS T2 ON T1.id = T2.campus WHERE T2.year = 2002 AND faculty > (SELECT max(faculty) FROM campuses AS T1 JOIN faculty AS T2 ON T1.id = T2.campus WHERE T2.year = 2002 AND T1.county = 'Orange')
human
Find the campus fee of 'San Jose State University' in year 2000.
assistant
SELECT t1.campusfee FROM csu_fees AS t1 JOIN campuses AS t2 ON t1.campus = t2.id WHERE t2.campus = 'San Jose State University' AND t1.year = 2000
human
What is the average fee on a CSU campus in 2005?
assistant
SELECT avg(campusfee) FROM csu_fees WHERE YEAR = 2005
human
Find the average fee on a CSU campus in 1996
assistant
SELECT avg(campusfee) FROM csu_fees WHERE YEAR = 1996
human
Find the count of universities whose campus fee is greater than the average campus fee.
assistant
SELECT COUNT( * ) FROM csu_fees WHERE campusfee > ( SELECT avg ( campusfee ) FROM csu_fees ) ;