Different SQL Views relate differently to a Database

As you start working with views, it is important to also introduce the fact that different views will relate differently to the database when updating information. The different modes supported by views, read-only and updateable, are really only a concern if you are creating a view to insert information into, or updating information from your database tables. For most reporting, the updateable status of a given view is less important. This is because with the reporting queries, you are generally comfortable with the read-only view, since all you want to do is review the results, not update the underlying tables.

A view is a SQL statement that is stored in the database with an associated name and 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 depends 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 modify exactly what they need and nothing else.
3. Summarize data from various tables which can be used to generate reports.

The CREATE VIEW statement lets you

1. indicate the name of the view and
2. the statements that will be used to create it.

Remember, you can use just about any of the different SQL clauses in your view. Here is an example of a statement that will create a view:

CREATE VIEW MyView  
AS SELECT * FROM Authors 
WHERE Au_State = 'AZ'

The result is a view named MyView. After you have completed this, you can simply select from the view directly:

SELECT * FROM MyView

When you do, you will get all columns from the Authors table which have 'AZ' as their state. You do not have to specify the state in the SELECT statement because it's called out in the view. Note: If you are using the Web-based PUBS database for this course, these two queries against the database will not work.

In SQL, the command to specify a view is

CREATE VIEW

. The view is given a (virtual) table name (or view name), a list of attribute names, and a query to specify the contents of the view. If none of the view attributes results from 1) applying functions or 2) arithmetic operations, we do not have to specify new attribute names for the view, since they would be the same as the names of the attributes of the defining tables in the default case. The views in View1 and View2 create virtual tables.

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT Dname, COUNT (*), SUM (Salary)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno
GROUP BY Dname;

In View1, we did not specify any new attribute names for the view WORKS_ON1 (although we could have); in this case,WORKS_ON1 inherits the names of the view attributes from the defining tables EMPLOYEE, PROJECT, and WORKS_ON. View View2 explicitly specifies new attribute names for the view DEPT_INFO, using a one-to-one correspondence between the attributes specified in the CREATE VIEW clause and those specified in the SELECT clause of the query that defines the view.
We can now specify SQL queries on a view or virtual table in the same way we specify queries involving base tables. For example, to retrieve the last name and first name of all employees who work on the 'ProductX' project, we can utilize the WORKS_ON1 view and specify the query as in QV1:

SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname='ProductX';

Complete the exercise below to create a view that joins two tables.
Create View - Exercise