Lesson Overview
In this lesson, students learn how to sort query results and use Oracle single-row functions to manipulate text, numbers, and dates for better reporting and analysis.
ORDER BY
UPPER
LOWER
INITCAP
TRIM
LENGTH
SUBSTR
ROUND
TRUNC
MOD
SYSDATE
MONTHS_BETWEEN
ADD_MONTHS
LAST_DAY
NEXT_DAY
Trainer
Mohamed Alswaify
Mobile: 0564842804
Basic Query Structure
SELECT * | columns
FROM table_name
[WHERE bool_expr AND | OR bool_expr]
[ORDER BY column | number | expr | alias [ASC] [DESC]]
Character Functions
UPPER()LOWER()INITCAP()TRIM()LENGTH()SUBSTR()
Number Functions
ROUND()TRUNC()MOD()
Date Functions
SYSDATEROUND(date)TRUNC(date)MONTHS_BETWEEN()ADD_MONTHS()LAST_DAY()NEXT_DAY()
Examples
Example 1 - Names Starting with J, A, or M
SELECT last_name
FROM employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'A%'
OR last_name LIKE 'M%';
Example 2 - ORDER BY Column Number
SELECT last_name, salary, hire_date, job_id
FROM employees
ORDER BY 2;
Example 3 - ORDER BY Descending
SELECT last_name, salary, hire_date, job_id
FROM employees
ORDER BY last_name DESC;
Example 4 - Character Functions
SELECT UPPER(last_name),
LOWER(last_name),
LOWER(hire_date),
INITCAP(job_id),
LENGTH(phone_number)
FROM employees
WHERE LENGTH(phone_number) = 18;
Example 5 - Case-Insensitive Search
SELECT UPPER(last_name), LOWER(last_name), LOWER(hire_date), INITCAP(job_id), LENGTH(phone_number)
FROM employees
WHERE LOWER(last_name) = LOWER('HALL');
SELECT salary
FROM employees
WHERE UPPER(last_name) = UPPER('HaLl');
Example 6 - Uppercase Name and Length
Display the last name in uppercase and its length for employees whose names start with A, J, or M and have length 6 or more.
SELECT UPPER(last_name) AS last_name,
LENGTH(last_name) AS len_name
FROM employees
WHERE (last_name LIKE 'A%'
OR last_name LIKE 'J%'
OR last_name LIKE 'M%')
AND LENGTH(last_name) >= 6
ORDER BY len_name ASC;
Example 7 - TRIM
SELECT *
FROM employees
WHERE job_id = TRIM('IT_PROG ');
Example 8 - SUBSTR with Hire Date
SELECT last_name,
hire_date,
SUBSTR(hire_date, 4),
SUBSTR(hire_date, 4, 3),
SUBSTR(hire_date, 8),
SUBSTR(hire_date, -2)
FROM employees;
Example 9 - Building Number from Locations
SELECT city, SUBSTR(street_address, 1, 4) AS "building num"
FROM locations
WHERE country_id IN ('US', 'JP');
Example 10 - Job Prefix
SELECT last_name, job_id
FROM employees
WHERE SUBSTR(job_id, 1, 2) IN ('IT', 'AD');
Example 11 - Year from Hire Date
SELECT last_name, '20' || SUBSTR(hire_date, 8)
FROM employees
WHERE SUBSTR(hire_date, 8) = '05';
Example 12 - ROUND and TRUNC
SELECT ROUND(19.521285285285285),
ROUND(19.421285285285285)
FROM dual;
SELECT ROUND(19.567285285285285, 2),
ROUND(19.421285285285285, 1)
FROM dual;
SELECT TRUNC(19.521285285285285),
TRUNC(19.421285285285285)
FROM dual;
Example 13 - Salary Percentage
SELECT last_name, ROUND(salary * 0.05655665)
FROM employees;
SELECT last_name, TRUNC(salary * 0.05655665)
FROM employees;
Example 14 - MOD
SELECT MOD(11, 3)
FROM dual;
Example 15 - New Salary
Display the employee number, last name, salary, and salary increased by 15.5% as a whole number. Label the column New Salary.
SELECT employee_id,
last_name,
salary,
ROUND(salary * 1.155) AS "New Salary"
FROM employees;
Example 16 - SYSDATE and Date Rounding
SELECT SYSDATE
FROM dual;
SELECT last_name, hire_date, ROUND(hire_date, 'MONTH')
FROM employees;
SELECT last_name, hire_date, TRUNC(hire_date, 'MONTH')
FROM employees;
Example 17 - YEAR Round and Trunc
SELECT last_name, hire_date, ROUND(hire_date, 'YEAR')
FROM employees;
SELECT last_name, hire_date, TRUNC(hire_date, 'YEAR')
FROM employees;
Example 18 - Months Between
SELECT last_name, SYSDATE - hire_date
FROM employees;
SELECT last_name, MONTHS_BETWEEN(SYSDATE, hire_date)
FROM employees;
SELECT last_name,
ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 1),
ROUND(MONTHS_BETWEEN(SYSDATE, hire_date), 1)
FROM employees;
Example 19 - ADD_MONTHS / LAST_DAY / NEXT_DAY
SELECT last_name, hire_date, hire_date + 3
FROM employees;
SELECT last_name, hire_date, ADD_MONTHS(hire_date, 3)
FROM employees;
SELECT LAST_DAY(SYSDATE)
FROM dual;
SELECT NEXT_DAY(SYSDATE, 'SUNDAY')
FROM dual;
Example 20 - Salary Review Date
SELECT last_name,
hire_date,
NEXT_DAY(ADD_MONTHS(hire_date, 3), 'MON')
FROM employees;
Questions
Display the last name, job, and salary for all employees whose job is SA_REP or ST_CLERK, and whose salary is not equal to 2500, 3500, or 7000.
Write a query that displays the last name for all employees whose name starts with the letters J, A, or M, and sort the results descending by last name.
Write a query that displays the last name in uppercase and the length of the last name for all employees whose name starts with J, A, or M and whose last name length is 6 or more. Give each column an appropriate label and sort the results by the employees’ last names.
Write a query that displays the last name in uppercase, the length of the last name, and the length of the phone number for all employees whose name starts with J, A, or M and whose phone number length is 12 or more. Give each column an appropriate label and sort the results by last name.
From the locations table, display city and building number, which is the number at the beginning of street address, for all addresses in US and JP.
Display each employee’s last name, hire date, and salary review date, which is the first Monday after three months from hiring.
Task
Build a query that combines ORDER BY and single-row functions to produce a clean HR report with formatted names, calculated values, and a sorted result.
Hint: Try using
UPPER(), LENGTH(), ROUND(), and ORDER BY together.