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
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
What are the names of all campuses located at Chico?
assistant
SELECT campus FROM campuses WHERE LOCATION = 'Chico'
human
Find the name of the campuses that is in Northridge, Los Angeles or in San Francisco, San Francisco.
assistant
SELECT campus FROM campuses WHERE LOCATION = 'Northridge' AND county = 'Los Angeles' UNION SELECT campus FROM campuses WHERE LOCATION = 'San Francisco' AND county = 'San Francisco'
human
Which university is in Los Angeles county and opened after 1950?
assistant
SELECT campus FROM campuses WHERE county = 'Los Angeles' AND YEAR > 1950
human
How many campuses are there in Los Angeles county?
assistant
SELECT count(*) FROM campuses WHERE county = 'Los Angeles'
human
Report the name of all campuses in Los Angeles county.
assistant
SELECT campus FROM campuses WHERE county = "Los Angeles" ;