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 least number of degrees in year 2001?
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 = 2001 ORDER BY t3.degrees LIMIT 1
human
report the total number of degrees granted between 1998 and 2002.
assistant
SELECT T1.campus , sum(T2.degrees) FROM campuses AS T1 JOIN degrees AS T2 ON T1.id = T2.campus WHERE T2.year >= 1998 AND T2.year <= 2002 GROUP BY T1.campus
human
Which campus has the most degrees conferred in all times?
assistant
SELECT campus FROM degrees GROUP BY campus ORDER BY sum(degrees) DESC LIMIT 1
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
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 is the total number of degrees granted after 2000 for each Orange county campus?
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 ;