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
Report the name of all campuses in Los Angeles county.
assistant
SELECT campus FROM campuses WHERE county = 'Los Angeles'
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
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
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
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" ) ;