Creating, Altering, and Dropping Views
In SQL database tables, views are virtual type table view formats created by querying data from single or multiple SQL tables. You can control and manage how data is represented in your SQL table database through views in SQL. Each SQL table view provides you with many tools and features to create different database table views based on the existing database. You can manage SQL table views as per your requirement by creating multiple individual views of a table, modifying existing database query views, and removing existing database query views as per the requirement.
So, let’s understand each database view operation in a better way.
Creating Views in SQL Tables.
To create a table view in a SQL database table, you apply the CREATE VIEW SQL statement. Whereas, the view statement in a database table is defined and designed by the SELECT SQL query.
Create Table View Syntax.
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
Create Table View Syntax Example.
CREATE VIEW employe_view AS
SELECT name, department, salary
FROM employees
WHERE salary > 30000;
Here we have created a table view named employe_tableview which displays the employee table column information such as name, employee department, and employee salary of all employees earning salary more than 30,000 in the existing SQL database employee table.
Altering View in SQL Tables.
After creating a view in an existing SQL database table, you can modify the existing view default definition. Remember, SQL does not provide direct permission to all users to use the ALTER VIEW command statement in the database system such as MySQL or SQLite. In some particular conditions, the user may have to drop the old table view in the SQL database table and create a new table view with the existing updated database table query.
CREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2, …
FROM new_table_name
WHERE new_condition;
Altering View example.
CREATE OR REPLACE VIEW employe_view AS
SELECT name, department, salary, hire_date
FROM employees
WHERE salary > 30000 AND hire_date > ‘2025-03-04’;
Here, this query provides modify permission to the employee view in the existing SQL database table. So that the existing table view contains the employee appointment date and produces and displays only the result of employees appointed after March 4, 2025.
Dropping Views in SQL Tables.
In a SQL database table, SQL database users use the DROP VIEW statement to delete a view from the database. It completely deletes the view in your SQL database table. Here, in the table delete process, deleting a table view does not have any major impact on your existing stored database table.
DROP VIEW view_name;
Dropping View Example.
DROP VIEW employe_view;
This SQL query statement will delete the employee_view view from the existing database.
