Lesson Overview
In this lesson, students learn how to convert values between data types, customize output formatting, handle null values, classify rows using CASE / DECODE, and summarize data using aggregate functions with GROUP BY and HAVING.
TO_CHAR
TO_DATE
TO_NUMBER
NVL
NVL2
CASE
DECODE
MAX
MIN
SUM
AVG
COUNT
GROUP BY
HAVING
Trainer
Mohamed Alswaify
Mobile: 0564842804
Core Topics
Conversion Functions
TO_CHAR()TO_DATE()TO_NUMBER()
General Functions
NVL()NVL2()CASEDECODE()
Group Functions
MAX()MIN()SUM()AVG()COUNT()
Examples
Example 1 - TO_CHAR with Date
SELECT last_name, hire_date, TO_CHAR(hire_date, 'day (DY) ddspth')
FROM employees;
Example 2 - Detailed Date Format
SELECT last_name, hire_date, TO_CHAR(hire_date, 'day (DY) dd "Of" month (mon) mm - yyyy')
FROM employees;
Example 3 - Year Only
SELECT last_name, hire_date, TO_CHAR(hire_date, 'yyyy')
FROM employees;
Example 4 - Required Date Formatting
Display each employee’s last name, hire date, and salary. Format the date similar to “Monday, the Thirty-First of July, 2000.”
SELECT last_name, hire_date, TO_CHAR(hire_date, 'Day, "The" Ddspth "Of" fm Month, yyyy.')
FROM employees;
Example 5 - TO_CHAR with Salary
SELECT last_name, salary, TO_CHAR(salary, '00,000')
FROM employees;
SELECT last_name, salary, TO_CHAR(salary, '999,999$')
FROM employees;
SELECT last_name, salary, TO_CHAR(salary, '999,999') || 'SAR'
FROM employees;
Example 6 - TO_DATE
SELECT last_name, hire_date
FROM employees
WHERE hire_date > TO_DATE('01-01-2005', 'dd-mm-yyyy');
Example 7 - TO_NUMBER
SELECT last_name, hire_date, salary
FROM employees
WHERE salary > TO_NUMBER('12,000', '00,000');
Example 8 - Hire Date Between Two Dates
Get employees hired between 2006-06-01 and 2007-07-30 using the same format.
SELECT last_name, hire_date
FROM employees
WHERE hire_date BETWEEN TO_DATE('2006-06-01', 'yyyy-mm-dd')
AND TO_DATE('2007-07-30', 'yyyy-mm-dd');
Example 9 - Using TO_CHAR in Comparison
SELECT last_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'yyyy-mm-dd') BETWEEN '2006-06-01' AND '2007-07-30';
Example 10 - NVL
SELECT last_name, salary, commission_pct, salary * NVL(commission_pct, 0) + salary
FROM employees;
Example 11 - NVL2
SELECT last_name, salary, NVL2(commission_pct, 0.1, 0), salary * NVL2(commission_pct, 0.1, 0) + salary
FROM employees;
Example 12 - NVL with Department
SELECT last_name, NVL(TO_CHAR(department_id), 'No Dept')
FROM employees;
Example 13 - Commission Label
Display employees’ last names and commission amounts. If there is no commission, show “No Commission.”
SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') AS comm
FROM employees;
Example 14 - CASE with Department Salary Rules
SELECT last_name,
department_id,
salary,
CASE
WHEN department_id = 30 THEN salary + 500
WHEN department_id = 60 THEN salary + 1000
WHEN department_id = 90 THEN salary - 500
ELSE salary
END AS new_salary
FROM employees;
Example 15 - CASE with Salary Levels
SELECT last_name,
department_id,
salary,
CASE
WHEN salary >= 12000 THEN 'High Salary'
WHEN salary >= 6000 THEN 'Normal Salary'
WHEN salary >= 3000 THEN 'Low Salary'
ELSE 'Very Low Salary'
END AS sal_level
FROM employees;
Example 16 - Simple CASE
SELECT last_name,
department_id,
salary,
CASE department_id
WHEN 30 THEN salary + 500
WHEN 60 THEN salary + 1000
WHEN 90 THEN salary - 500
ELSE salary
END AS new_salary
FROM employees;
Example 17 - DECODE
SELECT last_name,
department_id,
salary,
DECODE(department_id,
30, salary + 500,
60, salary + 1000,
90, salary - 500,
salary) AS new_salary
FROM employees;
Example 18 - Job Grade with CASE
SELECT last_name,
department_id,
salary,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE '0'
END AS grade
FROM employees;
Example 19 - Job Grade with DECODE
SELECT last_name,
department_id,
salary,
DECODE(job_id,
'AD_PRES', 'A',
'ST_MAN', 'B',
'IT_PROG', 'C',
'SA_REP', 'D',
'ST_CLERK', 'E',
'0') AS grade
FROM employees;
Example 20 - Basic Aggregate Functions
SELECT MAX(salary), MIN(salary), SUM(salary), ROUND(AVG(salary)), COUNT(last_name)
FROM employees;
Example 21 - Aggregate with WHERE
SELECT MAX(salary), MIN(salary), SUM(salary), ROUND(AVG(salary)), COUNT(last_name)
FROM employees
WHERE department_id = 30;
SELECT MAX(salary), MIN(salary), SUM(salary), ROUND(AVG(salary)), COUNT(last_name)
FROM employees
WHERE job_id = 'IT_PROG';
Example 22 - GROUP BY Department
SELECT department_id,
MAX(salary),
MIN(salary),
SUM(salary),
ROUND(AVG(salary)),
COUNT(last_name)
FROM employees
GROUP BY department_id;
Example 23 - GROUP BY Job
SELECT job_id,
MAX(salary),
MIN(salary),
SUM(salary),
ROUND(AVG(salary)),
COUNT(last_name)
FROM employees
GROUP BY job_id;
Example 24 - Group by Hire Year
SELECT TO_CHAR(hire_date, 'yyyy') AS year_hire, COUNT(*)
FROM employees
GROUP BY TO_CHAR(hire_date, 'yyyy')
ORDER BY 1;
Example 25 - HAVING
SELECT department_id,
MAX(salary),
MIN(salary),
SUM(salary),
ROUND(AVG(salary)),
COUNT(last_name)
FROM employees
WHERE department_id > 30
GROUP BY department_id
HAVING COUNT(last_name) >= 5
ORDER BY 1;
Questions
Write a query to get the first name and hire date from employees table where hire date is between '2006-06-01' and '2007-07-30' using the same format.
Create a query that displays employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM.
Using the CASE or DECODE function, write a query that displays the grade of all employees based on the value of JOB_ID using the required mapping.
Task
Build a report that combines TO_CHAR, NVL, CASE, and group functions to produce a clean, readable summary for HR data.
Hint: Try formatting dates and salary values, replacing null values, then summarizing the data by department or job.