Beginners Guide to SQL – SQL CREATE VIEW Statement

Hits: 9

(SQL Tutorials for Citizen Data Scientist)

SQL CREATE VIEW Statement

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

Creating Views to Simplify Table Access

A view is a virtual table whose definition is stored in the database. But, unlike tables, views do not actually contain any data. Instead, it provides a way to store commonly used complex queries in the database. However, you can use the view in a SQL SELECT statement to access the data just as you would use a normal or base table.

Views can also be used as a security mechanism by allowing users to access data through the view, rather than giving them direct access to the entire base tables.

Syntax

Views are created using the CREATE VIEW statement.

CREATE VIEW view_name AS select_statement;

To understand this clearly, let’s look at the following employees and departments tables.

+--------+--------------+--------+---------+
| 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 |
+--------+--------------+--------+---------+
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
Table: employees Table: departments

Suppose that you want retrieve the id and name of the employees along with their department name then you need to perform the left join operation, as follow:

Example

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

Once you execute the above query, you’ll get the output something like this:

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

But, whenever you want to access this record you need to type the whole query again. If you perform such operations quite often, it becomes really inconvenient and annoying.

In such situation you can create a view to make the query results easier to access, as follow:

Example

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

Now you can access the same records using the view emp_dept_view, like this:

Example

SELECT * FROM emp_dept_view;

As you can see how much time and effort you can save with the views.

Tip: A view always shows up-to-date data! The database engine executes the SQL query associated with the view and recreates the data, every time a view is queried.

Note: In MySQL you can also specify the ORDER BY clause in a view definition. But, in SQL Sever a view definition cannot contain an ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.


Replacing an Existing View

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

CREATE OR REPLACE VIEW view_name AS select_statement;

Note: When the OR REPLACE clause is used in CREATE VIEW statement, it will create a new view if the view does not exist, otherwise replaces an existing view.

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

-- Syntax for MySQL Database 
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 execute the following statement:

Example

SELECT * FROM emp_dept_view ORDER BY emp_id;

You will 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            |
+--------+--------------+--------+-----------------+

Note: SQL Server doesn’t support the OR REPLACE clause, therefore to replace the view you can simply drop that view and create a new one from stretch.


Updating Data Through a View

Theoretically, you can also perform INSERTUPDATE, and DELETE on views in addition to the SELECT statement. However, not all views are updatable i.e. capable of modifying the data of an underlying source table. There are some restrictions on the updatability.

Generally a view is not updatable if it contains any of the following:

  • The DISTINCTGROUP BY or HAVING clauses.
  • Aggregate functions such as AVG()COUNT()SUM()MIN()MAX(), and so forth.
  • The UNIONUNION ALLCROSSJOINEXCEPT or INTERSECT operators.
  • Subquery in the WHERE clause that refers to a table in the FROM clause.

If a view satisfies these conditions, you can modify the source table using that view.

The following 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;

Note: For insertability, the view must contain all columns in the base table that do not have a default value. Similarly, for updatability each updatable column in the view must correspond to an updatable column in a source table.


Dropping a View

Similarly, if you no longer need a view, you can use the DROP VIEW statement to drop it from the database, as shown in the following syntax:

DROP VIEW view_name;

The following command will drop the view emp_dept_view from the database.

Example

DROP VIEW emp_dept_view;

 

 

MySQL Tutorials for Business Analyst: How to use Views in MySQL

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!