SQL – DELETE View

SQL Views provide a powerful way to simplify complex queries and present data in a more understandable format. However, there may be times when we need to remove a view from your database schema. In SQL, the DELETE VIEW command does not exist. So we use the DROP VIEW command to delete a view.

In this article, We will learn about the SQL DROP VIEW with detail understanding with the help of examples and so on.

• When it comes to defining views then Views in SQL are referred to as virtual tables that are created whenever we use the SELECT command.
• The DELETE VIEW command does not exist. To remove a view from a database, we use the DROP VIEW command. This command deletes the view definition from the database. But it does not delete any underlying tables or data.
• Views are commonly used to simplify complex queries, provide an additional layer of security and present data in a more understandable format.

Syntax:

DROP VIEW view_name;

Let’s get into the DROP VIEW command with the examples:

Let’s Set Up an Environment

To understand SQL – DELETE View we need a table on which we will perform various operations and queries. Here we will consider a table called EMPLOYEES which contains data as shown below:

CREATE TABLE EMPLOYEES (
  ID INT PRIMARY KEY,
  HighSalaryEmployees VARCHAR(255),
  GOOD_EMPLOYEE VARCHAR(255),
  TOP_EMPLOYEE VARCHAR(100)
);
INSERT INTO EMPLOYEES (ID, HighSalaryEmployees, GOOD_EMPLOYEE, TOP_EMPLOYEE) VALUES
(1, 'SACHIN', 'Harper Lee', 'JOHN'),
(2, 'PRAKHAR', 'George Orwell', 'BHUVAN'),
(3, 'BADAL', 'F. Scott Fitzgerald', 'SHYAM'),
(4, 'ABHISHEK', 'J.D. Salinger', 'MADAN'),
(5, 'KARTHIK', 'J.R.R. Tolkien', 'AKASH');

Output:

Let’s Create a view name called HighSalaryEmployees

CREATE VIEW HighSalaryEmployees 
AS 
SELECT * FROM EMPLOYEES;

Output:

ID  | HighSalaryEmployees | GOOD_EMPLOYEE        | TOP_EMPLOYEE
----|----------------------|----------------------|--------------
1   | SACHIN               | Harper Lee           | JOHN
2   | PRAKHAR              | George Orwell        | BHUVAN
3   | BADAL                | F. Scott Fitzgerald  | SHYAM
4   | ABHISHEK             | J.D. Salinger        | MADAN
5   | KARTHIK              | J.R.R. Tolkien       | AKASH

Let’s Create a view name called GOOD_EMPLOYEE

CREATE VIEW GOOD_EMPLOYEE  AS  SELECT * FROM EMPLOYEES;

Output:

ID  | HighSalaryEmployees | GOOD_EMPLOYEE        | TOP_EMPLOYEE
----|----------------------|----------------------|--------------
1   | SACHIN               | Harper Lee           | JOHN
2   | PRAKHAR              | George Orwell        | BHUVAN
3   | BADAL                | F. Scott Fitzgerald  | SHYAM
4   | ABHISHEK             | J.D. Salinger        | MADAN
5   | KARTHIK              | J.R.R. Tolkien       | AKASH

Let’s Create a view name called TOP_EMPLOYEE

CREATE VIEW TOP_EMPLOYEE  AS  SELECT * FROM EMPLOYEES;

Output:

ID  | HighSalaryEmployees | GOOD_EMPLOYEE        | TOP_EMPLOYEE
----|----------------------|----------------------|--------------
1   | SACHIN               | Harper Lee           | JOHN
2   | PRAKHAR              | George Orwell        | BHUVAN
3   | BADAL                | F. Scott Fitzgerald  | SHYAM
4   | ABHISHEK             | J.D. Salinger        | MADAN
5   | KARTHIK              | J.R.R. Tolkien       | AKASH

To check the Created Views:

SELECT TABLE_SCHEMA, 
EMPLOYEES 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:

Now we are gonna delete the views which are called HighSalaryEmployees and TOP_EMPLOYEE.

DROP VIEW HighSalaryEmployees;
DROP VIEW TOP_EMPLOYEE;

Now for confirming again execute the query:

SELECT TABLE_SCHEMA, EMPLOYEES 
FROM 
INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='GFG';

Output:

Explanation: This query retrieves information about views from the INFORMATION_SCHEMA.VIEWS table for a specific TABLE_SCHEMA (‘GFG’). It selects the TABLE_SCHEMA and EMPLOYEES columns from the VIEWS table, filtering the results to only include views from the ‘GFG’ schema.

Overall, SQL Views offer a flexible and efficient way to manage and query your data. The DROP VIEW command allows you to easily remove views that are no longer needed, helping you maintain a clean and organized database schema. By understanding how to use views and the DROP VIEW command, you can improve the performance and manageability of your SQL databases.