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
How many faculty is there in total in the year of 2002?
assistant
SELECT sum(faculty) FROM faculty WHERE YEAR = 2002
human
What is the number of faculty lines in campus 'Long Beach State University' in 2002?
assistant
SELECT faculty FROM faculty AS T1 JOIN campuses AS T2 ON T1.campus = T2.id WHERE T1.year = 2002 AND T2.campus = 'Long Beach State University'
human
List the campus that have between 600 and 1000 faculty lines in year 2004.
assistant
SELECT T1.campus FROM campuses AS t1 JOIN faculty AS t2 ON t1.id = t2.campus WHERE t2.faculty >= 600 AND t2.faculty <= 1000 AND T1.year = 2004
human
For each Orange county campus, report the number of degrees granted after 2000.
assistant
SELECT T1.campus , sum(T2.degrees) FROM campuses AS T1 JOIN degrees AS T2 ON T1.id = T2.campus WHERE T1.county = 'Orange' AND T2.year >= 2000 GROUP BY T1.campus
human
Which campus has the most faculties in year 2003?
assistant
SELECT T1.campus FROM campuses AS T1 JOIN faculty AS T2 ON T1.id = T2.campus WHERE T2.year = 2003 ORDER BY T2.faculty DESC LIMIT 1
human
What are the names of the campus that have more faculties in 2002 than the maximum number 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" ) ;