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 lines are there in the university that conferred the most number of degrees in year 2002?
assistant
SELECT T2.faculty FROM campuses AS T1 JOIN faculty AS T2 ON T1.id = t2.campus JOIN degrees AS T3 ON T1.id = t3.campus AND t2.year = t3.year WHERE t2.year = 2002 ORDER BY t3.degrees DESC LIMIT 1
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
How many undergraduates are there in 'San Jose State University' in year 2004?
assistant
SELECT sum(t1.undergraduate) FROM discipline_enrollments AS t1 JOIN campuses AS t2 ON t1.campus = t2.id WHERE t1.year = 2004 AND t2.campus = 'San Jose State University'
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
What is the number of graduates in 'San Francisco State University' in year 2004?
assistant
SELECT sum(t1.graduate) FROM discipline_enrollments AS t1 JOIN campuses AS t2 ON t1.campus = t2.id WHERE t1.year = 2004 AND t2.campus = 'San Francisco State University'
human
How many faculty lines are there in 'San Francisco State University' in year 2004?
assistant
SELECT faculty FROM faculty AS T1 JOIN campuses AS T2 ON T1.campus = T2.id WHERE T1.year = 2004 AND T2.campus = "San Francisco State University" ;