Q3: Write the following queries in SQL, using the university schema. classroom(b
ID: 3689856 • Letter: Q
Question
Q3: Write the following queries in SQL, using the university 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 time, end time)
prereq(course id, prereq id)
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
b. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result
c. Find the maximum enrollment, across all sections, in Autumn 2009.
d. Find the IDs and names of all students who have not taken any course offering before spring 2009
e. Increase the salary of each instructor in the Comp. Sci. department by 10%.
f. Delete all courses that have never been offered (that is, do not occur in the section relation).
g. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
h. Create a new course “CS-001”, titled “Weekly Seminar”,with 0 credits.
i. Create a section of this course in autumn 2009, with sec id of 1.
j. Enroll every student in the Comp. Sci. department in the above section.
Explanation / Answer
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 time, end time)
prereq(course id, prereq id)
a. Find the titles of courses in the Comp. Sci. department that have 3 credits.
Sol: select title from course where deptname = "Comp. Sci" and credits >= '3' ;
b. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result
Sol: select name from student naturaljoin takes naturaljoin course where course.deptname='Comp.Sci';
c. Find the maximum enrollment, across all sections, in Autumn 2009.
Sol: select max(enrollment) from (select count(ID) as enrollment from section natural join takes where semester = ’Autumn’ and year = 2009 group by course id, sec id)
d. Find the IDs and names of all students who have not taken any course offering before spring 2009
Sol: select id, name from student naturaljoin takes where year<2009;
e. Increase the salary of each instructor in the Comp. Sci. department by 10%.
Sol: update instructor set salary = salary * 1.10 where dept name = ’Comp. Sci.’
f. Delete all courses that have never been offered (that is, do not occur in the section relation).
Sol: delete from course where course id not in (select course id from section)
g. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
Sol: insert into instructor select ID, name, dept name, 10000 from student where tot cred > 100
h. Create a new course “CS-001”, titled “Weekly Seminar”,with 0 credits.
Sol: insert into course (course id, title, dept name, credits) values ("CS-001", "Weekly Seminar", "Comp.Sci" , 0)
i. Create a section of this course in autumn 2009, with sec id of 1.
Sol: insert into section(course id, sec id, semester, year, building, room number, time slot id) values ("CS-001", "1", 2, 2009, 3, 1, TS01)
j. Enroll every student in the Comp. Sci. department in the above section.
Sol:
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.