If you have only 30 minutes to revise Oracle SQL Fundamentals I & II (Oracle Database
SQL Certified Associate level), focus on the highest-yield topics that commonly appear in
exams and interviews.
30-Minute Oracle SQL Revision Plan
1. Basic SELECT (3 min)
SQL
SELECT employee_id, first_name, salary
FROM employees;
Order of Execution
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
Aliases
SQL
SELECT first_name AS Name,
salary * 12 AS Annual_Salary
FROM employees;
2. WHERE Clause & Operators (3 min)
Comparison
SQL
WHERE salary > 5000
WHERE salary BETWEEN 5000 AND 10000
WHERE department_id IN (10,20,30)
WHERE first_name LIKE 'A%'
WHERE commission_pct IS NULL
Wildcards
SQL
'A%' -- Starts with A
'%A' -- Ends with A
'%A%' -- Contains A
'_A%' -- Second character A
Logical Operators
SQL
AND
OR
NOT
3. Single Row Functions (4 min)
Character Functions
SQL
UPPER('oracle')
LOWER('ORACLE')
INITCAP('oracle sql')
LENGTH('ORACLE')
SUBSTR('ORACLE',2,3)
REPLACE('ORACLE','ORA','SQL')
Number Functions
SQL
ROUND(45.678,2)
TRUNC(45.678,2)
MOD(10,3)
Date Functions
SQL
SYSDATE
ADD_MONTHS(SYSDATE,3)
MONTHS_BETWEEN(date1,date2)
NEXT_DAY(SYSDATE,'MONDAY')
LAST_DAY(SYSDATE)
4. Conversion Functions (2 min)
Implicit Conversion
Oracle automatically converts.
Explicit Conversion
SQL
TO_CHAR(SYSDATE,'DD-MON-YYYY')
TO_DATE('25-DEC-2025','DD-MON-YYYY')
TO_NUMBER('100')
5. Conditional Expressions (2 min)
CASE
SQL
SELECT employee_id,
salary,
CASE
WHEN salary > 10000 THEN 'HIGH'
WHEN salary > 5000 THEN 'MEDIUM'
ELSE 'LOW'
END
FROM employees;
NVL
SQL
NVL(commission_pct,0)
NVL2(expr,val_if_not_null,val_if_null)
COALESCE(col1,col2,col3)
6. Joins (5 min)
Inner Join
SQL
SELECT e.employee_id,
d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
Left Outer Join
SQL
SELECT *
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id;
Self Join
SQL
SELECT e.first_name Employee,
m.first_name Manager
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;
Cartesian Product
SQL
SELECT *
FROM employees, departments;
Avoid unless intentional.
7. Group Functions (3 min)
Aggregate Functions
SQL
COUNT(*)
SUM(salary)
AVG(salary)
MIN(salary)
MAX(salary)
GROUP BY
SQL
SELECT department_id,
AVG(salary)
FROM employees
GROUP BY department_id;
HAVING
SQL
SELECT department_id,
AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
8. Subqueries (3 min)
Single Row
SQL
SELECT *
FROM employees
WHERE salary >
(SELECT AVG(salary)
FROM employees);
Multiple Row
SQL
WHERE department_id IN
(
SELECT department_id
FROM departments
);
EXISTS
SQL
WHERE EXISTS
(
SELECT 1
FROM departments d
WHERE d.department_id=e.department_id
);
9. Set Operators (2 min)
UNION
Removes duplicates.
SQL
SELECT employee_id FROM employees
UNION
SELECT employee_id FROM retired_employees;
UNION ALL
Keeps duplicates.
INTERSECT
Common rows.
MINUS
Rows in first query not in second.
10. DDL & DML (3 min)
Create Table
SQL
CREATE TABLE students
(
student_id NUMBER,
name VARCHAR2(50)
);
Insert
SQL
INSERT INTO students
VALUES (1,'Ali');
Update
SQL
UPDATE students
SET name='Ahmed'
WHERE student_id=1;
Delete
SQL
DELETE FROM students
WHERE student_id=1;
Commit / Rollback
SQL
COMMIT;
ROLLBACK;
Exam Quick Facts (Must Memorize)
Topic Key Point
NULL NULL ≠ 0
Topic Key Point
COUNT(*) Counts NULLs
COUNT(column) Ignores NULLs
WHERE Before GROUP BY
HAVING After GROUP BY
NVL Replace NULL
UNION Removes duplicates
UNION ALL Keeps duplicates
Primary Key Unique + Not Null
Foreign Key References parent table
COMMIT Permanent save
ROLLBACK Undo uncommitted work
SYSDATE Current date/time
DUAL Dummy Oracle table
5 Most Important Practice Questions
Q1
SQL
SELECT COUNT(*)
FROM employees;
Difference from:
SQL
SELECT COUNT(commission_pct)
FROM employees;
Q2
Find employees earning above average salary.
SQL
SELECT *
FROM employees
WHERE salary >
(SELECT AVG(salary)
FROM employees);
Q3
Average salary by department.
SQL
SELECT department_id,
AVG(salary)
FROM employees
GROUP BY department_id;
Q4
Employees and department names.
SQL
SELECT e.first_name,
d.department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id;
Q5
Employees whose names start with A.
SQL
SELECT *
FROM employees
WHERE first_name LIKE 'A%';
If you're preparing for the Oracle SQL Fundamentals 1Z0-071/1Z0-047 style exam,
mastering the above topics covers roughly 80–90% of the core SQL questions you are
likely to encounter.