dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted April 27, 2020

Understanding Views in SQL

By Manoj Debnath

Tables in a SQL database may contain enormous amount of data, but they are not always in a useful format to be readily used. The volume of data must be filtered based upon some specified criteria for efficient use. Due to security reason, we might want to make public only a certain amount of data while rest might be accessible to the privileged users. The SQL DML operations are extensible and used to filter through one or more tables using complex query expressions. Leveraging the idea, we can create virtual tables from persistent base tables using SQL that would contain the exact data we need. This is the reason the SQL:2006 standard introduced the use of view tables, or views. The definition of a view or a virtual table exists as a schema object. This article introduces the concept of views in SQL, how it works and shows how it is implemented with some examples.

Introduction to SQL Views

SQL views are nothing but virtual tables reside in memory derived from one or more base tables. Virtual tables mean the tuples in views do not have physical existence and are not stored into the database. The tuples are like temporary data created as an outcome of the SQL query which typically draws filtered data from one or more base tables. As a result, there is a limitation on the type of operation that can be applied to a view table. For example, the update operation cannot be applied to all types views, but it has no limitation on applying SQL query on it.

The examples below are tested with the MySQL database. Start by creating a few tables:

my_company database:

CREATE DATABASE my_company;

CREATE TABLE Employee(
    empId INT(11) UNSIGNED CHECK (empId > 0),
    empName VARCHAR(20),
    birthDate DATE,
    address TEXT(128),
    gender VARCHAR(1),
    salary DECIMAL(15,2),
    managerId INT(11) UNSIGNED,
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(empId)
);
 
CREATE TABLE Department(
    deptId INT(11) UNSIGNED CHECK (empId > 0),
    deptName VARCHAR(20),
    deptMgrId INT(11) UNSIGNED,
    mgrStartDate DATE,
    PRIMARY KEY(deptId)
);

CREATE TABLE Project(
    projId INT(11) UNSIGNED CHECK (empId > 0),
    projName VARCHAR(20),
    projLocation TEXT(128),
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(projId)
);
 
CREATE TABLE EmpWorksOnProj(
    empId INT(11) UNSIGNED,
    projId INT(11) UNSIGNED,
    hoursWorked DECIMAL(4,2)
);  
 
ALTER TABLE Employee ADD CONSTRAINT fk_emp_mgr FOREIGN KEY(managerId) REFERENCES Employee(empId);
ALTER TABLE Employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);
ALTER TABLE Department ADD CONSTRAINT fk_dept_mgr FOREIGN KEY(deptMgrId) REFERENCES Employee(empId);
ALTER TABLE Project ADD CONSTRAINT fk_proj_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);

The views can be thought of as a reference table, and we can use it as frequently as we want although it may not exist physically. For example, we may frequently need to refer to the my_company database and find Employee and Project information. Note that there is many to many relationship between Employee and Project as one person can work on many project and also one project has many employees. Therefore, instead of specifying the join of three tables: Employee, EmpWorksOnProj, and Project every time we need a collaborative information and issue a query, we define a view that is specified as the outcome of the join among these tables. The view forms the virtual table created from the result of the query. The advantage is that the query now can retrieve from single resultant table rather than having to retrieve from three joined tables. The collection of tables: Employee, Project, Department etc. thus form the base tables or defining table of the view.

Let's create some views based upon the schema given above.

CREATE VIEW V1
AS
SELECT
   empName, projName, hoursWorked
FROM
   Employee, Project, EmpWorksOnProj
WHERE
   Employee.empId=EmpWorksOnProj.empId
AND
   Project.projId=EmpWorksOnProj.projId;
 

The way to specify SQL queries on view or virtual table is same as specifying queries involving base tables. You can use SQL SELECT on views to get the data as follows:

SELECT * FROM V1;

EmpName

ProjName

HoursWorked

Mickey Mouse

ClubHouse

6.50

...

...

...

Donald Duck

Farming

7.0

The following creates a second view:

CREATE VIEW V2 AS SELECT deptName, COUNT(*), SUM(salary) FROM Department, Employee WHERE Employee.deptId=Department.deptId GROUP BY deptName;

The SQL SELECT results in

SELECT * FROM V1;

DeptName

COUNT(*)

SUM(salary)

Music

5

56000.00

...

...

...

Drama

2

25400.00

Note that in view V1 the attribute names are derived from the base table. In V2 new attribute names are explicitly specified using one to one correspondence between the specified attributes of CREATE VIEW clause and those specified in the SELECT clause. The SELECT clause with the view is decisive of the definition of the view.

The information on view is always supposed to be up to date. That means it must always reflect the changes made on the base tables on which it is defined. This is interesting, because it means that the view is not actually materialized at the time of defining it, but later when a query is specified on it. The database management system at the background is responsible for keeping the view up to date.

UPDATE, INSERT and DELETE on views

In SQL, it is possible to create updatable views that can be used to change existing data or insert new rows into the view which in turn inserts or modifies the record in the base table. A view is updatable or not is determined by SELECT statement defined within the view definition. There is no special clause to designate a view to be updatable. Typically, the view definition must be simple and must not contain any aggregate functions such as SUM, AVG, MAX, MIN, COUNT. Any sort of grouping or DISTINCT or JOIN clause also makes view not updatable. Refer to the relevant database manual of the specific RDBMS for what makes a view non-updatable.

Let’s create a view that is updatable:

CREATE VIEW v3_ch_dept_name
AS
SELECT
   deptId, deptName, deptMgrId, mgrStartDate
FROM
   Department;

The SELECT query on view:

SELECT * FROM v3_ch_dept_name;

DeptId

DeptName

DeptMgrId

MgrStartDate

1

Music

123456789

2020-01-01

...

...

...

...

5

Drama

987654321

2018-03-05

Now update the view by changing the department name (deptName).

UPDATE
   v3_ch_dept_name
SET
   deptName = 'Security'
WHERE
   deptId = 5;

A row can be inserted in the view as follows:

INSERT
   INTO v3_ch_dept_name
VALUES (7,'Logistics',666884444,'1982-07-07');

Also we can DELETE a row from the view as follows:

DELETE FROM v3_ch_dept_name WHERE deptId = 7;

In MySQL, you can easily find the views in a database that are updatable or not using following SELECT command.

SELECT
   table_name
FROM
   information_schema.views
WHERE
   is_updatable like 'YES'
AND
   table_schema like 'my_company';

DROP views from the database

A view can always be disposed of with DROP VIEW <view_name> command.

DROP VIEW V1;

Note that when we execute the drop view command it removes the view definition. The underlying data stored in the base tables from which this view is derived remains unchanged. A view once dropped can be recreated with the same name.

The ALTER VIEW statement

Views are generally unalterable according to SQL:2006 standard, that means the ALTER VIEW statement does not work with views. However, there are RDBMS's like MySQL or SQL Server that supports this kind of statement. The Oracle believes in dropping the view first then recreating it rather than altering it. Therefore, the functionalities supported on views by RDBMS's vary from product to product.

Conclusion

The SQL views are also useful tool for accessing multiple data types. Complex queries can be stored within view definition. This leverages reuse because we can invoke the view instead of recreating the queries every time we need them. It is a convenient way to present information to user hiding many information that we do not want to expose to everyone. This important from the security perspective as well. Complex structures can be synthesized and presented in an easy format for the end user.

References:

Elmasri, Ramez, and Shamkant B. Navathe. Fundamentals of Database Systems. Pearson Education.



MySQL Archives




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM