SQL VIEW and Its Operation
A view
is nothing more than a SQL statement that is stored in the database with an
associated name. A view is actually a composition of a table in the form of a
predefined SQL query.
A view
can contain all rows of a table or select rows from a table. A view can be
created from one or many tables which depend on the written SQL query to create
a view.
Views, which are
kind of virtual tables, allow users to do the following:
1. Structure data in
a way that users or classes of users find natural or intuitive.
2. Restrict access to
the data such that a user can see and (sometimes) modify exactly what they need
and no more.
3. Summarize data
from various tables which can be used to generate reports.
Creating Views:
Database
views are created using the CREATE VIEW statement. Views can be created from a
single table, multiple tables, or another view.
To
create a view, a user must have the appropriate system privilege according to
the specific implementation.
The
basic CREATE VIEW syntax is as follows:
CREATE
VIEW view_name AS
SELECT
column1, column2.....
FROM
table_name
WHERE
[condition];
You
can include multiple tables in your SELECT statement in very similar way as you
use them in normal SQL SELECT query.
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Ramesh | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Chaitali | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
Now,
following is the example to create a view from CUSTOMERS table. This view would
be used to have customer name and age from CUSTOMERS table:
SQL
> CREATE VIEW CUSTOMERS_VIEW AS
SELECT
name, age
FROM
CUSTOMERS;
Now,
you can query CUSTOMERS_VIEW in similar way as you query an actual table.
Following is the
Example:
SQL
> SELECT * FROM CUSTOMERS_VIEW;
This
would produce the following result:
+------------+-------+
|
NAME | AGE |
+------------+-------+
|
Ramesh | 32 |
|
Khilan | 25 |
|
kaushik | 23 |
|
Chaitali | 25 |
|
Hardik | 27 |
|
Komal | 22 |
|
Muffy | 24 |
+------------+------+
Updating a View:
A view can be
updated under certain conditions:
1.
The SELECT clause may not contain the keyword DISTINCT.
2.
The SELECT clause may not contain summary functions.
3.
The SELECT clause may not contain set functions.
4.
The SELECT clause may not contain set operators.
5.
The SELECT clause may not contain an ORDER BY clause.
6.
The FROM clause may not contain multiple tables.
7.
The WHERE clause may not contain subqueries.
8.
The query may not contain GROUP BY or HAVING.
9.
Calculated columns may not be updated.
10.
All NOT NULL columns from the base table must be included in the view in
order for the INSERT query to function.
0 Comments