Structured Query Language/Views

From Wikibooks, open books for an open world
Jump to: navigation, search

← SELECT: Case Expression | INSERT 2 →

Often users and applications request information in a form which differs from the structure of existing tables. To achieve those requests the SELECT command offers plenty possibilities: projections, joins, group by clause and so on. If there are always the same requests, what is the case in particular for applications, or if the table structure intentionally should be hidden from the application-level, views can be defined. Furthermore the access rights to views may be different from those to tables.

Views look like a table. They have columns of a certain data type, which can be retrieved in the same way as columns of a table. But views are only definitions, they don't have data of its own! Their data is always the data of a table or is based on another view. A view is a different sight to the stored data or somewhat like a predefined SELECT.

Create a View[edit]

One creates a view by specify its name, column names - which is optionally - and especially the SELECT command on which the view is based. Within this SELECT all elements are allowed in the same way as in a standalone SELECT command. If no column names are specified the column names of the SELECT are used.

CREATE VIEW <view_name> [(column_name, ...)] AS
  SELECT ...  -- as usual
;

Examples and Explanations[edit]

Example 1: Hid Columns[edit]

As a first example here is the view person_view_1 which contains all but id and ssn columns of table person. Users which have the right to read from this view but not from the table person doesn't have access to id and ssn.

CREATE VIEW person_view_1 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person;
 
-- SELECTs on views have identical syntax as SELECTs on tables
SELECT *
FROM   person_view_1
ORDER BY lastname;
 
-- The column 'id' is not part of the view. Therefore it is not seen and cannot be used
-- anywhere in SELECTs to person_view_1.
-- This SELECT will generate an error message because of missing 'id' column:
SELECT *
FROM   person_view_1
WHERE  id = 5;

As indicated in the above 'order by' example it is possible to use all columns of the view (but not all of the table!) within any part of SELECTs to the view: in the projection, the WHERE, ORDER BY, GROUP BY and HAVING clauses, in function calls and so on.

-- SELECTs on views have identical syntax as SELECTs on tables
SELECT COUNT(lastname), lastname
FROM   person_view_1
GROUP BY lastname
ORDER BY lastname;

Example 2: Rename Columns[edit]

Next there is a renaming of a column. The column name lastname of the table will be familyname in the view.

-- first technique: list the desired column names within parenthesis after the view name
CREATE VIEW person_view_2a (firstname, familyname, date_of_birth, place_of_birth, weight) AS
  SELECT                    firstname, lastname,   date_of_birth, place_of_birth, weight
  FROM   person;
 
-- second technique: rename the column in the SELECT part
CREATE VIEW person_view_2b AS
  SELECT firstname, lastname AS familyname, date_of_birth, place_of_birth, weight
  FROM   person;
-- Hint: technique 1 overwrites technique 2
 
-- Access to person.lastname is possible via person_view_2a.familyname or person_view_2b.familyname.
-- The objects person.familyname, person_view_2a.lastname or person_view_2b.lastname does not exist!

Example 3: Apply WHERE Condition[edit]

Not only columns can be hidden in a view. It's also possible to hid complete rows, because the view definition may contain a WHERE clause.

-- Restrict access to few rows
CREATE VIEW person_view_3 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person
  WHERE  place_of_birth IN ('San Francisco', 'Richland');
 
-- Verify result:
SELECT *
FROM   person_view_3;

This view contains only persons born in San Francisco or Richland. All other persons are hidden. Therefore the following SELECT retrieves nothing although there are persons in the table which fulfil the condition.

-- No hit
SELECT *
FROM   person_view_3
WHERE  place_of_birth = 'Dallas';
 
-- One hit
SELECT *
FROM   person
WHERE  place_of_birth = 'Dallas';

Example 4: Use Functions[edit]

This example usues the sum() function.

-- 
CREATE VIEW person_view_4 AS
  -- General hint: Please consider that not all columns are availabe in a SELECT containing a GROUP BY clause
  SELECT lastname, COUNT(lastname) AS count_of_members
  FROM   person
  GROUP BY lastname
  HAVING COUNT(*) > 1;
 
-- Verify result: 2 rows
SELECT *
FROM   person_view_4;
 
-- The computed column 'count_of_members' may be part of a WHERE condition. 
-- This SELECT results in 1 row 
SELECT *
FROM   person_view_4
WHERE  count_of_members > 2;

In this example the elaborated construct 'GROUP BY / HAVING' is hidden from users and applications.

Example 5: Join[edit]

Next, there is an example where a view contains columns out of serveral tables. To do so a JOIN is neccessary. The view contains the name of persons in combination with the available contact information. As an INNER JOIN is used, some persons occur multiple, others not at all.

-- Persons and contacts
CREATE VIEW person_view_5 AS
  SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
  FROM   person p
  JOIN   contact c ON p.id = c.person_id;
 
-- Verify result
SELECT *
FROM   person_view_5;
 
SELECT *
FROM   person_view_5
WHERE  lastname = 'Goldstein';

The columns person.id an contact.person_id are used during the definition of the view. But they are not part of the projection and hence not available for SELECTs to the view.

Hint: The syntax and semantic of join operations is explained on a separate page.

Some more Hints[edit]

Within a CREATE VIEW statement one may use more elements of the regular SELECT statement than it is shown on this page, especially: SET operations, recursive definitions, CASE expressions, ORDER BY and so on.

If there is an ORDER BY clause within the CREATE VIEW and another one in a SELECT to this view, the later one overwrites the former.

Write Access via Views[edit]

In some cases, but not in general, it should be possible to change data (UPDATE, INSERT or DELETE command) in a table by accessing it via a view. Assume, as an counterexample, that one wants to change the column count_of_members of person_view_4 to a different value. What shall the DBMS do? The column is subject to an aggregate function which counts the number of existing rows in the underlying table. Shall it add some more random values into new rows respectively shall it delete random rows to satisfy the new value of count_of_members? Of course not!

On the other hand a very simple view like 'CREATE VIEW person_0 AS SELECT * from person;', which is an 1:1 copy of the original table, should be manageable by the DBMS. Where is the borderline between updateable and non updateable views? The SQL standard does not define it. But the concrete SQL implementations offer limited write-access to views based on their own rules. These rules sometimes are very fix, in other cases they consists of flexible techniques like 'INSTEAD OF' triggers to give programmers the chance to implement their own rules.

Here are some general rules which may be part of the implementors fixed rules to define, which views are updateable in his sens:

  • The view definition is based on one and only one table. It includes the Primary Key of this underlying table.
  • The view definition must not use any aggregate function.
  • The view definition must not have any DISTINCT-, GROUP BY- or HAVING-clause.
  • The view definition must not have any JOIN, SUBQUERY, SET operation, EXISTS or NOT EXISTS predicate.

If it is possible to use the UPDATE, INSERT or DELETE command to a view, the syntax is the same as with tables.

Clean up the Example Database[edit]

The DROP VIEW statement deletes a view definition. In doing so the data of the underlying table(s) is not affected.

Don't confuse the DROP command (definitions) with the DELETE command (data)!

DROP VIEW person_view_1;
DROP VIEW person_view_2a;
DROP VIEW person_view_2b;
DROP VIEW person_view_3;
DROP VIEW person_view_4;
DROP VIEW person_view_5;

Exercises[edit]

Create a view 'hobby_view_1' which contains all columns of table 'hobby' except 'id'.
Rename column 'remark' to 'explanation'. Create two different solutions.

Click to see solution
CREATE VIEW hobby_view_1a AS
  SELECT hobbyname, remark AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1a;
 
CREATE VIEW hobby_view_1b (hobbyname, explanation) AS
  SELECT hobbyname, remark
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1b;

Create a view 'hobby_view_2' with the same criteria as in the previous example. The only difference
is that the length of the explanation column is limited to 30 character. Hint: use the function
substr(<column name>, 1, 30) to determine the first 30 characters - she is not part of the SQL standard but works in plenty implementation.

Click to see solution
CREATE VIEW hobby_view_2 AS
  SELECT hobbyname, substr(remark, 1, 30) AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_2;

Create a view 'contact_view_3' which contains all rows of table contact with the exception of the 'icq' rows. Count the number of the view rows and compare it with the number of rows in the table 'contact'.

Click to see solution
CREATE VIEW contact_view_3 AS
  SELECT *
  FROM   contact
  WHERE  contact_type != 'icq';  -- an alternate operator with the same semantic as '!=' is '<>'
 
-- Verification
SELECT 'view',  COUNT(*) FROM contact_view_3
  UNION
SELECT 'table', COUNT(*) FROM contact;

Create a view 'contact_view_4' which contains one row per contact type with its notation and the number of occurences. Afterwords select those which occur more than once.

Click to see solution
CREATE VIEW contact_view_4 AS
  SELECT contact_type, COUNT(*) AS cnt
  FROM   contact
  GROUP BY contact_type;
 
-- Verification
SELECT *
FROM   contact_view_4;
 
-- Use columns of a view with the same syntax as column of a table.
SELECT *
FROM   contact_view_4
WHERE  cnt > 2;

Create a view 'person_view_6' which contains first- and lastname of persons plus the number of persons with the same name as the person itself (family name). Hint: the solution uses a correlated subquery.

Click to see solution
CREATE VIEW person_view_6 AS
  SELECT firstname, lastname, (SELECT COUNT(*) FROM person sq WHERE sq.lastname = p.lastname) AS cnt_family 
  FROM   person p;
 
-- Verification
SELECT *
FROM   person_view_6;

Clean up the example database.

Click to see solution
DROP VIEW hobby_view_1a;
DROP VIEW hobby_view_1b;
DROP VIEW hobby_view_2;
DROP VIEW contact_view_3;
DROP VIEW contact_view_4;
DROP VIEW person_view_6;