So, what happens if we try to alter a table in a way that affects a dependent view? We have the same table and view:
CREATE TABLE gym ( id integer PRIMARY KEY, name varchar(32), city varchar(32), monthly_fee decimal(5, 2), max_capacity integer );
CREATE VIEW big_gyms_london AS SELECT id, name, max_capacity FROM gym WHERE city = 'London' AND max_capacity > 50;
We now want to delete the max_capacity column, but we know that the big_gyms_london view uses it. As you may expect, the standard DROP COLUMN syntax won't work – we need to add the CASCADE modifier:
ALTER TABLE gym DROP COLUMN max_capacity CASCADE;
Note that the instruction above will delete the max_capacity column AND all views dependent on it.
Again, you're given the course table and a view dependent on it:
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';
Your task is to get rid of the max_students column from the course table.
