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