Advanced DDL
and DML
Statements
CSIT882: Data Management Systems
Outline
• TRUNCATE TABLE statement
• CREATE TABLE statement with subquery
• INSERT statement with subquery
• DELETE statement with subquery
• UPDATE statement with subquery
2
TRUNCATE TABLE statement
• TRUNCATE TABLE statement permamently 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 futre 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 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
3
TRUNCATE TABLE statement
• For example, delete all courses from a relational table COURSE
TRUNCATE TABLE COURSE;
4
Outline
• TRUNCATE TABLE statement
• CREATE TABLE statement with subquery
• INSERT statement with subquery
• DELETE statement with subquery
• UPDATE statement with subquery
5
CREATE TABLE statement with subquery
• CREATE TABLE statement with subquery creates a new relational
table and saves in the table the results of the processing of the subquery
• 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 a
subquery does not enforce any consistency constraints except
NULL/NOT NULL constraint
• For example, "create a table that contains the names of departments
together with the total number of courses offered by each department
and insert correct data into the table”
CREATE TABLE DCNT AS
( SELECT name, COUNT(cnum) TOTC
FROM DEPARTMENT LEFT OUTER JOIN COURSE
ON DEPARTMENT.name = COURSE.offered_by
GROUP BY name );
6
CREATE TABLE statement with subquery
• The consistency constraints can be enforced with ALTER TABLE
statement
ALTER TABLE DCNT ADD CONSTRAINT DCNT_pkey PRIMARY KEY(name);
ALTER TABLE DCNT ADD CONSTRAINT DCNT_fkey FOREIGN KEY (name)
REFERENCES DEPARTMENT(name);
7
Outline
• TRUNCATE TABLE statement
• CREATE TABLE statement with subquery
• INSERT statement with subquery
• DELETE statement with subquery
• UPDATE statement with subquery
8
INSERT statement with subquery
• INSERT statement with subquery inserts into a relational table
the rows retrieved by a given subquery
• INSERT statement with subquery is Data Manipulation Language
(DML) statement and because of that its actions can be reversed
with ROLLBACK statement
• For example, to enforce the consistency constraints from very
beginning we create a relational table DCNT first
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) );
9
INSERT statement with subquery
• 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 );
10
Outline
• TRUNCATE TABLE statement
• CREATE TABLE statement with subquery
• INSERT statement with subquery
• DELETE statement with subquery
• UPDATE statement with subquery
11
DELETE statement with subquery
• DELETE statement with subquery deletes from a relational table
all rows that satisfy WHERE condition
• WHERE condition includes a subquery
12
Outline
• TRUNCATE TABLE statement
• CREATE TABLE statement with subquery
• INSERT statement with subquery
• DELETE statement with subquery
• UPDATE statement with subquery
13
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
14
UPDATE statement with subquery
• UPDATE statement with subquery can use a subquery in SET
clause or in both WHERE and SET clauses
15
UPDATE statement with subquery
• For example, "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, "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 );
16
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 6.3.5 Subqueries, Chapter 6.3.8
EXISTS and NOT EXISTS, 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 queries in SQL ? (Part 3) Recipe
7.2 How to perform advanced data manipulations ?
17