Views or Virtual tables  

  • View = a virtual table that is computed when it is used

    • The content of a view (virtual table) is not stored (on disk)

    • The content of a view (virtual table) will be computed when the view (virtual table) is accessed

  Recall: SQL output is a table  

  • Consider the following query: 

        select fname, lname, dname, salary
    from employee, department
    where dno=dnumber

  Output:

     +--------+---------+----------------+----------+
     | fname  | lname   | dname          | salary   |
     +--------+---------+----------------+----------+
     | John   | Smith   | Research       | 30000.00 |
     | Frankl | Wong    | Research       | 40000.00 |
     | Alicia | Zelaya  | Administration | 25000.00 |
     | Jennif | Wallace | Administration | 43000.00 |
     | Ramesh | Narayan | Research       | 38000.00 |
     | Joyce  | English | Research       | 25000.00 |
     | Ahmad  | Jabbar  | Administration | 25000.00 |
     | James  | Borg    | Headquarters   | 55000.00 |
     +--------+---------+----------------+----------+

    Notice that:

    • The output of a query is a relation (= table) !!!

    • This table can be computed (with the above query) !!!

  Defining a view (virtual table) in SQL  

  • A view is defined using the CREATE VIEW command: 

       CREATE VIEW   virtTableName  AS  ( SQL query )

  • Example: 

       CREATE VIEW DeptEmps
   AS
   (
      select fname, lname, dname, salary        
      from employee, department
      where dno=dnumber
   )

    When DeptEmps is used in a query:

    • The SQL engine will execute the associated query to construct the virtual table

  Using a virtual table in SQL SELECT command  

  • You can use a virtual table exactly like an ordinary relation in an SQL query: 

         select * from DeptEmps where dname='Research';

  Output:

      +--------+---------+----------+----------+
      | fname  | lname   | dname    | salary   |
      +--------+---------+----------+----------+
      | John   | Smith   | Research | 30000.00 |
      | Frankl | Wong    | Research | 40000.00 |
      | Ramesh | Narayan | Research | 38000.00 |
      | Joyce  | English | Research | 25000.00 |
      +--------+---------+----------+----------+

  • Note:

    • You cannot update a virtual table

      (Because the virtual table does not exists)