Using SQL views

Date: February 19th, 2011
Author: Mark Kolin

Views are virtual tables. Basically they represent a part of a result set. Views are used both for managing access to data (in combination with rights management) and for creating an interface to the database so that if the structure of the database is altered the changes to the software using it are minimal.

A view has the following syntax:


  1. CREATE VIEW viewName AS
  2. SELECT column1, …, columnN
  3. FROM tableName
  4. WHERE someCondition

You can easily notice it is quite similar to an SQL query.

Now let’s consider the following table:

  1. CREATE TABLE Employees
  2. (
  3.  E_ID int NOT NULL PRIMARY KEY,
  4.  FirstName varchar(255),
  5.  LastName varchar(255) NOT NULL,
  6.  City varchar(255),
  7.  Address varchar(255)
  8. )

We want to create a view that returns only the FirstName and LastName columns of employees from Paris. We do it like this:

  1. CREATE VIEW ParisEmployeeInfo AS
  2. SELECT FirstName, LastName
  3. FROM Employees
  4. WHERE City = 'Paris'

We can query the view like it is a normal table:

  1. SELECT * FROM ParisEmployeeInfo

Or:

  1. SELECT FirstName
  2. FROM ParisEmployeeInfo
  3. WHERE LastName = 'Smith'

To edit an existing view we use the CREATE OR REPLACE VIEW statement:

  1. CREATE OR REPLACE VIEW ParisEmployeeInfo AS
  2. SELECT LastName
  3. FROM Employees
  4. WHERE City = 'Paris'

To delete a view we use the following syntax:

  1. DROP VIEW ParisEmployeeInfo

An alternative way of modifying views is a combination of DROP and CREATE.

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Leave a Reply