ORACLE SQL
Session 2
Objectives
• UNION,INTERSECT, MINUS Operators
• Sorting Query Result
• SQL Functions (Character Functions, Math Functions,
Aggregate Functions)
• Join
• GROUP BY Queries.
UNION [ALL], INTERSECT, MINUS
Operators
• Combine multiple queries
• Have equal precedence
• If a SQL statement contains multiple set operators,
Oracle evaluates them from the left to right if no
parentheses explicitly specify another order
Union
• All distinct rows selected by either query
• The number of columns and the data types of the columns
must be identical
• By default, the output is sorted in ascending order of the first
column of the SELECT clause
Union Example
SELECT employee_id, job_id FROM employees
UNION
SELECT employee_id, job_id FROM job_history;
Union All
• Return all rows from multiple queries
• Does not eliminate duplicate selected rows:
• The DISTINCT keyword cannot be used.
Example
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history ORDER BY employee_id;
Intersect
• Return all rows common to multiple queries
• The number of columns and the data types of the columns
must be identical
Example
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
Minus
• Returns rows from the first query
that are not present in the second query
• The number of columns and the data types of the columns
must be identical
Example
SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history;
SORTING QUERY RESULTS
Sort query result with the ORDER BY clause
◦ ASC: ascending order, default
◦ DESC: descending order
The ORDER BY clause comes last in the SELECT
statement.
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
…
Sorting by Multiple Columns
The order of ORDER BY list is the order of sort.
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
You
can sort by a column that is not in the
SELECT list.
SQL Functions
Input Output
Function
arg 1 Function
performs
arg 2 action
Result
value
arg n
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions functions
Single-Row Functions
Single row functions:
Manipulate data items
Accept arguments and return one value
Act on each row returned
Return one result per row
May modify the data type
Can be nested
Accept arguments which can be a column or an
expression
function_name
function_name [(arg1,
[(arg1, arg2,...)]
arg2,...)]
Single-row Functions
Character
General Number
Single-row
functions
Conversion Date
Conversion Functions
Data type
conversion
Implicit data type Explicit data type
conversion conversion
Implicit Data Type
Conversion
For assignments, the Oracle server can automatically
convert the following:
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
Explicit Data Type Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
Using the TO_CHAR Function with
Dates
TO_CHAR(date,
TO_CHAR(date, 'format_model')
'format_model')
The format model:
• Must be enclosed in single quotation marks and is case
sensitive
• Can include any valid date format element
• Is separated from the date value by a comma
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY')
Month_Hired FROM employees
WHERE last_name = 'Higgins';
Elements of the Date Format Model
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
Three-letter abbreviation of the
MON
month
Three-letter abbreviation of the
DY
day of the week
DAY Full name of the day of the week
DD Numeric day of the month
Using the TO_CHAR Function with
Numbers
TO_CHAR(number,
TO_CHAR(number, 'format_model')
'format_model')
These are some of the format elements you can use with the
TO_CHAR function to display a number value as a character:
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a thousand indicator
SELECT TO_CHAR(salary, '$99,999.00') AS
formatted_salary
FROM employees
WHERE last_name = ‘Bose';
RR Date Format
Current Year Specified Date RR Format YY Format
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
If the specified two-digit year is:
0–49 50–99
If two digits The return date is in The return date is in
of the 0–49 the current the century
current century before the
year are: The return date is Thecurrent
return one
date is in
50–99 in the century the current
after the current century
one
•If the current year is 2025 and you
use TO_DATE('25-JUN-23', 'DD-MON-RR'),
the result will be June 25, 2023.
•If the current year is 2025 and you
use TO_DATE('25-JUN-75', 'DD-MON-RR'),
the result will be June 25, 1975.
Nesting Functions
• Single-row functions can be nested to any level.
• Nested functions are evaluated from deepest level to the
least deep level.
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
Nesting Functions
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
General Functions
These functions work with any data type and pertain
to using nulls.
• NVL (expr1, expr2): returns expr2 if the expr1 is null, otherwise returns expr1.
• NVL2 (expr1, expr2, expr3): returns expr2 if expr1 is not null, otherwise return
expr3
• NULLIF (expr1, expr2): compares expr1 with expr2. If they are equal then returns
null, otherwise return expr1.
• COALESCE (expr1, expr2, ..., exprn):returns first non null exp in list.
ISNULL()
Name Salary
Amit 8000
Raj NULL
SELECT SUM(ISNULL(Salary, 10000) AS Salary FROM
Employee;
Salary
18000
SELECT Name FROM Employee WHERE ISNULL(Salary);
Name
Raj
SELECT SUM(IFNULL(Salary, 10000) AS Salary FROM Employee;
Salary
18000
COALESCE()
COALESCE function in SQL returns the first non-NULL expression among its
arguments. If all the expressions evaluate to null, then the COALESCE
function will return null.
Name Ph1 Ph2
Amit 123 345
Raj NULL 456
SELECT Name, COALESCE(Ph1, Ph2) AS Contact FROM Contact_info;
Name CONTACT
Amit 345
Raj 456
NVL Function
Converts a null to an actual value.
• Data types that can be used are date, character, and
number.
• Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
Using the NVL Function
1
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL 2
FROM employees;
1 2
Using the NVL2 Function
1
SELECT last_name, salary, commission_pct,
NVL2(commission_pct, 2
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
1 2
What Are Aggregate Functions?
Operate on sets of rows to give one result per group.
EMPLOYEES
The maximum
salary in
the EMPLOYEES
table.
…
Types of Aggregate Functions
• AVG
• COUNT
• MAX
• MIN
• SUM
Aggregate Functions Syntax
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
Using the AVG and SUM Functions
You can use AVG and SUM for numeric data.
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
Using the MIN and MAX Functions
You can use MIN and MAX for any data type.
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
Using the COUNT Function
COUNT(*) returns the number of rows in a table.
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
Using the COUNT Function
• COUNT(expr) returns the number of rows with non-null
values for the expr.
• Display the number of department values in the
EMPLOYEES table, excluding the null values.
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;
Using the DISTINCT
Keyword
• COUNT(DISTINCT expr) returns the number of distinct non-
null values of the expr.
• Display the number of distinct department values in the
EMPLOYEES table.
SELECT COUNT(DISTINCT department_id)
FROM employees;
Types of Joins
• Equijoin: join with a join condition containing an equality operator (=)
• Non-equijoin: used to return result from two or more tables where exact join is not possible.
• Outer join: returns all rows that satisfy the join condition and also returns some or all of
those rows from one table for which no rows from the other satisfy the join condition.
Left
Right
Full
• Self join: a join of a table to itself
Equijoin?
EMPLOYEES DEPARTMENTS
… …
Foreign key Primary key
Retrieving Records
with Equijoins
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
…
Non-Equijoins
EMPLOYEES JOB_GRADES
Salary in the EMPLOYEES
table must be between
…
lowest salary and highest
salary in the JOB_GRADES
table.
Retrieving Records
with Non-Equijoins
SELECT e.last_name, [Link], j.grade_level
FROM employees e, job_grades j
WHERE [Link]
BETWEEN j.lowest_sal AND j.highest_sal;
…
Outer Joins
DEPARTMENTS EMPLOYEES
There are no employees in
department 190.
Outer Joins Syntax
• You use an outer join to also see rows that do not
meet the join condition.
• The outer join operator is the plus sign (+).
SELECT
SELECT [Link],
[Link], [Link]
[Link]
FROM
FROM table1,
table1, table2
table2
WHERE
WHERE [Link](+)
[Link](+) == [Link];
[Link];
SELECT
SELECT [Link],
[Link], [Link]
[Link]
FROM
FROM table1,
table1, table2
table2
WHERE
WHERE [Link]
[Link] == [Link](+);
[Link](+);
Using Outer Joins
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
…
Self Joins
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
… …
MANAGER_ID in the WORKER table is equal to
EMPLOYEE_ID in the MANAGER table.
Joining a Table to Itself
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
…
Group Functions and Null
Values
Group functions ignore null values in the column.
SELECT AVG(commission_pct)
FROM employees;
Using the NVL Function
with Group Functions
The NVL function forces group functions to include null values.
SELECT AVG(NVL(commission_pct, 0))
FROM employees;
Creating Groups of Data
EMPLOYEES
4400
9500 The
average
3500
salary
in
EMPLOYEES
6400
table
for each
department.
10033
…
Creating Groups of Data:
The GROUP BY Clause Syntax
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
Divide rows in a table into smaller groups by using the
GROUP BY clause.
Using the GROUP BY Clause
All columns in the SELECT list that are not in group
functions must be in the GROUP BY clause.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
Grouping by More Than One
Column
EMPLOYEES
“Add up the
salaries in
the EMPLOYEES
table
for each job,
grouped by
department.
…
Using the GROUP BY Clause
on Multiple Columns
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
Illegal Queries
Using Group Functions
• You cannot use the WHERE clause to restrict groups.
• You use the HAVING clause to restrict groups.
• You cannot use group functions in the WHERE clause.
SELECT
SELECT department_id,
department_id, AVG(salary)
AVG(salary)
FROM
FROM employees
employees
WHERE
WHERE AVG(salary)
AVG(salary) >> 8000
8000
GROUP
GROUP BY
BY department_id;
department_id;
WHERE
WHERE AVG(salary)
AVG(salary) >> 8000
8000
**
ERROR
ERROR at
at line
line 3:
3:
ORA-00934:
ORA-00934: group
group function
function is
is not
not allowed
allowed here
here
Cannot use the
Cannot use the WHERE clause to
WHERE clause restrict groups
to restrict groups
Excluding Group Results: The
HAVING Clause
Use the HAVING clause to restrict groups:
1. Rows are grouped.
2. The group function is applied.
3. Groups matching the HAVING clause are displayed.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
Using the HAVING Clause
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
Excluding Group Results
EMPLOYEES
The maximum
salary
per department
when it is
greater than
$10,000
…
Using the HAVING Clause
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);