Dropping and altering views in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What happens after dropping a view?

Consider a view named employee_summary created on a table employees. After executing the command DROP VIEW employee_summary;, what will be the result of querying SELECT * FROM employee_summary;?

SQL

DROP VIEW employee_summary;
SELECT * FROM employee_summary;

A The query raises an error because the view no longer exists.

B The query returns an empty result set with the same columns as the view.

C The query returns all rows from the original employees table.

D The query returns the last cached result of the view before it was dropped.

💡 Hint

Think about what happens to a view after it is dropped and whether it still exists for querying.

When a view is dropped, it is removed from the database. Any query referencing that view will raise an error because the view no longer exists.

📝 Syntax

intermediate

Identify the correct syntax to alter a view

You want to change the definition of an existing view named customer_orders to include only orders from the last year. Which of the following SQL commands correctly updates the view?

A CREATE OR REPLACE VIEW customer_orders AS SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';

B UPDATE VIEW customer_orders SET definition = 'SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL ''1 year''';

C ALTER VIEW customer_orders AS SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';

D MODIFY VIEW customer_orders AS SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';

💡 Hint

Think about how to redefine a view without dropping it first.

Standard SQL does not support ALTER VIEW to change the query. Instead, CREATE OR REPLACE VIEW is used to redefine the view.

🧠 Conceptual

advanced

Effect of dropping a view on dependent objects

If a view sales_summary is dropped, which of the following statements about objects depending on this view is true?

A All dependent views and stored procedures will automatically update to remove references to the dropped view.

B Dropping the view will fail if there are dependent objects referencing it, unless those objects are dropped first.

C Dependent objects will continue to work normally because they use cached data from the dropped view.

D Dropping the view will cascade and automatically drop all dependent objects referencing it.

💡 Hint

Consider how databases handle dependencies when dropping objects.

Most databases prevent dropping a view if other objects depend on it, unless those dependent objects are dropped first or the drop is done with CASCADE.

🔧 Debug

advanced

Why does this ALTER VIEW command fail?

Given the command below, why does it fail in most SQL databases?

ALTER VIEW product_list AS SELECT id, name FROM products WHERE active = TRUE;

SQL

ALTER VIEW product_list AS SELECT id, name FROM products WHERE active = TRUE;

A The SELECT statement must include all columns from the base table.

B The WHERE clause is not allowed in view definitions.

C The view name must be enclosed in quotes to alter it.

D ALTER VIEW does not support redefining the SELECT statement; CREATE OR REPLACE VIEW should be used instead.

💡 Hint

Think about the standard way to change a view's query.

ALTER VIEW is not supported for changing the query in many SQL dialects. Instead, CREATE OR REPLACE VIEW is used.

❓ optimization

expert

Optimizing view updates with minimal downtime

You need to update a large view monthly_report used by many users. Dropping and recreating the view causes errors for users querying it during the update. Which approach minimizes downtime and errors?

A Rename the old view, create a new view with the original name, then drop the old view after users switch.

B Use <code>DROP VIEW monthly_report;</code> then <code>CREATE VIEW monthly_report AS ...;</code> during off-hours only.

C Use <code>CREATE OR REPLACE VIEW monthly_report AS ...;</code> to atomically update the view definition without dropping it first.

D Create a new view with a different name and ask users to switch manually to the new view.

💡 Hint

Consider atomic operations that do not remove the view before recreating it.

CREATE OR REPLACE VIEW updates the view atomically, so users querying the view do not experience errors or downtime.