human
Schema:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (ID, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (ID, course_id, sec_id, semester, year)
student (ID, name, dept_name, tot_cred)
takes (ID, course_id, sec_id, semester, year, grade)
advisor (s_ID, i_ID)
time_slot (time_slot_id, day, start_hr, start_min, end_hr, end_min)
prereq (course_id, prereq_id)
Please answer with SQL.
assistant
Sure, I understand.
human
How many rooms does the Lamberton building have?
assistant
SELECT count(*) FROM classroom WHERE building = 'Lamberton'
human
Find the maximum and average capacity among rooms in each building.
assistant
SELECT max(capacity) , avg(capacity) , building FROM classroom GROUP BY building
human
Find the room number of the rooms which can sit 50 to 100 students and their buildings.
assistant
SELECT building , room_number FROM classroom WHERE capacity BETWEEN 50 AND 100
human
How many rooms whose capacity is less than 50 does the Lamberton building have?
assistant
SELECT count(*) FROM classroom WHERE building = 'Lamberton' AND capacity < 50
human
Find the number of rooms with more than 50 capacity for each building.
assistant
SELECT count(*) , building FROM classroom WHERE capacity > 50 GROUP BY building
human
What are the distinct buildings with capacities of greater than 50?
assistant
SELECT DISTINCT building FROM classroom WHERE capacity > 50 ;