17AdvancedDDLandDML

Advanced DDL and DML statements

Outline

TRUNCATE TABLE statement
CREATE TABLE statement with subquery
INSERT statement with subquery
DELETE statement with subquery
UPDATE statement with subquery

TRUNCATE TABLE statement permanently deletes all rows from a relational table, such that the deletions cannot be rolled back.

The deletions cannot be rolled back, because TRUNCATE TABLE statement is a Data Definition Language, D D L statement.

TRUNCATE TABLE statement does not have WHERE clause and because of that, it can only delete ALL rows from a relational table.

TRUNCATE TABLE statement deletes all rows much faster than DELETE statement, because database system does not need to save rollback information.

TRUNCATE TABLE statement returns unused persistent storage, to a pool of free persistent storage, while DELETE statement does not do that.

TRUNCATE TABLE statement does not delete a definition of a relational table from a data dictionary.

RUNCATE TABLE statement is recommended, when we would like to delete the large amounts of data and we know well what we are doing, because the only way to restore data is from a backup, if we have one ?

TRUNCATE TABLE statement

TRUNCATE TABLE statement permamenetly deletes all rows from a relational table
TRUNCATE TABLE statement is a Data Definition Language (DDL) statement and because of that it cannot be reversed (in the future we shall learn how to reverse DML statements)
TRUNCATE TABLE statement does not have WHERE clause and because of that it can only delete ALL rows from a relational table
TRUNCATE TABLE statement deletes all rows much faster than DELETE statement because a database system does not need to save rollback information
TRUNCATE TABLE statement returns unused persistent storage to a pool of free persistent storage while DELETE statement does not do that

TRUNCATE TABLE statement

For example, delete all courses from a relational table COURSE

Advanced DDL and DML statements

CREATE TABLE statement with subquery

CREATE TABLE statement with subquery creates a relational table and saves in the table the results of a given SELECT statement
CREATE TABLE statement with subquery is Data Definition Language (DDL) statement and its actions cannot be reversed
A relational table created by CREATE TABLE statement with subquery does not have any consistency constraints enforced except NULL/NOT NULL constraint
For example, a table, that contains the names of departments together with the total number of courses offered by each department can be created and correct data can be inserted into it in the following way

CREATE TABLE statement with subquery

The names of columns in a new relational table are taken from the names of columns in SELECT clause or from alias names attached to the columns names or functions
A relational table DCNT created and loaded with data has two columns: name and totc

The consistency constraints can be enforced with ALTER TABLE statements


ALTER TABLE DCNT ADD CONSTRAINT DCNT_pkey PRIMARY KEY(name);
ALTER TABLE DCNT ADD CONSTRAINT DCNT_fkey FOREIGN KEY (name)
                                          REFERENCES DEPARTMENT(name);

Advanced DDL and DML statements

Outline

TRUNCATE TABLE statement
CREATE TABLE statement with subquery
INSERT statement with subquery
DELETE statement with subquery
UPDATE statement with subquery

INSERT statement with subquery

INSERT statement with subquery inserts into a relational table the rows retrived by a given SELECT statement
INSERT statement with subquery is Data Manipluation Language (DML) statement an because of that its actions can be reversed with ROLLBACK statement

For example, to enforce the consistency constraints first, we create a relational table DCNT with ordinary CREATE TABLE statement


CREATE TABLE DCNT(
 name          VARCHAR(50)  NOT NULL,
 total_courses DECIMAL(2)    NOT NULL,
  CONSTRAINT DCNT_pkey PRIMARY KEY(name),
  CONSTRAINT DCNT_fkey FOREIGN KEY (name) REFERENCES DEPARTMENT(name) );

When a relational table: DCNT, is created, we use INSERT statement with subquery, to load data into a relational table: DCNT.

A sample INSERT statement with subquery is given in the present slide.

Note, that INSERT statement with subquery does not have a keyword: VALUES, separating a table name with an optional schema, from a list of values in INSERT statement, that insert a single row.

A subquery is implemented as an inline view without a name of the view.

An inline view, is a SELECT statement with GROUP BY clause and aggregation function in SELECT clause.

It is important, that the total number and the types of the columns, in an already created relational table: DCNT, must match the number and type of columns in an inline view.

INSERT statement with subquery, is a data manipulation statement and its results can be reversed with ROLLBACK statement.

INSERT statement with subquery

And then we use INSERT statement with subquery to load data into a relational table DCNT


INSERT INTO DCNT
( SELECT name, COUNT(cnum)
  FROM DEPARTMENT LEFT OUTER JOIN COURSE
                  ON DEPARTMENT.name = COURSE.offered_by
  GROUP BY name );

Note, that in this case there is no need for an alias name following COUNT(cnum)

Advanced DDL and DML statements

Outline

TRUNCATE TABLE statement
CREATE TABLE statement with subquery
INSERT statement with subquery
DELETE statement with subquery
UPDATE statement with subquery

DELETE statement with subquery operates in the same way, as DELETE statement with a simple condition in WHERE clause.

It deletes from a relational table all rows, that satisfy a condition in WHERE clause.

The only difference, is such that a condition in WHERE clause includes a number of subqueries.

A diagram given in the present slide explains a situation where we would like to delete from a relational table: TABLE 1, all rows, that have a value in column: COLUMN 1, included in a set of values from a column: COLUMN 2, that satisfy a condition: Condition 2, in a relational table: TABLE 2.

DELETE statement is interpreted in the following way.

If a subquery does not contain the references to a relational table: TABLE 1, then it is processed first and the results are saved as the results of processing of an inline view.

Next, a relational table: TABLE 1, is read row by row and a condition in WHERE clause is evaluated for each row.

If a value in a column: COLUMN 2, in the current row is included in the results of processing of inline view, then the condition evaluates to true.

If evaluation of a condition in WHERE clause returns true, then the current row is deleted from a relational table TABLE 1.

If a subquery contains the references to a relational table: TABLE 1, then the table is read row by row and like with the correlated nested queries, the references are replaced with the values from the respective rows taken from a TABLE 1.

Then, a subquery is processed for the current row and the results are stored in an inline view.

Next, a condition in WHERE clause is evaluated.

If evaluation of a condition in WHERE clause returns true, then the current row is deleted from a relational table TABLE 1.

Next, the same procedure is repeated for each row in a relational table: TABLE 1.

DELETE statement with subquery is a data manipulation language statement and its outcomes can be reversed with ROLLBACK statement.

DELETE statement with subquery

DELETE statement with subquery deletes from a relational table all rows that satisfy WHERE condition
WHERE condition includes a subquery

Assume, that we would like to delete all courses offered by a department chaired by Peter.

At the moment we do not know what is a name of department chaired by Peter. First, we create SELECT statement that finds a name of department chaired by Peter. When ready we close SELECT statement in left and right brackets ( ) creartin an inline view,

Next we create DELETE statement, that deletes all rows from a relational table COURSE where a value of attribute offered_by is equal to one fo the values returned by processing of online view created earlier.

A sample implementation of complete DELETE statement is given at the top of the present slide.

In the next example, we would like delete from a relational table DEPARTMENt information about all departments that offer no courses.

We shall implement such statement using negated EXISTS quantifier in WHERE clause. To do so we have to reformulate the original task in the following way: delete from DEPARTMENT table information about all departments such that does not exists a course offered by department.

A sample implementation of DELETE statement is given at the bottom of the present slide.

Note, a reference to DEPARTMENT.name in WHERE condition. It is the same reference to a relational table DEPARTMENT as in a correlated nested query.

Processing of DELETE statement is performed in the following way. A row is taken from DEPARTMENT table. A name of department take from the row is inserted into WHERE condition of inner SELECT statement in a place of DEPARTMENT.NAME. An inner SELECT statement is processed and if the result is empty then NOT EXISTS condition evaluates to true and a row taken from DEPARTMENT table is deleted. Otherwise a row is not deleted. Such procedure is repeated fro each row in DEPARTMENT table.

DELETE statement with subquery

For example, we would like to delete all courses offered by a department chaired by Peter

DELETE FROM COURSE
WHERE offered_by = ( SELECT name
                     FROM DEPARTMENT
                     WHERE chair = 'Peter' );

For example, we would like to delete all departments that offer no courses


DELETE FROM DEPARTMENT
WHERE NOT EXISTS ( SELECT 'whatever'
                   FROM COURSE
                   WHERE COURSE.offered_by = DEPARTMENT.name );

Note, a reference to DEPARTMENT.name in WHERE condition
It is the same reference to a relational table DEPARTMENT as in a correleated nested query

Advanced DDL and DML statements

Outline

TRUNCATE TABLE statement
CREATE TABLE statement with subquery
INSERT statement with subquery
DELETE statement with subquery
UPDATE statement with subquery

UPDATE statement with subquery, updates in a relational table all rows, that satisfy a condition given in WHERE clause.

The new values are determined in an expression in SET clause.

Both a condition in WHERE clause and an expression in SET clause can use subqueries.

The present slide explains how UPDATE statement with subquery in WHERE clause is processed. UPDATE statement with subquery in SET clause is explained in the next slide.

UPDATE statement changes the values in a column: Column 2, in a relational table: TABLE 1, depending on the evaluation of a condition: Condition 1 in the results of subquery. If a subquery does not contain any references to: TABLE 1, then it is processed one time and WHERE condition in UPDATE statement is changed into: COLUMN 1 IN the results of subquery. Next, the rows from a relational table: TABLE 1, are read one by one and all rows where, a condition in WHERE clause evaluates to be true are updated.

If a subquery contains a reference to a column in a relational table: TABLE 1, then the subquery is processed for each row picked from: TABLE 1, with the reference replaced with a value taken from the current row in: TABLE 1, A condition in WHERE clause is evaluated for each row from a relational table: TABLE 1. If a condition in WHERE clause evaluates to be true, then a row is updated. Such procedure is repeated for all rows in a relational table: TABLE 1.

UPDATE statement with subquery

UPDATE statement with subquery updates in a relational table all rows that satisfy WHERE condition with the values determined in SET clause
UPDATE statement with subquery can use a subquery in WHERE clause

UPDATE

It is possible to use a subquery in SET clause of UPDATE statement, under a condition, that subquery returns at most one value. The present slide visualises the processing of UPDATE statement, with a subquery in SET clause.

If a subquery does not contain any references to a column in updated table: TABLE 1, then the subquery is processed and a result of processing is used to update a value in a column: COLUMN 2 in a relational table: TABLE 1, in all rows, that evaluate a condition in WHERE clause to be true. If WHERE clause is not given, then all rows in a relational table: TABLE 1, are updated. It is why all rows in a column: COLUMN 2, in a relational table: TABLE 1, are updated to a red colour in the present slide.

If a subquery contains a reference to a column in a relational table: TABLE 1, then the subquery is processed for each row from a relational table: TABLE 1, and a result of processing is used, to change a value in a column: COLUMN 2, in the current row.

Such procedure and processing of a subquery is repeated for all rows in a relational table: TABLE 1.

UPDATE statement with subquery

UPDATE statement with subquery can use a subquery in SET clause or in both WHERE and SET clauses

UPDATE

In the first example, we implement UPDATE statement, that increases the total number of staff members by 5, in all Departments that offer more than 20 courses. To implement UPDATE statement, we first implement a subquery, that finds the names of Departments, that offer more than 20 courses.

Such subquery can be implemented as a typical SELECT statement, with GROUP BY and HAVING clauses. A relational table: COURSE, is grouped over the values in a column: offered by, and all groups that have less or equal 20 rows are removed. SELECT clause of the subquery picks the values in a column: offered by, from all groups that have more than 20 rows. Finally, UPDATE statement, picks the rows from a relational table: DEPARTMENT, and it increases total staff number by 5 in all rows where a condition: name IN the results of subquery, evaluates to be true.

In the second example, we first add to a relational table: DEPARTMENT, a column, that supposed to contain the total number of courses offered by each department. Next, we implement UPDATE statement, that sets the correct values in the column. We start from the implementation of: ALTER TABLE statement, that adds a column: total courses, to a relational table DEPARTMENT.

The new column has: NULL consistency constraint, because after addition the column does not contain any values. It is possible to enforce: NOT NULL consistency constraint and provide a default value for a column: total courses. Implementation of UPDATE statement, that sets the correct values in a column: total courses, requires a subquery in SET clause.

There is no need for WHERE clause in UPDATE statement because all rows must be updated. The subquery in SET clause implemented as SELECT statement, finds the total number of courses offered by a given Department. To provide information about the current Department, a condition in WHERE clause contains a reference to a column: name, in a relational table: DEPARTMENT.

UPDATE statement is processed in the following way. A row is picked from a relational table: DEPARTMENT. A value in a column: name, from the row replaces a reference: DEPARTMENT dot name, in WHERE condition of the subquery. The subquery is processed and it returns a single number as the result of: COUNT title, function.

Note, that if a Department with a given name offers no courses, then the subquery returns zero. Then, a value returned by a subquery becomes a value in a column: total courses, in the row. The same procedure is repeated for all rows remaining in a relational table: DEPARTMENT.

UPDATE statement with subquery

For example, we would like to increase the total number of staff members by 5 in all departments that offer more than 20 courses

UPDATE DEPARTMENT
SET total_staff_number = total_staff_number + 5
WHERE name IN ( SELECT offered_by
                FROM COURSE
                GROUP BY offered_by
                HAVING COUNT(cnum) > 20 );

For example, we would like to add to table DEPARTMENT a column that contains the total number of courses offered by each department and insert the correct values into the column

	      
ALTER TABLE DEPARTMENT ADD ( total_courses DECIMAL(2) );

UPDATE DEPARTMENT
SET total_courses = ( SELECT COUNT(title)
                      FROM COURSE
                      WHERE COURSE.offered_by = DEPARTMENT.name );

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
How to ... ? Cookbook, Recipe 7.2 How to perform advanced data manipulations ?