Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Sql Func Format Sql Func Count ❯

SQL Views

A view is a virtual table.

This chapter explains how to create, update, and delete views.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table.

SQL CREATE VIEW Syntax

Note: Views always display the latest data! Whenever a user queries a view, the database engine rebuilds the data using the view's SQL statement.

SQL CREATE VIEW Example

The sample database Northwind has some views installed by default.

The view "Current Product List" lists all active products (not discontinued) from the "Products" table. This view is created with the following SQL:

We can query the above view as follows:

Another view in the Northwind sample database selects all products from the "Products" table with a unit price higher than the average unit price:

We can query the above view as follows:

Another view in the Northwind sample database calculates the total sales for each category in 1997. Note that this view takes data from another view named "Product Sales for 1997":

We can query the above view as follows:

We can also add conditions to the query. Now, we only need to see the total sales for the "Beverages" category:

SQL Updating a View

You can update a view using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

Now, we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:

SQL Server

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{ 
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     
}

-schema_name: The name of the schema to which the view belongs.

-view_name: The view to be altered.

-column: The name(s) of one or more columns that will be part of the specified view (separated by commas).

SQL Dropping a View

You can delete a view by using the DROP VIEW command.

SQL DROP VIEW Syntax

❮ Sql Func Format Sql Func Count ❯