Structured Query Language (SQL) is a language that allows users to communicate with databases, enabling them to store, retrieve, and manipulate data. The CREATE VIEW statement is one of the most important statements in SQL because it enables users to create views, which are virtual tables containing data from one or more tables.
The purpose of this article is to introduce you to the CREATE VIEW statement in SQL Server, explaining its syntax and how to use it to create simple and complex views. Furthermore, we will explore the different examples of using the CREATE VIEW statement in SQL Server.
Syntax of CREATE VIEW statement
The CREATE VIEW statement is used to create a virtual table in SQL that is a named SELECT statement. The CREATE VIEW statement syntax is as follows:
CREATE [ OR ALTER ] VIEW [ schema_name. ] view_name [ ( column [ ,...n ] ) ]
AS
SELECT [ column [ ,...n ] ]
FROM table_name [JOIN table_name ON join_condition]
WHERE search_condition
GROUP BY column [ ,...n ]
HAVING search_condition
ORDER BY column [ ASC | DESC ];The CREATE VIEW statement begins by specifying whether the view is newly created or is being altered using the OR ALTER option. After that, it specifies the name of the schema (if any) and the name of the view.
You can also specify a list of columns in parentheses for the view. Following the view name and column list (if specified), the SELECT statement is specified.
The SELECT statement specifies the data that will be used to create the view. In this statement, you can use the WHERE clause to filter the data, the JOIN clause to combine data from multiple tables, and the GROUP BY and HAVING clauses to group and aggregate data.
Finally, the ORDER BY clause is used to sort the data in the view, and the ASC and DESC keywords are used to specify the sort order.
Redefining views using OR ALTER keywords
The OR ALTER keyword in the CREATE VIEW statement can be used to change the definition of a view. With this keyword, you can add or remove columns from the view without dropping and recreating it.
For example, consider the following CREATE VIEW statement:
CREATE VIEW customer_list
AS
SELECT customer_id, first_name, last_name, email
FROM customers
ORDER BY last_name, first_name;Suppose that you want to add the customer_name column to this view without dropping and recreating it. You can use the OR ALTER keyword to redefine the view as follows:
CREATE OR ALTER VIEW customer_list
AS
SELECT customer_id, first_name, last_name, email, CONCAT(first_name, ' ', last_name) AS customer_name
FROM customers
ORDER BY last_name, first_name;This statement uses the CONCAT function to combine the first_name and last_name columns into a single customer_name column. Note that the view definition has been changed using the OR ALTER keyword.
2. Examples of using CREATE VIEW statement in SQL Server
Creating a simple view example
Consider the following scenario: You have an online store that sells products, and you want to see the daily sales figures for each product. To create this view, you need to combine data from the Orders, Order_items, and Products table.
You can achieve this result using the following CREATE VIEW statement:
CREATE VIEW daily_sales
AS
SELECT order_date, product_name, SUM(quantity*price) AS sales
FROM orders
JOIN order_items ON order_items.order_id = orders.order_id
JOIN products ON products.product_id = order_items.product_id
GROUP BY order_date, product_name;This statement creates a view called “daily_sales” that shows the sales figures for products on each day the orders were placed.
Redefining a view example
Suppose you want to redefine the daily sales view by adding a customer_name column. You can achieve this result using the following CREATE OR ALTER VIEW statement:
CREATE OR ALTER VIEW daily_sales
AS
SELECT order_date, product_name, customer_name, SUM(quantity*price) AS sales
FROM orders
JOIN order_items ON order_items.order_id = orders.order_id
JOIN products ON products.product_id = order_items.product_id
JOIN customers ON customers.customer_id = orders.customer_id
GROUP BY order_date, product_name, customer_name
ORDER BY order_date;This statement uses the OR ALTER keyword to redefine the existing view, adding the customer_name column using the CONCAT function.
Creating a view using aggregate functions example
Suppose you have a retail store with multiple staff and you want to see how much each staff member has sold in a given period. You can achieve this result using the following CREATE VIEW statement:
CREATE VIEW staff_sales
AS
SELECT staff_name, SUM(quantity*price) AS total_sales
FROM order_items
JOIN orders ON orders.order_id = order_items.order_id
JOIN staffs ON staffs.staff_id = orders.staff_id
WHERE order_date >= '2021-01-01' AND order_date < '2022-01-01'
GROUP BY staff_name;This statement creates a view called “staff_sales” that shows how much each staff member has sold within a given period, using the SUM() function to compute the total sales.
Conclusion
In conclusion, the CREATE VIEW statement in SQL Server is a powerful tool for creating virtual tables with customized data. It allows you to combine data from multiple tables, filter data, sort data, group data, and even perform calculations on the data.
In this article, we have covered the basics of the CREATE VIEW syntax, as well as demonstrated examples of how to use the CREATE VIEW statement to create simple and complex views. By using the different examples we have outlined, you should be able to create customized and meaningful views to help analyze and manage your data more efficiently.
In summary, the CREATE VIEW statement in SQL Server is a powerful tool for creating virtual tables with customized data. This article has covered the syntax of the CREATE VIEW statement and demonstrated how to use it to create simple and complex views, including examples of redefining views and using aggregate functions.
The ability to create customized and meaningful views helps to analyze and manage data more efficiently. Understanding the power of this tool is important for any database user, and the examples provided can serve as a valuable resource.
By mastering the CREATE VIEW statement, users can gain valuable insights from their data and streamline their work processes.