Querying Database Views

Database views provide the following advantages:

• De-couple applications from lower levels (relative to the three level schema architecture).

  You can use database views to change to the conceptual schema (for example, add columns to a table) without causing side effects on applications.

• Create views tailored to the users (applications) and their needs.

  The application programmers do not have to know the full database structure but only the excerpt that is relevant to them.

• Simplify queries.

  Complex queries referencing views are easier to formulate, provided that the view provides an appropriate pre-selection of data.

• Limit access to data.

  You can use views to realize value-dependent access privileges or to make sure certain columns are never visible for certain users.

Example of a view containing only red cars:

1234567
CREATE VIEW redCars 

     AS

SELECT CarID, Color, PlateNumber, HP AS Power
    FROM Car
WHERE Color = 'red';

You can select data from a view in the same way you select from a table.

Which red cars have more than 100 HP?

1234
SELECT CarID, PlateNumber, Power 
    FROM redCars
WHERE Power > 100
ORDER BY Power DESC, CarID ASC;

In this example, the redCars view would have listed all red cars, but the addition of the WHERE clause further reduced the result set to red card with more than 100 HP.

You can restrict a view to specific columns and rows of the underlying base table.

1234567
CREATE VIEW Audis 

     AS

SELECT Brand, Color, HP
    FROM Car
WHERE Brand = 'Audi';

You can also use the following in the SELECT part of the CREATE VIEW statement:

• Calculations and functions

• Any type of join; the need to formulate the same complex join condition frequently in application development is a typical case where views can be used to simplify

• A TOP N clause to limit the number of rows returned

• DISTINCT

  Note

  A view can contain a projection leading to potential duplicates in result sets.

• Aggregate expressions, GROUP BY and HAVING clauses

• Subqueries, uncorrelated or correlated

• UNION [ALL]

• An ORDER BY clause to achieve a default sort order of results

  Note

  This order can be overridden when SELECT is chosen from the view. For this reason, the use of ORDER BY is questionable and not recommended.

If you rename columns in both the CREATE VIEW statement and in the SELECT clause, the name of the CREATE VIEW clause "wins". As this can cause confusion, we recommend renaming columns only in the SELECT clause.

1234567
CREATE VIEW Audis (Manufacturer, Color, Power) 

     AS

SELECT Brand AS Description, Color, HP AS HorsePower
    FROM Car
WHERE Brand = 'Audi';

You can also define a view based on another view.

1234567
CREATE VIEW Volkswagen 

     AS

SELECT CarID, PlateNumber, Color, HP
    FROM Car
WHERE Brand = 'VW';

1234567
CREATE VIEW blackVolkswagen 

     AS

SELECT CarID, PlateNumber, HP
    FROM Volkswagen
WHERE Color = 'black';

Views are commonly created with a join expression to hide the complexity of the underlying query from the user or application that queries the view.

1234567
CREATE VIEW OwnerInfo 

     AS

SELECT Name, Birthday, PlateNumber, Brand, Color
    FROM Owner JOIN Car ON Owner.OwnerID = Car.Owner
WHERE Birthday IS NOT NULL;

You can discard an existing view, the data in the underlying table(s) are not affected.

1
DROP VIEW redCars;

You can INSERT new rows into a view. The new rows are not inserted into the view, but into the underlying base table!

12345
INSERT INTO redCars 
VALUES ('F77', 'red', 'HD-MT 2509', 170);

SELECT * 
    FROM Car;

You can UPDATE rows in a view as well. The rows are not changed in the view, but in the underlying base table!

123456
UPDATE redCars
SET Power = 120
WHERE Power < 120;

SELECT * 
    FROM Car; 

You can DELETE rows from a view. The rows are not deleted from the view, but from the underlying base table!

123456
DELETE
FROM redCars  
WHERE Power < 125;

SELECT * 
    FROM Car;

Such data modifications through database views are not always possible. You cannot modify data through a view if the view definition contains any of the following:

• A calculation or function in the projection list

• A subquery in the projection list

• DISTINCT

• The TOP N clause

• Aggregate expressions

• A GROUP BY clause

• UNION

Only one table may be affected by a change statement. If the view is defined on a join of tables, INSERT and DELETE are not allowed, and UPDATE only permitted if the column(s) changed come from a single underlying table.

Additionally, the change can only affect columns visible through the view. If a view is defined on five columns of a table that contains nine columns, only those five columns can be specified in a WHERE, SET, or VALUES clause. In such cases, an INSERT would only be allowed if the four columns not visible through the view allowed NULL or default values.

Finally, Only whole rows are modified for DELETE. Even if some columns in the underlying table were not visible from the view, a DELETE on the view would remove entire rows in the table, not just the columns visible from the view!

Because the underlying table(s) of a view are not always visible, it is always safer to restrict view usage to queries and disallow data changes.