We know how to delete a view or a table with dependent views, but how do we alter the view itself? Let's say we have the following view:
CREATE VIEW big_gyms_london AS SELECT id, name, max_capacity FROM gym WHERE city = 'London' AND max_capacity > 50;
The condition has changed. We now treat gyms as 'big' if their capacity is over 100 people. How will we update our view?
The SQL standard itself doesn't include the option to alter an existing view. However, many popular databases go beyond the standard. In PostgreSQL, you need to use the command CREATE OR REPLACE VIEW:
CREATE OR REPLACE VIEW big_gyms_london AS SELECT id, name, max_capacity FROM gym WHERE city = 'London' AND max_capacity > 100;
The syntax above will replace the old view definition.
Note that the syntax above may not work in other databases. MS SQL Server, for instance, uses a different instruction to that end. Always check the documentation of your database to find out the details.
Professor Smith actually has a middle name (Alan). The change has been reflected in all the rows of the course table – the lecturer column value is now "John Alan Smith". Modify the courses_prof_smith view, which is currently defined like this:
CREATE VIEW courses_prof_smith AS SELECT id, name, max_students, has_exam FROM course WHERE lecturer = 'John Smith';
The view should now retrieve rows for the lecturer "John Alan Smith".
Start with:
CREATE OR REPLACE VIEW
