Instruction
We weren't able to delete the big_gyms_london view in the previous exercise. This is because there was another object – the big_gyms_london_avg view – that was dependent on it.
When we write DROP VIEW big_gyms_london, we actually mean DROP VIEW big_gyms_london RESTRICT. The RESTRICT option informs the database that it should abort the delete operation if there are any objects that depend on the view we're trying to delete.
However, we can modify the code a bit:
DROP VIEW big_gyms_london CASCADE;
The CASCADE option tells the database to delete the view and any other objects (i.e., views) that depend on it. Use this option with care: you cannot undo the deletion!
Exercise
You're now given the course table again, but you're also given two views:
CREATE TABLE course ( id integer PRIMARY KEY, name varchar(128), lecturer varchar(128), ects_points integer, max_students integer, has_exam boolean );
CREATE VIEW courses_prof_smith AS SELECT id, name, max_students, has_exam FROM course WHERE lecturer = 'John Smith';
CREATE VIEW big_courses_prof_smith_no_exam AS SELECT id, name, max_students FROM courses_prof_smith WHERE has_exam IS FALSE AND max_students > 50;
Your task is to delete the courses_prof_smith view.
Stuck? Here's a hint!
Use:
DROP VIEW ... CASCADE
