Dropping a view removes only the view definition, not the actual data stored in the base tables. This makes it safe to delete views without affecting the original tables or their contents. The DROP VIEW command is commonly used when:
- Remove unnecessary views to improve performance.
- Delete unused views to prevent unwanted data exposure.
- Maintain only required views for easier database management.
Syntax:
DROP VIEW view_name;- DROP VIEW: Removes an existing view from the database.
- view_name: The name of the view that you want to delete.
Example: Implementation of DROP VIEW
This example shows creating a view first and then deleting it using the DROP VIEW command to remove the view definition from the database.
Creating Views
The Employees table stores employee details. CREATE VIEW helps display specific data in a simplified format.
View for High Salary Employees
The HighSalaryEmployees view filters the EMPLOYEES table to show only employees with a salary above 50,000, returning ID, Name, Position, Salary, and Department.
Query:
CREATE VIEW HighSalaryEmployees AS
SELECT * FROM Employees WHERE Salary > 50000;
Output:
View for Developers
The Developers view displays employees whose Position contains the word "Developer". It returns the Name, Position, and Department of employees working in development-related roles.
Query:
CREATE VIEW Developers AS
SELECT Name, Position, Department FROM EMPLOYEES WHERE Position LIKE '%Developer%';
Output:
View for Employees in IT Department
The IT Employees view displays employees who belong to the IT department. It retrieves all employee details, making it easier to access and manage IT-related employee data.
Query:
CREATE VIEW ITEmployees AS
SELECT * FROM EMPLOYEES WHERE Department = 'IT';
Output:
Query: To check the Created Views
SELECT TABLE_SCHEMA, TABLE_NAME AS AVAILABLE_VIEWS
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='GFG';
To confirm that our views are created we can use the query mentioned above it will show us so our the views that we have created in form of table something like this:
Deleting a View
When a view is no longer required, it can be removed using the DROP VIEW statement. For example, the HighSalaryEmployees and ITEmployees views can be deleted using the DROP VIEW command.
Query:
DROP VIEW HighSalaryEmployees;
DROP VIEW ITEmployees;
Verifying the Deletion
To confirm the views have been successfully deleted, we can run the following query again. The HighSalaryEmployees and IT Employees views should no longer appear in the result.
SELECT TABLE_SCHEMA, TABLE_NAME AS AVAILABLE_VIEWS
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='GFG';
Output:
