- SQL Basics
- SQL Home
- SQL Syntax
- SQL RDBMS
- SQL Databases
- SQL Data Types
- SQL Operators
- SQL Expressions
- SQL Database
- SQL Create Database
- SQL Select Database
- SQL Drop Database
- SQL Table
- SQL Create Table
- SQL Drop Table
- SQL Keywords
- SQL Insert Into
- SQL Select
- SQL Where
- SQL And & Or
- SQL Update
- SQL Delete
- SQL Like
- SQL Select Top
- SQL Group By
- SQL Order By
- SQL Distinct
- SQL In
- SQL Between
- SQL Joins
- SQL Joins
- SQL Inner Join
- SQL Left Join
- SQL Right Join
- SQL Full Join
- SQL Constraints
- SQL Constraints
- SQL Unique
- SQL Not Null
- SQL Primary Key
- SQL Foreign Key
- SQL Check
- SQL Default
- SQL Create Index
- SQL Advance
- SQL Aliases
- SQL Union
- SQL Auto Increment
- SQL Views
- SQL Dates
- SQL Transactions
- SQL Injection
- SQL Test
- SQL Online Test
- Give Online Test
- All Test List
SQL Views
SQL VIEW is a virtual table based on the result-set of an SQL statement.
Following is the general form to create SQL VIEW:
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Note - A VIEW always shows up-to-date data. The database engine recreates the data, using the VIEW's SQL statement, every time a user queries a view
SQL CREATE VIEW Example
If you have Northwind database, you can see that, by default, it has several views installed.
The VIEW "Current Product List" lists all the active products (products that are not discontinued) from the table "Products"
The VIEW is created with the following SQL Statement:
CREATE VIEW [Current Product List] AS SELECT P_ID,P_Name FROM Products WHERE Discontinued=No
Now, we can query the view above as follows:
SELECT * FROM [Current Product List]
Another VIEW in the Northwind sample database selects every product in the table "Products" with a unit price higher than the average unit price
Let's look at the following SQL Statement:
CREATE VIEW [Products Above Average Price] AS SELECT P_Name,UnitPrice FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
Now, we can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another VIEW in the Northwind database calculates the total sale for each category in 1997
Note that this VIEW selects its data from the another VIEW called "Product Sales for 1997"
Let's look at the following SQL Statement:
CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT C_Name,Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY C_Name
Now We can query the VIEW above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the "Mountain Dew" category:
SQL Example Source Code:SELECT * FROM [Category Sales For 1997] WHERE C_Name='Mountain Dew'
Update SQL VIEW
You can update a VIEW.
The CREATE/REPLACE VIEW Syntax:
Following is the general form of the SQL CREATE or REPLACE VIEW:
CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Now we want to add the column "Category" to the "Current Product List" VIEW. We will update the VIEW with the following SQL Statement:
CREATE VIEW [Current Product List] AS SELECT P_ID,P_Name,Category FROM Products WHERE Discontinued=No
Drop SQL VIEW
You can drop or delete a VIEW with the DROP VIEW command
The DROP VIEW Syntax
Following is the general form of the SQL DROP VIEW:
DROP VIEW view_name
« Previous Tutorial Next Tutorial »
Like/Share Us on Facebook 😋