Write the following queries in SQL, using the university schema. (1.5 Point) cla

Question

Write the following queries in SQL, using the university schema.                                              (1.5 Point)

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

Find the titles of courses in the Comp. Sci. department that have 3 credits.

select title
from course
where dept name = ’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

select distinct name
from student
where ID in ( select ID
from course natural join takes
where dept_name = 'Comp. Sci.');

c.Find the maximum enrollment, across all sections, in Autumn 2009.

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

select id, name
From student
Except
Select id, name
from student natural join takes
where year<2009

e.Increase the salary of each instructor in the Comp. Sci. department by 10%.

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).

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.

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.

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

i.Create a section of this course in autumn 2009, with sec id of 1.

insert into section values ('CS-001', '1', 'Fall', '2009', NULL , NULL , NULL );

j.Enroll every student in the Comp. Sci. department in the above section

insert into takes
select ID,'CS-001','1','Fall','2009','0'
from student
where dept_name = 'Comp. Sci.';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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