The CREATE VIEW Statement in SQL

The CREATE VIEW Statement in SQL: Video

This video lesson shows you how to use the CREATE VIEW statement in SQL. Need training for SQL? Click here for the complete video training.

About Views in SQL:

A view is a virtual table that is based on the results of a SELECT statement. They are very much like the result set of a query. They always display the most recent data from the underlying tables from which they are constructed because whenever a user queries a view, most RDBMS will recreate the view from the SELECT statement upon which the view is based. A view will often present data from joined base tables in a relational database, which stores data according to the specifications required by relational database design and the rules of normalization, in a more user-friendly format. It is also possible to query a view by using the name of the view within the FROM clause of the SELECT statement. This allows more sophisticated end-users to query and access the data they need to view for reports within the view, so that they will not need to access the base tables within the database.

You create views by using the CREATE VIEW statement in SQL. You can most often change the structure of an existing view by using the ALTER VIEW statement. You can delete a view by using the DROP VIEW statement. Note that all of these statements are considered Data Definition Language statements in SQL. However, since you must create a SELECT statement in order to define a view, they have been included in their own separate chapter within this course. In this chapter, you will examine how to implement views in SQL by using the CREATE VIEW, ALTER VIEW, and DROP VIEW statements. Note that these three statements are classified as being Data Definition Language (DDL) statements within SQL. They are only being discussed in their own separate chapter from the rest of the DDL statements because you can only create a view after you have learned how to use the SELECT statement in SQL.

The CREATE VIEW Statement in SQL: Syntax

You use the CREATE VIEW statement to create a view of the data within your base tables as a separate virtual table within your database. The core SQL of the CREATE VIEW statement is shown below. The “view_name” parameter is the name of the new view, and the “select_statement” parameter is the SELECT statement used to define which records and fields that will appear within the view.

CREATE VIEW view_name AS

select_statement

There is some variety of view implementation within relational database management systems. You will need to check the specific documentation of your RDBMS to familiarize yourself with any restrictions placed upon views within the application. For example, you can often use an ORDER BY clause when creating views in MySQL 5,7, but it is generally not permitted within SQL Server 2012. Below are hyperlinks to web pages that explain the implementation of the CREATE VIEW statement in MySQL 5.7, SQL Server 2012, and Access 2013.

MySQL 5.7:

https://dev.mysql.com/doc/refman/5.7/en/create-view.html

SQL Server 2012:

https://technet.microsoft.com/en-us/library/ms187956.aspx

Access 2013:

https://msdn.microsoft.com/en-us/library/office/ff836312.aspx