SQL CREATE VIEW Statement (original) (raw)

Last Updated : 30 Jan, 2025

The SQL **CREATE VIEW statement is a very **powerful feature in **RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or many underlying tables.

This article explains how the **SQL CREATE VIEW statement can be used to create, modify, and drop views, along with their pros and cons. It also provides practical examples of how database management can involve **customized report generation, enforcing access control, and **simplifying complex structures.

SQL CREATE VIEW Statement

The SQL **CREATE VIEW statement is the central idea used to create a virtual table that does not store data itself but rather provides a dynamic representation of data obtained from one or more underlying tables. This arrangement lets users simplify intricate queries, improve security, and make abstracted data structures easy to access and manipulate.

**Syntax:

The syntax to create a view in sql is as follows:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

**Explanation of Syntax:

• **CREATE VIEW **view_name: This part of the statement specifies that a new view with the given name (view_name) will be created.
• **AS: This keyword is used to indicate that the following SELECT statement will define the structure and data for the view.
• **SELECT column1, column2, ...: Here, you specify the columns that you want the view to include. These can be columns from one or more tables or even expressions derived from those columns.
• **FROM table_name: This part of the statement specifies the table or tables whose data will populate your view.
• **WHERE condition: Optionally you may add a WHERE clause so as to give conditions under which data retrieved by this view should be filtered by. This is very helpful when creating views with subsets of data.

Examples of SQL CREATE VIEW Statement

The CREATE VIEW statement in SQL is used to create a **virtual table based on the result of a query. Views help simplify **complex queries and enhance security by restricting access to specific columns or rows.

Example 1: Creating a Simple View

Consider the table products having three columns **product_id, product_name, and price. Suppose we have to create a view that contains only products whose prices are greater than $50.

**Query:

CREATE VIEW expensive_products AS
SELECT product_id, product_name, price
FROM products
WHERE price > 50;

products Table:

products table

**expensive_products **View:

expensive_products

**Explanation:

• We develop a view called **expensive_products.
• The view obtains columns **product_id, product_name, and **price from table products.
• We filter it so that it only includes rows with **prices bigger than $50.

Example 2: Creating a Joined View

Assume that we have two tables **employees and **departments. We need to build a view combining information about both tables in order to show each employee’s name along with their department name:

**Query:

CREATE VIEW employee_department_info AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

**Employees table:

employees table

**Departments table:

departments table

**employee_department_info view:

Output of the employee_department_info view

**Explanation:

• A view named **employee_department_info is created by us.
• Columns **employee_id, first_name, last_name from employees table along with **department_name from departments table are selected by this view.
• An inner join between employees and departments on **department_id is performed to get department name for each employee.

Now when querying the **employee_department_info view we shall have a list of employees together with their corresponding department names.

Conclusion

In conclusion, the SQL **CREATE VIEW statement is a useful resource for those in **database development and **administration because it creates virtual tables that provide simplified access to data stored in one or more underlying tables. The views encapsulate complex queries, control data access, and abstract the data structures enhancing the performance of **Database Management Systems (DBMS), security, and manageability.

Therefore, by using views properly developers can avoid writing unnecessary code **enhance query execution time, and finally make sure that their databases have a higher level of **security when processing errors appear. View mastering is important in improvingefficiencyandeffectivenessduring **database management tasks regardless of whether you are operating small-scale applications or significant enterprise databases.