SQL View

SQL VIEW

View is a virtual table based on the result of a query created using the existing tables. View comprises of rows and columns similar to a real table. The fields in a view are populated from one or more than one real tables exist in the database.


Syntax:

CREATE VIEW view_name AS
SELECT col_name_1, col_name_2, … , col_name_n
FROM tbl_name
WHERE <condition>

A view always generates an up to date records. The DBMS regenerates data every time, using the SQL Statement saved in the View.

Demo Tables

Table Category

Cat_ID Category_Name
1 Keyboard
2 Mouse
3 LCD Display
4 Camera
5 Printer
6 Scanner
7 Laptop

Table Products

ID Cat_ID Product_Name Price
1 1 A4_Tech Keyboard 32
2 2 A4_Tech Optical Mouse 28
3 3 HP 17” LCD Display Unit 140
4 1 A4_Tech Wireless Keyboard 80
5 3 Philips 17” LED Display Unit 125
6 5 HP Laser Jet 1020 Printer 175
7 1 Bluetooth Keyboard 125
8 4 A4_Tech HD WebCam 60

Example

For this example, we will be using the above two tables, ‘Category’ and ‘Products’. Both the tables are related.

Now, we will create a view named “viewCatWiseProducts”.

The query will be as follows:

CREATE VIEW viewCatWiseProducts AS
SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
INNER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
ORDER BY CAT_ID;

Now, whenever we need to query the above, the following query syntax will be used:

SELECT * FROM viewCatWiseProducts

This query will return the following records:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
2 Mouse A4_Tech Optical Mouse 28
3 LCD Display HP 17” LCD Display Unit 140
4 Keyboard A4_Tech Wireless Keyboard 80
5 LCD Display Philips 17” LED Display Unit 125
6 Printer HP Laser Jet 1020 Printer 175
7 Keyboard Bluetooth Keyboard 125
8 Camera A4_Tech HD WebCam 60

It is further possible to apply conditions on the view to get more customized or filtered information.

In the following example, we will extract only those records that fall under the “Keyboard” category

Syntax would be:

SELECT *

FROM viewCatWiseProducts

WHERE Category_Name = ‘Keyboard’

The output would be:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
4 Keyboard A4_Tech Wireless Keyboard 80
7 Keyboard Bluetooth Keyboard 125

Updating a View

The following syntax is used to update an existing view.

CREATE OR REPLACE VIEW view_name AS
SELECT col_name_1, col_name_2, … , col_name_n
FROM tbl_name
WHERE <condition>

Example

CREATE VIEW viewCatWiseProducts AS
SELECT Products.ID, Category.Category_Name, Products.Name, Products.Price
FROM Category
INNER JOIN Products
ON Category.Cat_ID=Products.Cat_ID
WHERE Products.Price <= 100
ORDER BY CAT_ID;

In above view, the output will only show those products which have price less than or equal to 100.

SELECT * FROM viewCatWiseProducts

The above query will return following:

ID Category_Name Product_Name Price
1 Keyboard A4_Tech Keyboard 32
2 Mouse A4_Tech Optical Mouse 28
4 Keyboard A4_Tech Wireless Keyboard 80
8 Camera A4_Tech HD WebCam 60

Dropping or Deleting a View

To drop or delete a view, following syntax is used:

DROP VIEW view_name

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes