Creating a view, by example

The following is an example of a simple Oracle view using the Student table. Note: You may have to update some of the rows to add data to see the effects of this view.

CREATE OR REPLACE VIEW vw_STUDENTS1
AS
SELECT 
       FIRSTNAME,
       LASTNAME,
       BIRTH_DTTM,
       (FIRSTNAME || ' ' || LASTNAME)  "FULLNAME_FL",
       trunc(months_between(sysdate,BIRTH_DTTM)/12) "AGE"     
FROM   COURSEREGISTRATION.STUDENTS
/

In general, you should adopt some naming standard for your views. This standard is vw_<name of view>. The name should be somewhat reflective of the purpose of the view. You can clearly see that I did not do such a good job with this. Sometimes coming up with a short descriptive name for your view is easier said than done.

The syntax for creating a view is...

CREATE OR REPLACE VIEW `<your_view_name>` 

AS

...followed by a normal SQL SELECT. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. The scenarios are endless. It really depends on the purpose of the view.

As you can see in our view we are formatting the first and last name. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement. You can also see that we have take the birth date column and calculated age.

Executing a View

Execute an SQL View

The example below shows all of the code from the view. You could also do a SELECT *, or further restrict the columns you want to see. You can also add additional row restriction to the view as we have done.

SELECT FIRSTNAME   ,
       LASTNAME    ,
       BIRTH_DTTM  ,
       FULLNAME_FL ,
       AGE         
FROM   VW_STUDENTS1
WHERE  AGE IS NOT NULL 
/
       
        

Creating a View containing one or more SQL Tables

Another key advantage of a view is that it allows us to join multiple tables together. 

CREATE OR REPLACE VIEW vw_OCCUPIED_SEATS_BY_CLASS
AS
SELECT 
       c.COURSE_DESIGNATER_FK AS  "COURSE",   
       b.SEAT_NUM ,
       (a.FIRSTNAME || ' ' || a.LASTNAME) AS "STUDENT"         
 
FROM         STUDENTS          a 
        JOIN CLASSREGISTRATION b 
             ON  a.STUDENT_ID = b.STUDENT_ID_FK 
        JOIN CLASSES c
             ON  c.CLASSES_NUM = b.CLASSES_NUM
/
       
        

Above is a simple view that provides us with a listing of occupied/unoccupied seats for our classes. As you can see from the examples below, we can use this view in a variety of different ways. Note that for each scenario that we did not need to join any tables. The grunt work is already done. 

Using our View

View a single class

SELECT COURSE   ,
       SEAT_NUM ,
       STUDENT  
FROM   VW_OCCUPIED_SEATS_BY_CLASS
WHERE  COURSE = 'Perl100' and STUDENT <> '1'
/    
       

COURSE   SEAT_NUM  STUDENT          
----------------------------------
Perl100  1         Madge Lowdown    
Perl100  2         Robert Frapples  
Perl100  3         Mary Lamacker    
Perl100  4         Helga Joens      
Perl100  5         Maggie Jomomma   
Perl100  6         Mary Meigh       
Perl100  7          JONES           
Perl100  8         Bob JONES        
Perl100  9         Ted Applebee     
Perl100  10        Jon Nesbitt      
Perl100  11        Mary Lamacker    
Perl100  12        Mark Jackson    
       

Count open seats by class

SELECT 
       COURSE   ,
       COUNT(SEAT_NUM) "# Open Seats" 
FROM   VW_OCCUPIED_SEATS_BY_CLASS
GROUP BY COURSE
/
     
        

COURSE          # Open Seats  
----------------------------
dbOrchestra100  16            
Perl100         12             
        

Column name considerations

The column name MUST be unique in a view. Note the following example.

CREATE OR REPLACE View vw_NAME_CONFLICT
AS
SELECT 
a.CLASSES_NUM , 
b.CLASSES_NUM 

FROM CLASSES a 
JOIN CLASSESREGISTRATION b 
ON a.CLASSES_NUM = b.CLASSES_NUM
/          
     
        

Duplicate column name 'classes_num'

Here is how to resolve this issue. Create a unique name using "as".

CREATE OR REPLACE VIEW vw_NAME_CONFLICT
AS
  SELECT a.CLASSES_NUM   "CLASSES_CLASSES_NUM"        , 
         b.CLASSES_NUM   "CLASSREGISTRATION_CLASSES_NUM"
   
    FROM         CLASSES         a 
          JOIN CLASSREGISTRATION b 
               ON  a.CLASSES_NUM = b.CLASSES_NUM

/   
     
        

Drop a View

DROP VIEW COURSEREGISTRATION.VW_NAME_CONFLICT 
/
        
        

If you find this site useful and are a book buyer/reader... You can support my site by clicking on one of the Links below to Abe books By doing this, I will receive a small commision on your purchase and making your purchase will not cost you any more money than if you went directly the site yourself!  Abe Books sells books at a fraction of the original cost. I have purchased many books from them originally cost $40.00 for as little as $5 to $10 dollars including shipping costs Click on applicable icon to find books at Abe Books on ...
Oracle 8i and 9i	Oracle 10g	PL/SQL
Abe Books is one of my affiliates. I only use affiliates that I also purchase products from. I love Abe books. If it is printed they likely have a copy. I have also included a generic link to Abe Books below. Once on their site, you will find that they have a nice, easy to use search engine so to find what you are looking for.