Views (SQL)

A view is a virtual table derived from other tables.

• There are two ways they are implemented in implementation:
  • Query modification: copy and paste queries
  • View materialisation: short-term physical implementation
• They are limited in terms of update operations they can perform.
• Useful for security and authorisation.
• Prevents redundant data storage.

Operations we can perform:

• Create View (SQL)

  To create a new view, we use the following syntax:

  CREATE VIEW WORKS_ON1 AS
  SELECT name, pname, hours
  FROM   EMPLOYEE, PROJECT, WORKS_ON
  WHERE  ssn=essn
    AND  pno=pnumber

  Link to original

• Select from the view: we can select from the view as if it were an ordinary table.

  SELECT name
  FROM   WORKS_ON1
  WHERE  pname='productX';

• Drop the view: we can drop the view when it’s no longer needed.

  DROP WORKS_ON1;