SQL Create Views, SQL Tutorial, Global Guide Line Technology.
Bookmark Bookmark: SQL Create Views, SQL Tutorial Forum | Blog |

      Home                    

Basic SQL Guide.           
SQL Introduction.               
 What is Table?                  
 SQL Create Table.            
 SQL Insert Statement.      
 SQL Select Keyword.        
 SQL Order by clause.        
 SQL Count Statement.      
 SQL Group By Clause.      
 SQL Having Clause.          
 SQL Alias Tables.             
 SQL Join Tables.              
 SQL Outer Join.               
 SQL Update Statement.   
 SQL Delete Statement.    
 SQL Distinct Keyword.      
 SQL Where Keyword.       
 SQL And Or Keyword.      
 SQL In Keyword.              
 SQL Between Keyword.   
 SQL Like Keyword.          
 SQL Functions.                
 SQL Concatenate.           
 SQL Substrings.               
 SQL Trim Function.         
 SQL Constraints.              
 SQL Primary Key.            
 SQL Foreign Key.            
 SQL Create Views.
 SQL Create Index.           
 SQL Alter Table.             
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                

SQL Tutorial >> SQL Create Views.


     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     
 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]

Copyright 2005 -  2018 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 214 visitors are online now