Dropping and altering views in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to drop a view named 'employee_view'.

SQL

DROP [1] employee_view;

Drag options to blanks, or click blank then click option'

AINDEX

BVIEW

CTABLE

DDATABASE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DROP TABLE instead of DROP VIEW.

Forgetting the keyword VIEW.

Trying to drop a database instead of a view.

To remove a view, you use DROP VIEW followed by the view name.

2fill in blank

medium

Complete the code to rename a view from 'old_view' to 'new_view'.

SQL

ALTER [1] old_view RENAME TO new_view;

Drag options to blanks, or click blank then click option'

ATABLE

BDATABASE

CVIEW

DINDEX

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALTER TABLE instead of ALTER VIEW.

Trying to rename a database or index instead of a view.

To rename a view, use ALTER VIEW old_name RENAME TO new_name;.

3fill in blank

hard

Fix the error in the code to modify the view 'sales_view' to select only sales above 1000.

SQL

CREATE OR REPLACE [1] sales_view AS SELECT * FROM sales WHERE amount > 1000;

Drag options to blanks, or click blank then click option'

AVIEW

BINDEX

CDATABASE

DTABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CREATE OR REPLACE TABLE instead of VIEW.

Trying to replace a database or index.

To update a view, use CREATE OR REPLACE VIEW followed by the view name and new query.

4fill in blank

hard

Fill in the blank to drop a view only if it exists, avoiding errors if it does not.

SQL

DROP [1] IF EXISTS customer_view;

Drag options to blanks, or click blank then click option'

ANOT

BTABLE

CINDEX

DVIEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using TABLE instead of VIEW.

Using INDEX instead of VIEW.

Use DROP VIEW IF EXISTS view_name; to safely drop a view only if it exists.

5fill in blank

hard

Fill all three blanks to recreate a view named 'active_users' selecting users with status 'active'.

SQL

CREATE OR [1] [2] active_users AS SELECT * FROM users WHERE status [3] 'active';

Drag options to blanks, or click blank then click option'

AREPLACE

BVIEW

C=

DTABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TABLE instead of VIEW.

Omitting REPLACE when updating a view.

Using wrong comparison operator instead of '='.

Use CREATE OR REPLACE VIEW view_name AS SELECT ... WHERE column = value; to update or create a view.

Practice

1. What does the SQL command DROP VIEW view_name; do?

easy

A. It updates the data inside the view view_name.

B. It renames the view view_name to another name.

C. It creates a new view called view_name.

D. It deletes the view named view_name from the database.

Solution

1. Step 1: Understand the purpose of DROP VIEW

   The command DROP VIEW is used to remove a view from the database completely.

2. Step 2: Analyze the command effect

   Using DROP VIEW view_name; deletes the view named view_name, so it no longer exists.

3. Final Answer:

   It deletes the view named view_name from the database. -> Option D

4. Quick Check:

   DROP VIEW removes a view [OK]

Hint: DROP VIEW deletes a view completely from the database [OK]

Common Mistakes:

• Thinking DROP VIEW updates data inside the view
• Confusing DROP VIEW with CREATE VIEW
• Assuming DROP VIEW renames the view

2. Which of the following is the correct syntax to change the query inside an existing view named employee_view?

easy

A. MODIFY VIEW employee_view SELECT * FROM employees;

B. UPDATE VIEW employee_view SET query = 'SELECT * FROM employees';

C. ALTER VIEW employee_view AS SELECT * FROM employees WHERE active = 1;

D. CHANGE VIEW employee_view TO SELECT * FROM employees;

Solution

1. Step 1: Recall ALTER VIEW syntax

   The correct syntax to change a view's query is ALTER VIEW view_name AS SELECT ....

2. Step 2: Check each option

   ALTER VIEW employee_view AS SELECT * FROM employees WHERE active = 1; uses the correct syntax. Options B, C, and D use invalid or non-existent commands.

3. Final Answer:

   ALTER VIEW employee_view AS SELECT * FROM employees WHERE active = 1; -> Option C

4. Quick Check:

   ALTER VIEW uses AS SELECT [OK]

Hint: Use ALTER VIEW view_name AS SELECT ... to update a view [OK]

Common Mistakes:

• Using UPDATE or MODIFY instead of ALTER
• Omitting AS keyword
• Trying to rename view with ALTER VIEW

3. Given a view active_customers defined as SELECT * FROM customers WHERE status = 'active', what will be the result after running ALTER VIEW active_customers AS SELECT * FROM customers WHERE status = 'inactive'; and then querying SELECT * FROM active_customers;?

medium

A. It will return all customers with status 'inactive'.

B. It will cause a syntax error.

C. It will return all customers regardless of status.

D. It will return all customers with status 'active'.

Solution

1. Step 1: Understand ALTER VIEW effect

   The ALTER VIEW command changes the query inside the view to the new SELECT statement.

2. Step 2: Analyze the new query

   The view now selects customers where status = 'inactive'. So querying the view returns inactive customers.

3. Final Answer:

   It will return all customers with status 'inactive'. -> Option A

4. Quick Check:

   ALTER VIEW changes the view's query [OK]

Hint: ALTER VIEW changes the query; output matches new SELECT [OK]

Common Mistakes:

• Assuming view still returns old data after ALTER
• Thinking ALTER VIEW causes errors if view exists
• Confusing view data with table data

4. You try to run ALTER VIEW sales_view AS SELECT * FROM sales WHERE amount > 1000; but get an error. What is the most likely cause?

medium

A. The view sales_view does not exist.

B. The SELECT query inside ALTER VIEW is missing a semicolon.

C. You cannot use WHERE clauses inside ALTER VIEW.

D. ALTER VIEW requires DROP VIEW before use.

Solution

1. Step 1: Check ALTER VIEW prerequisites

   ALTER VIEW requires the view to already exist to modify it.

2. Step 2: Identify common error cause

   If the view sales_view does not exist, ALTER VIEW will fail with an error.

3. Final Answer:

   The view sales_view does not exist. -> Option A

4. Quick Check:

   ALTER VIEW fails if view missing [OK]

Hint: Ensure view exists before ALTER VIEW [OK]

Common Mistakes:

• Thinking WHERE clause is invalid in ALTER VIEW
• Assuming semicolon inside ALTER VIEW causes error
• Believing DROP VIEW is needed before ALTER VIEW

5. You have a view product_summary showing product names and total sales. You want to update it to include only products with sales over 500. Which sequence of commands correctly updates the view without losing it?

hard

A. DROP VIEW product_summary; CREATE VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500;

B. ALTER VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500;

C. UPDATE VIEW product_summary SET query = 'SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500;';

D. REPLACE VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500;

Solution

1. Step 1: Understand how to update a view's query

   ALTER VIEW updates the query inside an existing view without dropping it.

2. Step 2: Check each option's correctness

   ALTER VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500; uses ALTER VIEW with the correct query. DROP VIEW product_summary; CREATE VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500; drops and recreates the view, which is not needed. Options B and D use invalid commands.

3. Final Answer:

   ALTER VIEW product_summary AS SELECT name, SUM(sales) FROM products GROUP BY name HAVING SUM(sales) > 500; -> Option B

4. Quick Check:

   Use ALTER VIEW to update view query safely [OK]

Hint: Use ALTER VIEW to update query without dropping [OK]

Common Mistakes:

• Dropping view unnecessarily before updating
• Using UPDATE VIEW or REPLACE VIEW which are invalid
• Not including HAVING clause in the new query