CHAPTER 8:
ADVANCED SQL
ICE BREAKER
4 PICS 1 WORD
INSTRUCTIONS:
EACH ROUND WE WILL PRESENT FOUR PICTURES THAT MAY OR MAY
NOT BE RELATED TO AN ADVANCED SQL CONCEPT.
YOU WILL HAVE 30 SECONDS TO GUESS THE ANSWER.
USE THE RAISE HAND FEATURE IN THE GOOGLE MEET TO ANSWER.
YOU CAN ONLY PARTICIPATE ONCE
IF YOU GUESS THE CORRECT ANSWER, YOU WILL RECEIVE 5 POINTS,
WHICH WILL BE ADDED TO YOUR SCORE DURING OUR QUIZ.
J
ANSWER : JOIN
R G
ANSWER : TRIGGER
S R N
ANSWER : CONSTRAINT
OBJECTIVES
The relational set operators UNION, UNION ALL, INTERSECT, and MINUS
How to use the advanced SQL JOIN operator syntax
The different types of subqueries and correlated queries
How to use SQL functions to manipulate dates, strings, and other data
How to create and use updatable views
How to create and use triggers and stored procedures
How to create embedded SQL
To make UNION,
INTERSECT, and MINUS
work properly, it
should be
UNION-COMPATIBLE
CHAR(15) and VARCHAR(45) are not alike in sizes but they are similar in
storing string data.
UNION
The UNION statement combines rows
from two or more queries without
including duplicate rows
The syntax of the UNION statement is:
query UNION query
UNION VS UNION ALL
does NOT
includes
include
duplicate
duplicate
values
values
INTERSECT
Used to combine rows from two queries,
returning only the rows that appear in
both sets
The syntax of the INTERSECT statement is:
query INTERSECT query
MINUS
Used to return rows from the first result
set that do not appear in the second
result set.
The syntax of the MINUS statement is:
query MINUS query
LET’S REVIEW
JOIN is an SQL clause used to query and
access data from multiple tables, based on
logical relationships between those tables.
JOINS INCLUDES:
Natural Join Cross Join
Full / Full
Inner Join
Outer Join
Left / Left Right / Right
Outer Join Outer Join
CROSS
JOIN
Cross Join is used to generate a paired
combination of each row of the first table
with each row of the second table.
SYNTAX:
CROSS
JOIN
STUDENT RESULT:
MARKS QUERY:
NATURAL
JOIN
Natural Join returns all rows with matching
columns and eliminates duplicate columns.
This style of query is used when the tables
share on or more attributes with common
names.
SYNTAX:
INNER
JOIN
Inner Join combines rows from two or more
tables on a related column between them. It
only returns the rows that have matching
values in specified column.
SYNTAX:
NATURAL & INNER
JOIN
STUDENT QUERY NATURAL JOIN: RESULT:
MARKS QUERY INNER JOIN:
LEFT / LEFT OUTER
JOIN
The LEFT JOIN keyword returns all records
from the left table (table1), and the
matching records from the right table
(table2). The result is 0 records from the
right side, if there is no match.
In some databases LEFT JOIN is called LEFT
OUTER JOIN.
SYNTAX:
LEFT / LEFT OUTER
JOIN RESULT:
Customer
Invoice
QUERY:
RIGHT / RIGHT OUTER
JOIN
The RIGHT JOIN keyword returns all records
from the right table (table2), and the
matching records from the left table
(table1). The result is 0 records from the left
side, if there is no match.
In some databases RIGHT JOIN is called
RIGHT OUTER JOIN.
SYNTAX:
RIGHT / RIGHT OUTER
JOIN RESULT:
Customer
Invoice
QUERY:
FULL / FULL OUTER
JOIN
The FULL OUTER JOIN or FULL JOIN keyword
returns all records when there is a match in
left (table1) or right (table2) table records.
SYNTAX:
FULL / FULL OUTER
JOIN RESULT:
Customer
Invoice
QUERY:
SUBQUERIES &
CORRELATED QUERIES
WHERE ALL
IN FROM
HAVING ATTRIBUTE LIST
ANY CORRELATED
SUBQUERIES
SUBQUERIES
A subquery (inner query) is a query nested inside another query
(the outer query).
A subquery is normally expressed inside parentheses.
A subquery is based on the use of the SELECT statement to
return one or more values or null to another query.
WHERE
SUBQUERY SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);
The most common use of a subquery is
within the WHERE clause to filter results
based on the output of another query.
SUBQUERY
WITH JOINS
SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
JOIN LINE USING (INV_NUMBER)
JOIN PRODUCT USING (P_CODE)
WHERE P_CODE = (SELECT P_CODE FROM PRODUCT
WHERE P_DESCRIPT = ‘Claw hammer’);
lists all customers who have purchased hammers,
IN saws, or saw blades.
SUBQUERY SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
Checks if a value exists within the result JOIN LINE USING (INV_NUMBER)
set of the subquery JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT
WHERE P_DESCRIPT LIKE '%hammer%'
Use IN when you want to compare a OR P_DESCRIPT LIKE '%saw%');
single attribute to a list of values
HAVING SELECT P_CODE, SUM(LINE_UNITS)
FROM LINE
SUBQUERY GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);
Subqueries in the HAVING clause allow
you to apply conditions to groups.
ANY & ALL
SUBQUERY
ANY - Compares a value with any ALL - Compares a value with all
value in the subquery result set. values in the subquery result set
if a value meets the condition for if a value satisfies the condition
at least one value from a subquery. for every value returned by the
subquery.
Select employees whose salary is greater than any salary in the Sales department.
EMPLOYEE
SELECT name, salary FROM Employees WHERE
salary > ANY (SELECT salary FROM Employees
EMP_ID NAME DEPARTMENT SALARY
WHERE department = 'Sales');
1 Alice HR 50000 salary > 80,000 or 75000
NAME SALARY
2 Bob IT 70000
Eve 80000
3 Carol IT 60000
Grace 90000
4 David HR 55000
Select employees whose salary is greater than all salaries in the HR department.
5 Eve Sales 80000
NAME SALARY
6 Frank Sales 75000 SELECT name, salary FROM Bob 70000
Employees Carol 60000
7 Grace IT 90000
WHERE salary > ALL (SELECT
Eve 80000
salary FROM Employees
WHERE department = 'HR'); Frank 75000
Grace 90000
FROM
SUBQUERY SELECT
FROM
DISTINCT CUSTOMER.CUS_CODE, CUSTOMER.CUS_LNAME
CUSTOMER,
(SELECT INVOICE.CUS_CODE FROM INVOICE NATURAL JOIN
A subquery that generates a temporary LINE WHERE P_CODE = '13-Q2/P2') CP1,
(SELECT INVOICE.CUS_CODE FROM INVOICE NATURAL JOIN
table for use in the main (outer) query.
LINE WHERE P_CODE = '23109-HB') CP2
WHERE CUSTOMER.CUS_CODE = CP1.CUS_CODE AND
CP1.CUS_CODE = CP2.CUS_CODE;
Scenario: You want to know all customers w
ho have purchased both products 13-Q2/P2
and 23109-HB. All product purchases are
stored in the LINE table.
ATTRIBUTE LIST
(NON-CORRELATED SUBQUERY)
Inner query is independent of outer
query.
Inner query executed first
Returns a list of values or a single
value.
(1,2)
OUTPUT
CORRELATED
SUBQUERY
Inner query is dependent of outer
query.
Inner query is executed repeatedly
for each row in the outer query
SELECT * FROM EMPLOYEE E
WHERE NOT EXISTS
(SELECT 1 FROM SALES S
WHERE E.EMP_ID = S.EMP_ID):
OUTPUT
ICE BREAKER
4 PICS 1 WORD
INSTRUCTIONS:
EACH ROUND WE WILL PRESENT FOUR PICTURES THAT MAY OR MAY
NOT BE RELATED TO AN ADVANCED SQL CONCEPT.
YOU WILL HAVE 30 SECONDS TO GUESS THE ANSWER.
USE THE RAISE HAND FEATURE IN THE GOOGLE MEET TO ANSWER.
YOU CAN ONLY PARTICIPATE ONCE
IF YOU GUESS THE CORRECT ANSWER, YOU WILL RECEIVE 5 POINTS,
WHICH WILL BE ADDED TO YOUR SCORE DURING OUR QUIZ.
Q E
ANSWER : SEQUENCE
T I
ANSWER : FUNCTION
U
ANSWER : UNION
SQL FUNCTIONS
DATE AND TIME FUNCTIONS
NUMERIC FUNCTIONS
STRING FUNCTIONS
CONVERSION FUNCTIONS
SQL FUNCTIONS
used to perform calculations and manipulations on data stored in a relational database.
Functions always use a numerical, date, or string value. The value may be part of the
command itself (a constant or literal) or it may be an attribute located in a table.
DATE AND TIME
FUNCTIONS
essential for performing operations on data involving dates and time.
These functions allow you to format date and time values, calculate differences, or extract specific
parts from a date.
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
YEAR - Returns a four-digit year
Syntax: YEAR(date_value)
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
MONTH - Returns a two-digit month code
Syntax: MONTH(date_value)
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
DAY - Returns the number of the day
Syntax: DAY(date_value)
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
DATE() − MS Access
GETDATE() − SQL Server - Returns today’s date
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
DATEADD − SQL Server -Adds a number of selected time periods to a date
Syntax: DATEADD(datepart, number, date)
Datepart can be minutes, hours, days, weeks, months, quarters, or years.
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF SQL SERVER/MS ACCESS
DATEDIFF − SQL Server- Subtracts two dates
Syntax: DATEDIFF(datepart, startdate, enddate)
Datepart can be minutes, hours, days, weeks, months, quarters, or years.
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF ORACLE
TO_CHAR - Returns a character string or a formatted string from a date value
Syntax: TO_CHAR(date_value, fmt)
fmt = format used; can be: MONTH: name of month, MON: three-letter month
name, MM: two-digit month name, D: number for day of week, DD: number day of
month, DAY: name of day of week, YYYY: four-digit year value, YY: two-digit year
value
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF ORACLE
TO_DATE - Returns a date value using a character string and a date format mask
Syntax: TO_DATE(char_value, fmt)
fmt = format used; can be: MONTH: name of month, MON: three-letter month
name, MM: two-digit month name, D: number for day of week, DD: number day of
month, DAY: name of day of week, YYYY: four-digit year value, YY: two-digit year
value
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF ORACLE
SYSDATE - Returns today’s date
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF ORACLE
ADD_MONTHS - Adds a number of months to a date; useful for adding months or
years to a date
Syntax: ADD_MONTHS(date_value, n)
n - number of months/day
Sample Query:
DATE AND TIME
FUNCTIONS
EXAMPLES FUNCTION OF ORACLE
LAST_DAY - Returns the date of the last day of the month given in a date
Syntax: LAST_DAY(date_value)
Sample Query:
NUMERIC
FUNCTIONS
can be grouped in many different ways, such as algebraic, trigonometric, and
logarithmic.
It takes one numeric parameter and return one value.
EXAMPLE OF NUMERIC FUNCTIONS
ABS - Returns the absolute value of a number
Syntax: ABS(numeric_value)
Sample Query:
NUMERIC
FUNCTIONS
ROUND - Rounds a value to a specified precision (number of digits)
Syntax: ROUND(numeric_value, p)
p = precision
Sample Query:
NUMERIC
FUNCTIONS
CEIL/CEILING/FLOOR - Returns the smallest integer greater than or equal to a
number or returns the largest integer equal to or less than a number respectively
Syntax: CEIL(numeric_value) − Oracle
CEILING(numeric_value) − SQL Server
FLOOR(numeric_value)
Sample Query:
STRING
FUNCTIONS
Are used to perform operations on string data types
String manipulations are among the most-used functions in programming.
EXAMPLE OF STRING FUNCTIONS
Concatenation - || − Oracle & + − MS Access/SQL Server - Concatenates data
from two different character columns and returns a single column
Syntax: Oracle - strg_value || strg_value
SQL Server/MS Access: strg_value + strg_value
Sample Query:
STRING
FUNCTIONS
UPPER/LOWER - Returns a string in all capital or all lowercase letters
Syntax: UPPER(strg_value) or LOWER(strg_value)
Sample Query:
STRING
FUNCTIONS
SUBSTRING - Returns a substring or part of a given string parameter
Syntax: SUBSTR(strg_value, p, l) − Oracle
SUBSTRING(strg_value,p,l) − SQL Server
p = start position
l = length of characters
Sample Query:
STRING
FUNCTIONS
LENGTH - Returns the number of characters in a string value
Syntax: LENGTH(strg_value) − Oracle
LEN(strg_value) − SQL Server
Sample Query:
CONVERSION
FUNCTIONS
Allows you to take a value of a given data type and convert it to the equivalent
value in another data type.
EXAMPLE OF CONVERSION FUNCTIONS
Sample Query:
Numeric to Character: TO_CHAR − Oracle
CAST − SQL Server
CONVERT − SQL Server - Returns a character
string from a numeric value.
Syntax: Oracle: TO_CHAR(numeric_value, fmt)
SQL Server: CAST (numeric AS varchar(length))
or CONVERT(varchar(length), numeric)
CONVERSION
FUNCTIONS
Date to Character: TO_CHAR − Oracle
CAST − SQL Server
CONVERT − SQL Server - Returns a character string or a formatted character
string from a date value
Syntax: Oracle: TO_CHAR(date_value, fmt)
SQL Server: CAST (date AS varchar(length))
CONVERT(varchar(length), date)
Sample Query:
CONVERSION
FUNCTIONS
String to Number: TO_NUMBER - Returns a formatted number from a character
string, using a given format
Syntax: Oracle: TO_NUMBER(char_value, fmt)
fmt = format used; can be:
9: displays a digit Sample Query:
0: displays a leading zero
, : displays the comma
. : displays the decimal point
$: displays the dollar sign
B: leading blank
S: leading sign
MI: trailing minus sign
CONVERSION
FUNCTIONS
CASE − SQL Server
DECODE − Oracle - Compares an attribute or expression with a series of values
and returns an associated value or a default value if no match is found
Syntax: Oracle: DECODE(e, x, y, d)
e : attribute or expression, x: value with which to compare e, y: value to return if
e=x, d: default value to return if e is not equal to x
Syntax: SQL Server: CASE When condition
THEN value1
ELSE value2
END
CONVERSION
FUNCTIONS
Sample Query:
SEQUENCES
CREATE SEQUENCE MAXVALUE
IF NOT EXISTS MINVALUE
DATA_TYPE START
INCREMENT CYCLE
etc.
CREATE
SEQUENCE
EXAMPLE:
CREATE SEQUENCE creates
a new sequence number
generator. This involves CREATE SEQUENCE name
creating and initializing a
new special single-row CREATE SEQUENCE student_id_seq
table with the name name.
IF NOT EXISTS
SEQUENCE
Do not throw an error if a EXAMPLE:
relation with the same name
CREATE SEQUENCE IF NOT EXISTS name
already exists. A notice is
issued in this case. Note that
there is no guarantee that the CREATE SEQUENCE IF NOT EXISTS
existing relation is anything like student_id_seq
the sequence that would have
been created — it might not
even be a sequence.
DATA_TYPE
SEQUENCE
The optional clause AS EXAMPLE:
data_type specifies the data CREATE SEQUENCE IF NOT EXISTS name
type of the sequence. Valid AS data_type
types are smallint, integer,
and bigint. bigint is the CREATE SEQUENCE IF NOT EXISTS
default. The data type student_id_seq
determines the default AS SMALLINT
minimum and maximum
values of the sequence.
INCREMENT
SEQUENCE
The optional clause INCREMENT EXAMPLE:
BY increment specifies which CREATE SEQUENCE IF NOT EXISTS
value is added to the current student_id_seq
sequence value to create a
new value. A positive value will
INCREMENT BY 5
make an ascending sequence,
a negative one a descending
sequence. The default value is 1.
MINVALUE
SEQUENCE
EXAMPLE:
The optional clause MINVALUE
minvalue determines the CREATE SEQUENCE IF NOT EXISTS student_id_seq
minimum value a sequence can
generate. If this clause is not MINVALUE 5
supplied or NO MINVALUE is
specified, then defaults will be
ALTER SEQUENCE student_id_seq
used. The default for an ascending
sequence is 1. The default for a NO MINVALUE;
descending sequence is the
minimum value of the data type.
MAXVALUE
SEQUENCE
The optional clause MAXVALUE EXAMPLE:
maxvalue determines the
maximum value for the CREATE SEQUENCE IF NOT EXISTS
sequence. If this clause is not student_id_seq
supplied or NO MAXVALUE is
specified, then default values will MAXVALUE 100
be used. The default for an
ascending sequence is the ALTER SEQUENCE student_id_seq
maximum value of the data type.
The default for a descending NO MAXVALUE;
sequence is -1.
START
SEQUENCE
The optional clause START EXAMPLE:
WITH start allows the CREATE SEQUENCE IF NOT EXISTS
sequence to begin student_id_seq
anywhere. The default
starting value is minvalue for START 10
ascending sequences and
maxvalue for descending
ones.
CYCLE
SEQUENCE
The CYCLE option allows the EXAMPLE:
sequence to wrap around when CREATE SEQUENCE IF NOT EXISTS
the maxvalue or minvalue has
student_id_seq
been reached by an ascending
or descending sequence
respectively. If the limit is CYCLE
reached, the next number
generated will be the minvalue
or maxvalue, respectively.
NO CYCLE
SEQUENCE
EXAMPLE:
If NO CYCLE is specified, any
calls to nextval after the CREATE SEQUENCE IF NOT EXISTS
sequence has reached its student_id_seq
maximum value will return
NO CYCLE
an error. If neither CYCLE or
NO CYCLE are specified, NO
CYCLE is the default.
ADDITIONAL PARAMETERS
SEQUENCE
OWNED BY TABLE_NAME.COLUMN_NAME
TEMP
OWNED BY NONE
If specified, the sequence object
The OWNED BY option causes
is created only for this session,
the sequence to be associated and is automatically dropped on
with a specific table column, session exit. Existing permanent
such that if that column (or its sequences with the same name
whole table) is dropped, the are not visible (in this session)
sequence will be while the temporary sequence
automatically dropped as well exists, unless they are referenced
with schema-qualified names.
ADDITIONAL PARAMETERS
SEQUENCE
CACHE EXAMPLE:
The optional clause CACHE
cache specifies how many CREATE TEMP SEQUENCE
sequence numbers are to be temp_student_id_seq
preallocated and stored in
memory for faster access. The CACHE 10
minimum value is 1 (only one OWNED BY temp_student.ID;
value can be generated at a
time, i.e., no cache), and this is
also the default.
PROCEDURAL
SQL
is an extension of SQL that allows for
writing procedural code inside SQL. It
adds control structures such as loops,
conditionals, variables, exception
handling, and the ability to define
functions and procedures.
PL/SQL is a loadable procedural language for the PostgreSQL
database system. The design goals of PL/SQL were to create a
loadable procedural language that
can be used to create functions, procedures, and triggers,
adds control structures to the SQL language,
can perform complex computations,
inherits all user-defined types, functions, procedures, and
operators,
can be defined to be trusted by the server,
is easy to use.
ANONYMOUS PL/SQL BLOCKS IF...ELSE
PostreSQL Oracle SQL
LOOPING
LOOPING
EXCEPTION HANDLING
STORED
PROCEDURES
Are precompiled database objects
that contain a sequence of SQL
statements. They are essentially
reusable code blocks that can be
executed multiple times without
having to retype or recompile the SQL
statements.
STORED PROCEDURES
TRIGGERS
Automating business procedures and automatically maintaining data
integrity and consistency are critical in a modern business environment.
One of the most critical business procedures is proper inventory
management.
TRIGGERS
A trigger is procedural SQL code that is automatically invoked by the RDBMS upon
the occurrence of a given data manipulation event. It is useful to remember that:
A trigger is invoked before or after a data row is inserted, updated, or deleted.
A trigger is associated with a database table.
Each database table may have one or more triggers.
A trigger is executed as part of the transaction that triggered it.
TRIGGER DEFINITION
CREATE OR REPLACE TRIGGER trigger_name
[BEFORE / AFTER] [DELETE / INSERT / UPDATE OF column_name] ON table_name
[FOR EACH ROW]
[DECLARE]
[variable_namedata type[:=initial_value] ]
BEGIN
PL/SQL instructions;
..........
END;
TRIGGER DEFINITION
A trigger definition contains the following parts:
The triggering timing: BEFORE or AFTER. This timing indicates when the
trigger’s PL/SQL code executes; in this case, before or after the triggering
statement is completed.
The triggering event: the statement that causes the trigger to execute
(INSERT, UPDATE, or DELETE).
The triggering level: There are two types of triggers: statement-level triggers
and row-level triggers.
The triggering action: The PL/SQL code enclosed between the BEGIN and END
keywords. Each statement inside the PL/SQL code must end with a semicolon
“;”.
TRIGGERING LEVEL
Statement-Level Row-Level
is assumed if you omit the FOR requires use of the FOR EACH
EACH ROW keywords. This type of ROW keywords. This type of
trigger is executed once, before trigger is executed once for each
or after the triggering statement row affected by the triggering
is completed. This is the default statement. If you update 10 rows,
case. the trigger executes 10 times.
STATEMENT-LEVEL
OLD
UPDATED
ROW-LEVEL
ROW-LEVEL
You could also create triggers whose
actions depend on the type of DML
statement (INSERT, UPDATE, or DELETE)
that fires the trigger.
TRIGGER ACTION BASED
ON CONDITIONAL DML
IF INSERTING THEN ... END IF;
PREDICATES
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;
PROCESSING WITH
CURSORS
allows for processing multiple rows
returned by a query, one row at a
time. This is especially useful for
operations that need to be
performed on each row
individually.
IMPLICIT CURSORS EXPLICIT CURSORS
are automatically created by are user-defined cursors that allow
PostgreSQL when you execute a SQL you to retrieve multiple rows from a
statement that returns a single row query. You need to declare, open,
or performs a DML operation fetch from, and close these cursors
(INSERT, UPDATE, DELETE). You don't manually.
need to declare or manage them
manually.
EXPLICIT CURSORS
The declaration syntax for an explicit cursor within a PL/SQL block is as follows:
cursor_name CURSOR FOR select-query;
IMPLICIT CURSORS
EXPLICIT CURSORS
STORED
FUNCTIONS
Stored procedures and functions are
very similar. A stored function is
basically a named group of
procedural and SQL statements that
returns a value (indicated by a
RETURN statement in its program
code).
STORED STORED
PROCEDURES FUNCTIONS
To create a function, you use the following syntax:
STORED FUNCTION
EXAMPLE :
STORED FUNCTION
EXAMPLE :
Calling the Function and Output
ADVANCED SQL
END OF PRESENTATION
