Write the following queries in SQL, using the university schema. Find the titles

Question

Write the following queries in SQL, using the university schema. Find the titles of courses in the Comp. Sci. department that have 3 credits. 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 Find the maximum enrollment, across all sections, in Autumn 2009. 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%. Delete all courses that have never been offered (that is, do not occur in the section relation). Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. Create a new course "CS-001", titled "Weekly Seminar", with 0 credits. Create a section of this course in autumn 2009, with sec id of 1. Enroll every student in the Comp. Sci. department in the above section.

Explanation / Answer

a.
select title from course where dept='Comp. Sci.' and credits=3;

b.
select distinct(name) from student where dept='Comp. Sci.';

c.
select count(course_id) from section group by course_id having year='Autumn 2009';

d.

e.
update instructor set salary=salary+(salary*10/100) where dept='Comp. Sci.';

f.
delete from course where course_id <> section.course_id;

g.

h.
insert into course values('CS-001','Weekly Seminar','Comp. Sci', 0);

i.
insert into section values('CS-001', 1, 1, 'autumn 2009',3, 4, 11);

j.
update takes set course_id='CS-001';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.