SQL Advanced Apps (VIEW)

SQL VIEW

A view is a table that is visualized

In SQL, the view is a visual table based on the result set of the SQL statement

A view contains rows and columns, as if it were a real table. A field in a view is a field from a real table in one or more databases. We can add SQL functions, WHERE, and join statements to the view, or we can submit the data as if they came from a single table

Note: The design and structure of a database is not affected by functions, where, or join statements in the view

SQL CREATE VIEW　　

CREATE VIEW view_name asselect column_name (S) from table_namewhere condition

Note: views always display the most recent data. The database engine rebuilds data through SQL statements whenever the user queries the view

　　

SQL CREATE VIEW Instance

You can use views from within a query, inside a stored procedure, or from within another view. By adding functions, joins, and so on to the view, you can accurately submit the data you want to submit to the user

Sample database Northwind has some views that are installed by default. The view "Current Product List" lists all products that are in use from the product table. This view is created using the following SQL:

CREATE VIEW [Current Product List] asselect ProductID, Productnamefrom productswhere discontinued=no

You can query the view above

SELECT * FROM [current Product List]

Another view of the Northwind sample database selects products with all units in the product table that are higher than the average unit price:

You can query the view above like this:

SELECT * FROM [Products Above Average Price]

Another view instance from the Northwind database calculates the total sales for each category in 1997. Notice that this view picks up the data from another view called Product Sales for 1997:

You can query the view above like this:

SELECT * FROM [Category Sales for 1997]

You can also add conditions to the query. Now, we just need to see all the sales of the "Beverages" Category:

SELECT * FROM [Category Sales for 1997]where categoryname= ' beverages '

　　

Update view

You can use the following syntax

CREATE OR REPLACE VIEW view_name asselect column_name (s) from table_namewhere condition

Now, add the Category column to the current Product List view

CREATE VIEW [Current Product List] Asselect ProductID, ProductName, Categoryfrom productswhere discontinue=no

　　

Undo View

You can use the following command

DROP VIEW view_name

　　

SQL Advanced Apps (VIEW)