Lesson 7	How do I create view in SQL?
Objective	Create a view that joins two tables

Create SQL View

Different Database Views

The CREATE VIEW statement lets you

indicate the name of the view and
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.

CREATE VIEW Command

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.

View1:

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

View2:

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:

QView1:

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

Create View - Exercise

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