Table of Contents Link to heading

• Database views
• VIEW Syntax
• Examples
• Complex query
  • Not using a VIEW
  • Using a VIEW
• Aggregate on Aggregate
  • Incorrect solution
  • Correct solution, using a view
• Vertical inheritance
• Horizontal inheritance
• Rename columns
• Important Notes
• Updatable Views
• Notes on updatable views

Database views Link to heading

Views are very useful for complex queries Queries with lots of joins Queries with complex conditions (e.g. aggregates) Especially if the queries are used frequently

Views are necessary to express certain queries queries that combine and nest aggregate operators queries that make sophisticated use on the UNION operator

Views are useful for security/access control: views can be defined to show parts of a table for specific users Views can replace table access (depending on how they are defined)

Views are like virtual tables It is created by a SELECT query It does not store data Materialised/updatable views can be created that store data in the original base tables

When the view is accessed, the expression is evaluated in real time and the result is presented No different to writing and running a query

VIEW Syntax Link to heading

CREATE VIEW someName
AS
<SELECT QUERY>;

CREATE VIEW someName
AS
<SELECT QUERY>;

Examples Link to heading

CREATE VIEW SimpsonsFamily
AS
SELECT haracterID, characterName, characterRole
FROM Characters
WHERE (characterName LIKE '%Simpson');

Views can reference other views,

CREATE VIEW  AdminEmployee  (firstName, surname, salary) AS
SELECT firstName, surname, salary
FROM Employees
WHERE dept = 'Administration'

CREATE VIEW JuniorEmployee AS
SELECT * FROM AdminEmployee
WHERE salary < 50

Complex query Link to heading

Given the following example database.

📝 Find the department with the highest salary expenditure.

Not using a VIEW Link to heading

SELECT dept
FROM Employees
GROUP BY dept
HAVING SUM(salary) = (
SELECT MAX(totalSalary) FROM        (   SELECT SUM(salary) AS totalSalary
    FROM Employees
    GROUP BY dept
) AS calc
)

Using a VIEW Link to heading

CREATE VIEW SalaryBudget (dept,totalSalary) AS
SELECT dept, SUM(salary)
FROM Employees
GROUP BY dept

SELECT dept
FROM SalaryBudget
WHERE salaryTotal = (
    SELECT MAX (salaryTotal)
    FROM SalaryBudget
)

Aggregate on Aggregate Link to heading

📝 Find the average number of staff per department.

Incorrect solution Link to heading

can’t have cascade of aggregate operators.

SELECT AVG(COUNT(*))
FROM Employees
GROUP BY dept

Cannot perform an aggregate function
on an expression containing an aggregate
or a subquery.

Correct solution, using a view Link to heading

CREATE VIEW NoOfEmployees AS
    SELECT dept, COUNT(*) AS employeeCount
    FROM Employees GROUP BY dept

SELECT AVG(employeeCount) FROM NoOfEmployees

Vertical inheritance Link to heading

For example:

When selecting records, use the VIEW (not the table) When modifying records, use the tables (not the view)

Horizontal inheritance Link to heading

Rename columns Link to heading

Views can be used to rename columns and provide only specific data.

The below view returns only part (2 columns) from of the Simpsons table and only members of the Simpsons family.

CREATE VIEW SimpsonsView

AS

SELECT characterID AS charID,
characterName AS charName
FROM Characters
WHERE characterName LIKE '%Simpson'

or

CREATE VIEW SimpsonsView (charID, charName)

AS

SELECT characterID, characterName
FROM Characters
WHERE characterName LIKE '%Simpson'

Important Notes Link to heading

Every column in the SELECT query that defines a view must have a name Especially true of aggregate values, calculations and functions

Views cannot make use of the ORDER BY clause If you want to re-order the results, you must do it when you call the view

Views in MS-SQL can be deleted even if they are used by other views Calling a view that uses a view that has been dropped will throw a runtime error PostgreSQL wins here :’(

Views are defined using the Data Definition Language (DDL)

CREATE VIEW xx AS <Query>

Views can also be modified using the same DDL

ALTER VIEW xx AS <Query>
DROP VIEW xx

Updatable Views Link to heading

Updateable Views allow the use of Data Manipulation Language (DML) in addition to selecting records.

• INSERT, UPDATE and DELETE
• This will depend on the view definition (what tables the view contains!)
  • A Complex view may only allow UPDATE/DELETE in addition to SELECT
  • A Simple view that reflects some columns in one table may allow UPDATE/DELETE/INSERT
  • This is also dependent on the DBMS

Notes on updatable views Link to heading

A view is an Updateable View only if

1. It represents data from one table only (no JOINs)
2. It does not remove duplicates (no DISTINCT)
3. Attributes not present in the view allow NULL or have a DEFAULT value specified
4. It does not contain Aggregate or GROUP BY clauses
5. Subqueries in the view must not refer to the same table used by the view (but can refer to other tables)

Many of the rules applying to Updatable Views make sense:

• If it contains aggregates, or a DISTINCT or GROUP BY clause.
• It doesn’t make sense to insert/update a value that is calculated across multiple rows.
• If the View affects multiple base tables, inserting a record may violate FK constraints or create NULL values for attributes not present in the view.