16Views

Views

Outline

Inline views
Queries with WITH clause
Relational views

Inline views

An inline view is SELECT statement enclosed within ( and ) brackets and followed by an optional inline view name
A view name is a name of a temporary relational table created while SELECT statement is processed
A temporary relational table is created only for a period of time when SELECT statement, that contains inline view is processed
A name of inline view can be used within SELECT statement in any place where a name of a relational table can be used

Inline views

Sample database

CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

We start from the implementation of the following query: "find the chairs of departments that offer more than one course" The query, can be decomposed into a sequence of queries such, that the results from one query are used in the next one.

We start from implementation of a query: "find the total number of courses offered by a department" At the moment, we do not know what is a name of a department and because of that we replace a name of a department, with the ellipsis informally known as dot dot dot. An implementation of the first query is given in the middle of the present slide.

We enclose SELECT statement with left and right bracket, to make it an inline view. Next, we implement the target query: "find the chairs of departments, such that the total number of courses offered by a department is greater than one". If the first query returns the total number of courses offered by a department, then we can use its implementation in WHERE condition of the target query. First, we create an outer SELECT statement that finds chairs of departments in a relational table: DEPARTMENT.

Then, we use an inline view created earlier in the WHERE condition testing whether the contents of inline view is greater than one. Of course, we have to replace the ellipsis (dot dot dot) with a name of department taken from a relational table: DEPARTMENT. An implementation of the target query is given at the bottom of the present slide.

The query is processed in the following way. The first row from a relational table: DEPARTMENT, is evaluated against the condition in WHERE clause. To evaluate the condition in WHERE clause, we compute the results of the inline view for a given name of a department. If the condition evaluates to be true, a chair of department is appended to the results.

Then, we take the next row from a relational table: DEPARTMENT, and we again evaluate it against the condition in WHERE clause. To do so, we compute the inline view again, for the current name of a department. If the condition evaluates to be true, then a chair of department is appended to the results.

And so on and so on.

Inline views

A query find the chair people of departments, that offer more than one course can be decomposed into the following queries

Find the total number of courses offered by a department


( SELECT count(*)
  FROM COURSE
  WHERE COURSE.offered_by = ... )

Find the chair people of departments, such that the total number of courses offered by a department is greater than one


SELECT DEPARTMENT.chair
FROM DEPARTMENT
WHERE ( SELECT count(*)
        FROM COURSE
        WHERE COURSE.offered_by = DEPARTMENT.name )  > 1;

Next, we use the inline view to implement a query, that "finds the chairs of departments, that offer more than one course and the total number of courses offered by each department"

The query can be decomposed into the following subqueries. First, we implement a subquery that finds the names of departments, that offer more than one course and the total number of courses offered by each department. We can implement it as a typical query with: GROUP BY and HAVING clauses, please see SELECT statement in the middle of the slide.

To create an inline view, we enclose the subquery within left and right brackets and we append a name of the view: MORE THAN ONE. We also give a name: total, to a column in the inline view.

The column contains information about the total number of courses offered by each department.

In the next step, we use the inline view: MORE THAN ONE, as a temporary relational table, in: FROM, clause of the outer SELECT statement. The inline view is joined with relational table: DEPARTMENT, such that it would be possible to find the chair people of the departments. The join is performed over a join condition: offered by in an inline equals to a name of department.

The final SELECT statement is given at the bottom of the present slide.

Inline views

A query find the chair people of departments, that offer more than one course and the total number of courses offered by each department can be decomposed into the following queries
Find the names of departments, that offer more than one course and the total number of courses offered by each department

Find the chair people of departments, that offer more than one course and the total number of courses offered by each department


SELECT DEPARTMENT.chair, MORETHAN1.total
FROM DEPARTMENT JOIN ( SELECT offered_by, count(*) total
                       FROM COURSE
		       GROUP BY offered_by
		       HAVING count(*) > 1 ) MORETHAN1
                ON DEPARTMENT.name = MORETHAN1.offered_by;

In the next example, we implement the same query: "find the chairs of departments, that offer more than one course and the total number of courses offered by each department", in a different way.

This time, we start from the implementation of the same inline view as in the first example: "find the total number of courses offered by a department". We enclose the inline view within the brackets and we give it a name: TOTAL C The implementation of inline view: TOTAL C, is given in the middle of the present slide.

Next, we implement the second inline view: CHAIR TOTAL, that contains the names of chair people together with the total number of courses offered by the departments chaired. To do so, we use an inline view: TOTAL C, in SELECT clause of a view: CHAIR TOTAL, and we replace the ellipsis (dot dot dot) with a name of a department taken from a relational table: DEPARTMENT.

An implementation of inline view: CHAIR TOTAL, is given at the bottom of the present slide.

Inline views

A query find the chair people of departments, that offer more than one course and the total number of courses offered by each department can be decomposed into the following queries

Find the total number of courses offered by a department


( SELECT count(*)
  FROM COURSE
  WHERE COURSE.offered_by = ... ) TOTALC

Find the chair people of departments and the total number of courses offered by each department


( SELECT chair, ( SELECT count(*)
                  FROM COURSE
         	  WHERE COURSE.offered_by = DEPARTMENT.name ) TOTALC
  FROM DEPARTMENT ) CHAIRTOTAL

An inline view: CHAIR TOTAL, is used in FROM clause of the final SELECT statement, together with WHERE condition that eliminates the chair people of departments that offer only one course. Note, that WHERE condition, refers to a column: total c, in an online view: CHAIR TOTAL.

Please note a nested structure of inline views. We have an inline view: TOTAL C, at the lowest level of nesting. The view contains information about the total number of courses offered by a given department.

An inline view: TOTAL C, is later on used in SELECT clause of a higher level inline view: CHAIR TOTAL, that finds the chair people of the departments and the total number of courses offered by each department.

Finally, an inline view: CHAIR TOTAL is used in SELECT statement that retrieves the names of chair people and the departments that offer more than one course.

Inline views

Find the chair people of departments, that offer more than one course and the total number of courses offered by each department


SELECT chair, TOTALC
FROM ( SELECT chair, ( SELECT count(*)
                       FROM COURSE
                       WHERE COURSE.offered_by = DEPARTMENT.name ) TOTALC
       FROM DEPARTMENT ) CHAIRTOTAL
WHERE CHAIRTOTAL.TOTALC > 1;

It is quite important to indent the clauses of the respective SELECT statements in a way, that shows which SELECT, FROM and WHERE clauses belong to the same SELECT statement

Views

We return to a query with the operation of LEFT OUTER JOIN, implemented some time ago.

Implementation of the query uses the LEFT OUTER JOIN operation to: "find the names of all Departments together with the total number of courses offered by each Department and to include the Departments, that offer no courses".

his time, the implementation process decomposes the query into the sub queries. The query can be decomposed into the following two subqueries:

First, we "find the names of Departments and the numbers of courses offered by each Department"

And next, we "aggregate the results from the first query over the names of Departments and we count the total number of courses offered by each Department".

To implement the first query, we use the operation of: LEFT OUTER JOIN, to get the names of all Departments and then, we associate the names with the course numbers or with: NULL, when a Department offers no courses.

We use WITH clause to give a name: DEPT COURSE to an inline view that contains the names of all Departments together with the associated course numbers. The application of: WITH clause, does not need to nest the inline views inside each other. We can simply create a sequence of inline view definitions, that refer to each other using the names provided in WITH clause. The first query can be implemented as an inline view DEPT COURSE, at the beginning of WITH clause.

Please, see implementation of the first inline view at the bottom of the present slide. Please also note a comma at the end of SELECT statement. The commas separate the definitions of inline views within: WITH clause.

Queries with WITH clause

Consider a query, that finds the names of all departments together with the total number of courses offered by each department, include the departments that offer no courses
The query can be decomposed into the following two queries:
- find the names of departments and the numbers of courses offered by each department, and a query ...
- aggregate the results from the first query over the names of departments and count the total number of courses offered by each department

The first query can be implemented as a query definition DEPT_COURSE within WITH clause

WITH DEPT_COURSE AS
                   ( SELECT name, cnum
                     FROM DEPARTMENT LEFT OUTER JOIN COURSE
                                     ON DEPARTMENT.name = COURSE.offered_by ),

A query definition is an inline view, with a name preceeding SELECT statement

Queries with WITH clause

The second query can be implemented as a query definition DC_COUNT, that references a query definition DEPT_COURSE within WITH clause


WITH DEPT_COURSE AS
                   ( SELECT name, cnum
                     FROM DEPARTMENT LEFT OUTER JOIN COURSE
                                     ON DEPARTMENT.name = COURSE.offered_by ),
     DC_COUNT AS 
                   ( SELECT name, COUNT(cnum) total_courses
                     FROM DEPT_COURSE
                     GROUP BY name )

Note, that a comma (,) separates the query definitions included in WITH clause
The last query definion included in WITH clause does not have a comma following it
A keyword AS following a name of query definition separates a name of query definition from an inline view

The final query is implemented as SELECT statement, that follows the implementation of inline view DC COUNT. The final SELECT statement picks the columns: name and total courses, from an inline view DC_COUNT.

The complete implementation of a query, that "finds the names of all Departments together with the total number of courses offered by each Department and includes the Departments that offer no courses", is given in the present slide.

Note, that WITH clause allows for decomposition of the complex retrieval problems into the sequences of steps such, that an earlier create inline views can be used in the following inline views. Usually, SELECT statement with WITH clause, is a sequence of inline view definitions such, that the contents of an inline view becomes an input to the implementation of the next inline view.

Queries with WITH clause

The final query is implemented as SELECT statement appended to a query definition DC_COUNT within WITH clause


WITH DEPT_COURSE AS
                   ( SELECT name, cnum
                     FROM DEPARTMENT LEFT OUTER JOIN COURSE
                                     ON DEPARTMENT.name = COURSE.offered_by ),
     DC_COUNT AS 
                   ( SELECT name, COUNT(cnum) total_courses
                     FROM DEPT_COURSE
                     GROUP BY name )
    SELECT name
    FROM DC_COUNT);

Note, that there is no comma after the last query definition DC_COUNT

In another example, WITH clause is used to reduce the complexity of the following query: "find the chairs of Departments, that offer both 6 and 12 credit point courses".

Please note, that a simplistic solution, that applies a condition: credits = 6 and credits = 12, is obviously incorrect, because a value in a single slot of a relational table cannot be in the same moment equal to 6 and to 12.

We use the set algebra, to find an intersection of a set of names of Departments, that offer 6 credit point courses and a set of names of Departments, that offer 12 credit point courses. Such plan contributes to the following decomposition of the queries into a sequence of inline views.

First, we create an inline view to "find the names of Departments that offer 6 credit point courses".

Next, we create an inline view to "find the names of Departments that offer 12 credit point courses".

Next, we create an inline view to "find the names of Departments included in both views created earlier".

Next, we create an inline view to "find the chairs of Departments included in the previous view".

The first inline view is implemented as: COURSE 6 C R, given at the bottom of the present slide.

An inline view: COURSE 6 C R, contains the names of Departments that offer 6 credit point courses.

Queries with WITH clause

In another example, WITH clause is used to reduce the complexity of implementation of a query, that finds the chair people of departments, that offer both 6 and 12 credit point courses
The query is decomposed into the following subqueries:
- Find the names of departments, that offer 6 credit point courses
- Find the names of departments, that offer 12 credit point courses
- Find the names of departments included in both results from the subqueries above
- Find the chair people of departments included in the previous subquery

The first subquery is implemented as a query definition COURSE6CR within WITH clause


WITH COURSE6CR AS
                 ( SELECT offered_by 
                   FROM COURSE
                   WHERE credits = 6 ),

Queries with WITH clause

A subquery, that finds the names of departments, that offer 12 credit point courses is implemented as a query definition COURSE12CR and it is appended to WITH clause


WITH COURSE6CR AS
                 ( SELECT offered_by 
                   FROM COURSE
                   WHERE credits = 6 ),
     COURSE12CR AS
                 ( SELECT offered_by
                   FROM COURSE
                   WHERE credits = 12 ),

Queries with WITH clause

A subquery, that finds the names of departments that offer both 6 and 12 credit point courses is implemented as a query definition COURSE6_12CR and it is appended to WITH clause


WITH COURSE6CR AS
                 ( SELECT offered_by 
                   FROM COURSE
                   WHERE credits = 6 ),
     COURSE12CR AS
                 ( SELECT offered_by
                   FROM COURSE
                   WHERE credits = 12 ),
     COURSE6_12CR AS
                 ( SELECT COURSE6CR.offered_by
                   FROM COURSE6CR JOIN COURSE12CR
                                  ON COURSE6CR.offered_by = COURSE12CR.offered_by ),

Queries with WITH clause

A subquery, that finds the chair people of departments that offer both 6 and 12 credit point courses is implemented as a query definition CHAIR and it is appended to WITH clause


WITH COURSE6CR AS
                 ( SELECT offered_by 
                   FROM COURSE
                   WHERE credits = 6 ),
     COURSE12CR AS
                 ( SELECT offered_by
                   FROM COURSE
                   WHERE credits = 12 ),
     COURSE6_12CR AS
                 ( SELECT COURSE6CR.offered_by
                   FROM COURSE6CR JOIN COURSE12CR
                                  ON COURSE6CR.offered_by = COURSE12CR.offered_by ),
     CHAIR AS
                 ( SELECT DEPARTMENT.chair
                   FROM COURSE6_12CR JOIN DEPARTMENT
                                     ON COURSE6_12CR.offered_by = DEPARTMENT.name )

Finally a query: "find the chairs of Departments that offer both 6 and 12 credit point courses", is implemented as SELECT statement appended to a list of implementations of inline views.

Practically, the last inline view: CHAIR, contains all information we are looking for. It is why, the final SELECT statement, retrieves all rows and all columns from an inline view: CHAIR.

Please see the final SELECT statement, at the bottom of the present slide.

WITH clause allows for a simple decomposition of a complex SELECT statement, into a sequence of named inline views, where a name of an inline view created earlier can be used in an inline view created later. The previous application of nested inline views also allows for a decomposition of a complex query but, the nestings of the views usually create the complex SELECT statements. Naming and referencing of the earlier created inline views in: FROM clauses, eliminates the necessity for the nestings of inline views.

Queries with WITH clause

Finally a query, that finds the chair people of departments that offer both 6 and 12 credit point courses is implemented as SELECT statement and it is appended to WITH clause

WITH COURSE6CR AS
                 ( SELECT offered_by 
                   FROM COURSE
                   WHERE credits = 6 ),
     COURSE12CR AS
                 ( SELECT offered_by
                   FROM COURSE
                   WHERE credits = 12 ),
     COURSE6_12CR AS
                 ( SELECT COURSE6CR.offered_by
                   FROM COURSE6CR JOIN COURSE12CR
                                  ON COURSE6CR.offered_by = COURSE12CR.offered_by ),
     CHAIR AS
                 ( SELECT DEPARTMENT.chair
                   FROM COURSE6_12CR JOIN DEPARTMENT
                                     ON COURSE6_12CR.offered_by = DEPARTMENT.name )
SELECT *
FROM CHAIR;

Views

Outline

Inline views
Queries with WITH clause
Relational views

It is important to note, that a definition of inline view is valid only for a period of time when SELECT statement, that contains such definition, is processed. When processing of SELECT statement with the inline views is completed the definitions of inline views are removed from the system. It may happen, that an inline view can be used to simplify implementation of more than one SELECT statement. Then, we need something like a library of the inline view definitions, that can be used in many different SELECT statements.

A definition of an inline view that is permanently stored by a database system is called: "relational view". A relational view can be also considered as a virtual relational table, or derived relational table, that occupies no persistent storage and it is recomputed from the very beginning, each time it is used in SELECT statement. The definitions of relational views are stored in a private database of a database system, called: data dictionary or data repository. We shall return to a concept of data dictionary later in this subject.

A relational view is stored in a data dictionary as a pair: name of a view, as the first component of the pair and: SELECT statement, that defines the structure and contents of the view, as the second component of a pair. An example given in the slide creates a definition of relational view, that joins few relational tables and stores such definition in a data dictionary under a name of a relational view: V.

It is very important to remember, that when a relational is view created, the SELECT statement, that defines the view is not processed. It means, that the materialization of relational view is not permanently stored in persistent storage. Only a definition of relational view, is permanently stored in persistent storage.

There exists one more category of views called: "materialized view". "Materialized views" are the outcomes of processing of SELECT statement permanently stored in persistent storage. We shall not talk about "materialized views" in this subject.

Relational views

A relational view is a virtual relational table (derived relational table), that occupies no persistent storage and it is computed from very beginning every time it is used in SELECT statement
A relational view is stored by a database management system as a pair (name of a view, SELECT statement that defines the structure and contents of the view)

RVIEW

Assume, that we created a relational view: V, in a way presented in the previous slide and we listed it in the red oval in the middle of the present slide. Assume, that we implemented SELECT statement with a name of the relational view: V, in FROM clause. How SELECT statement with a name of a relational view listed in FROM clause, is processed by a database system ?

A database system, or more precisely, a query processor retrieves a definition of a relational view: V, from a data dictionary and replaces a name: V, with SELECT statement that defines the view. So, at the end we get SELECT statement with an inline view.

Each time a name of relational view is used in a SELECT statement, its definition replaces the name of view and it becomes an inline view. It means, that if a relational view is used in many SELECT statements then each time it is used its definition is recomputed by a database system from very beginning many times. If a definition of a relational view replaces a name of the view in SELECT statement then a relational view can be treated as a virtual relational table that can be referenced in any SELECT, statement.

SELECT statement with a view replaced by its definition, is listed at the bottom of the present slide.

Relational views

Each time a name of a relational view is used in SELECT statement, its definition replaces the name of a view and it becomes an inline view

RVIEW

For example, let us create a relational view that contains the names of all Departments together with the total number of courses offered by each Department.

A relational view can be created with: CREATE VIEW statement. CREATE VIEW statement provides a name of a relational view and the names of columns in a virtual relational table, that represents a relational view. It is called: a schema of a relational view. A keyword: AS, separates a schema of a relational view from SELECT statements that defines the virtual contents of a relational view.

In our example, a definition of a relational view: VDEPT, is a SELECT statement, that outer joins the relational tables: DEPARTMENT and COURSE, and then groups the results of the join by the values in a column: name, from: DEPARTMENT table, and counts the total values in a column: c num, in each group.

A definition of a relational view CDEPT is given in the present slide.

Relational views

For example, create a relational view, that contains the names of all departments together with the total number of courses offered by each department


CREATE VIEW VDEPT( name, total_courses ) AS
( SELECT name, count(COURSE.cnum)
  FROM DEPARTMENT LEFT OUTER JOIN COURSE
                  ON DEPARTMENT.name = COURSE.offered_by
  GROUP BY DEPARTMENT.name );

Note, that LEFT OUTER JOIN operation is used to join the relational tables DEPARTMENT and COURSE to inlude all names of departments no matter if a department offers a course or not
Also note, that a column cnum in a relational table COURSE is used for counting the values in each group, do you remember why it is so ?

Relational views

Then, a view VDEPT can be used to implement a query, that finds the names of departments that offer more than 1 course

The same query when implemented with GROUP BY and HAVING is given below


SELECT name, count(cnum)
FROM DEPARTMENT LEFT OUTER JOIN COURSE
                ON DEPARTMENT.name = COURSE.offered_by
GROUP BY name
HAVING count(cnum) > 1;

Application of a relational view VDEPT splits the complexity of implementation over two simpler SELECT statements

Relational views can be used to reduce the complexity of SELECT statements, in the same way as inline views within SELECT statements and inline views attached to WITH clause.

For example, a query: "find the chairs of Departments that offer, both 6 and 12 credit point courses”, can be decomposed into the following sequence of definitions of relational views and the final SELECT statement. A relational view: V6, contains the names of Departments that offer 6 credit point courses.

Next, a relational view V12, contains the names of Departments that offer 12 credit point courses.

Next, a relational view: VNAME, contains the names of Departments included in both relational views V6 and V12

Next, a relational view: VCHAIR, contains the chair people of Departments included in relational view: VNAME.

Then, a simple SELECT statement, can be applied to select the contents of a relational view: VCHAIR, to get the final results.

Implementation of a relational view V6 is listed at the bottom of the present slide.

Relational views

A relational view can be used to reduce the complexity of SELECT statements
For example, a query, that finds the chair people of departments that offer both 6 and 12 credit point courses can be decomposed into the following four queries:
V6: Find the names of departments, that offer 6 credit point courses
V12: Find the names of departments, that offer 12 credit point courses
VNAME: Find the names of departments included in both V6 and V12
VCHAIR: Find the chair people of departments included in VNAME

Relational views

The views are implemented in the following way

V6: Find the names of departments that offer 6 credit point courses

CREATE VIEW V6( name ) AS
( SELECT offered_by
  FROM COURSE
  WHERE credits = 6 );

V12: Find the names of departments that offer 12 credit point courses

CREATE VIEW V12( name ) AS
( SELECT offered_by
  FROM COURSE
  WHERE credits = 12 );

VNAME: Find the names of departments included in both V6 and V12

CREATE VIEW VNAME( name ) AS
( SELECT V6.name
  FROM V6 JOIN V12
       ON V6.name = V12.name );

Relational views

VCHAIR: Find the chairs of departments included in VNAME

CREATE VIEW VCHAIR( chair ) AS
( SELECT DEPARTMENT.chair
  FROM VNAME JOIN DEPARTMENT
	     ON VNAME.name = DEPARTMENT.NAME );

The final query is the following

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapters 5, 7, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 7.4.1 Creating a View Pearson Education Ltd, 2015
D. Darmawikarta, SQL for MySQL A Beginner’s Tutorial, Chapter 7 Subqueries, Chapter 9 Views, Brainy Software Inc. First Edition: June 2014
How to ... ? Cookbook, How to implement relational views? (Part 2) Recipe 7.3 How to implement relational views ?