Oracle & PL/SQL
FPSC Lecturer Computer Science — Study Notes
7 High-Yield Subtopics • Concepts, Syntax & Exam Traps
Contents
1. SQL Command Categories (DDL / DML / DCL / TCL)
2. SELECT, JOINs, GROUP BY / HAVING
3. Keys (Primary/Foreign) & Normalization (1NF–BCNF)
4. PL/SQL Block Structure (DECLARE / BEGIN / EXCEPTION / END)
5. Procedures vs Functions vs Triggers
6. Cursors (Implicit / Explicit)
7. Transactions & ACID Properties
1. SQL Command Categories (DDL / DML / DCL / TCL)
OVERVIEW
SQL commands are grouped into four functional categories based on what they do to the database. FPSC loves testing "which
category does X command belong to" as a direct MCQ.
KEY POINTS
• DDL (Data Definition Language): defines/modifies the STRUCTURE of database objects. Commands: CREATE, ALTER,
DROP, TRUNCATE, RENAME. DDL statements are auto-committed (cannot be rolled back in Oracle).
• DML (Data Manipulation Language): manipulates the DATA inside tables. Commands: INSERT, UPDATE, DELETE,
(SELECT is sometimes classified separately as DQL - Data Query Language). DML changes CAN be rolled back until committed.
• DCL (Data Control Language): controls ACCESS/PERMISSIONS. Commands: GRANT, REVOKE.
• TCL (Transaction Control Language): manages TRANSACTIONS. Commands: COMMIT, ROLLBACK, SAVEPOINT.
SYNTAX / EXAMPLE
CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50)); -- DDL
ALTER TABLE Student ADD Age INT; -- DDL
INSERT INTO Student VALUES (1, 'Ali', 22); -- DML
UPDATE Student SET Age = 23 WHERE ID = 1; -- DML
DELETE FROM Student WHERE ID = 1; -- DML
GRANT SELECT ON Student TO user2; -- DCL
REVOKE SELECT ON Student FROM user2; -- DCL
COMMIT; -- TCL
ROLLBACK; -- TCL
⚠ EXAM TIP / COMMON TRAP
TRUNCATE is DDL (removes all rows, cannot be rolled back, resets identity, is FAST) — students constantly misclassify it as
DML because it "deletes data." DELETE is DML (row-by-row, can be rolled back, slower, does NOT reset identity/auto-increment).
This TRUNCATE vs DELETE distinction is one of the most frequently tested Oracle MCQs in FPSC-style papers.
2. SELECT, JOINs, GROUP BY / HAVING
OVERVIEW
SELECT is the core data retrieval statement; JOINs combine rows from multiple tables; GROUP BY/HAVING enable aggregate
analysis. This is the highest-frequency "write/identify the query" area.
KEY POINTS
• Basic SELECT syntax order (as WRITTEN): SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY.
• Logical execution order (how the DB actually processes it) DIFFERS from written order: FROM -> WHERE -> GROUP BY ->
HAVING -> SELECT -> ORDER BY. (This exact contrast is a favorite FPSC trap.)
• INNER JOIN: returns only matching rows in both tables.
• LEFT (OUTER) JOIN: all rows from left table + matched rows from right (NULL if no match).
• RIGHT (OUTER) JOIN: all rows from right table + matched rows from left.
• FULL OUTER JOIN: all rows from both tables, NULLs where no match on either side.
• CROSS JOIN: Cartesian product (every row of table A paired with every row of table B).
• SELF JOIN: a table joined with itself (using aliases), used for hierarchical data (e.g. employee-manager).
• WHERE filters INDIVIDUAL ROWS before grouping; HAVING filters GROUPS after aggregation — WHERE cannot use
aggregate functions (SUM, COUNT, AVG), HAVING can.
• DISTINCT removes duplicate rows from the result.
SYNTAX / EXAMPLE
SELECT Dept, COUNT(*) AS TotalEmp, AVG(Salary) AS AvgSal
FROM Employee
WHERE Status = 'Active'
GROUP BY Dept
HAVING COUNT(*) > 5
ORDER BY AvgSal DESC;
SELECT [Link], [Link]
FROM Employee E
INNER JOIN Department D ON [Link] = [Link];
⚠ EXAM TIP / COMMON TRAP
The #1 trap — using an aggregate function (e.g. COUNT(*) > 5) inside a WHERE clause instead of HAVING. WHERE executes
BEFORE grouping happens, so it has no concept of a "group" yet; only HAVING can filter on aggregated values. Also: LEFT JOIN
vs RIGHT JOIN direction is commonly swapped as a distractor — "LEFT" always means "keep everything from the table written
FIRST/LEFT of the JOIN keyword."
3. Keys (Primary/Foreign) & Normalization (1NF-BCNF)
OVERVIEW
Keys enforce uniqueness and relationships between tables; normalization is the step-by-step process of structuring tables to
eliminate redundancy and anomalies. This is consistently one of the highest-yield database theory areas.
KEY POINTS
• Primary Key: uniquely identifies each row; CANNOT be NULL; only ONE primary key per table (though it can be
composite/multi-column).
• Candidate Key: any column (or set of columns) that COULD qualify as a primary key (unique + not null); a table can have
multiple candidate keys, one is chosen as the primary key.
• Foreign Key: a column that references a Primary Key in ANOTHER table; enforces referential integrity; CAN contain NULLs
and CAN contain duplicates.
• Composite Key: a primary key made of TWO OR MORE columns combined.
• Unique Key: enforces uniqueness like a primary key, but CAN accept one NULL value (Oracle-specific nuance) and a table can
have MULTIPLE unique keys.
• Normalization progression (strictly sequential, cumulative):
◦ 1NF: eliminate repeating groups — every column holds a single ATOMIC (indivisible) value, no arrays/multi-values in one
cell.
◦ 2NF: (must satisfy 1NF) eliminate PARTIAL dependency — every non-key attribute depends on the WHOLE composite
primary key, not just part of it. Only relevant if PK is composite.
◦ 3NF: (must satisfy 2NF) eliminate TRANSITIVE dependency — no non-key column depends on ANOTHER non-key
column; every non-key column depends ONLY, DIRECTLY on the primary key.
◦ BCNF: stricter version of 3NF — every determinant (any column that determines another column's value) MUST itself be a
candidate key.
• Denormalization: the deliberate REVERSE process (re-introducing redundancy) done for performance/reporting reasons.
SYNTAX / EXAMPLE
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
⚠ EXAM TIP / COMMON TRAP
Partial Dependency (2NF issue) vs Transitive Dependency (3NF issue) is THE classic confusion. Partial dependency can ONLY
happen when the primary key is COMPOSITE (multi-column) — if a table has a single-column PK, it automatically satisfies 2NF
once it satisfies 1NF. Transitive dependency involves a chain: PK -> Non-Key-A -> Non-Key-B (B depends on A, not directly on
PK) — this is what 3NF removes.
4. PL/SQL Block Structure (DECLARE / BEGIN / EXCEPTION / END)
OVERVIEW
Every PL/SQL program is built from a block — a logical unit that groups related declarations and statements. FPSC often tests
which keywords are MANDATORY vs OPTIONAL in this structure.
KEY POINTS
• Structure: DECLARE (optional) -> BEGIN (mandatory) -> EXCEPTION (optional) -> END; (mandatory).
• DECLARE section: variable, constant, cursor, and exception declarations — this ENTIRE section is optional; if there's nothing
to declare, it's simply omitted.
• BEGIN section: contains the actual executable SQL/PL-SQL statements — this is MANDATORY, every block must have one.
• EXCEPTION section: handles runtime errors that occur in the BEGIN section — OPTIONAL, but critical for robust error
handling.
• END: terminates the block, always followed by a semicolon (END;) — MANDATORY.
• Anonymous Block: a PL/SQL block with NO name; not stored in the database; compiled and executed once, then discarded;
cannot be called/reused later.
• Named Block (Subprogram): Procedures, Functions, Packages, Triggers — these ARE stored permanently in the database and
can be called repeatedly by name.
• Nested Blocks: a block can be placed inside the executable section of another block.
SYNTAX / EXAMPLE
DECLARE
v_salary NUMBER;
BEGIN
SELECT Salary INTO v_salary FROM Employee WHERE ID = 101;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
⚠ EXAM TIP / COMMON TRAP
Students often think DECLARE is mandatory because it's listed first — it is NOT; only BEGIN and END are mandatory in every
PL/SQL block. Also, SQL is DECLARATIVE (you state WHAT you want), while PL/SQL is PROCEDURAL (you state HOW to
do it step-by-step with variables/loops/conditionals) — this exact distinction is a recurring conceptual MCQ.
5. Procedures vs Functions vs Triggers
OVERVIEW
These are the three named, reusable PL/SQL program units stored in the database, but each is invoked differently and serves a
distinct purpose — FPSC frequently tests direct comparisons between all three.
KEY POINTS
• Stored Procedure: a named PL/SQL block that performs an action; does NOT have to return a value (though it can return values
via OUT parameters); called explicitly using EXEC or CALL; CANNOT be used directly inside a SQL SELECT statement.
• Function: a named PL/SQL block that MUST return exactly ONE value via a RETURN statement; can be called from within a
SQL expression/SELECT statement (e.g., SELECT my_function(x) FROM dual); typically used for calculations/derived values.
• Trigger: a named PL/SQL block that executes AUTOMATICALLY in response to a specified DML event (INSERT, UPDATE,
DELETE) on a table — it is NEVER called manually by the programmer.
• Trigger timing: BEFORE trigger (fires before the DML event completes, can modify/validate data before it's written) vs AFTER
trigger (fires after the DML event completes, e.g., for logging/auditing).
• Trigger level: Row-level trigger (fires once PER ROW affected, uses FOR EACH ROW) vs Statement-level trigger (fires ONCE
per statement, regardless of how many rows are affected).
• Inside a row-level trigger, :NEW refers to the new/incoming value of a column, :OLD refers to the value before the change
(available for UPDATE/DELETE).
SYNTAX / EXAMPLE
-- Procedure
CREATE OR REPLACE PROCEDURE GiveRaise (p_id IN NUMBER, p_amount IN NUMBER) AS
BEGIN
UPDATE Employee SET Salary = Salary + p_amount WHERE ID = p_id;
END;
-- Function
CREATE OR REPLACE FUNCTION GetSalary (p_id IN NUMBER) RETURN NUMBER AS
v_sal NUMBER;
BEGIN
SELECT Salary INTO v_sal FROM Employee WHERE ID = p_id;
RETURN v_sal;
END;
-- Trigger
CREATE OR REPLACE TRIGGER trg_AuditSalary
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
INSERT INTO SalaryLog VALUES (:[Link], :[Link], :[Link], SYSDATE);
END;
⚠ EXAM TIP / COMMON TRAP
The single most-tested distinction — a Function MUST return a value and can sit inside a SELECT statement; a Procedure does
NOT have to return a value and CANNOT be used inside a SELECT statement. Separately, a Trigger is NEVER manually invoked
— if an MCQ option says "called using EXEC," that rules it out as a trigger every time.
6. Cursors (Implicit / Explicit)
OVERVIEW
A cursor is a pointer/context area that lets PL/SQL process the result of a multi-row SQL query one row at a time. FPSC tests both
the conceptual difference and the exact lifecycle syntax.
KEY POINTS
• Implicit Cursor: automatically created and managed by Oracle for EVERY DML statement (INSERT/UPDATE/DELETE) and
single-row SELECT INTO — the programmer does not declare it. Referred to internally as "SQL" (e.g., SQL%ROWCOUNT).
• Explicit Cursor: manually DECLARED by the programmer to handle a query that returns MULTIPLE rows, giving full control
over row-by-row processing.
• Explicit cursor lifecycle (exact order, always tested): DECLARE the cursor -> OPEN the cursor -> FETCH rows (usually in a
LOOP) -> CLOSE the cursor.
• Cursor attributes (usable on both types): %FOUND (TRUE if last fetch returned a row), %NOTFOUND (TRUE if last fetch
returned no row — commonly used as the loop-exit condition), %ROWCOUNT (number of rows processed so far), %ISOPEN
(TRUE if cursor is currently open).
• Cursor FOR loop: a shortcut syntax that automatically opens, fetches every row, and closes the cursor — no manual
OPEN/FETCH/CLOSE needed.
SYNTAX / EXAMPLE
DECLARE
CURSOR emp_cursor IS SELECT Name, Salary FROM Employee WHERE Dept = 'IT';
v_name [Link]%TYPE;
v_sal [Link]%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_sal);
END LOOP;
CLOSE emp_cursor;
END;
⚠ EXAM TIP / COMMON TRAP
Students forget the mandatory ORDER — you cannot FETCH from a cursor that hasn't been OPENed, and failing to CLOSE a
cursor wastes memory (a common "best practice" MCQ). Also: a plain "SELECT ... INTO ..." for a SINGLE row uses an IMPLICIT
cursor automatically — students wrongly assume any SELECT needs an explicit cursor; explicit cursors are needed ONLY when a
query can return MULTIPLE rows.
7. Transactions & ACID Properties
OVERVIEW
A transaction is a logical, indivisible unit of work in a database; the ACID properties are the four guarantees that make
transactions reliable — this is one of the most conceptually tested DBMS theory blocks.
KEY POINTS
• Transaction: a sequence of one or more SQL operations treated as a SINGLE logical unit — either ALL operations succeed
(commit) or NONE do (rollback).
• Atomicity: the transaction is "all or nothing" — if any part fails, the ENTIRE transaction is rolled back as if nothing happened.
• Consistency: a transaction takes the database from one VALID state to another valid state, never leaving it in a
broken/inconsistent state (all constraints/rules must hold before and after).
• Isolation: concurrently executing transactions do NOT interfere with each other's intermediate (uncommitted) states — each
transaction behaves as if it's running alone.
• Durability: once a transaction is COMMITTED, its changes are PERMANENT and survive even a system crash/power failure
(written to persistent storage).
• COMMIT: permanently saves all changes made in the current transaction; cannot be undone after this.
• ROLLBACK: undoes all changes made since the last COMMIT (or since the start of the transaction).
• SAVEPOINT: marks an intermediate point within a transaction, allowing a PARTIAL rollback to that specific point rather than
undoing the entire transaction.
SYNTAX / EXAMPLE
BEGIN
UPDATE Account SET Balance = Balance - 500 WHERE AccID = 1;
SAVEPOINT after_withdraw;
UPDATE Account SET Balance = Balance + 500 WHERE AccID = 2;
-- ROLLBACK TO after_withdraw; (would undo only the deposit, keep the withdrawal)
COMMIT;
END;
⚠ EXAM TIP / COMMON TRAP
The classic exam scenario is the "bank transfer" example (debit one account, credit another) used to illustrate WHY Atomicity
matters — if the credit step fails after the debit succeeds, Atomicity ensures the whole transaction rolls back so money isn't lost.
Also, students frequently mix up Consistency (validity of DATA/constraints) with Isolation (independence of CONCURRENT
transactions) — Consistency is about data correctness rules; Isolation is about concurrency control.