Oracle 19c Database

+ SQL Complete Mastery

From Absolute Beginner to Professional

A practical, simple-language guide to SQL and Oracle Database 19c.

Every concept explained with real examples from scratch.

What you will master:

Database Concepts • SQL from Zero • Oracle 19c Architecture
DDL • DML • DQL • DCL • TCL — All SQL Categories
Joins • Subqueries • Functions • Views • Indexes
PL/SQL • Stored Procedures • Triggers • Sequences
Oracle Security • Backup & Recovery • Performance Tuning

PLUS: Free Online Practice Platforms — No Installation Needed!

■ Designed for students who cannot run Oracle locally — all examples work on free online platforms
TABLE OF CONTENTS
Ch Topic Level

1 What is a Database? — Concepts Every Developer Must Know Beginner

2 Where to Practice Oracle Online — Free Resources Beginner

3 SQL Foundations — The Language of Databases Beginner

4 DDL — Creating and Managing Database Structure Beginner

5 DML — Inserting, Updating, and Deleting Data Beginner

6 DQL — The SELECT Statement — Querying Data Beginner→Inter.

7 WHERE, ORDER BY, GROUP BY — Filtering and Sorting Intermediate

8 SQL Functions — Single Row and Group Functions Intermediate

9 Joins — Combining Data from Multiple Tables Intermediate

10 Subqueries — Queries Inside Queries Intermediate

11 DCL and TCL — Security and Transactions Intermediate

12 Views, Sequences, Synonyms, and Indexes Advanced

13 Oracle 19c Architecture — How Oracle Works Internally Advanced

14 PL/SQL — Oracle's Programming Language Advanced

15 Stored Procedures, Functions, and Packages Advanced

16 Triggers — Automatic Database Actions Advanced

17 Oracle Security — Users, Roles, Privileges Advanced

18 Backup, Recovery, and RMAN Expert

19 Performance Tuning and Query Optimisation Expert

20 Real Project — Seminary Database Design Expert

21 Complete SQL + Oracle Cheat Sheet All Levels

CHAPTER 1 — What is a Database?
Core concepts every developer must understand first

1.1 The Problem That Databases Solve

Imagine you are managing student records for Kasita Seminary. You have 300 students. You could store
everything in Excel files — one file per student, or one big spreadsheet. But what happens when: two people
edit the same file at the same time? You need to find all students who failed this semester? You accidentally
delete a row? You need to connect student records with their course fees and their exam results?

These problems are exactly why databases were invented. A DATABASE is an organised, structured
collection of data that can be stored, retrieved, updated, and managed efficiently — even by many users at
the same time.

■ ANALOGY: Think of a database like a very organised library. The library building = the database. Each
shelf = a table. Each book on the shelf = a row of data. The catalogue system = the index. The librarian =
the database management system (DBMS) that helps you find what you need quickly.

1.2 DBMS vs Database — Know the Difference

Term What It Is Real Example

Database The actual stored data — files on disk The student_records data stored on the server

DBMS Software that manages the database — reads,

Oraclewrites,
19c, MySQL,
securesPostgreSQL, SQL Server

SQL Language used to communicate with the DBMS

SELECT * FROM students WHERE year = 2

Schema Blueprint/structure of the database — tables

Theand
design
relationships
showing students table, courses table, etc.

Table Data organised in rows and columns — like

students
a spreadsheet
table: id, name, email, course_id, year

Row/Record One entry of data in a table — one student's

1, Alice
details
Mwangi, alice@[Link], 3, 2

Column/Field One attribute/property — one type of information

name, email, year — these are columns

Primary Key Unique identifier for each row — like a national

student_id
ID = 1001 — no two students share this

Foreign Key Links one table to another — creates relationships

course_id in students table links to courses table

1.3 Why Oracle? Why Is It Used by Enterprises?

There are many DBMS options: MySQL, PostgreSQL, SQL Server, SQLite, MongoDB. Oracle Database is
the most widely used enterprise database in the world. Banks, governments, hospitals, airlines, and large
corporations choose Oracle because:

• Reliability: Oracle has over 40 years of stability. It powers systems where downtime means millions of
dollars lost — like stock exchanges and banking systems.
• Scalability: Oracle can handle databases with terabytes of data and thousands of concurrent users
without performance degradation.
• Advanced features: Real Application Clusters (RAC), Automatic Storage Management (ASM), Data
Guard for disaster recovery — features no other DBMS matches.
• Security: Oracle has the most comprehensive security features of any DBMS — row-level security,
transparent data encryption, database vault, audit trails.
 • PL/SQL: Oracle's built-in programming language allows you to write complex business logic directly
inside the database — procedures, triggers, packages.
• Support and compliance: Enterprise-grade support, legal compliance (GDPR, HIPAA), and regulatory
certifications that organisations require.
■ ORACLE: Oracle 19c is the Long Term Support (LTS) release — meaning it receives patches
and support until 2027 (extended to 2030). This is why universities and enterprises still teach and
use 19c. It is stable, well-documented, and widely deployed.

1.4 Relational Database Concepts — The Foundation

Oracle is a RELATIONAL database. Relational means data is stored in tables and tables are connected to
each other through relationships. This model was invented by Edgar Codd at IBM in 1970 and remains the
dominant database model today.

Concept Meaning Seminary Example

Entity A thing you store information about Student, Course, Lecturer, Exam

Attribute A property of an entity Student has: name, email, year, gpa

Primary Key Uniquely identifies each row student_id — every student gets a unique number

Foreign Key Links two tables together students.course_id → courses.course_id

One-to-Many One row relates to many in another table

One course has MANY students enrolled

Many-to-Many Many rows in one table relate to manyStudents

in another
take MANY subjects; subjects have MANY students

One-to-One One row relates to exactly one in another

Onetable
student has one student profile record

Normalisation Removing duplicate data, organising efficiently

Storing course name ONCE in courses table, not in every student row

NULL Absence of a value — not zero, not empty

A student's
string phone number not provided = NULL

Constraint Rule enforced by the database NOT NULL: name cannot be empty. UNIQUE: email must be unique
CHAPTER 2 — Where to Practice Oracle Online
Free platforms — no installation needed

2.1 The Good News — You Do Not Need to Install Oracle

Oracle Database 19c requires at least 8GB RAM, 20GB disk space, and a 64-bit OS — resources that many
students cannot afford on their local machines. The great news is that there are excellent FREE online
platforms where you can write and run real Oracle SQL without installing anything. Here are the best options:

■ PRACTICE ONLINE: START HERE IMMEDIATELY — These are your primary practice tools:

Platform URL What You Get Best For

Oracle LiveSQL [Link] Free official Oracle SQL environment. Real

PRIMARY
Oracle 19c.
— Use
No signup
this first.
forOfficial
basic use.
Oracle.
HasFree.
tutor

Oracle APEX [Link]/en/learn/getting-started

Free Oracle APEX workspace with full Oracle
Best DB
for access.
PL/SQL,5GB
full database
storage. Real
projects,
Oracle
tables,
19c ep

DB Fiddle (Oracle) [Link] Online SQL editor supporting Oracle, MySQL,

QuickPostgreSQL
SQL testingside
and by
comparing
side. Great
Oracle
for quick
vs othe
te

SQLiteOnline [Link] Runs SQL in browser — not Oracle but good

Basic
forSQL
basic
practice
SQL concepts
when offline
that transfer directly

W3Schools SQL Tryit [Link]/sql/[Link] SQL editor with sample database

Learning
pre-loaded.
basic SELECT, WHERE, JOIN concep

HackerRank SQL [Link]/domains/sqlSQL challenges and problems with auto-grading.

PracticeHas
and Oracle
testing support.
your SQL knowledge

LeetCode Database [Link]/problemset/database

Real SQL interview questions used by tech
Advanced
companies.
SQL problem solving

Oracle Dev Gym [Link] Official Oracle learning platform. Quizzes,Structured

workouts, learning
SQL challenges.
with Oracle-specific
Free [Link]

2.2 Setting Up Oracle LiveSQL — Step by Step

Oracle LiveSQL is the best starting point. Here is exactly how to use it:

1. Go to: [Link] in your browser

2. Click 'Sign In' — create a free Oracle account if you do not have one (use any email address)
3. Once logged in, click 'SQL Worksheet' — this is your SQL editor
4. Type any SQL statement and press Ctrl+Enter or click 'Run' to execute
5. Your tables and data persist between sessions — you can save your work
6. Explore 'Script Library' — thousands of Oracle SQL examples ready to run
■ PRACTICE ONLINE: [Link] — This is where you will type every SQL example in this
book. Open it now and keep it open as you read.

2.3 Setting Up Oracle APEX — For Full Oracle Experience

When you are ready for PL/SQL, stored procedures, and a real Oracle environment, Oracle APEX gives you
a full database workspace for free:

1. Go to: [Link]/en/learn/getting-started
2. Click 'Request a Free Workspace'
3. Fill in your details — select 'Database Application' as workspace type
4. Wait for approval email (usually within a few hours)
5. Log in and navigate to 'SQL Workshop' → 'SQL Commands'
 6. This gives you a full Oracle 19c database that YOU own — create tables, load data, write procedures,
everything
■ ORACLE: Oracle APEX workspace is the closest you will get to a real Oracle installation without
installing anything. It is what professional Oracle developers use for development and is used by
over 500,000 organisations worldwide.
CHAPTER 3 — SQL Foundations
The language you use to talk to Oracle

3.1 What is SQL?

SQL (Structured Query Language — pronounced 'sequel' or 'S-Q-L') is the standard language for
communicating with relational databases. You use SQL to: create tables, insert data, retrieve data, update
data, delete data, and control who can do what. SQL is not a programming language like PHP or Python — it
is a QUERY language designed specifically for databases.

SQL Category
Full Name What It Does Commands

DDL Data Definition Language Creates and changes database structure —CREATE,
tables, columns,
ALTER,constraints
DROP, TRUNCATE, RENAME

DML Data Manipulation LanguageWorks with the actual data — adding, changing,
INSERT,
removing
UPDATE,
rows DELETE, MERGE

DQL Data Query Language Retrieves data from tables SELECT

DCL Data Control Language Controls who can access what — security GRANT, REVOKE

TCL Transaction Control Language

Manages transactions — save or undo groups
COMMIT,
of changes
ROLLBACK, SAVEPOINT

3.2 SQL Rules and Syntax Basics

• SQL is NOT case-sensitive for keywords: SELECT = select = Select. However, convention is to write
SQL KEYWORDS in UPPERCASE for readability.
• Statements end with a semicolon (;) — this tells Oracle the statement is complete.
• String values use SINGLE quotes: 'Alice' not "Alice"
• Column and table names are case-insensitive: students = STUDENTS = Students
• Comments: single line with -- and multi-line with /* comment */
• NULL means no value — not zero, not empty string. Special handling required.
• Oracle uses DUAL — a special one-row, one-column table for testing expressions.
SQL basics and DUAL

-- This is a single line comment

/* This is a
multi-line comment */
-- Testing expressions with DUAL (Oracle-specific)
SELECT 5 + 3 FROM DUAL;
SELECT SYSDATE FROM DUAL; -- current date and time
SELECT USER FROM DUAL; -- current logged-in user
SELECT 'Hello Oracle' FROM DUAL;

Result:
8
24-MAR-26
SYSTEM
Hello Oracle

3.3 Oracle Data Types — What Types of Data Can You Store?
Data Type What It Stores Example Notes

VARCHAR2(n) Variable-length text up to n characters

VARCHAR2(100) for name
Most common string type in Oracle. Always prefer over CHAR

CHAR(n) Fixed-length text — always nCHAR(10)

charactersfor code Pads with spaces if shorter. Use for fixed codes like 'M' or 'F'

NUMBER(p,s) Numbers — p=total digits, s=decimal

NUMBER(10,2)
places for salary
NUMBER(5) for integer, NUMBER(8,2) for 1234567.89

DATE Date AND time — no separate

DATE
TIME
fortype
enrollment_date
in Oracle Stores: day, month, year, hour, minute, second

TIMESTAMP Date and time with fractional TIMESTAMP(6)

seconds for audit
Morelogsprecise than DATE — use for audit trails

CLOB Large text — up to 4GB CLOB for document content

Character Large Object — for long text like essays

BLOB Binary data — images, files BLOB for student photoBinary Large Object — for files stored in database

BOOLEAN True/False — NOT availableN/A

in SQL
in SQL
(onlytables
PL/SQL) Use NUMBER(1) with 0/1 or CHAR(1) 'Y'/'N' instead

■ ORACLE: Oracle does NOT have INT, VARCHAR, or BOOLEAN in SQL (only in PL/SQL). Always
use NUMBER for integers, VARCHAR2 for strings, and DATE for dates. This is one of the biggest
differences from MySQL or PostgreSQL.
CHAPTER 4 — DDL — Creating and Managing Database
Structure
CREATE, ALTER, DROP, TRUNCATE

4.1 CREATE TABLE — Building Your Database Structure

The CREATE TABLE statement defines a new table — its name, columns, data types, and constraints. Think
of it as designing a spreadsheet before entering any data — you decide what columns exist and what rules
apply to each column.

CREATE TABLE with constraints

-- Create the courses table first (students will reference it)

CREATE TABLE courses (
course_id NUMBER(5) PRIMARY KEY,
course_name VARCHAR2(100) NOT NULL,
duration_yrs NUMBER(1) NOT NULL,
department VARCHAR2(50),
created_at DATE DEFAULT SYSDATE
);
-- Create the students table with constraints
CREATE TABLE students (
student_id NUMBER(10) PRIMARY KEY,
student_number VARCHAR2(20) UNIQUE NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE NOT NULL,
phone VARCHAR2(20),
course_id NUMBER(5) NOT NULL,
year_of_study NUMBER(1) NOT NULL,
gpa NUMBER(3,2) DEFAULT 0.00,
active CHAR(1) DEFAULT 'Y',
enrollment_date DATE DEFAULT SYSDATE,
-- Foreign key constraint
CONSTRAINT fk_student_course
FOREIGN KEY (course_id) REFERENCES courses(course_id),
-- Check constraint
CONSTRAINT chk_year
CHECK (year_of_study BETWEEN 1 AND 7),
CONSTRAINT chk_gpa
CHECK (gpa BETWEEN 0.00 AND 4.00),
CONSTRAINT chk_active
CHECK (active IN ('Y', 'N'))
);

■ NOTE: Notice: courses table is created BEFORE students because students has a foreign key pointing
to courses. You must create the referenced table first. This order matters!

4.2 Constraints — Rules That Protect Your Data

Constraint Purpose Example

PRIMARY KEY Uniquely identifies each row. Cannot be NULL

student_id
or duplicate.
NUMBER PRIMARY KEY
 NOT NULL Column must have a value — cannot be empty.
first_name VARCHAR2(50) NOT NULL

UNIQUE All values in column must be different. Can

email
haveVARCHAR2(100)
one NULL. UNIQUE

FOREIGN KEY Links to primary key in another table. Enforces

FOREIGN
referential
KEY (course_id)
integrity. REFERENCES courses(course_id)

CHECK Value must satisfy a condition. CHECK (gpa BETWEEN 0 AND 4)

DEFAULT Provides a default value when none is given.

enrollment_date DATE DEFAULT SYSDATE

4.3 ALTER TABLE — Changing Existing Tables

ALTER TABLE

-- Add a new column to existing table

ALTER TABLE students ADD (national_id VARCHAR2(20));
-- Add multiple columns at once
ALTER TABLE students ADD (
date_of_birth DATE,
gender CHAR(1),
address VARCHAR2(200)
);
-- Modify a column (change data type or size)
ALTER TABLE students MODIFY (phone VARCHAR2(30));
-- Rename a column
ALTER TABLE students RENAME COLUMN national_id TO id_number;
-- Add a constraint after table creation
ALTER TABLE students ADD CONSTRAINT chk_gender
CHECK (gender IN ('M', 'F', 'O'));
-- Drop a constraint
ALTER TABLE students DROP CONSTRAINT chk_gender;
-- Drop a column
ALTER TABLE students DROP COLUMN address;
-- Rename a table
RENAME students TO seminary_students;
-- Rename it back
RENAME seminary_students TO students;

4.4 DROP and TRUNCATE — Removing Data and Tables

DROP and TRUNCATE

-- TRUNCATE — removes ALL rows but keeps the table structure

-- Much faster than DELETE for clearing all data
-- Cannot be rolled back (no ROLLBACK after TRUNCATE)
TRUNCATE TABLE students;
-- DROP TABLE — removes the entire table permanently
-- ALL data, structure, indexes, and constraints are gone
DROP TABLE students;
-- DROP with CASCADE CONSTRAINTS
-- Drops table even if other tables have foreign keys pointing to it
DROP TABLE courses CASCADE CONSTRAINTS;
-- DROP only if it exists (prevents error if table doesn't exist)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE students';
EXCEPTION
WHEN OTHERS THEN NULL; -- ignore error if table not found
END;
/
■ WARNING: DROP TABLE is PERMANENT and IRREVERSIBLE. There is no recycle bin by
default in Oracle SQL. Always double-check the table name before running DROP. In production,
never run DROP without a full backup first.
CHAPTER 5 — DML — Working with Data
INSERT, UPDATE, DELETE, and MERGE

5.1 INSERT — Adding Data

INSERT statements

-- First insert courses (students depend on them)

INSERT INTO courses (course_id, course_name, duration_yrs, department)
VALUES (1, 'Theology', 4, 'Religious Studies');
INSERT INTO courses (course_id, course_name, duration_yrs, department)
VALUES (2, 'Philosophy', 3, 'Humanities');
INSERT INTO courses VALUES (3, 'Ethics and Leadership', 3, 'Humanities', SYSDATE);
-- Insert a student
INSERT INTO students
(student_id, student_number, first_name, last_name,
email, phone, course_id, year_of_study, gpa)
VALUES
(1001, 'STU-2024-001', 'Alice', 'Mwangi',
'alice@[Link]', '+255712345678', 1, 2, 3.75);
-- Insert multiple rows at once (Oracle 19c supports this)
INSERT ALL
INTO students (student_id, student_number, first_name, last_name,
email, course_id, year_of_study)
VALUES (1002, 'STU-2024-002', 'Bob', 'Okafor',
'bob@[Link]', 2, 1)
INTO students (student_id, student_number, first_name, last_name,
email, course_id, year_of_study)
VALUES (1003, 'STU-2024-003', 'Carol', 'Sithole',
'carol@[Link]', 1, 3)
SELECT * FROM DUAL;
-- INSERT with subquery (insert results of a query)
-- Copy all year-1 students to an archive table
INSERT INTO students_archive
SELECT * FROM students WHERE year_of_study = 1;

5.2 UPDATE — Changing Existing Data

UPDATE statements

-- Update one student's GPA

UPDATE students
SET gpa = 3.90
WHERE student_id = 1001;
-- Update multiple columns at once
UPDATE students
SET gpa = 3.85,
year_of_study = 3,
phone = '+255723456789'
WHERE student_number = 'STU-2024-001';
-- Update all students in a course
UPDATE students
SET active = 'N'
WHERE course_id = 2
 AND year_of_study = 1;
-- Update using a subquery
-- Give a 0.1 GPA bonus to students in the top course
UPDATE students
SET gpa = gpa + 0.1
WHERE course_id = (
SELECT course_id FROM courses
WHERE course_name = 'Theology'
);

■ WARNING: ALWAYS use a WHERE clause with UPDATE and DELETE. UPDATE students SET
active = 'N'; (without WHERE) will deactivate EVERY SINGLE STUDENT. This is one of the most
costly mistakes in database work. Always add WHERE before running UPDATE or DELETE.

5.3 DELETE — Removing Rows

DELETE statements

-- Delete a specific student

DELETE FROM students
WHERE student_id = 1003;
-- Delete students who have been inactive for more than 2 years
DELETE FROM students
WHERE active = 'N'
AND enrollment_date < ADD_MONTHS(SYSDATE, -24);
-- Delete with subquery
DELETE FROM students
WHERE course_id IN (
SELECT course_id FROM courses
WHERE department = 'Discontinued'
);
-- After DELETE you can still ROLLBACK (unlike TRUNCATE)
ROLLBACK; -- undoes the DELETE if you made a mistake
-- Or confirm it:
COMMIT; -- makes the DELETE permanent

5.4 MERGE — Upsert: Insert or Update in One Statement

MERGE statement

-- MERGE = UPDATE if row exists, INSERT if it does not

-- Very useful for synchronising data from external sources
MERGE INTO students target
USING (
SELECT 1001 AS student_id,
'alice@[Link]' AS email,
3.92 AS gpa
FROM DUAL
) source
ON (target.student_id = source.student_id)
WHEN MATCHED THEN
UPDATE SET [Link] = [Link],
[Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (student_id, email, gpa)
VALUES (source.student_id, [Link], [Link]);
-- If student 1001 exists: updates email and GPA
-- If student 1001 does not exist: inserts a new row
CHAPTER 6 — DQL — The SELECT Statement
The most important SQL command — retrieving data

6.1 The SELECT Statement — Your Most Used Command

The SELECT statement retrieves data from one or more tables. You will use SELECT more than all other
SQL commands combined. Understanding SELECT deeply is the most important SQL skill.

SELECT fundamentals

-- SELECT syntax (clauses in order):

-- SELECT columns
-- FROM table(s)
-- WHERE condition (filter rows)
-- GROUP BY grouping
-- HAVING condition on groups
-- ORDER BY sorting
-- Select all columns from students
SELECT * FROM students;
-- Select specific columns only
SELECT first_name, last_name, email, gpa
FROM students;
-- Column aliases -- give columns friendly names
SELECT
first_name || ' ' || last_name AS full_name,
email AS email_address,
gpa AS grade_point_average
FROM students;
-- Select with expression
SELECT
first_name,
year_of_study,
year_of_study + 1 AS next_year,
gpa * 100 AS gpa_percent
FROM students;
-- DISTINCT -- remove duplicate values
SELECT DISTINCT year_of_study FROM students;
SELECT DISTINCT department FROM courses;

Result:
FULL_NAME EMAIL GPA
------------------ ----------------------- ----
Alice Mwangi alice@[Link] 3.75
Bob Okafor bob@[Link] 0.00

6.2 The WHERE Clause — Filtering Rows

WHERE clause

-- WHERE filters which rows are returned

-- Comparison operators
SELECT * FROM students WHERE year_of_study = 2;
SELECT * FROM students WHERE gpa > 3.5;
SELECT * FROM students WHERE gpa >= 3.0 AND gpa <= 4.0;
 SELECT * FROM students WHERE year_of_study != 1;
SELECT * FROM students WHERE year_of_study <> 1; -- same as !=
-- BETWEEN (inclusive of both ends)
SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.9;
SELECT * FROM students
WHERE enrollment_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';
-- IN (matches any value in the list)
SELECT * FROM students WHERE year_of_study IN (1, 2, 3);
SELECT * FROM courses WHERE department IN ('Humanities', 'Sciences');
-- NOT IN
SELECT * FROM students WHERE year_of_study NOT IN (1, 2);
-- LIKE (pattern matching)
-- % means any number of characters
-- _ means exactly one character
SELECT * FROM students WHERE last_name LIKE 'M%';
-- All students whose last name starts with M
SELECT * FROM students WHERE email LIKE '%@[Link]';
-- All students with [Link] email
SELECT * FROM students WHERE student_number LIKE 'STU-2024-___';
-- STU-2024- followed by exactly 3 characters
-- IS NULL / IS NOT NULL
SELECT * FROM students WHERE phone IS NULL; -- no phone provided
SELECT * FROM students WHERE phone IS NOT NULL; -- phone is provided
-- Combining conditions with AND, OR, NOT
SELECT * FROM students
WHERE course_id = 1
AND year_of_study >= 2
AND gpa > 3.0;
SELECT * FROM students
WHERE (year_of_study = 1 OR year_of_study = 4)
AND active = 'Y';

6.3 ORDER BY — Sorting Results

ORDER BY

-- ORDER BY controls the sort order of results

-- ASC = ascending (A-Z, 0-9, oldest-newest) -- DEFAULT
-- DESC = descending (Z-A, 9-0, newest-oldest)
-- Sort by last name alphabetically
SELECT first_name, last_name, gpa
FROM students
ORDER BY last_name ASC;
-- Sort by GPA highest first
SELECT first_name, last_name, gpa
FROM students
ORDER BY gpa DESC;
-- Sort by multiple columns
SELECT first_name, last_name, year_of_study, gpa
FROM students
ORDER BY year_of_study ASC, gpa DESC;
-- Sorts by year first, then by GPA within same year
-- NULL values: NULLS FIRST or NULLS LAST
SELECT first_name, phone
FROM students
ORDER BY phone NULLS LAST; -- students with no phone appear at end
-- Order by column position (1 = first column in SELECT)
SELECT first_name, last_name, gpa
FROM students
ORDER BY 3 DESC; -- order by 3rd column (gpa) descending
CHAPTER 7 — GROUP BY and HAVING
Aggregating and filtering grouped data

7.1 GROUP BY — Summarising Data

GROUP BY groups rows that have the same value in specified columns, so you can apply aggregate
functions (COUNT, SUM, AVG, MAX, MIN) to each group. Think of it as: 'count/sum/average PER course' or
'PER year'.

GROUP BY basic

-- How many students are in each year?

SELECT year_of_study, COUNT(*) AS total_students
FROM students
GROUP BY year_of_study
ORDER BY year_of_study;

Result:
YEAR_OF_STUDY TOTAL_STUDENTS
------------- -------------
1 45
2 38
3 29
4 12

GROUP BY with multiple aggregates

-- Average GPA per course

SELECT c.course_name,
COUNT(s.student_id) AS total_students,
ROUND(AVG([Link]), 2) AS average_gpa,
MAX([Link]) AS highest_gpa,
MIN([Link]) AS lowest_gpa
FROM students s
JOIN courses c ON s.course_id = c.course_id
GROUP BY c.course_name
ORDER BY average_gpa DESC;

7.2 HAVING — Filtering Groups

WHERE filters individual rows BEFORE grouping. HAVING filters GROUPS AFTER grouping. Use HAVING
when your condition involves an aggregate function.

HAVING clause

-- Show only courses with more than 20 students

SELECT c.course_name, COUNT(s.student_id) AS total
FROM students s
JOIN courses c ON s.course_id = c.course_id
GROUP BY c.course_name
HAVING COUNT(s.student_id) > 20
ORDER BY total DESC;
-- WHERE vs HAVING -- very important distinction
SELECT year_of_study,
ROUND(AVG(gpa), 2) AS avg_gpa
FROM students
WHERE active = 'Y' -- WHERE filters ROWS first
GROUP BY year_of_study
HAVING AVG(gpa) > 3.0 -- HAVING filters GROUPS after
ORDER BY year_of_study;

■ NOTE: Memory trick: WHERE works on individual ROWS (before grouping). HAVING works on
GROUPS (after GROUP BY). You cannot use aggregate functions like AVG() or COUNT() in a WHERE
clause — use HAVING for that.
CHAPTER 8 — SQL Functions
Single-row functions and aggregate functions

8.1 String Functions

String functions

-- UPPER, LOWER, INITCAP

SELECT UPPER('alice mwangi') FROM DUAL; -- ALICE MWANGI
SELECT LOWER('ALICE MWANGI') FROM DUAL; -- alice mwangi
SELECT INITCAP('alice mwangi') FROM DUAL; -- Alice Mwangi
-- LENGTH
SELECT first_name, LENGTH(first_name) AS name_length FROM students;
-- SUBSTR(string, start_position, length)
SELECT SUBSTR('Alice Mwangi', 1, 5) FROM DUAL; -- Alice
SELECT SUBSTR('Alice Mwangi', 7) FROM DUAL; -- Mwangi
SELECT SUBSTR('Alice Mwangi', -6) FROM DUAL; -- Mwangi (from end)
-- INSTR(string, search, start, occurrence) -- find position
SELECT INSTR('alice@[Link]', '@') FROM DUAL; -- 6
-- CONCAT and || operator (concatenation)
SELECT CONCAT(first_name, ' ') || last_name AS full_name FROM students;
SELECT first_name || ' ' || last_name AS full_name FROM students;
-- TRIM, LTRIM, RTRIM
SELECT TRIM(' Alice ') FROM DUAL; -- 'Alice'
SELECT LTRIM(' Alice ') FROM DUAL; -- 'Alice '
SELECT RTRIM(' Alice ') FROM DUAL; -- ' Alice'
-- LPAD, RPAD -- pad with characters
SELECT LPAD(student_id, 8, '0') FROM students; -- 00001001
SELECT RPAD(first_name, 20, '.') FROM students; -- 'Alice...............'
-- REPLACE
SELECT REPLACE('Hello World', 'World', 'Oracle') FROM DUAL; -- Hello Oracle
-- REGEXP_REPLACE (regular expression)
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM students;
-- Removes all non-numeric characters from phone

8.2 Number Functions

Number functions

SELECT ROUND(3.14159, 2) FROM DUAL; -- 3.14

SELECT ROUND(3.75, 0) FROM DUAL; -- 4
SELECT TRUNC(3.99, 0) FROM DUAL; -- 3 (truncate, never rounds up)
SELECT TRUNC(3.99, 1) FROM DUAL; -- 3.9
SELECT CEIL(3.1) FROM DUAL; -- 4 (ceiling -- round up always)
SELECT FLOOR(3.9) FROM DUAL; -- 3 (floor -- round down always)
SELECT ABS(-42) FROM DUAL; -- 42 (absolute value)
SELECT MOD(17, 5) FROM DUAL; -- 2 (remainder: 17 / 5 = 3 r 2)
SELECT POWER(2, 10) FROM DUAL; -- 1024 (2 to the power of 10)
SELECT SQRT(144) FROM DUAL; -- 12
-- Practical use
SELECT first_name,
gpa,
ROUND(gpa * 25, 1) AS score_out_of_100
FROM students;
8.3 Date Functions — Very Important in Oracle
Date functions

-- Current date/time
SELECT SYSDATE FROM DUAL; -- current date and time (server time)
SELECT SYSTIMESTAMP FROM DUAL; -- with timezone and fractional seconds
SELECT CURRENT_DATE FROM DUAL; -- session date (may differ from SYSDATE)
-- Date arithmetic
SELECT SYSDATE + 7 FROM DUAL; -- 7 days from now
SELECT SYSDATE - 30 FROM DUAL; -- 30 days ago
SELECT SYSDATE + (3/24) FROM DUAL; -- 3 hours from now
-- ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL; -- 6 months from now
SELECT ADD_MONTHS(SYSDATE, -12) FROM DUAL; -- 1 year ago
-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE, enrollment_date) AS months_enrolled
FROM students;
-- TRUNC for dates
SELECT TRUNC(SYSDATE) FROM DUAL; -- today at midnight
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; -- first day of this month
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL; -- first day of this year
-- TO_DATE -- convert string to date
SELECT TO_DATE('24-MAR-2026', 'DD-MON-YYYY') FROM DUAL;
SELECT TO_DATE('2026-03-24', 'YYYY-MM-DD') FROM DUAL;
-- TO_CHAR -- convert date to formatted string
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') FROM DUAL; -- 24/03/2026
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI') FROM DUAL; -- 24-MAR-2026 14:30
SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- TUESDAY
SELECT TO_CHAR(SYSDATE, 'MONTH YYYY') FROM DUAL; -- MARCH 2026
-- EXTRACT
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; -- 2026
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 3
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; -- 24

8.4 Conversion and NULL Functions

NULL handling and CASE

-- NVL(value, replace_if_null) -- replace NULL with something

SELECT first_name, NVL(phone, 'No Phone') AS contact
FROM students;
-- Students with no phone show 'No Phone' instead of NULL
-- NVL2(value, if_not_null, if_null)
SELECT first_name,
NVL2(phone, 'Has Phone', 'No Phone') AS phone_status
FROM students;
-- COALESCE -- returns first non-NULL value
SELECT first_name,
COALESCE(mobile, home_phone, work_phone, 'No Contact') AS best_phone
FROM students;
-- NULLIF(val1, val2) -- returns NULL if val1 = val2, else returns val1
SELECT NULLIF(gpa, 0) FROM students; -- returns NULL for students with 0 GPA
-- CASE expression -- like IF/ELSE in SQL
SELECT first_name, gpa,
CASE
WHEN gpa >= 3.7 THEN 'Distinction'
WHEN gpa >= 3.3 THEN 'Merit'
WHEN gpa >= 2.7 THEN 'Pass'
WHEN gpa >= 2.0 THEN 'Satisfactory'
ELSE 'Below Standard'
END AS classification
FROM students
ORDER BY gpa DESC;
-- DECODE -- Oracle's original CASE expression (still widely used)
SELECT first_name, active,
DECODE(active, 'Y', 'Active Student', 'N', 'Inactive', 'Unknown')
AS status
FROM students;
CHAPTER 9 — Joins — Combining Data from Multiple
Tables
The most powerful SQL concept

9.1 Why Joins Exist

In a properly designed relational database, related data is stored in SEPARATE tables to avoid repetition.
The student's course name is stored ONCE in the courses table, not in every student row. Joins let you
COMBINE these tables to get a complete picture.

■ ANALOGY: A join is like looking up a contact in your phone. You have the person's name and their
phone ID number. The actual phone number is in a separate contacts table. A JOIN looks up the ID in the
contacts table and brings back the phone number — combining information from two separate places into
one result.

9.2 INNER JOIN — The Most Common Join

INNER JOIN returns only rows where a matching record exists in BOTH tables. If a student has a course_id
that does not exist in courses, they are NOT returned.

INNER JOIN

-- Show students with their course names

SELECT s.first_name,
s.last_name,
s.year_of_study,
c.course_name,
[Link]
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id
ORDER BY s.last_name;
-- Short form using JOIN (INNER is the default)
SELECT s.first_name || ' ' || s.last_name AS full_name,
c.course_name,
[Link]
FROM students s
JOIN courses c ON s.course_id = c.course_id
WHERE [Link] > 3.5
ORDER BY [Link] DESC;

Result:
FULL_NAME COURSE_NAME GPA
---------------- ------------- ----
Alice Mwangi Theology 3.75
Dave Osei Theology 3.82

9.3 LEFT OUTER JOIN — Include All From Left Table

LEFT JOIN returns ALL rows from the LEFT (first) table, and matching rows from the right table. If no match
in right table, NULLs appear for right table columns. Use this when you want ALL records from one table,
regardless of matches.
LEFT JOIN

-- Show ALL courses, even those with no students enrolled

SELECT c.course_name,
COUNT(s.student_id) AS enrolled_students
FROM courses c
LEFT JOIN students s ON c.course_id = s.course_id
GROUP BY c.course_name
ORDER BY enrolled_students DESC;
-- Show ALL students, with their lecturer's name if assigned
-- (Students without a lecturer still appear)
SELECT s.first_name,
s.last_name,
NVL(l.lecturer_name, 'Not Assigned') AS supervisor
FROM students s
LEFT JOIN lecturers l ON s.lecturer_id = l.lecturer_id;

9.4 RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN
Other join types

-- RIGHT JOIN -- all rows from right table, matching from left
-- (Less common -- you can usually rewrite as a LEFT JOIN)
SELECT s.first_name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.course_id;
-- FULL OUTER JOIN -- all rows from BOTH tables
-- Unmatched rows show NULL for the missing side
SELECT s.first_name, c.course_name
FROM students s
FULL OUTER JOIN courses c ON s.course_id = c.course_id;
-- CROSS JOIN -- every row from left x every row from right
-- Creates a Cartesian product (use carefully!)
-- 5 courses x 3 exam_types = 15 rows
SELECT c.course_name, e.exam_type
FROM courses c
CROSS JOIN exam_types e;
-- SELF JOIN -- a table joining with ITSELF
-- Example: find students and their mentors (both in students table)
SELECT s1.first_name || ' ' || s1.last_name AS student,
s2.first_name || ' ' || s2.last_name AS mentor
FROM students s1
JOIN students s2 ON s1.mentor_id = s2.student_id;
-- NATURAL JOIN -- joins on columns with same name (use with care)
SELECT first_name, course_name
FROM students
NATURAL JOIN courses; -- joins automatically on course_id

Join Type Returns When to Use

INNER JOIN Only rows with matches in BOTH tables

Most common — when you need related data from both tables

LEFT JOIN All from left + matching from right (NULLs

When you
forwant
no match)
all records from one table regardless of matches

RIGHT JOIN All from right + matching from left Same as LEFT JOIN but from right perspective — rare

FULL OUTER JOIN All from BOTH tables (NULLs where

When
no match)
you need all records from both tables

CROSS JOIN Every combination of rows from both

Generating
tables combinations, test data — use carefully

SELF JOIN Table joined to itself Hierarchical data — employees and their managers
CHAPTER 10 — Subqueries
Queries inside queries — powerful SQL technique

10.1 What is a Subquery?

A subquery is a SELECT statement nested inside another SQL statement. The inner query (subquery) runs
first, and its result is used by the outer query. Subqueries allow you to answer complex questions that cannot
be solved in one simple query.

10.2 Single-Row Subquery

Single-row subquery

-- Find students with above-average GPA

SELECT first_name, last_name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students)
ORDER BY gpa DESC;
-- The subquery returns ONE value (the average)
-- The outer query uses that value in WHERE
-- Find students in the same course as Alice Mwangi
SELECT first_name, last_name
FROM students
WHERE course_id = (
SELECT course_id
FROM students
WHERE first_name = 'Alice'
AND last_name = 'Mwangi'
)
AND (first_name != 'Alice' OR last_name != 'Mwangi');

10.3 Multi-Row Subquery — IN, ANY, ALL

Multi-row subquery

-- IN -- matches any value returned by subquery

-- Students enrolled in Humanities department courses
SELECT first_name, last_name
FROM students
WHERE course_id IN (
SELECT course_id
FROM courses
WHERE department = 'Humanities'
);
-- NOT IN -- excludes matching values
SELECT first_name, last_name
FROM students
WHERE student_id NOT IN (
SELECT student_id FROM exam_results WHERE score < 50
);
-- Students who have NEVER scored below 50
-- ANY -- matches if condition is true for ANY subquery value
-- Students with GPA higher than ANY year-1 student
SELECT first_name, gpa FROM students
 WHERE gpa > ANY (SELECT gpa FROM students WHERE year_of_study = 1);
-- Same as: WHERE gpa > (SELECT MIN(gpa) FROM students WHERE year=1)
-- ALL -- condition must be true for ALL subquery values
-- Students with GPA higher than ALL year-1 students
SELECT first_name, gpa FROM students
WHERE gpa > ALL (SELECT gpa FROM students WHERE year_of_study = 1);
-- Same as: WHERE gpa > (SELECT MAX(gpa) FROM students WHERE year=1)

10.4 Correlated Subquery and EXISTS

EXISTS and correlated subqueries

-- EXISTS -- returns true if subquery returns at least one row

-- Students who have at least one exam result
SELECT first_name, last_name
FROM students s
WHERE EXISTS (
SELECT 1
FROM exam_results e
WHERE e.student_id = s.student_id
);
-- NOT EXISTS -- students with NO exam results
SELECT first_name, last_name
FROM students s
WHERE NOT EXISTS (
SELECT 1 FROM exam_results e
WHERE e.student_id = s.student_id
);
-- Subquery in SELECT clause
SELECT s.first_name,
[Link],
(SELECT ROUND(AVG(gpa), 2)
FROM students
WHERE course_id = s.course_id) AS course_avg_gpa
FROM students s;
-- Shows each student's GPA alongside their course average
-- Inline view (subquery in FROM clause)
SELECT course_name, avg_gpa
FROM (
SELECT c.course_name, ROUND(AVG([Link]), 2) AS avg_gpa
FROM students s JOIN courses c ON s.course_id = c.course_id
GROUP BY c.course_name
) course_stats
WHERE avg_gpa > 3.0
ORDER BY avg_gpa DESC;
CHAPTER 11 — DCL and TCL
Security, transactions, and data integrity

11.1 Transactions — Why They Matter

A TRANSACTION is a group of SQL statements that must ALL succeed or ALL fail together. This is called
ATOMICITY — the A in ACID properties. Transactions protect your data from partial updates that would
leave it in a corrupt state.

■ ANALOGY: Imagine transferring money from Account A to Account B: Step 1: Deduct from A. Step 2:
Add to B. If the system crashes after Step 1 but before Step 2, money has disappeared! A transaction
wraps both steps — either BOTH happen, or NEITHER happens.
Transaction control

-- TCL commands control transactions

-- Example: Transfer student between courses (must be atomic)
-- Either BOTH changes happen, or neither
-- Step 1: Remove from old course waitlist
UPDATE course_waitlist
SET status = 'Transferred'
WHERE student_id = 1001 AND course_id = 1;
-- Step 2: Enroll in new course
UPDATE students
SET course_id = 2
WHERE student_id = 1001;
-- Step 3: Log the transfer
INSERT INTO transfer_log (student_id, from_course, to_course, transfer_date)
VALUES (1001, 1, 2, SYSDATE);
-- If all 3 statements succeeded, make them permanent:
COMMIT;
-- If something went wrong, undo ALL changes since last COMMIT:
ROLLBACK;
-- SAVEPOINT -- create a checkpoint within a transaction
SAVEPOINT before_transfer;
UPDATE students SET course_id = 3 WHERE student_id = 1002;
-- Something went wrong...
ROLLBACK TO SAVEPOINT before_transfer; -- undo only back to savepoint
-- The earlier changes are still there
COMMIT; -- commit what remains

■ NOTE: In Oracle, DML statements (INSERT, UPDATE, DELETE) do NOT auto-commit. You must
explicitly COMMIT to save changes permanently. DDL statements (CREATE, DROP, ALTER) DO
auto-commit in Oracle — you cannot roll them back.

11.2 DCL — GRANT and REVOKE

DCL - GRANT and REVOKE

-- GRANT -- give a user permission to do something

-- Grant SELECT on students table to a user
GRANT SELECT ON students TO registrar_user;
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON students TO academic_staff;
-- Grant all privileges on a table
GRANT ALL ON courses TO admin_user;
-- Grant with GRANT OPTION (user can pass the privilege on)
GRANT SELECT ON students TO manager WITH GRANT OPTION;
-- Grant system privilege (create tables, sessions)
GRANT CREATE SESSION TO new_student_user;
GRANT CREATE TABLE TO developer_user;
-- REVOKE -- take back a privilege
REVOKE SELECT ON students FROM registrar_user;
REVOKE INSERT ON students FROM academic_staff;
-- Create a role and grant it
CREATE ROLE student_readonly;
GRANT SELECT ON students TO student_readonly;
GRANT SELECT ON courses TO student_readonly;
-- Now grant role to users
GRANT student_readonly TO alice_user;
GRANT student_readonly TO bob_user;
-- Revoking the role removes all permissions at once
REVOKE student_readonly FROM alice_user;
CHAPTER 12 — Views, Sequences, Synonyms, and Indexes
Advanced database objects

12.1 Views — Virtual Tables

A VIEW is a saved SELECT statement that you can query like a table. The view does not store data — it runs
the SELECT each time you query it. Views simplify complex queries, hide sensitive columns, and provide
security.

Views

-- Create a view for student summary (hide sensitive data)

CREATE OR REPLACE VIEW v_student_summary AS
SELECT s.student_id,
s.student_number,
s.first_name || ' ' || s.last_name AS full_name,
c.course_name,
s.year_of_study,
[Link],
[Link]
FROM students s
JOIN courses c ON s.course_id = c.course_id
WHERE [Link] = 'Y';
-- NOTE: password, national_id, personal info NOT included
-- Now query the view like a table
SELECT * FROM v_student_summary WHERE course_name = 'Theology';
SELECT full_name, gpa FROM v_student_summary WHERE gpa > 3.5;
-- Create a view for academic staff (shows more details)
CREATE OR REPLACE VIEW v_academic_report AS
SELECT c.course_name,
COUNT(s.student_id) AS total_students,
ROUND(AVG([Link]), 2) AS avg_gpa,
MAX([Link]) AS top_gpa
FROM students s JOIN courses c ON s.course_id = c.course_id
GROUP BY c.course_name;
-- Drop a view
DROP VIEW v_student_summary;

12.2 Sequences — Auto-Generating Numbers

A SEQUENCE is an Oracle object that generates unique numbers automatically. You use sequences to
generate primary key values. Oracle does not have AUTO_INCREMENT like MySQL — you use sequences
instead.

Sequences

-- Create a sequence for student IDs

CREATE SEQUENCE seq_student_id
START WITH 1001 -- first value
INCREMENT BY 1 -- increase by 1 each time
MAXVALUE 9999999 -- maximum value
NOCYCLE -- do not restart after maxvalue
CACHE 20; -- pre-generate 20 values for performance
 -- Use sequence in INSERT
INSERT INTO students (student_id, first_name, last_name, email, course_id, year_of_study)
VALUES (seq_student_id.NEXTVAL, 'Eve', 'Nakamura', 'eve@[Link]', 1, 1);
-- NEXTVAL -- gets next value and advances sequence
SELECT seq_student_id.NEXTVAL FROM DUAL;
-- CURRVAL -- gets current value (after NEXTVAL was called)
SELECT seq_student_id.CURRVAL FROM DUAL;
-- Oracle 12c+ Identity Columns (simpler than sequences)
CREATE TABLE new_students (
student_id NUMBER GENERATED ALWAYS AS IDENTITY,
first_name VARCHAR2(50) NOT NULL,
...
);
-- No need to specify student_id in INSERT -- auto-generated

12.3 Indexes — Making Queries Fast

An INDEX is a separate data structure that makes searching faster. Without an index, Oracle reads every
row (FULL TABLE SCAN). With an index, Oracle jumps directly to matching rows (INDEX SCAN). Think of
an index like the index at the back of a book — you go to 'Oracle' in the index to find the page, rather than
reading every page.

Indexes

-- Create a simple index on a frequently searched column

CREATE INDEX idx_students_email ON students(email);
CREATE INDEX idx_students_lastname ON students(last_name);
-- Composite index -- for queries filtering on multiple columns
CREATE INDEX idx_students_course_year
ON students(course_id, year_of_study);
-- Helps queries like: WHERE course_id = 1 AND year_of_study = 2
-- Unique index -- ensures no duplicates (like UNIQUE constraint)
CREATE UNIQUE INDEX idx_students_stunum
ON students(student_number);
-- Drop an index
DROP INDEX idx_students_email;
-- View indexes on a table
SELECT index_name, column_name, uniqueness
FROM user_ind_columns
WHERE table_name = 'STUDENTS';

■ EXPERT: Oracle automatically creates indexes on PRIMARY KEY and UNIQUE constraints.
Create additional indexes on columns you frequently use in WHERE clauses, JOIN conditions,
and ORDER BY. But do not over-index — every index slows down INSERT, UPDATE, and DELETE
because the index must also be updated.
CHAPTER 13 — Oracle 19c Architecture
How Oracle works internally

13.1 Oracle Instance vs Oracle Database

In Oracle, there is a critical distinction between the INSTANCE and the DATABASE:

Component What It Is Lives In

Oracle Database The actual data files on disk — the persistent storage
Hard disk

Oracle Instance Memory structures + background processes that RAM

access
+ CPU
the database

SGA (System Global Area) Shared memory area — buffers and caches usedRAM
by all sessions

PGA (Program Global Area) Private memory area for each user session RAM (per session)

Background Processes Automated processes: SMON, PMON, DBWn, LGWR,

CPU CKPT

Data Files (.dbf) The actual data stored on disk Disk

Control Files Track database structure and history Disk

Redo Log Files Record all changes for recovery Disk

Parameter File (spfile) Database configuration settings Disk

13.2 Key Memory Structures

Memory Area Purpose Why It Matters

Buffer Cache Caches recently used data blocks from disk

Avoids slow disk reads — frequently accessed data served from RAM

Shared Pool Caches parsed SQL and PL/SQL code Avoids re-parsing same queries — significant performance gain

Library Cache Sub-component of Shared Pool — stores Reuses

execution
query
plans
execution plans for repeated queries

Redo Log Buffer Temporarily holds redo (change) records before

Very fast
writing
— writes
to disk
to disk in batches

Large Pool Optional — used for RMAN backups, parallel

Separate
queries
memory for large operations

Java Pool Memory for Java stored procedures in Oracle

Only needed if using Java in Oracle

13.3 Key Background Processes

Process Name What It Does

DBWn Database Writer

Writes modified data blocks from Buffer Cache to data files on disk

LGWR Log Writer Writes redo log buffer to redo log files — critical for recovery

SMON System MonitorPerforms instance recovery on startup after crash. Cleans up temp segments.

PMON Process Monitor

Cleans up after failed user sessions. Releases locks and resources.

CKPT Checkpoint Signals DBWn to write dirty blocks. Updates control files with checkpoint info.

ARCH Archiver Copies redo logs to archive location — enables point-in-time recovery

MMON Manageability Monitor

Collects performance statistics for AWR (Automatic Workload Repository)
CHAPTER 14 — PL/SQL
Oracle's programming language — logic inside the database

14.1 What is PL/SQL?

PL/SQL (Procedural Language / SQL) is Oracle's extension of SQL that adds programming constructs:
variables, conditions (IF/ELSE), loops (FOR, WHILE), error handling (EXCEPTION), and modular code
(procedures, functions, packages). PL/SQL runs INSIDE Oracle — not in your application. It is extremely fast
for data-intensive operations because it does not need to send data back and forth between the database
and the application.

14.2 PL/SQL Block Structure

PL/SQL anonymous block

-- Basic PL/SQL Anonymous Block

-- All PL/SQL code has this DECLARE-BEGIN-EXCEPTION-END structure
DECLARE
-- Variable declarations
v_student_name VARCHAR2(100);
v_gpa NUMBER(3,2);
v_student_id NUMBER := 1001; -- := is assignment in PL/SQL
v_message VARCHAR2(200);
v_count NUMBER(5) := 0;
BEGIN
-- Executable statements
-- SELECT INTO assigns query result to variable
SELECT first_name || ' ' || last_name, gpa
INTO v_student_name, v_gpa
FROM students
WHERE student_id = v_student_id;
-- Conditional logic
IF v_gpa >= 3.7 THEN
v_message := v_student_name || ' achieved Distinction!';
ELSIF v_gpa >= 3.3 THEN
v_message := v_student_name || ' achieved Merit.';
ELSIF v_gpa >= 2.0 THEN
v_message := v_student_name || ' Passed.';
ELSE
v_message := v_student_name || ' needs improvement.';
END IF;
-- Output to console (Oracle LiveSQL shows this)
DBMS_OUTPUT.PUT_LINE(v_message);
DBMS_OUTPUT.PUT_LINE('GPA: ' || v_gpa);
EXCEPTION
-- Error handling
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Student not found: ' || v_student_id);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple students match - refine query');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
 END;
/
-- The / at the end tells Oracle to execute the PL/SQL block

14.3 Loops in PL/SQL

PL/SQL loops

-- FOR loop -- iterate a known number of times

BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Year ' || i || ' students:');
END LOOP;
END;
/
-- WHILE loop -- iterate while condition is true
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
-- Cursor FOR loop -- iterate over query results
-- This is the most common and most useful loop
BEGIN
FOR student_rec IN (
SELECT first_name, last_name, gpa
FROM students
WHERE active = 'Y'
ORDER BY gpa DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
student_rec.first_name || ' ' ||
student_rec.last_name || ': ' ||
student_rec.gpa
);
END LOOP;
END;
/

■ PRACTICE ONLINE: Enable DBMS_OUTPUT in Oracle LiveSQL: In Script Mode, DBMS_OUTPUT

is shown automatically. In SQL Worksheet mode, run SET SERVEROUTPUT ON first.
CHAPTER 15 — Stored Procedures, Functions, and
Packages
Reusable PL/SQL programs

15.1 Stored Procedures — Reusable Operations

Stored procedure

-- A procedure is a named PL/SQL block stored in the database

-- It can be called repeatedly without rewriting the code
CREATE OR REPLACE PROCEDURE enroll_student (
p_student_id IN NUMBER,
p_course_id IN NUMBER,
p_result OUT VARCHAR2
) AS
v_existing NUMBER;
v_course_exists NUMBER;
BEGIN
-- Check course exists
SELECT COUNT(*) INTO v_course_exists
FROM courses WHERE course_id = p_course_id;
IF v_course_exists = 0 THEN
p_result := 'ERROR: Course does not exist';
RETURN;
END IF;
-- Check student not already enrolled
SELECT COUNT(*) INTO v_existing
FROM students
WHERE student_id = p_student_id
AND course_id = p_course_id;
IF v_existing > 0 THEN
p_result := 'ERROR: Student already in this course';
RETURN;
END IF;
-- Perform enrollment
UPDATE students
SET course_id = p_course_id
WHERE student_id = p_student_id;
COMMIT;
p_result := 'SUCCESS: Student enrolled in course ' || p_course_id;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_result := 'ERROR: ' || SQLERRM;
END enroll_student;
/
-- Calling the procedure
DECLARE
v_msg VARCHAR2(200);
BEGIN
enroll_student(1001, 2, v_msg);
DBMS_OUTPUT.PUT_LINE(v_msg);
 END;
/

15.2 Functions — Return a Value

Stored function

-- A function is like a procedure but RETURNS a value

-- Use functions for calculations you want to use in SQL queries
CREATE OR REPLACE FUNCTION get_student_grade (
p_gpa IN NUMBER
) RETURN VARCHAR2 AS
BEGIN
RETURN CASE
WHEN p_gpa >= 3.7 THEN 'Distinction'
WHEN p_gpa >= 3.3 THEN 'Merit'
WHEN p_gpa >= 2.7 THEN 'Pass'
WHEN p_gpa >= 2.0 THEN 'Satisfactory'
ELSE 'Fail'
END;
END get_student_grade;
/
-- Use the function in a SQL query
SELECT first_name, last_name, gpa,
get_student_grade(gpa) AS grade_classification
FROM students
ORDER BY gpa DESC;
CHAPTER 16 — Triggers
Automatic actions when data changes

16.1 What is a Trigger?

A TRIGGER is a PL/SQL block that Oracle executes AUTOMATICALLY when a specific event occurs —
usually INSERT, UPDATE, or DELETE on a table. You do not call a trigger — it fires by itself when the event
happens.

• Use triggers for: automatic audit logging, enforcing complex business rules, auto-populating derived
columns, maintaining data consistency.
Triggers

-- Trigger to log all changes to student GPA

CREATE OR REPLACE TRIGGER trg_gpa_audit
AFTER UPDATE OF gpa ON students
FOR EACH ROW
BEGIN
INSERT INTO gpa_audit_log (
student_id, old_gpa, new_gpa,
changed_by, changed_at
) VALUES (
:OLD.student_id,
:[Link], -- :OLD = values BEFORE the change
:[Link], -- :NEW = values AFTER the change
USER, -- Oracle function: current logged-in user
SYSDATE
);
END trg_gpa_audit;
/
-- BEFORE trigger -- validate or modify data BEFORE it is saved
CREATE OR REPLACE TRIGGER trg_validate_gpa
BEFORE INSERT OR UPDATE OF gpa ON students
FOR EACH ROW
BEGIN
-- Auto-correct negative GPA to 0
IF :[Link] < 0 THEN
:[Link] := 0;
END IF;
-- Auto-cap GPA at 4.0
IF :[Link] > 4.0 THEN
:[Link] := 4.0;
END IF;
END;
/
-- Trigger for automatic student number generation
CREATE OR REPLACE TRIGGER trg_auto_student_number
BEFORE INSERT ON students
FOR EACH ROW
WHEN (NEW.student_number IS NULL) -- only if not provided
BEGIN
:NEW.student_number :=
'STU-' || TO_CHAR(SYSDATE, 'YYYY') ||
'-' || LPAD(seq_student_id.NEXTVAL, 4, '0');
END;
/
CHAPTER 17 — Oracle Security
Users, roles, privileges, and database hardening

17.1 Oracle User Management

User management

-- Create a new database user

CREATE USER seminary_admin
IDENTIFIED BY 'SecureP@ss2024!'
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;
-- User cannot do anything without privileges
-- Grant minimum required privileges
GRANT CREATE SESSION TO seminary_admin; -- allow login
GRANT CREATE TABLE TO seminary_admin; -- allow creating tables
GRANT CREATE VIEW TO seminary_admin;
GRANT CREATE PROCEDURE TO seminary_admin;
GRANT CREATE SEQUENCE TO seminary_admin;
-- Change password
ALTER USER seminary_admin IDENTIFIED BY 'NewP@ss2024!';
-- Lock and unlock users
ALTER USER seminary_admin ACCOUNT LOCK; -- lock (prevent login)
ALTER USER seminary_admin ACCOUNT UNLOCK; -- unlock
-- Set password expiry
ALTER USER seminary_admin PASSWORD EXPIRE; -- force password change on next login
-- Drop a user (CASCADE removes all their objects too)
DROP USER seminary_admin CASCADE;
-- View all users
SELECT username, account_status, created FROM dba_users;
SELECT username, account_status, created FROM all_users;

17.2 Oracle Privilege Hierarchy

Privilege Level What It Controls Examples

System Privileges Actions on the database itself CREATE TABLE, CREATE USER, CREATE SESSION, DROP ANY TABLE

Object Privileges Actions on specific objects SELECT ON students, INSERT ON courses, EXECUTE ON procedure

Role Named collection of privileges thatDBA,

can be
CONNECT,
granted together
RESOURCE, custom roles you create

■ SECURITY: Oracle security principle: Grant the MINIMUM privileges needed. Never grant DBA
role to application users. Create specific roles for specific tasks. Audit all privilege grants with:
SELECT * FROM dba_sys_privs WHERE grantee = 'USER'; Revoke unused privileges immediately.
Review grants quarterly.
CHAPTER 18 — Backup, Recovery, and RMAN
Protecting your data

18.1 Why Backup is Critical

Data is the most valuable asset in any organisation. Hardware fails, humans make mistakes, disasters
happen, ransomware encrypts everything. Without a backup strategy, a single incident can destroy years of
data. Oracle provides powerful backup and recovery tools — RMAN (Recovery Manager) is the primary one.

18.2 RMAN — Recovery Manager

RMAN backup commands

-- Connect to RMAN
$ rman TARGET /
-- Full database backup
RMAN> BACKUP DATABASE;
-- Full backup with compression
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
-- Incremental backup (only changed blocks since last backup)
-- Level 0 = full baseline
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
-- Level 1 = changes since last level 0 or 1
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
-- Backup specific tablespace
RMAN> BACKUP TABLESPACE users;
-- Backup to specific location
RMAN> BACKUP DATABASE FORMAT '/backup/db_%d_%T_%[Link]';
-- Verify backup is usable
RMAN> RESTORE DATABASE VALIDATE;
-- List all backups
RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;
-- Delete old backups (older than 7 days)
RMAN> DELETE BACKUP COMPLETED BEFORE 'SYSDATE-7';

Backup Type What It Does When to Use

Full Backup Complete copy of entire database Weekly or before major changes

Incremental Level 0 Full backup used as baseline for incrementals Start of incremental backup cycle

Incremental Level 1 Only blocks changed since last Level 0 or LevelDaily

1 — much faster than full backup

Archived Log Backup Backs up archived redo logs for point-in-time recovery
After each archived log is generated

Tablespace Backup Backs up specific tablespace only When specific data changes frequently

Control File Backup Backs up the database control file After any structural change to database
CHAPTER 19 — Performance Optimisation
Making Oracle queries faster

19.1 Understanding Query Execution Plans

When you run a SQL query, Oracle creates an EXECUTION PLAN — the step-by-step strategy it uses to
retrieve the data. Understanding execution plans tells you WHY a query is slow and how to fix it.

Execution plans

-- EXPLAIN PLAN -- show how Oracle will execute a query

EXPLAIN PLAN FOR
SELECT s.first_name, s.last_name, c.course_name
FROM students s
JOIN courses c ON s.course_id = c.course_id
WHERE [Link] > 3.5;
-- View the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- AUTOTRACE -- automatic execution plan + statistics
SET AUTOTRACE ON;
SELECT * FROM students WHERE email LIKE '%@[Link]';
SET AUTOTRACE OFF;
-- What to look for in execution plan:
-- TABLE ACCESS FULL = full table scan (often means missing index)
-- INDEX RANGE SCAN = using index (good for selective queries)
-- INDEX UNIQUE SCAN = using unique index (fastest)
-- NESTED LOOPS = join method (good for small tables)
-- HASH JOIN = join method (good for large tables)

19.2 Common Performance Problems and Fixes

Problem Symptom Solution

Full table scan TABLE ACCESS FULL in plan, slow

Addonindex
largeon
tables
WHERE/JOIN column

Missing index on join Slow joins between large tables Add index on foreign key column

Implicit conversion Column converted in WHERE: WHERE

MatchTO_NUMBER(col)=5
data types: store numbers as numbers, not strings

SELECT * Fetching all columns when only 2 needed

Select only the columns you need

N+1 queries Application runs 1 query + N moreUse

in aJOINs
loop or subqueries to get all data in one query

Unindexed LIKE LIKE '%keyword%' — leading wildcard

Use LIKE
prevents
'keyword%'
index use
(no leading %) to allow index, or Oracle Text

No statistics Optimiser makes wrong choices without

EXEC current
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE')
stats

Too many commits Committing every single row in a loop

Batch
is commits:
slow COMMIT every 1000 rows
CHAPTER 20 — Real Project: Seminary Database Design
Apply everything you have learned

20.1 Complete Seminary Database Schema

Complete seminary schema

-- ============================================================
-- KASITA SEMINARY MANAGEMENT SYSTEM
-- Complete Database Schema
-- ============================================================
-- 1. Departments
CREATE TABLE departments (
dept_id NUMBER(5) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
dept_name VARCHAR2(100) NOT NULL,
dept_head VARCHAR2(100)
);
-- 2. Courses
CREATE TABLE courses (
course_id NUMBER(5) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
course_code VARCHAR2(10) UNIQUE NOT NULL,
course_name VARCHAR2(100) NOT NULL,
duration_yrs NUMBER(1) NOT NULL,
dept_id NUMBER(5) REFERENCES departments(dept_id),
max_students NUMBER(5) DEFAULT 50
);
-- 3. Lecturers
CREATE TABLE lecturers (
lecturer_id NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
staff_number VARCHAR2(20) UNIQUE NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE NOT NULL,
dept_id NUMBER(5) REFERENCES departments(dept_id),
hire_date DATE DEFAULT SYSDATE
);
-- 4. Students
CREATE TABLE students (
student_id NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_number VARCHAR2(20) UNIQUE NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE NOT NULL,
phone VARCHAR2(20),
date_of_birth DATE,
gender CHAR(1) CHECK (gender IN ('M','F','O')),
course_id NUMBER(5) NOT NULL REFERENCES courses(course_id),
year_of_study NUMBER(1) NOT NULL CHECK (year_of_study BETWEEN 1 AND 7),
gpa NUMBER(3,2) DEFAULT 0.00 CHECK (gpa BETWEEN 0 AND 4),
active CHAR(1) DEFAULT 'Y' CHECK (active IN ('Y','N')),
enrollment_date DATE DEFAULT SYSDATE,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- 5. Subjects (many-to-many with students)
CREATE TABLE subjects (
subject_id NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
subject_code VARCHAR2(10) UNIQUE NOT NULL,
subject_name VARCHAR2(100) NOT NULL,
credits NUMBER(2) DEFAULT 3,
lecturer_id NUMBER(10) REFERENCES lecturers(lecturer_id)
);
-- 6. Student Subjects (Enrollment pivot table)
CREATE TABLE student_subjects (
enrollment_id NUMBER(15) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_id NUMBER(10) NOT NULL REFERENCES students(student_id),
subject_id NUMBER(10) NOT NULL REFERENCES subjects(subject_id),
semester NUMBER(1) NOT NULL,
academic_year CHAR(9) NOT NULL, -- e.g. '2024/2025'
enrolled_date DATE DEFAULT SYSDATE,
CONSTRAINT uq_student_subject UNIQUE (student_id, subject_id, academic_year)
);
-- 7. Exam Results
CREATE TABLE exam_results (
result_id NUMBER(15) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_id NUMBER(10) NOT NULL REFERENCES students(student_id),
subject_id NUMBER(10) NOT NULL REFERENCES subjects(subject_id),
exam_type VARCHAR2(20) NOT NULL, -- 'CAT1','CAT2','FINAL'
score NUMBER(5,2) CHECK (score BETWEEN 0 AND 100),
exam_date DATE DEFAULT SYSDATE,
academic_year CHAR(9) NOT NULL
);
-- 8. Indexes for performance
CREATE INDEX idx_students_course ON students(course_id);
CREATE INDEX idx_students_active ON students(active);
CREATE INDEX idx_exam_student ON exam_results(student_id);
CREATE INDEX idx_enrollment ON student_subjects(student_id, subject_id);
-- 9. Useful view
CREATE OR REPLACE VIEW v_student_full AS
SELECT s.student_number,
s.first_name || ' ' || s.last_name AS full_name,
c.course_name, c.course_code,
s.year_of_study, [Link],
get_student_grade([Link]) AS grade,
[Link],
s.enrollment_date
FROM students s
JOIN courses c ON s.course_id = c.course_id;

■ PROJECT: Take this schema and build it on Oracle LiveSQL or Oracle APEX. Then: insert real
sample data, write all the queries from this book against it, create procedures to enroll students,
and create triggers for audit logging. This one project covers every concept in this entire book.
CHAPTER 21 — Complete SQL + Oracle Cheat Sheet

SQL Categories Quick Reference

Category Commands What They Do

DDL CREATE, ALTER, DROP, TRUNCATE, RENAME

Define and manage database structure

DML INSERT, UPDATE, DELETE, MERGE Add, change, and remove data

DQL SELECT Retrieve and query data

DCL GRANT, REVOKE Control access and permissions

TCL COMMIT, ROLLBACK, SAVEPOINT Manage transactions

Essential Oracle Functions

Category Function Example Result

String UPPER(x) UPPER('alice') ALICE

String LOWER(x) LOWER('ALICE') alice

String INITCAP(x) INITCAP('alice mwangi') Alice Mwangi

String LENGTH(x) LENGTH('Alice') 5

String SUBSTR(x,s,n) SUBSTR('Alice',1,3) Ali

String INSTR(x,s) INSTR('alice@[Link]','@') 6

String TRIM(x) TRIM(' Alice ') Alice

String REPLACE(x,s,r) REPLACE('Hello','Hello','Hi') Hi

Number ROUND(x,d) ROUND(3.567,2) 3.57

Number TRUNC(x,d) TRUNC(3.999,1) 3.9

Number CEIL(x) CEIL(3.1) 4

Number FLOOR(x) FLOOR(3.9) 3

Number MOD(x,y) MOD(17,5) 2

Number ABS(x) ABS(-42) 42

Date SYSDATE SELECT SYSDATE FROM DUAL Current date+time

Date ADD_MONTHS(d,n) ADD_MONTHS(SYSDATE,3) 3 months from now

Date MONTHS_BETWEEN(d1,d2)
MONTHS_BETWEEN(d1,d2) Number of months between

Date TO_DATE(s,f) TO_DATE('2024-01-15','YYYY-MM-DD') Date value

Date TO_CHAR(d,f) TO_CHAR(SYSDATE,'DD/MM/YYYY') '24/03/2026'

Date EXTRACT(p FROM d)EXTRACT(YEAR FROM SYSDATE) 2026

NULL NVL(x,y) NVL(phone,'None') 'None' if phone is NULL

NULL NVL2(x,a,b) NVL2(phone,'Has','No') 'Has' if not null

NULL COALESCE(x,y,z) COALESCE(a,b,c,'default') First non-NULL value

Aggregate COUNT(*) COUNT(*) FROM students Total number of rows

Aggregate SUM(x) SUM(credits) Total sum

Aggregate AVG(x) AVG(gpa) Average value

 Aggregate MAX(x) MAX(gpa) Highest value

Aggregate MIN(x) MIN(gpa) Lowest value

JOIN Types Quick Reference

JOIN Type Returns Syntax

INNER JOIN Matching rows in both tables FROM a INNER JOIN b ON [Link] = [Link]

LEFT JOIN All from left + matches from right FROM a LEFT JOIN b ON [Link] = [Link]

RIGHT JOIN All from right + matches from left FROM a RIGHT JOIN b ON [Link] = [Link]

FULL OUTER JOIN All from both tables FROM a FULL OUTER JOIN b ON [Link] = [Link]

CROSS JOIN Every combination FROM a CROSS JOIN b

SELF JOIN Table with itself FROM a a1 JOIN a a2 ON a1.mgr_id = a2.emp_id

Online Practice Resources

Platform URL Best For

Oracle LiveSQL [Link] PRIMARY — Real Oracle 19c. Free. Start here.

Oracle APEX [Link] Full Oracle database. PL/SQL. Projects.

Oracle Dev Gym [Link] Structured quizzes and SQL challenges.

DB Fiddle [Link] Quick SQL testing across multiple DBs.

HackerRank SQL [Link]/domains/sql SQL challenges with auto-grading.

LeetCode DB [Link]/problemset/database Advanced SQL interview problems.

W3Schools SQL [Link]/sql Basic concepts with interactive editor.

Oracle Docs [Link]/en/database/oracle/oracle-database/19

Official Oracle 19c documentation.

Ask TOM [Link] Expert Oracle Q&A from Oracle's own team.

Oracle Tutorial [Link] Step-by-step Oracle and SQL tutorials.

You now have a complete Oracle 19c and SQL professional guide.
The path to mastery: Read a chapter → Open Oracle LiveSQL → Type every example →
Modify it → Build the seminary project.

SQL is learned by DOING, not by reading. Every example in this book works on Oracle
LiveSQL — open it now!