3.12
Write the SQL statements using the university schema to perform the following operations:
- Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits.
- Create a section of this course in Fall 2017, with sec_id of 1, and with the location of this section not yet specified.
- Enroll every student in the Comp. Sci. department in the above section.
- Delete enrollments in the above section where the student’s ID is 12345.
- Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course?
- Delete all takes tuples corresponding to any section of any course with the word “advanced” as a part of the title; ignore case when matching the word with the title.
- Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits.
INSERT INTO course (course_id, title,dept_name, credits)
VALUES ('CS-001','Weekly Seminar','Comp. Sci.', 0);
- Create a section of this course in Fall 2017, with sec_id of 1, and with the location of this section not yet specified.
INSERT INTO section (course_id, sec_id, semester, year) VALUES
'CS-001', '1', 'Fall', 2017) (
- Enroll every student in the Comp. Sci. department in the above section.
INSERT INTO takes (id, course_id, sec_id, semester, year)
SELECT student.id,'CS-001', '1', 'Fall', 2017
FROM student
WHERE student.dept_name = 'Comp. Sci.'
- Delete enrollments in the above section where the student’s ID is 12345.
DELETE FROM takes
WHERE ID = '12345' AND (course_id, sec_id, semester, year) = ('CS-001', '1', 'Fall', 2017)
- Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course?
To delete the course CS-001 we use the following query:
DELETE FROM course
WHERE course_id = 'CS-001';
Below we can see the schema of the section relation. Taken from db-book.com.
create table section
varchar(8),
(course_id varchar(8),
sec_id varchar(6)
semester check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
year numeric(4,0) check (year > 1701 and year < 2100),
varchar(15),
building varchar(7),
room_number varchar(4),
time_slot_id primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course (course_id)
on delete cascade,
foreign key (building, room_number) references classroom (building, room_number)
on delete set null
);
In the above query we see ON DELETE CASCADE. This means when any course tuple is deleted all corresponding section tuples will also be deleted. Therefore when you delete CS-001 all the corresponding rows in the section relation will also be deleted.
- Delete all takes tuples corresponding to any section of any course with the word “advanced” as a part of the title; ignore case when matching the word with the title.
DELETE FROM takes
WHERE course_id IN (
SELECT course_id
FROM course
WHERE LOWER(title) LIKE '%advanced%'
)