Dropping and altering views in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL command DROP VIEW do?

It removes an existing view from the database permanently. After dropping, the view no longer exists and cannot be queried.

beginner

How do you change the structure or definition of an existing view in SQL?

You use the CREATE OR REPLACE VIEW statement with the new definition. This updates the view without dropping it first.

intermediate

True or False: You can use ALTER VIEW to modify a view's SELECT statement in all SQL databases.

False. Not all SQL databases support ALTER VIEW. Many require dropping and recreating the view or using CREATE OR REPLACE VIEW.

beginner

What happens if you try to drop a view that does not exist using DROP VIEW view_name;?

The database will return an error unless you use DROP VIEW IF EXISTS view_name; which avoids errors if the view is missing.

intermediate

Why might you prefer CREATE OR REPLACE VIEW over dropping and recreating a view?

Because it keeps permissions and dependencies intact and avoids errors if the view is referenced elsewhere in the database.

Which SQL command removes a view permanently?

A DROP VIEW view_name;

B ALTER VIEW view_name;

C CREATE VIEW view_name;

D RENAME VIEW view_name;

DROP VIEW deletes the view permanently from the database.

How do you update the SELECT query of an existing view without dropping it first?

A ALTER VIEW view_name AS SELECT ...;

B DROP VIEW view_name; CREATE VIEW view_name AS SELECT ...;

C CREATE OR REPLACE VIEW view_name AS SELECT ...;

D UPDATE VIEW view_name SET SELECT ...;

CREATE OR REPLACE VIEW updates the view's definition safely.

What does DROP VIEW IF EXISTS view_name; do?

A Drops the view only if it exists, avoiding errors if it doesn't.

B Creates the view if it does not exist.

C Renames the view if it exists.

D Alters the view if it exists.

This command safely drops a view only if it exists.

Which statement is true about ALTER VIEW?

A All SQL databases support <code>ALTER VIEW</code> to change view definitions.

B Some databases support <code>ALTER VIEW</code>, but many require <code>CREATE OR REPLACE VIEW</code>.

C No SQL database supports <code>ALTER VIEW</code>.

D <code>ALTER VIEW</code> deletes the view.

Support for ALTER VIEW varies; many use CREATE OR REPLACE VIEW instead.

Why is CREATE OR REPLACE VIEW often preferred over dropping and recreating a view?

A It renames the view.

B It automatically backs up the view.

C It deletes all data in the view.

D It keeps permissions and dependencies intact.

This method preserves permissions and avoids breaking dependencies.

Explain how to safely update the definition of an existing view in SQL.

Think about commands that replace without deleting.

Describe what happens when you drop a view and why you might want to use IF EXISTS.

Consider error handling when the view might not be there.