2ndSem

3.12

3 min read

Write the SQL statements using the university schema to perform the following operations:

a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits.
b. Create a section of this course in Fall 2017, with sec_id of 1, and with the location of this section not yet specified.
c. Enroll every student in the Comp. Sci. department in the above section.
d. Delete enrollments in the above section where the student’s ID is 12345.
e. Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course?
f. 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.

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

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

c. 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.'

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

e. 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
        (course_id              varchar(8),
         sec_id                 varchar(8),
         semester               varchar(6)
                check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
         year                   numeric(4,0) check (year > 1701 and year < 2100),
         building               varchar(15),
         room_number            varchar(7),
         time_slot_id           varchar(4),
         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.


f. 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%'
)

Graph View