1. Create a new course “CS-001”, titled “Weekly Seminar”, with 3 credits.
insert into course
values (‘CS-001’, ‘Weekly Seminar’, ‘Comp. Sci.’, 3)
2. Create a section of the course from Problem 13 in Fall 2017, with sec_id of 1, and with the
location of this section not yet specified.
insert into section
values (‘CS-001’, 1, ‘Fall’, 2017, null, null, null)
3. Enroll every student in the Comp. Sci. department in the section from Problem 14
insert into takes
select ID, ‘CS-001’, 1, ‘Fall’, 2017, null
from student
where dept name = ‘Comp. Sci.’
4. Delete enrollment in the section from Problem 15 where the student’s ID is 1402.
delete from takes
where course_id= ‘CS-001’ and sec_id = 1 and
year = 2017 and semester = ‘Fall’ and ID=1402
5. Delete the course CS-001. What will happen if you run this delete statement without first
deleting offerings (sections) of this course?
delete from takes
where course id = ‘CS-001‘;
delete from section
where course id = ‘CS-001‘;
delete from course
where course id = ‘CS-001‘;
6. Using the university schema, write an SQL query to find the name and ID of those Accounting
students advised by an instructor in the Physics department.
select student.ID, student.name
from student, advisor, instructor
where student.ID = advisor.s_ID
and instructor.ID= advisor.i_iD
and student.dept_name= ‘Accounting’
and instructor.dept_name= ‘Physics’;
3 rows
7. Using the university schema, write an SQL query to find the names of those departments
whose budget is higher than that of Geology. List them in alphabetic order.
select X.dept_name
from department as X, department as H
where H.dept_name = ‘Geology’ and X.budget > H.budget
order by X.dept_name;
14 rows
8. Using the university schema, use SQL to do the following: For each student who has retaken
a course at least twice (i.e., the student has taken the course at least three times), show the
course ID and the student’s ID.
Please display your results in order of course ID and do not display duplicate rows.
select distinct ID, course_id
from takes
group by ID, course_id
having count(*) > 2
order by course_id;
10 rows
9. Using the university schema, write an SQL query to find the ID and title of each course in
Psychology that has had at least one section with afternoon hours (i.e., ends at or after
12:00pm). (You should eliminate duplicates if any.)
select distinct course.course_id, course.title
from course, section, time_slot
where course.course_id = section.course_id
and section.time_slot_id = time_slot.time_slot_id
and time_slot.end_hr >= 12
and course.dept_name = ‘Psychology’;
5 rows
10. Using the university schema, write an SQL query to find the number of students in each
section. The result columns should appear in the order “course_id, sec_id, year, semester,
num”. You do not need to output sections with 0 students.
select course_id, sec_id, `year`, semester, count(*) as num
from takes
group by course_id, sec_id, `year`, semester
100 rows
11. Rewrite the query
select *
from section natural join classroom
without using a natural join but instead using an inner join with a using condition
select *
from section join classroom using (building, room number)
100 rows
12. Write an SQL query using the university schema to find if there are students
who have never taken a course at the university. Do this using no subqueries and
no set operations (use an outer join).
select ID
from student left outer join takes using (ID)
where course_id is null
0 rows
13. Create a view tot_credits (year, num_credits), that shows the total number of credits taken by
all student in each year.
create view tot_credits(`year`, num_credits) as
select `year`, sum(credits)
from takes natural join course
group by `year`;
select * from tot_credits;
10 rows
14. Using the university schema, write an SQL query to find the ID and name of each instructor
who has never given an A grade in any course she or he has taught. (Instructors who have never
taught a course trivially satisfy this condition.)
select A.ID, A.`name`
from instructor A
where NOT EXISTS
(select B.ID
from instructor B, teaches, takes
where A.ID = teaches.ID
and teaches.course_id = takes.course_id
and teaches.`year` = takes.`year`
and teaches.semester= takes.semester
and teaches.sec_id= takes.sec_id
and takes.grade = ‘A ‘);
19 rows. Note: Please make a note of A in last line is followed by a space, since grade has a
datatype of varchar(2).
http://a.id/
http://b.id/
http://a.id/
15. (bonus) Rewrite the preceding query, but also ensure that you include only instructors who
have given at least one other non-null grade in some course
select distinct instructor.ID, instructor.name
from instructor, teaches, takes
where instructor.ID = teaches.ID
and teaches.course_id = takes.course_id
and teaches.year = takes.year
and teaches.semester = takes.semester
and teaches.sec_id = takes.sec_id
and takes.grade is not null
and instructor.ID in (
select A.ID
from instructor A
where NOT EXISTS
(select B.ID
from instructor B, teaches, takes
where A.ID = teaches.ID
and teaches.course_id = takes.course_id
and teaches.`year` = takes.`year`
and teaches.semester= takes.semester
and teaches.sec_id= takes.sec_id
and takes.grade = ‘A ‘)
);
0 rows. Note: since the instructors who taught some courses and gave non-null grades, but never
gave ‘A’, do not exist.