Recall that the SQL1 standard treated the SQL Data Definition Language (DDL) as a static specification of the structure of a database, including its tables and views. For this reason, the SQL1 standard did not provide the ability to drop a view when it was no longer needed. However, all major DBMS brands have provided this capability for some time. Because views behave like tables and a view cannot have the same name as a table, some DBMS brands used the DROP TABLE statement to drop views as well. Other SQL implementations provided a separate DROP VIEW statement.
The SQL2 standard formalized support for dropping views through a DROP VIEW statement. It also provides for detailed control over what happens when a user attempts to drop a view when the definition of another view depends on it. For example, suppose two views on the SALESREPS table have been created by these two CREATE VIEW statements:
CREATE VIEW EASTREPS AS
SELECT *
FROM SALESREPS
WHERE REP OFFICE IN (11, 12, 13)
CREATE VIEW NYREPS AS
SELECT *
FROM EASTREPS
WHERE REP OFFICE = 11
For purposes of illustration, the NYREPS view is defined in terms of the EASTREPS view, although it could just as easily have been defined in terms of the underlying table. Under the SQL2 standard, the following DROP VIEW statement removes both of the views from the database:
DROP VIEW EASTREPS CASCADE
The CASCADE option tells the DBMS to delete not only the named view, but also any views that depend on its definition. In contrast, this DROP VIEW statement:
DROP VIEW EASTREPS RESTRICT
fails with an error, because the RESTRICT option tells the DBMS to remove the view only if no other views depend on it. This provides an added precaution against unintentional side-effects of a DROP VIEW statement. The SQL2 standard requires that either RESTRICT or CASCADE be specified. But many commercial SQL products support a version of the DROP VIEW statement without an explicitly specified option for backward compatibility with earlier versions of their products released before the publication of the SQL2 standard. The specific behavior of dependent views in this case depends on the particular DBMS brand.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.