Dropping and altering views in SQL - Mini Project: Build & Apply

Dropping and Altering Views in SQL

📖 Scenario: You are managing a small company's database. You have created a view to simplify access to employee contact details. Now, you need to update this view to include department information and also learn how to remove views when they are no longer needed.

🎯 Goal: Build SQL commands to create a view, alter it to include more data, and then drop the view when it is no longer required.

📋 What You'll Learn

Create a view named employee_contacts that shows employee names and emails.

Alter the employee_contacts view to also include the department name.

Drop the employee_contacts view.

💡 Why This Matters

🌍 Real World

Views help simplify complex queries and provide a consistent way to access data in business applications.

💼 Career

Database administrators and developers often create, modify, and drop views to optimize data access and maintain database structure.

1

Create the initial view

Write a SQL statement to create a view called employee_contacts that selects employee_name and email columns from the employees table.

SQL

-- Write the CREATE VIEW statement for employee_contacts here

Need a hint?

Use CREATE VIEW view_name AS SELECT columns FROM table; syntax.

2

Add a configuration for the department column

Write a SQL statement to alter the employee_contacts view to include the department_name column by joining the departments table on employees.department_id = departments.department_id.

SQL

-- Write the ALTER VIEW statement to include department_name here

Need a hint?

Use CREATE OR REPLACE VIEW to update the view with a JOIN.

3

Drop the view

Write a SQL statement to drop the view named employee_contacts.

SQL

-- Write the DROP VIEW statement for employee_contacts here

Need a hint?

Use DROP VIEW view_name; to remove a view.

4

Recreate the original view after dropping

Write a SQL statement to recreate the original employee_contacts view that selects only employee_name and email from the employees table.

SQL

-- Write the CREATE VIEW statement for employee_contacts again here

Need a hint?

Recreate the view using the original columns.