SQL CREATE View Statement with an example

4 years ago
Zaid Bin Khalid
5332 Views
4

In this session, you will learn how to create, update, and delete a view by using SQL.

Creating Views to Simplify Table Access.

Create view contains rows and columns in SQL, just like a original table. The columns in the view statement are the columns from one or more original tables in the database. In SQL functions, WHERE, and JOIN statements are also be added to the view.

There are two ways to create a view in SQL Server.

By using SQL Server management studio UI.
By using the SQL Server query statement.

Views can be used for a few reasons.

Some of the main reasons are as follows.

To simplify database structure to the individuals using it.
As a security mechanism to DBAs for allowing users to access data without granting those permissions to directly access the underlying base tables.
To provide backward compatibility to applications that are using our database.

In this topic, we are going to check the CREATE VIEW SQL syntax, to see what views are about or used for.

Syntax.

Views are created using the CREATE VIEW statement.

CREATE VIEW view_name AS select_statement;

To understand this easily let us take an example with two tables table 1 is the employee table and table 2 is the department table.

+--------+--------------+--------+---------+
| emp_id | emp_name     | salary | dept_id |
+--------+--------------+--------+---------+
|      1 | Ethan Hunt   |   5000 |       4 |
|      2 | Tony Montana |   6500 |       1 |
|      3 | Sarah Connor |   8000 |       5 |
|      4 | Rick Deckard |   7200 |       3 |
|      5 | Martin Blank |   5600 |    NULL |
+--------+--------------+--------+---------+
Table: employees

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
Table: departments

Replacing an Existing View.

If you want to update or replace an existing view in MySQL, you can either drop that view and create a new one or just use the OR REPLACE clause in the CREATE VIEW statement, as follows.

CREATE OR REPLACE VIEW view_name AS select_statement;

. The below SQL statement will change or replace the definition or statement of the existing view emp_dept_view by adding a new column salary to it.

CREATE OR REPLACE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

After updating the view, if you want to execute the following statement you will get output result as shown.

SELECT * FROM emp_dept_view ORDER BY emp_id;

Now you see one more column salary in the resulting output, as follow.

+--------+--------------+--------+-----------------+
| emp_id | emp_name     | salary | dept_name       |
+--------+--------------+--------+-----------------+
|      1 | Ethan Hunt   |   5000 | Human Resources |
|      2 | Tony Montana |   6500 | Administration  |
|      3 | Sarah Connor |   8000 | Sales           |
|      4 | Rick Deckard |   7200 | Finance         |
|      5 | Martin Blank |   5600 | NULL            |
+--------+--------------+--------+-----------------+

Updating Data Through a View.

You can also perform INSERT, UPDATE and DELETE function on views with the help of the SELECT statement. However, not all views are update-able i.e. capable of modifying the data of a basic source table.

There are some restrictions on the update.

Since, A view is not update-able till it contains any of the following conditions such as:

The DISTINCT, GROUP BY or HAVING clauses.
Aggregate functions such as AVG(), COUNT(), SUM(), MIN(), MAX(), and so forth.
The UNION, UNION ALL, CROSS JOIN, EXCEPT or INTERSECT operators.
Subquery in the WHERE clause that refers to a table in the FROM clause.

The below statement will update the salary of the employee whose emp_id is equal to 1.

UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;

Dropping a View.

If you no longer interested in view statement, you can either use the DROP VIEW statement to delete it from the database. The basic DROP view syntax can be expressed as: