Back
Next
SQL Tutorial will guide us now SQL Views, SQL Views can be considered as
virtual tables. Generally speaking, a table has a set of definition, and it
physically stores the data. A view also has a set of definitions, which is build
on basses of table or tables or other view or views, and it
does not physically store the data. SQL Views are basically depends on
existing tables.
Syntax for SQL Create View
The syntax for creating an SQL View is as follows:
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
"SQL Statement" can be any of the SQL statements we have discussed in this SQL
Tutorial before.
Let's use a simple example to illustrate. Now we have the following table:
Structure of Employees
| empID |
Integer, Not Null and Primary Key |
| FirstName |
char(50) |
| LastName |
char(50) |
| Email |
char(25) |
| Phone |
Integer |
and we want to create a view called View_Employees that contains only the
FirstName, LastName, and Email columns from Employees table, we would query as
under,
Example of SQL Create View
CREATE VIEW View_Employees
AS SELECT FirstName, LastName, Email
FROM Employees
Now we have a view with name of View_Employees with the following structure:
View View_Employees
(FirstName char(50),
LastName char(50),
Email char(25))
We can also use a SQL View to apply SQL Joins to
two tables. In this case, users only see one
view rather than two tables, and the SQL statement
users need to issue becomes much simpl. Let's see, now
we have the following two tables: Table: Employees
| FirstName |
LastName |
Email |
DOB |
Phone |
| Austin |
Hennery |
austin.it@gamail.com |
24/11/1978 |
446 102-2222 |
| Creston |
Narvon |
narvon_crest@hotmail.com |
22/04/1976 |
443 325-4545 |
| Kate |
Bladen |
kbladen@usa.net |
01/05/1980 |
326 503-3232 |
| Angela |
Julie |
julieee@yahoo.co.uk |
11/12/1977 |
326 323-8888 |
| Bobbi |
Ferguson |
bobi123@kiwibox.com |
10/02/1970 |
506 252-9875 |
| Cute |
Alfa |
cafa@gmail.com |
19/05/1972 |
506 272-4566 |
Table Employees_Salary
| FirstName |
Month |
Salary |
| Austin |
January |
1000$ |
| Creston |
January |
1000$ |
| Kate |
January |
1000$ |
| Angela |
January |
1900$ |
| Bobbi |
January |
1000$ |
| Cute |
January |
1000$ |
and we want to build an SQL View that will display salary by Employees with full
name. We would issue the following SQL statement to create our required SQL
View.
Example of SQL Create View on Joins
CREATE VIEW View_Employees_Salary
AS SELECT emp.FirstName || ' ' || emp.LastName
|| ' getting Salary $' || empSal.Salary
|| ' per month'
FROM Employees emp, Employees_Salary empSal
WHERE emp.FirstName = empSal.FirstName
ORDER BY emp.FirstName;
This gives us a view, View_Employees_Salary, that has been defined
to select salary of each employee and show with its full name and separate the
first name form the last name with a white space. If we want to find out the
content of this view, we will use this below query,
SELECT * FROM View_Employees_Salary
| Result of SQL View with SQL Join |
| Austin Hennery getting Salary $ 1000 per month |
| Creston Narvon getting Salary $ 1000 per month |
| Kate Bladen getting Salary $ 1000 per month |
| Angela Julie getting Salary $ 1900 per month |
| Bobbi Ferguson getting Salary $ 1000 per month |
| Cute Alfa getting Salary $ 1000 per month |
Back
Next
| 
Home 
