SQL Intermediate
2Aggregate Functions
•
•
6Anatomy of an SQL Statement - Revisited
clauses statement
Predicate / search
condition
7GROUP BY
▪ If a GROUP BY clause is used with aggregate
function, the DBMS will apply the aggregate
function to the different groups defined in the
clause rather than all rows.
SELECT unit_code, avg(mark)
FROM enrolment
GROUP BY unit_code
ORDER BY unit_code;
SELECT avg(mark)
FROM enrolment;
8
9SELECT unit_code, avg(mark)
FROM enrolmentA
GROUP BY unit_code
ORDER BY unit_code;
Unit_code Mark Studid Year
FIT2094 80 111 2016
FIT2094 20 111 2015
FIT2004 100 111 2016
FIT2004 40 222 2015
FIT2004 40 333 2015
SELECT avg(mark)
FROM enrolmentA;
SELECT unit_code, avg(mark), count(*)
FROM enrolmentA
GROUP BY unit_code
ORDER BY unit_code;
What output is produced?
11
Unit_code Mark Studid Year
FIT2094 80 111 2016
FIT2094 20 111 2015
FIT2004 100 111 2016
FIT2004 40 222 2015
FIT2004 40 333 2015
SELECT unit_code, avg(mark), count(*)
FROM enrolmentA
GROUP BY unit_code, year
ORDER BY unit_code, year;
What output is produced?
13
HAVING clause
▪ It is used to put a condition or conditions on the
groups defined by GROUP BY clause.
SELECT unit_code, count(*)
FROM enrolment
GROUP BY unit_code
HAVING count(*) > 2;
14
Unit_code Mark Studid Year
FIT2094 80 111 2016
FIT2094 20 111 2015
FIT2004 100 111 2016
FIT2004 40 222 2015
FIT2004 40 333 2015
SELECT unit_code, avg(mark), count(*)
FROM enrolmentA
GROUP BY unit_code
HAVING count(*) > 2
ORDER BY unit_code;
SELECT unit_code, avg(mark), count(*)
FROM enrolmentA
GROUP BY unit_code
HAVING avg(mark) > 55
ORDER BY unit_code;
What output is produced?
16
SELECT unit_code, count(*)
FROM enrolment
WHERE mark IS NULL
GROUP BY unit_code
HAVING count(*) > 1;
HAVING and WHERE clauses
• The WHERE clause is applied to ALL rows in the table.
• The HAVING clause is applied to the groups defined by the GROUP BY clause.
• The order of operations performed is FROM, WHERE, GROUP BY, HAVING and then
ORDER BY.
• On the above example, the logic of the process will be:
• All rows where mark is NULL are retrieved. (due to the WHERE clause)
• The retrieved rows then are grouped into different unit_code.
• If the number of rows in a group is greater than 1, the unit_code and the total is
displayed. (due to the HAVING clause)
17
Unit_code Mark Studid Year
FIT2094 80 111 2016
FIT2094 20 111 2015
FIT2004 100 111 2016
FIT2004 40 222 2015
FIT2004 40 333 2015
SELECT unit_code, avg(mark), count(*)
FROM enrolmentA
WHERE year = 2015
GROUP BY unit_code
HAVING avg(mark) > 30
ORDER BY avg(mark) DESC;
What output is produced?
21
SELECT stu_lname, stu_fname, avg(mark)
FROM enrolment e JOIN student s
ON s.stu_nbr = e.stu_nbr
GROUP BY s.stu_nbr;
The above SQL generates error message
Why and how to fix this?
• Why? Because the grouping is based on the stu_nbr, whereas the display is
based on stu_lname and stu_fname. The two groups may not have the same
members.
• How to fix this?
• Include the stu_lname,stu_fname as part of the GROUP BY condition.
• Attributes that are used in the SELECT, HAVING and ORDER BY must be
included in the GROUP BY clause.
22
Subqueries
•
23
Types of Subqueries
Single-value
Main query
Subquery
returns APPLE
Multiple-row subquery (a list of values – many rows, one column)
APPLE
PEAR
Main query
Subquery
returns
Multiple-column subquery (many rows, many columns)
APPLE 4.99
PEAR 3.99
Main query
Subquery
returns
27
Comparison Operators for Subquery
• Operator for single value comparison.
=, <, >
• Operator for multiple rows or a list comparison.
–equality
• IN
–inequality
•ALL, ANY combined with <, >
35
Summary
▪ Aggregate Functions
–count, min, max, avg, sum
▪ GROUP BY and HAVING clauses.
▪ Subquery
–Inner vs outer query
–comparison operators (IN, ANY, ALL)
36
PART 2
PL/SQL - Triggers (FIT3171)
37
Oracle Triggers
▪ A trigger is PL/SQL code associated with a table, which performs an action
when a row in a table is inserted, updated, or deleted.
▪ Triggers are used to implement some types of data integrity constraints that
cannot be enforced at the DBMS design and implementation levels
▪ A trigger is a stored procedure/code block associated with a table
▪ Triggers specify a condition and an action to be taken whenever that
condition occurs
▪ The DBMS automatically executes the trigger when the condition is met
("fires")
▪ A Trigger can be ENABLE'd or DISABLE'd via the ALTER command
– ALTER TRIGGER trigger_name ENABLE;
38
Oracle Triggers - general form
CREATE [OR REPLACE] TRIGGER
{BEFORE | AFTER | INSTEAD OF }
{UPDATE | INSERT | DELETE}
[OF ] ON
[FOR EACH ROW]
[WHEN]
DECLARE
BEGIN
…. trigger body goes here …..
END;
39
Triggering Statement
BEFORE|AFTER INSERT|UPDATE [of colname]|DELETE ON Table
▪ The triggering statement specifies:
– the type of SQL statement that fires the trigger body.
– the possible options include DELETE, INSERT, and UPDATE. One, two,
or all three of these options can be included in the triggering statement
specification.
– the table associated with the trigger.
▪ Column List for UPDATE
– if a triggering statement specifies UPDATE, an optional list of columns
can be included in the triggering statement.
– if you include a column list, the trigger is fired on an UPDATE statement
only when one of the specified columns is updated.
– if you omit a column list, the trigger is fired when any column of the
associated table is updated
40
Trigger Body
BEGIN
.....
END;
▪ is a PL/SQL block that can include SQL and PL/SQL statements. These
statements are executed if the triggering statement is issued and the trigger
restriction (if included) evaluates to TRUE.
▪ Within a trigger body of a row trigger, the PL/SQL code and SQL statements
have access to the old and new column values of the current row affected
by the triggering statement.
▪ Two correlation names exist for every column of the table being modified:
one for the old column value and one for the new column value.
41
Correlation Names
▪ Oracle uses two correlation names in conjunction with every column
value of the current row being affected by the triggering statement.
These are denoted by:
OLD.ColumnName & NEW.ColumnName
• For DELETE, only OLD.ColumnName is meaningful
• For INSERT, only NEW.ColumnName is meaningful
• For UPDATE, both are meaningful
▪ A colon must precede the OLD and NEW qualifiers when they are used
in a trigger's body, but a colon is not allowed when using the qualifiers in
the WHEN clause.
▪ Old and new values are available in both BEFORE and AFTER row
triggers.
42
FOR EACH ROW Option
▪ The FOR EACH ROW option determines whether the trigger is a row trigger or a
statement trigger. If you specify FOR EACH ROW, the trigger fires once for each
row of the table that is affected by the triggering statement. The absence of the
FOR EACH ROW option means that the trigger fires only once for each
applicable statement, but not separately for each row affected by the statement.
CREATE OR REPLACE TRIGGER display_salary_increase
AFTER UPDATE OF empmsal ON employee
FOR EACH ROW
WHEN (new.empmsal > 1000)
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Employee: ‘|| :new.empno ||’ Old salary: ‘||
:old.empmsal || ‘ New salary: ‘|| :new.empmsal);
END;
43
Statement Level Trigger
▪ Executed once for the whole table but will have to check all rows in the
table.
▪ In many cases, it will be inefficient.
▪ No access to the correlation values :new and :old.
44
Oracle Data FK Integrity
▪ Oracle offers the options:
– UPDATE
• no action (the default - not specified)
– DELETE
• no action (the default - not specified)
• cascade
• set null
▪ Subtle difference between "no action" and "restrict"
– RESTRICT - will not allow action if child records exist, checks first
– NO ACTION - allows action and any associated triggers, then checks integrity
▪ Databases implementations vary, for example:
– Oracle no RESTRICT
– IBM DB2, SQLite implement both as above
45
Common use of triggers
▪ In the model above OWNER is the PARENT (PK end) and VEHICLE is the
CHILD (FK end)
▪ What should the database do to maintain integrity if the user:
– attempts to UPDATE the owner_no of the owner (parent)
– attempts to DELETE an owner who still has vehicles in the vehicle
table
▪ Oracle, by default, takes the safe approach
– UPDATE NO ACTION (no update of PK permitted if child records)
– DELETE NO ACTION (no delete permitted if child records)
– what if you as the developer want UPDATE CASCADE?
46
Oracle Triggers
CREATE OR REPLACE TRIGGER Owner_Upd_Cas
BEFORE UPDATE OF owner_no ON owner
FOR EACH ROW
BEGIN
UPDATE vehicle
SET owner_no = :new.owner_no
WHERE owner_no = :old.owner_no;
DBMS_OUTPUT.PUT_LINE ('Corresponding owner number in the VEHICLE
table has also been updated');
END;
/
▪ SQL Window: To CREATE triggers, include the RUN command (/) after the last
line of the file
Implement UPDATE CASCADE rule
OWNER 1 ---- has --- M VEHICLE
:new.owner_no – value of owner_no after update
:old.owner_no – value of owner_no before update
47
Common use of triggers - data integrity
▪ A trigger can be used to enforce user-defined integrity by triggering on a
preset condition, carrying out some kind of test and then if the test fails, the
trigger can raise an error (and stop the action) via a call to
raise_application_error
The syntax for this call is:
the -20000 is the error number which is reported to the user, the error
message is the error message the user will see. The error number can be
any number less than or equal to -20000.
48
Common use of triggers - data integrity - example
For example: a trigger which will ensure any unit added (ie. inserted) to the UNIT table has a unit code
which starts with 'FIT'. Test your trigger and ensure it works correctly and shows your error message.
CREATE OR REPLACE TRIGGER check_unit_code BEFORE
INSERT ON unit
FOR EACH ROW
BEGIN
IF :new.unit_code NOT LIKE 'FIT%' THEN
raise_application_error(-20000, 'Unit code must begin with FIT');
END IF;
END;
/
-- Test Harness
-- display before value
select * from unit;
insert into unit values ('ABC0001','Test Insert',6);
-- display after value
select * from unit;
-- closes transaction
rollback;
49
Mutating Table
▪ A table that is currently being modified through an INSERT, DELETE or
UPDATE statement SHOULD NOT be read from or written to because it is in
a transition state between two stable states (before and after) where data
integrity can be guaranteed.
– Such a table is called mutating table.
50
Triggers Case Study
51
• The student enrolment database contains two derived attributes
no_student (total number of students) and ave_mark (average mark) .
• The total number of students is updated when an enrolment is added or
deleted.
• The average mark is updated when an update on attribute mark is
performed.
• For audit purpose, any deletion of enrolment needs to be recorded in an
audit table. The recorded information includes the username who
performed the deletion, the date and time of the deletion, the student no
and unit code.
60
Test Harness
▪ it is not sufficient to code a trigger only, a suitable test harness must be
developed at the same time and used to ensure the trigger is working correctly.
-- display before value
select * from unit;
-- test the trigger for insertion
insert into enrolment values (11111111,’FIT2001’,2013,2,null,null);
-- display after value
select * from unit;
-- test the trigger for deletion
delete from enrolment where stu_nbr = 11111111 and unit_code = ’FIT2001’and enrol_year =
2013 and enrol_semester = 2;
-- display after value
select * from unit; select * from audit_trail;
-- closes transaction
rollback;
61
create or replace
TRIGGER DELETE_STATEMENT
AFTER DELETE ON ENROLMENT
BEGIN
INSERT INTO enrol_history VALUES (SYSDATE, USER, 'Deleted');
END;
create or replace
TRIGGER DELETE_ENROLMENT
AFTER DELETE ON ENROLMENT
FOR EACH ROW
BEGIN
INSERT INTO audit_trail VALUES
(SYSDATE, USER, :old.stu_nbr, :old.unit_code);
END;
Statement Level Trigger
Row Level Trigger
62
Oracle Triggers
▪ Use triggers where:
– a specific operation is performed, to ensure related actions are also performed
– to enforce integrity where data has been denormalised
– to maintain an audit trail
– global operations should be performed, regardless of who performs the
operation
– they do NOT duplicate the functionality built into the DBMS
– their size is reasonably small (< 50 - 60 lines of code)
▪ Do not create triggers where:
– they are recursive
– they modify or retrieve information from triggering tables















































































































































































{BEFORE | AFTER | INSTEAD OF }
{UPDATE | INSERT | DELETE}
[OF ] ON
[FOR EACH ROW]
[WHEN]
DECLARE
BEGIN
…. trigger body goes here …..
END;
39
Triggering Statement
BEFORE|AFTER INSERT|UPDATE [of colname]|DELETE ON Table
▪ The triggering statement specifies:
– the type of SQL statement that fires the trigger body.
– the possible options include DELETE, INSERT, and UPDATE. One, two,
or all three of these options can be included in the triggering statement
specification.
– the table associated with the trigger.
▪ Column List for UPDATE
– if a triggering statement specifies UPDATE, an optional list of columns
can be included in the triggering statement.
– if you include a column list, the trigger is fired on an UPDATE statement
only when one of the specified columns is updated.
– if you omit a column list, the trigger is fired when any column of the
associated table is updated
40
Trigger Body
BEGIN
.....
END;
▪ is a PL/SQL block that can include SQL and PL/SQL statements. These
statements are executed if the triggering statement is issued and the trigger
restriction (if included) evaluates to TRUE.
▪ Within a trigger body of a row trigger, the PL/SQL code and SQL statements
have access to the old and new column values of the current row affected
by the triggering statement.
▪ Two correlation names exist for every column of the table being modified:
one for the old column value and one for the new column value.
41
Correlation Names
▪ Oracle uses two correlation names in conjunction with every column
value of the current row being affected by the triggering statement.
These are denoted by:
OLD.ColumnName & NEW.ColumnName
• For DELETE, only OLD.ColumnName is meaningful
• For INSERT, only NEW.ColumnName is meaningful
• For UPDATE, both are meaningful
▪ A colon must precede the OLD and NEW qualifiers when they are used
in a trigger's body, but a colon is not allowed when using the qualifiers in
the WHEN clause.
▪ Old and new values are available in both BEFORE and AFTER row
triggers.
42
FOR EACH ROW Option
▪ The FOR EACH ROW option determines whether the trigger is a row trigger or a
statement trigger. If you specify FOR EACH ROW, the trigger fires once for each
row of the table that is affected by the triggering statement. The absence of the
FOR EACH ROW option means that the trigger fires only once for each
applicable statement, but not separately for each row affected by the statement.
CREATE OR REPLACE TRIGGER display_salary_increase
AFTER UPDATE OF empmsal ON employee
FOR EACH ROW
WHEN (new.empmsal > 1000)
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Employee: ‘|| :new.empno ||’ Old salary: ‘||
:old.empmsal || ‘ New salary: ‘|| :new.empmsal);
END;
43
Statement Level Trigger
▪ Executed once for the whole table but will have to check all rows in the
table.
▪ In many cases, it will be inefficient.
▪ No access to the correlation values :new and :old.
44
Oracle Data FK Integrity
▪ Oracle offers the options:
– UPDATE
• no action (the default - not specified)
– DELETE
• no action (the default - not specified)
• cascade
• set null
▪ Subtle difference between "no action" and "restrict"
– RESTRICT - will not allow action if child records exist, checks first
– NO ACTION - allows action and any associated triggers, then checks integrity
▪ Databases implementations vary, for example:
– Oracle no RESTRICT
– IBM DB2, SQLite implement both as above
45
Common use of triggers
▪ In the model above OWNER is the PARENT (PK end) and VEHICLE is the
CHILD (FK end)
▪ What should the database do to maintain integrity if the user:
– attempts to UPDATE the owner_no of the owner (parent)
– attempts to DELETE an owner who still has vehicles in the vehicle
table
▪ Oracle, by default, takes the safe approach
– UPDATE NO ACTION (no update of PK permitted if child records)
– DELETE NO ACTION (no delete permitted if child records)
– what if you as the developer want UPDATE CASCADE?
46
Oracle Triggers
CREATE OR REPLACE TRIGGER Owner_Upd_Cas
BEFORE UPDATE OF owner_no ON owner
FOR EACH ROW
BEGIN
UPDATE vehicle
SET owner_no = :new.owner_no
WHERE owner_no = :old.owner_no;
DBMS_OUTPUT.PUT_LINE ('Corresponding owner number in the VEHICLE
table has also been updated');
END;
/
▪ SQL Window: To CREATE triggers, include the RUN command (/) after the last
line of the file
Implement UPDATE CASCADE rule
OWNER 1 ---- has --- M VEHICLE
:new.owner_no – value of owner_no after update
:old.owner_no – value of owner_no before update
47
Common use of triggers - data integrity
▪ A trigger can be used to enforce user-defined integrity by triggering on a
preset condition, carrying out some kind of test and then if the test fails, the
trigger can raise an error (and stop the action) via a call to
raise_application_error
The syntax for this call is:
the -20000 is the error number which is reported to the user, the error
message is the error message the user will see. The error number can be
any number less than or equal to -20000.
48
Common use of triggers - data integrity - example
For example: a trigger which will ensure any unit added (ie. inserted) to the UNIT table has a unit code
which starts with 'FIT'. Test your trigger and ensure it works correctly and shows your error message.
CREATE OR REPLACE TRIGGER check_unit_code BEFORE
INSERT ON unit
FOR EACH ROW
BEGIN
IF :new.unit_code NOT LIKE 'FIT%' THEN
raise_application_error(-20000, 'Unit code must begin with FIT');
END IF;
END;
/
-- Test Harness
-- display before value
select * from unit;
insert into unit values ('ABC0001','Test Insert',6);
-- display after value
select * from unit;
-- closes transaction
rollback;
49
Mutating Table
▪ A table that is currently being modified through an INSERT, DELETE or
UPDATE statement SHOULD NOT be read from or written to because it is in
a transition state between two stable states (before and after) where data
integrity can be guaranteed.
– Such a table is called mutating table.
50
Triggers Case Study
51
• The student enrolment database contains two derived attributes
no_student (total number of students) and ave_mark (average mark) .
• The total number of students is updated when an enrolment is added or
deleted.
• The average mark is updated when an update on attribute mark is
performed.
• For audit purpose, any deletion of enrolment needs to be recorded in an
audit table. The recorded information includes the username who
performed the deletion, the date and time of the deletion, the student no
and unit code.
60
Test Harness
▪ it is not sufficient to code a trigger only, a suitable test harness must be
developed at the same time and used to ensure the trigger is working correctly.
-- display before value
select * from unit;
-- test the trigger for insertion
insert into enrolment values (11111111,’FIT2001’,2013,2,null,null);
-- display after value
select * from unit;
-- test the trigger for deletion
delete from enrolment where stu_nbr = 11111111 and unit_code = ’FIT2001’and enrol_year =
2013 and enrol_semester = 2;
-- display after value
select * from unit; select * from audit_trail;
-- closes transaction
rollback;
61
create or replace
TRIGGER DELETE_STATEMENT
AFTER DELETE ON ENROLMENT
BEGIN
INSERT INTO enrol_history VALUES (SYSDATE, USER, 'Deleted');
END;
create or replace
TRIGGER DELETE_ENROLMENT
AFTER DELETE ON ENROLMENT
FOR EACH ROW
BEGIN
INSERT INTO audit_trail VALUES
(SYSDATE, USER, :old.stu_nbr, :old.unit_code);
END;
Statement Level Trigger
Row Level Trigger
62
Oracle Triggers
▪ Use triggers where:
– a specific operation is performed, to ensure related actions are also performed
– to enforce integrity where data has been denormalised
– to maintain an audit trail
– global operations should be performed, regardless of who performs the
operation
– they do NOT duplicate the functionality built into the DBMS
– their size is reasonably small (< 50 - 60 lines of code)
▪ Do not create triggers where:
– they are recursive
– they modify or retrieve information from triggering tables

学霸联盟