SQL Views

SQL Views

A view is a virtual table. This chapter shows how to create, update, and delete a view.

SQL CREATE VIEW Statement

1. In SQL, a view is a virtual table based on the result-set of an SQL statement.
2. 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.
3. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

   CREATE VIEW view_name AS
   SELECT column_name(s)
   FROM table_name
   WHERE condition
   

4. 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 Examples

1. If you have the Northwind database you can see that it has several views installed by default.
2. The view Current Product List lists all active products (products that are not discontinued) from the Products table. The view is created with the following SQL:

   CREATE VIEW [Current Product List] AS
   SELECT ProductID,ProductName FROM Products WHERE Discontinued=No;
   

SQL Updating a View

You can update a view by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

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

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products WHERE Discontinued=No;

Tables vs Views

Table

Storage Unit Contain Rows and Columns. RDBMS is composed of tables that contain related data. table is stored in the database. Tables have physical existence.

View

Logical Subset of Tables. View is an Database Object we can use DML it affects the base table and view. We can create a object for that table. View is a virtual table which do not have any physical existence.

1. This is one type of relation which is not a part of the physical database.
2. It has no direct or physical relation with the database.
3. Views can be used to provide security mechanism.
4. Modification through a view (e.g. insert, update, delete) generally not permitted

A VIEW is only a mirror image of table which is used at places where large access to a table is required. Views cannot be updated,deleted and modified but we could select data from views easily.

Base Relation:

1. A base relation is a relation that is not a derived relation.
2. While it can manipulate the conceptual or physical relations stored in the data.
3. It does not provide security.
4. Modification may be done with a base relation.

We can assign the view, a name & relate it the query expression as “Create View as”

Let EMPLOYEE be the relation. We create the table EMPLOYEE as follows:-

Create table EMPLOYEE
(Emp_No integer of null,
Name char (20),
Skill chars (20),
Sal_Rate decimal (10, 2),
DOB date,
Address char (100),)

For a very personal or confidential matter, every user is not permitted to see the Sal_Rate of an EMPLOYEE. For such users, DBA can create a view, for example, EMP_VIEW defined as:-

Create view EMP_VIEW as
(Select Emp_No, Name, Skill, DOB, Address
         From EMPLOYEE)

View :

1. Views are created from one or more than one table by joins, with selected columns.
2. Views acts as a layer between user and table.
3. Views are created to hide some columns from the user for security reasons, and to hide information exist in the column.
4. Views reduces the effort for writing queries to access specific columns every time.
5. Reports can be created on views.
6. View doesn’t contain any data.

We can get some attributes of a table or a group of tables as view. View is a query file which stores the Sql Query. When executed, the query returns the rows from the tables specified in the query which satisfies the conditions (to a dynamic virtual table which has the column names specified in the query). Each time we run the View, the View displays different result set depending on the data in the table.

Tags

1. Spring Data JPA - Entities