Oracle Database SQL Certified Associate: SQL Fundamentals

Oracle Database SQL Associate

The Oracle Database SQL Certified Associate certification (1Z0-071) validates SQL proficiency with Oracle Database. This certification demonstrates your ability to write SQL queries, manipulate data, create database objects, and understand relational database fundamentals using Oracle's SQL syntax.

Oracle Database powers critical enterprise applications worldwide, making Oracle SQL expertise highly valuable.

Exam Format

Exam Topics

Relational Database Concepts

Database Fundamentals:

• Understand relational database theory
• Recognize E-R diagrams and data models
• Understand normalization (1NF, 2NF, 3NF)
• Identify database schema objects
• Understand ACID properties

Oracle Architecture:

• Understand Oracle instance vs database
• Recognize schema objects (tables, views, indexes, sequences)
• Understand tablespaces and datafiles
• Know user and schema relationships

Retrieving Data Using SELECT

Basic SELECT:

-- Simple SELECT
SELECT employee_id, first_name, last_name
FROM employees;

-- All columns
SELECT *
FROM departments;

-- Column aliases
SELECT first_name AS "First Name",
       salary * 12 AS annual_salary
FROM employees;

-- DISTINCT
SELECT DISTINCT department_id
FROM employees;

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

-- Literal values
SELECT first_name, 'Employee' AS type
FROM employees;

WHERE Clause:

-- Comparison operators
SELECT *
FROM employees
WHERE salary > 10000;

-- BETWEEN
SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 10000;

-- IN
SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

-- LIKE
SELECT *
FROM employees
WHERE last_name LIKE 'S%';  -- Starts with S
WHERE first_name LIKE '%a%';  -- Contains a
WHERE phone_number LIKE '___-____-____';  -- Pattern

-- IS NULL / IS NOT NULL
SELECT *
FROM employees
WHERE commission_pct IS NULL;

-- Logical operators
SELECT *
FROM employees
WHERE salary > 10000 AND department_id = 20;

SELECT *
FROM employees
WHERE department_id = 10 OR department_id = 20;

SELECT *
FROM employees
WHERE NOT department_id = 30;

ORDER BY:

-- Ascending (default)
SELECT *
FROM employees
ORDER BY salary;

-- Descending
SELECT *
FROM employees
ORDER BY hire_date DESC;

-- Multiple columns
SELECT *
FROM employees
ORDER BY department_id, salary DESC;

-- Column position
SELECT first_name, salary
FROM employees
ORDER BY 2 DESC;

Restricting and Sorting Data

Row Limiting:

-- FETCH FIRST
SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- OFFSET
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Percentage
SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- WITH TIES
SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

Single-Row Functions

Character Functions:

-- UPPER, LOWER, INITCAP
SELECT UPPER(last_name), LOWER(first_name), INITCAP(email)
FROM employees;

-- CONCAT, LENGTH, SUBSTR
SELECT CONCAT(first_name, last_name),
       LENGTH(first_name),
       SUBSTR(last_name, 1, 3)
FROM employees;

-- INSTR, REPLACE, TRIM
SELECT INSTR(email, 'A'),
       REPLACE(phone_number, '-', '.'),
       TRIM(' ' FROM '  text  ')
FROM employees;

-- LPAD, RPAD
SELECT LPAD(salary, 10, '*'),
       RPAD(first_name, 15, '.')
FROM employees;

Numeric Functions:

-- ROUND, TRUNC
SELECT ROUND(salary/12, 2) AS monthly_salary,
       TRUNC(salary/12, 2)
FROM employees;

-- MOD, POWER, SQRT
SELECT MOD(salary, 1000),
       POWER(2, 10),
       SQRT(salary)
FROM employees;

-- CEIL, FLOOR
SELECT CEIL(salary/1000),
       FLOOR(salary/1000)
FROM employees;

Date Functions:

-- SYSDATE, CURRENT_DATE
SELECT SYSDATE, CURRENT_DATE
FROM dual;

-- ADD_MONTHS
SELECT ADD_MONTHS(hire_date, 6)
FROM employees;

-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE, hire_date)
FROM employees;

-- NEXT_DAY, LAST_DAY
SELECT NEXT_DAY(SYSDATE, 'MONDAY'),
       LAST_DAY(SYSDATE)
FROM dual;

-- EXTRACT
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
       EXTRACT(MONTH FROM hire_date) AS hire_month
FROM employees;

-- TO_CHAR, TO_DATE
SELECT TO_CHAR(hire_date, 'DD-MON-YYYY'),
       TO_CHAR(salary, '$99,999.99')
FROM employees;

SELECT TO_DATE('01-JAN-2025', 'DD-MON-YYYY')
FROM dual;

Conversion Functions:

-- TO_CHAR (number to string)
SELECT TO_CHAR(salary, '$999,999.99')
FROM employees;

-- TO_NUMBER (string to number)
SELECT TO_NUMBER('12345')
FROM dual;

-- TO_DATE (string to date)
SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD')
FROM dual;

-- CAST
SELECT CAST(salary AS VARCHAR2(10)),
       CAST('123' AS NUMBER)
FROM employees;

Conditional Functions:

-- NVL, NVL2
SELECT salary,
       commission_pct,
       NVL(commission_pct, 0) AS commission,
       NVL2(commission_pct, salary * commission_pct, 0)
FROM employees;

-- COALESCE
SELECT COALESCE(commission_pct, salary, 0)
FROM employees;

-- NULLIF
SELECT NULLIF(department_id, 10)
FROM employees;

-- CASE expression
SELECT first_name,
       salary,
       CASE
         WHEN salary < 5000 THEN 'Low'
         WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
         WHEN salary > 10000 THEN 'High'
         ELSE 'Unknown'
       END AS salary_grade
FROM employees;

-- DECODE (Oracle-specific)
SELECT first_name,
       department_id,
       DECODE(department_id,
              10, 'Administration',
              20, 'Marketing',
              30, 'Purchasing',
              'Other') AS department_name
FROM employees;

Displaying Data from Multiple Tables

Inner Joins:

-- ANSI SQL JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
  ON e.department_id = d.department_id;

-- Oracle syntax
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Multiple joins
SELECT e.first_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

Outer Joins:

-- LEFT OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
  ON e.department_id = d.department_id;

-- RIGHT OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
  ON e.department_id = d.department_id;

-- FULL OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.department_id = d.department_id;

-- Oracle (+) syntax for outer join
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

Self Joins and Cross Joins:

-- Self join (employees and their managers)
SELECT e.first_name AS employee,
       m.first_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

-- CROSS JOIN (Cartesian product)
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

Non-Equijoins:

-- Join using non-equality condition
SELECT e.first_name, e.salary, j.grade_level
FROM employees e
JOIN job_grades j
  ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Aggregating Data

Group Functions:

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) AS total_employees,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees;

-- COUNT with DISTINCT
SELECT COUNT(DISTINCT department_id)
FROM employees;

-- COUNT with NVL
SELECT COUNT(NVL(commission_pct, 0))
FROM employees;

GROUP BY:

-- Basic grouping
SELECT department_id,
       COUNT(*) AS emp_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Multiple columns
SELECT department_id, job_id,
       COUNT(*) AS emp_count
FROM employees
GROUP BY department_id, job_id;

-- With ORDER BY
SELECT department_id,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

HAVING Clause:

-- Filter groups
SELECT department_id,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

-- Multiple conditions
SELECT department_id,
       COUNT(*) AS emp_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 7000;

Subqueries

Single-Row Subqueries:

-- In WHERE clause
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- In HAVING clause
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Multiple-Row Subqueries:

-- IN
SELECT first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 1700
);

-- ANY
SELECT first_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department_id = 30
);

-- ALL
SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 30
);

Correlated Subqueries:

-- Correlated subquery
SELECT e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

-- EXISTS
SELECT first_name, department_id
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

Data Manipulation (DML)

INSERT:

-- Single row
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'IT Support', 1700);

-- Multiple rows
INSERT INTO departments
SELECT department_id + 100, department_name, location_id
FROM departments
WHERE location_id = 1700;

-- INSERT ALL
INSERT ALL
  INTO sales VALUES (1, 100, '01-JAN-2025')
  INTO sales VALUES (2, 200, '02-JAN-2025')
SELECT * FROM dual;

UPDATE:

-- Simple update
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;

-- Update with subquery
UPDATE employees
SET department_id = (
  SELECT department_id
  FROM departments
  WHERE department_name = 'IT'
)
WHERE job_id = 'IT_PROG';

-- Update multiple columns
UPDATE employees
SET salary = salary * 1.15,
    commission_pct = 0.1
WHERE department_id = 30;

DELETE:

-- Simple delete
DELETE FROM employees
WHERE employee_id = 999;

-- Delete with subquery
DELETE FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 3000
);

-- TRUNCATE (faster, cannot rollback)
TRUNCATE TABLE temp_employees;

MERGE:

MERGE INTO employees_copy e
USING employees s
  ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = s.salary,
             e.department_id = s.department_id
WHEN NOT MATCHED THEN
  INSERT (employee_id, first_name, last_name, salary)
  VALUES (s.employee_id, s.first_name, s.last_name, s.salary);

Creating and Managing Tables

CREATE TABLE:

-- Basic table
CREATE TABLE employees_new (
  employee_id NUMBER(6) PRIMARY KEY,
  first_name VARCHAR2(20) NOT NULL,
  last_name VARCHAR2(25) NOT NULL,
  email VARCHAR2(25) UNIQUE,
  hire_date DATE DEFAULT SYSDATE,
  salary NUMBER(8,2) CHECK (salary > 0),
  department_id NUMBER(4),
  CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

-- CREATE TABLE AS SELECT (CTAS)
CREATE TABLE high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 10000;

-- External table
CREATE TABLE ext_employees (
  employee_id NUMBER,
  first_name VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
  LOCATION ('employees.csv')
);

ALTER TABLE:

-- Add column
ALTER TABLE employees_new
ADD (phone_number VARCHAR2(20));

-- Modify column
ALTER TABLE employees_new
MODIFY (first_name VARCHAR2(30));

-- Drop column
ALTER TABLE employees_new
DROP COLUMN phone_number;

-- Rename column
ALTER TABLE employees_new
RENAME COLUMN email TO email_address;

-- Add constraint
ALTER TABLE employees_new
ADD CONSTRAINT sal_check CHECK (salary > 1000);

-- Drop constraint
ALTER TABLE employees_new
DROP CONSTRAINT sal_check;

-- Disable/Enable constraint
ALTER TABLE employees_new
DISABLE CONSTRAINT emp_dept_fk;

ALTER TABLE employees_new
ENABLE CONSTRAINT emp_dept_fk;

DROP TABLE:

-- Drop table
DROP TABLE temp_employees;

-- Drop with purge (skip recycle bin)
DROP TABLE temp_employees PURGE;

-- Truncate (faster than delete)
TRUNCATE TABLE temp_employees;

Creating Other Schema Objects

Views:

-- Simple view
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Complex view
CREATE OR REPLACE VIEW dept_summary AS
SELECT d.department_name,
       COUNT(e.employee_id) AS emp_count,
       AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- Updateable view
CREATE VIEW it_employees AS
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 60
WITH CHECK OPTION;

-- Drop view
DROP VIEW emp_dept_view;

Sequences:

-- Create sequence
CREATE SEQUENCE emp_seq
START WITH 1000
INCREMENT BY 1
MAXVALUE 9999
NOCACHE
NOCYCLE;

-- Use sequence
INSERT INTO employees (employee_id, first_name)
VALUES (emp_seq.NEXTVAL, 'John');

SELECT emp_seq.CURRVAL FROM dual;

-- Alter sequence
ALTER SEQUENCE emp_seq INCREMENT BY 10;

-- Drop sequence
DROP SEQUENCE emp_seq;

Indexes:

-- Create index
CREATE INDEX emp_last_name_idx
ON employees(last_name);

-- Unique index
CREATE UNIQUE INDEX emp_email_idx
ON employees(email);

-- Composite index
CREATE INDEX emp_dept_job_idx
ON employees(department_id, job_id);

-- Function-based index
CREATE INDEX emp_upper_name_idx
ON employees(UPPER(last_name));

-- Drop index
DROP INDEX emp_last_name_idx;

Synonyms:

-- Create synonym
CREATE SYNONYM emp FOR employees;

-- Public synonym
CREATE PUBLIC SYNONYM emp FOR hr.employees;

-- Drop synonym
DROP SYNONYM emp;

Study Resources

Official Oracle Resources

• description

  Oracle Database SQL Language Reference - Complete SQL reference

  open_in_new
• school

  Oracle University - Official training

  open_in_new
• description

  Oracle Live SQL - Free online SQL practice

  open_in_new

Recommended Materials

• 📘 OCA Oracle Database SQL Exam Guide by Steve O'Hearn - Comprehensive study guide
• 🎓 Udemy Oracle SQL Courses - Video training
• 📝 Transcender Practice Exams - Realistic practice questions

Practice Questions

FAQ

---

Oracle SQL certification validates essential database skills for enterprise environments. Master SQL with Oracle, and you'll have skills applicable to many relational databases!