Unit 1: Introduction to RDBMS [10 Hours]
This unit lays the foundation for working with Oracle databases. It covers the architecture of Oracle DBMS, how to interact with it using SQL*Plus, and the essential SQL command categories that power every database operation — from creating tables to querying data with joins and subqueries. A strong grasp of this unit is essential because every later topic builds directly on these concepts.
1.1 Overview of Oracle Database Architecture
Core Definition
Oracle Database Architecture refers to the internal structural organization of an Oracle DBMS, comprising memory structures, background processes, and physical storage components that work together to store, manage, and retrieve data efficiently.
Think of Oracle as a busy office building. The filing cabinets are the physical files on disk. The employees are background processes doing specific jobs. The shared office space (memory) is where everyone temporarily places documents they are working on. The building manager coordinates everything so data gets in and out without confusion.
Detailed Explanation
Oracle's architecture is divided into three main layers:
1. Memory Structures
- System Global Area (SGA): A shared memory region allocated when the database instance starts. It holds the database buffer cache (recently used data blocks), shared pool (parsed SQL and data dictionary cache), and redo log buffer (records of all changes).
- Program Global Area (PGA): Private memory allocated for each individual server process. Holds session-specific data like sort areas and cursor state.
2. Background Processes
- DBWR (Database Writer): Writes modified data blocks from buffer cache to data files.
- LGWR (Log Writer): Writes redo log entries from the redo log buffer to redo log files.
- SMON (System Monitor): Handles instance recovery on startup.
- PMON (Process Monitor): Cleans up failed user processes and releases resources.
- CKPT (Checkpoint): Signals DBWR to write dirty blocks and updates file headers.
3. Physical Storage
- Data Files (.dbf): Store actual table data and indexes.
- Control Files: Hold metadata about the database structure.
- Redo Log Files: Record all changes for recovery purposes.
An Oracle Instance = SGA + Background Processes. An Oracle Database = Instance + Physical Files. This distinction matters: you connect to an instance, but your data lives in the database.
Let's Break It Down
Picture a restaurant kitchen. The SGA is the counter where orders are temporarily placed and ingredients are prepped. The background processes are the cooks, dishwasher, and manager doing their specific roles continuously. The data files are the walk-in freezer where everything is permanently stored. When a customer (user) places an order (query), the waiter (server process) coordinates with the kitchen staff without the customer ever seeing the back-end chaos.
Summary
- Oracle architecture has three layers: memory (SGA, PGA), background processes, and physical files.
- SGA is shared; PGA is private per user session.
- Key background processes: DBWR, LGWR, SMON, PMON, CKPT — each has one specific job.
- Oracle Instance = SGA + Processes; Oracle Database = Instance + Physical Storage.
- Redo log files are the recovery safety net — they record every change made.
One-Liner Revision
Oracle's architecture combines shared memory (SGA), background workers, and physical files to reliably process, store, and recover data.
1.2 Familiar with SQL*Plus
Core Definition
SQL*Plus is Oracle's command-line interface tool that allows users to connect to an Oracle database, run SQL statements, execute PL/SQL blocks, and format query output. It serves as the primary interactive environment for database administrators and developers working directly with Oracle.
SQL*Plus is like a terminal window that speaks Oracle's language. You type commands, the database responds. No graphical interface — just text in, results out. It is where DBAs spend most of their working time.
Detailed Explanation
To connect to SQL*Plus, the basic command is:
sqlplus username/password@database
Or connect locally:
sqlplus username/password
Once connected, you get the SQL> prompt. From here you can:
- Run any SQL statement (SELECT, INSERT, UPDATE, DELETE, CREATE, etc.)
- Execute SQL*Plus-specific commands (which do not need a semicolon)
- Run PL/SQL blocks
- Format output using SET commands
- Execute scripts from files
An important distinction: SQL commands are sent to Oracle for processing (end with ;). SQL*Plus commands control the environment itself and run immediately without a semicolon.
Common Mistake
Students often add a semicolon to SQL*Plus-specific commands like LIST, DESCRIBE, or RUN. These commands are processed by SQL*Plus directly, not by the Oracle engine — adding a semicolon causes an error or unexpected behavior.
Summary
- SQL*Plus is Oracle's command-line tool for running SQL and PL/SQL.
- Connection syntax:
sqlplus user/pass@db - SQL commands end with semicolons; SQL*Plus commands do not.
- It doubles as a scripting environment for batch database operations.
One-Liner Revision
SQL*Plus is Oracle's text-based interface for executing SQL, formatting results, and managing the database session interactively.
1.3 SQL*Plus Commands
Core Definition
SQL*Plus commands are environment-control instructions recognized and executed by the SQL*Plus tool itself — not by the Oracle database engine. They manage the SQL buffer, control output formatting, and assist in building and editing SQL statements interactively.
While SQL talks to the database, SQL*Plus commands talk to your working environment. They control what you see, how you edit your query before running it, and how output is displayed.
Detailed Explanation
The following are the core SQL*Plus commands you must know:
DESCRIBE (DESC)
Displays the structure of a table — column names, data types, and whether nulls are allowed.
DESC employees;
-- OR
DESCRIBE employees;
Output shows each column, its type (VARCHAR2, NUMBER, DATE), and NULL/NOT NULL constraint.
LIST (L)
Displays the current contents of the SQL buffer — the last SQL statement you typed. Useful for reviewing what is in the buffer before running or editing it.
LIST
-- or just:
L
APPEND (A)
Adds text to the end of the current line in the SQL buffer. Used to extend an existing command without retyping it.
-- If buffer contains: SELECT * FROM emp
A WHERE deptno = 10
-- Buffer becomes: SELECT * FROM emp WHERE deptno = 10
Note: there must be a space after A before the text you want to append.
CHANGE (C)
Finds and replaces text in the current buffer line. Syntax: C/old_text/new_text/
-- Buffer line: SELECT * FROM emp WHERE deptno = 10
C/10/20/
-- Result: SELECT * FROM emp WHERE deptno = 20
INPUT (I)
Inserts a new line after the currently selected line in the SQL buffer. Press Enter on a blank INPUT to stop.
-- After line 2:
2 FROM emp
I
-- Cursor waits — type new lines, blank Enter to stop
DEL
Deletes one or more lines from the SQL buffer. DEL alone deletes the current line. DEL n deletes line n. DEL m n deletes lines m through n.
DEL 3 -- deletes line 3
DEL 2 4 -- deletes lines 2, 3, and 4
DEL -- deletes current line
CLEAR BUFFER (CL BUFF)
Erases all contents of the SQL buffer completely. Use this when you want to start fresh.
CLEAR BUFFER
-- or shorthand:
CL BUFF
Memory Hook
Buffer editing commands in order of what you do: List it → Append to it → Change in it → Insert into it → Delete from it → CL BUFF to wipe it. Think: L A C I D (Lucid) — use them to keep your SQL buffer lucid and clean.
Test Yourself
- What is the difference between
APPEND and INPUT in SQL*Plus? - You want to change the word "emp" to "employees" in your current buffer line. Write the exact command.
- How do you view the current contents of the SQL buffer?
Answers: 1) APPEND adds text to the end of the current line; INPUT inserts a new line after the current line.
2) C/emp/employees/
3) Type LIST or just L.
Summary
DESCRIBE reveals a table's column structure, types, and constraints.LIST shows the current SQL buffer contents.APPEND adds text to the end of the current buffer line.CHANGE /old/new/ does a find-and-replace in the current line.INPUT inserts new lines; DEL removes lines; CLEAR BUFFER wipes everything.
One-Liner Revision
SQL*Plus buffer commands (LIST, APPEND, CHANGE, INPUT, DEL, CLEAR BUFFER) let you view, edit, and manage SQL statements before executing them.
1.4 Accepting Values at Runtime
Core Definition
Accepting values at runtime in SQL*Plus means prompting the user to enter a value interactively when a SQL statement is executed, rather than hardcoding the value in the query. This is achieved using substitution variables prefixed with & or &&.
Instead of writing a new query every time you search for a different employee, you write the query once with a placeholder. Each time you run it, SQL*Plus asks: "What value should I use?" You type the answer, and it substitutes it in automatically.
Detailed Explanation
Single Ampersand (&) — Prompted Every Run
Using &variable_name prompts the user each time the statement is executed.
SELECT * FROM employees WHERE department_id = &dept_id;
-- SQL*Plus will display: Enter value for dept_id:
If the value being substituted is a string or date, it must be enclosed in single quotes:
SELECT * FROM employees WHERE last_name = '&lname';
Double Ampersand (&&) — Prompted Once, Reused
Using &&variable_name defines the variable for the entire session. The user is prompted only once; subsequent references to the same variable reuse the entered value without re-prompting.
SELECT * FROM employees WHERE department_id = &&dept;
ACCEPT Command — Custom Prompts
The ACCEPT command gives more control: you define the variable name, data type, and the exact prompt message shown to the user.
ACCEPT dept_id NUMBER PROMPT 'Enter Department ID: '
SELECT * FROM employees WHERE department_id = &dept_id;
The ACCEPT command supports NUMBER, CHAR, and DATE data types and optionally a DEFAULT value if the user presses Enter without typing anything.
DEFINE Command — Set Without Prompting
You can also preset a variable's value using DEFINE, so no prompt appears:
DEFINE dept_id = 10
SELECT * FROM employees WHERE department_id = &dept_id;
VERIFY Setting
By default, SQL*Plus shows the old (before substitution) and new (after substitution) text. To suppress this:
SET VERIFY OFF
Common Mistake
Forgetting to wrap &variable in single quotes when substituting string or date values. Without quotes, Oracle treats the entered text as a column name or keyword, causing an ORA-00904 "invalid identifier" error.
Test Yourself
- What is the difference between
& and && substitution variables? - Write a query that accepts a last name at runtime and returns matching employee records.
- Which command gives a custom prompt message instead of the default "Enter value for..."?
Answers: 1) & prompts every time the query runs; && prompts once and stores the value for the session.
2) SELECT * FROM employees WHERE last_name = '&lname';
3) The ACCEPT command with a PROMPT clause.
Summary
&variable prompts for a value each execution; &&variable prompts once per session.- String and date substitutions must be wrapped in single quotes.
ACCEPT provides custom prompts with type checking; DEFINE sets a value without prompting.SET VERIFY OFF hides the before/after substitution output.
One-Liner Revision
Runtime substitution variables (&, &&, ACCEPT) make SQL*Plus queries dynamic by accepting user input at execution time instead of hardcoded values.
1.5 Using Script Files in SQL*Plus
Core Definition
Script files in SQL*Plus are external text files containing SQL statements and SQL*Plus commands, saved with a .sql extension, that can be executed as a batch from within SQL*Plus. They allow reusable, repeatable database operations without retyping commands each session.
Instead of typing twenty SQL statements every time you start work, you save them all in a file once and run the whole file with a single command. Like a macro for your database work.
Detailed Explanation
Creating a Script File
Script files are plain text files. Create them in any text editor (Notepad, vim, etc.) and save with the .sql extension. Example file get_emp.sql:
-- get_emp.sql
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = &dept_id;
Running a Script File
Use the START command or the @ shorthand:
START get_emp.sql
-- or equivalently:
@get_emp.sql
-- or with full path:
@C:\scripts\get_emp.sql
@@ (Double @) runs a script relative to the directory of the currently running script. Useful when one script calls another script in the same folder.
SAVE Command
Saves the current SQL buffer to a file:
SAVE myquery.sql -- creates new file (fails if exists)
SAVE myquery.sql REPLACE -- overwrites existing file
SAVE myquery.sql APPEND -- adds buffer contents to existing file
GET Command
Loads a script file into the SQL buffer without running it. You can then edit it before execution:
GET myquery.sql
EDIT Command
Opens the SQL buffer in the system's default text editor (Notepad on Windows):
EDIT -- opens current buffer
EDIT myfile.sql -- opens specific file
SPOOL Command
Captures all SQL*Plus output (query results, messages) to a file:
SPOOL output.txt -- starts capturing
-- run queries here
SPOOL OFF -- stops capturing and closes file
Test Yourself
- What is the difference between
START/@ and GET in SQL*Plus? - How do you save the current SQL buffer to a new file called
report.sql? - What does the
SPOOL command do?
Answers: 1) START/@ runs the script immediately; GET loads it into the buffer for editing without running it.
2) SAVE report.sql
3) Redirects SQL*Plus screen output to a file so you can keep a record of the session results.
Summary
- Script files are
.sql text files run via START or @. SAVE writes the buffer to a file; GET loads a file into the buffer without running it.EDIT opens the buffer or a file in the system text editor.SPOOL captures output to a file; SPOOL OFF closes it.
One-Liner Revision
Script files let you store, reuse, and execute sequences of SQL commands from external .sql files using @ or START in SQL*Plus.
1.6 Overview of Fundamental SQL Commands
Core Definition
Fundamental SQL commands are the categorized set of SQL instructions that define, manipulate, and control data within a relational database. They are grouped into four major categories: DDL, DML, DCL, and TCL (Transaction Control Language).
SQL's power comes from four distinct roles it plays: building the database structure, putting data in and changing it, controlling who gets access, and managing transactions. Each category handles one of these roles.
Detailed Explanation
| Category | Full Name | Purpose | Key Commands |
|---|
| DDL | Data Definition Language | Define and modify database structure (schema) | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language | Insert, update, delete, and retrieve data | SELECT, INSERT, UPDATE, DELETE, MERGE |
| DCL | Data Control Language | Control access and permissions | GRANT, REVOKE |
| TCL | Transaction Control Language | Manage transactions and data integrity | COMMIT, ROLLBACK, SAVEPOINT |
Key behavioral difference: DDL commands in Oracle are auto-committed — they take effect immediately and cannot be rolled back. DML changes remain in a pending transaction state until you explicitly COMMIT or ROLLBACK.
Summary
- SQL commands are grouped into DDL, DML, DCL, and TCL based on their function.
- DDL defines structure; DML handles data; DCL controls access; TCL manages transactions.
- DDL is auto-committed in Oracle; DML requires explicit COMMIT or ROLLBACK.
One-Liner Revision
SQL commands fall into four groups: DDL (structure), DML (data), DCL (access control), and TCL (transactions).
1.7 DDL — Data Definition Language
Core Definition
Data Definition Language (DDL) consists of SQL commands used to define, modify, and remove database schema objects such as tables, indexes, views, and sequences. DDL changes the structure of the database itself, not the data it contains.
DDL is the architect's toolkit. Before any data can be stored, someone has to design and build the containers (tables). DDL is how that happens.
Detailed Explanation
CREATE — Creates a new database object (table, view, index, sequence, etc.)
CREATE TABLE students (
student_id NUMBER(5) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
dob DATE,
gpa NUMBER(3,2)
);
The CREATE TABLE statement defines: column names, data types, widths, and constraints in one block.
ALTER — Modifies an existing object's structure.
-- Add a column
ALTER TABLE students ADD email VARCHAR2(100);
-- Modify a column's data type or width
ALTER TABLE students MODIFY name VARCHAR2(100);
-- Drop a column
ALTER TABLE students DROP COLUMN dob;
DROP — Permanently removes an object and all its data.
DROP TABLE students;
This is irreversible (unless Oracle's Flashback feature is enabled). All data, constraints, and indexes on that table are deleted.
TRUNCATE — Removes all rows from a table instantly, but keeps the table structure intact.
TRUNCATE TABLE students;
Faster than DELETE for clearing a table because it does not generate undo data per row. But like DROP, it is auto-committed and cannot be rolled back.
RENAME — Renames an existing object.
RENAME students TO enrolled_students;
Common Mistake
Confusing TRUNCATE and DELETE: both remove rows, but DELETE is DML (can be rolled back, fires triggers, slower on large tables). TRUNCATE is DDL (auto-committed, cannot be rolled back, does not fire row-level triggers, very fast). Using TRUNCATE when you need rollback capability will permanently destroy your data.
Test Yourself
- What is the key difference between
DROP and TRUNCATE? - Write a DDL statement to add a column
phone VARCHAR2(15) to an existing table called faculty. - Why can DDL statements not be rolled back in Oracle?
Answers: 1) DROP removes the table structure and data entirely; TRUNCATE removes all rows but leaves the table structure intact.
2) ALTER TABLE faculty ADD phone VARCHAR2(15);
3) DDL commands issue an implicit COMMIT before and after execution, making them permanent immediately.
Summary
- DDL commands: CREATE (build), ALTER (modify), DROP (destroy), TRUNCATE (empty), RENAME (rename).
- All DDL commands are auto-committed — no rollback possible.
- TRUNCATE is faster than DELETE but irreversible; DROP removes the entire structure.
One-Liner Revision
DDL commands define and modify database structures (CREATE, ALTER, DROP, TRUNCATE, RENAME) and auto-commit immediately without the option to roll back.
1.8 DML — Data Manipulation Language
Core Definition
Data Manipulation Language (DML) consists of SQL commands used to insert, retrieve, update, and delete data stored within database tables. Unlike DDL, DML operations are part of a transaction and can be undone using ROLLBACK until a COMMIT is issued.
Once the table exists (built by DDL), DML is how data gets in, changes, and comes out. Every SELECT query, every INSERT of a new record, every salary update — all DML.
Detailed Explanation
SELECT — Retrieves data from one or more tables. The most-used SQL command.
SELECT column1, column2 FROM table_name WHERE condition;
Can include filtering (WHERE), sorting (ORDER BY), grouping (GROUP BY), and functions (COUNT, SUM, AVG).
INSERT — Adds new rows to a table.
-- Insert with values in order of columns
INSERT INTO students (student_id, name, gpa) VALUES (101, 'Anita', 3.8);
-- Insert all columns (order must match table definition)
INSERT INTO students VALUES (102, 'Sagar', '2001-05-15', 3.6);
UPDATE — Modifies existing row data.
UPDATE students SET gpa = 3.9 WHERE student_id = 101;
Without a WHERE clause, UPDATE modifies every row in the table. Always double-check the condition before running.
DELETE — Removes specific rows from a table.
DELETE FROM students WHERE student_id = 102;
Without WHERE, it deletes all rows (but unlike TRUNCATE, this can be rolled back).
MERGE — Combines INSERT and UPDATE into one statement based on a condition. Used for "upsert" scenarios: update the row if it exists, insert if it does not.
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN INSERT (id, salary) VALUES (s.id, s.salary);
Common Mistake
Running UPDATE or DELETE without a WHERE clause in production. Without filtering, every row is affected. Always write the WHERE clause first, test it with a SELECT, then add UPDATE/DELETE. The absence of an error does not mean success — it may mean you just wiped your entire table.
Test Yourself
- What happens if you run
DELETE FROM employees; without a WHERE clause? - Can a DML operation be rolled back? Under what condition is it permanent?
- Write a DML statement to increase the salary of all employees in department 30 by 10%.
Answers: 1) All rows in the employees table are deleted, but since DELETE is DML, it can still be rolled back before COMMIT.
2) Yes, DML can be rolled back. It becomes permanent only after a COMMIT is issued.
3) UPDATE employees SET salary = salary * 1.10 WHERE department_id = 30;
Summary
- DML commands: SELECT (read), INSERT (add), UPDATE (change), DELETE (remove), MERGE (upsert).
- DML operations are transactional: changes are pending until COMMIT or undone by ROLLBACK.
- DELETE without WHERE removes all rows but is rollback-safe; TRUNCATE is not.
One-Liner Revision
DML commands (SELECT, INSERT, UPDATE, DELETE, MERGE) manipulate table data and remain reversible with ROLLBACK until explicitly committed.
1.9 DCL — Data Control Language
Core Definition
Data Control Language (DCL) consists of SQL commands used to control access privileges and security permissions within a database. It governs who can perform which operations on which objects, ensuring data security and access management.
DCL is the security gate. It determines who gets a key to which room in the database building. A DBA uses DCL to grant access or take it away.
Detailed Explanation
GRANT — Gives a user or role permission to perform specific operations on database objects.
-- Grant SELECT permission on a table
GRANT SELECT ON employees TO sagar;
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON employees TO anita;
-- Grant with the ability to pass permissions on to others
GRANT SELECT ON employees TO sagar WITH GRANT OPTION;
-- Grant system-level privileges
GRANT CREATE TABLE TO anita;
REVOKE — Removes previously granted permissions.
-- Revoke a specific privilege
REVOKE SELECT ON employees FROM sagar;
-- Revoke multiple privileges
REVOKE INSERT, UPDATE ON employees FROM anita;
Privileges come in two types:
- Object Privileges: Rights on specific objects (SELECT, INSERT, UPDATE, DELETE, EXECUTE on a table, view, or procedure).
- System Privileges: Rights to perform database-level actions (CREATE TABLE, CREATE USER, DROP ANY TABLE).
Roles are named groups of privileges. Instead of granting privileges to each user individually, privileges are granted to a role, and the role is granted to users. Common Oracle built-in roles: CONNECT, RESOURCE, DBA.
Test Yourself
- What is the difference between
GRANT and REVOKE? - What does
WITH GRANT OPTION do when granting a privilege? - What is the difference between object privileges and system privileges?
Answers: 1) GRANT gives permissions; REVOKE takes them away.
2) It allows the recipient to grant that same privilege to other users.
3) Object privileges apply to specific database objects (tables, views); system privileges apply to database-level actions (creating objects, managing users).
Summary
- DCL controls database security with two commands: GRANT (give access) and REVOKE (remove access).
- Privileges are either object-level (on specific tables/views) or system-level (database-wide actions).
- Roles group privileges together for easier user management.
WITH GRANT OPTION lets a user pass their received privilege on to others.
One-Liner Revision
DCL commands GRANT and REVOKE manage who can access and perform operations on database objects, enforcing security at both object and system levels.
1.10 Joins in SQL
Core Definition
A JOIN in SQL is an operation that combines rows from two or more tables based on a related column between them, allowing data spread across multiple tables to be retrieved in a single query result set.
In a normalized database, information is split across tables to avoid repetition. Joins are how you put related pieces back together in a query. An employee table has department IDs but not department names — a join fetches both in one shot.
Detailed Explanation
Oracle SQL supports several types of joins, each serving a different retrieval need:
INNER JOIN (Equijoin) — Returns only rows where the join condition is satisfied in both tables.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Rows with no matching department in the departments table are excluded from the result.
LEFT OUTER JOIN — Returns all rows from the left table and matched rows from the right. Unmatched right-side rows appear as NULL.
SELECT e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
Employees with no assigned department are still included (their department_name shows as NULL).
RIGHT OUTER JOIN — Returns all rows from the right table and matched rows from the left.
SELECT e.last_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
All departments are shown, even those with no employees.
FULL OUTER JOIN — Returns all rows from both tables. NULL fills in where no match exists on either side.
SELECT e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
CROSS JOIN (Cartesian Join) — Returns every combination of rows from both tables (m × n rows). Rarely used intentionally.
SELECT e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;
SELF JOIN — Joins a table to itself. Useful for hierarchical data (employee and their manager, both in the same table).
SELECT e.last_name AS Employee, m.last_name AS Manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
| Join Type | Rows Returned | NULL Rows? |
|---|
| INNER JOIN | Only matching rows from both sides | No |
| LEFT OUTER JOIN | All left rows + matched right rows | Yes, from right |
| RIGHT OUTER JOIN | All right rows + matched left rows | Yes, from left |
| FULL OUTER JOIN | All rows from both sides | Yes, from both |
| CROSS JOIN | Every row × every row (m × n) | No |
| SELF JOIN | Table joined to itself | Depends on type |
Let's Break It Down
Imagine two lists: students and hostels. INNER JOIN gives you only students who live in a hostel. LEFT JOIN gives you all students, with NULL for those without a hostel. RIGHT JOIN gives you all hostels, with NULL for those with no students. FULL JOIN gives you the complete picture — every student and every hostel. CROSS JOIN pairs every student with every hostel (useful for generating combinations, dangerous accidentally).
Test Yourself
- What type of join returns all rows from the left table, even when there is no match in the right table?
- Write a self-join query to find each employee and their manager's name from an
employees table that has employee_id and manager_id columns. - How many rows does a CROSS JOIN of a 5-row table and a 4-row table produce?
Answers: 1) LEFT OUTER JOIN.
2) SELECT e.last_name Employee, m.last_name Manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id;
3) 5 × 4 = 20 rows.
Summary
- INNER JOIN: matched rows only from both tables.
- LEFT/RIGHT OUTER JOIN: all rows from one side, NULLs for unmatched other side.
- FULL OUTER JOIN: all rows from both sides with NULLs where no match.
- CROSS JOIN: cartesian product (every row × every row).
- SELF JOIN: a table joins itself — used for hierarchical/recursive relationships.
One-Liner Revision
SQL joins combine rows from multiple tables based on a related column — choose INNER, OUTER, CROSS, or SELF depending on whether unmatched rows should appear and from which side.
1.11 Subqueries in SQL
Core Definition
A subquery (also called a nested query or inner query) is a SQL query embedded within another SQL statement (outer query). The subquery executes first, and its result is used by the outer query as a condition, value, or temporary data set.
When a query needs to use a value that can only be found by running another query first, you nest one inside the other. The inner query runs first, hands its answer to the outer query, which then uses it to complete its job.
Detailed Explanation
Where Subqueries Can Appear:
- In the
WHERE clause (most common) - In the
FROM clause (as an inline view) - In the
SELECT clause (scalar subquery) - In the
HAVING clause
Single-Row Subquery — Returns exactly one row. Used with single-row comparison operators (=, >, <, >=, <=, <>).
-- Find employees who earn more than the average salary
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
The inner query calculates the average; the outer query filters employees above it.
Multiple-Row Subquery — Returns more than one row. Must use multi-row operators: IN, ANY, ALL.
-- Find employees who work in departments located in city 'Seattle'
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id = 1700
);
Correlated Subquery — The inner query references a column from the outer query. It executes once for each row processed by the outer query, making it powerful but potentially slower.
-- Find employees who earn more than the average for their own department
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id -- references outer query
);
Inline View (Subquery in FROM) — A subquery in the FROM clause acts like a temporary table.
SELECT dept_avg.department_id, dept_avg.avg_sal
FROM (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) dept_avg
WHERE dept_avg.avg_sal > 8000;
| Subquery Type | Returns | Operator |
|---|
| Single-Row | One row, one value | =, >, <, <>, etc. |
| Multiple-Row | Multiple rows | IN, ANY, ALL |
| Correlated | Row-by-row result (references outer) | Any, with outer reference |
| Inline View | Temporary table (in FROM clause) | — |
Common Mistake
Using a single-row operator (=) when the subquery returns multiple rows. Oracle throws ORA-01427: "single-row subquery returns more than one row." If your inner query can return multiple results, use IN instead of =.
Test Yourself
- What is the key difference between a single-row subquery and a multiple-row subquery?
- Write a subquery to find the names of employees who earn the highest salary in the company.
- What makes a correlated subquery different from a regular subquery?
Answers: 1) Single-row returns one value and uses single-row operators (=, >, etc.); multiple-row returns several values and requires IN, ANY, or ALL.
2) SELECT last_name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
3) A correlated subquery references a column from the outer query, executing once per outer row, while a regular subquery executes independently once and returns a fixed result used by the outer query.
Summary
- A subquery is a query inside another query — the inner query runs first.
- Single-row subqueries use =, >, <; multiple-row subqueries use IN, ANY, ALL.
- Correlated subqueries reference the outer query and re-run for each outer row.
- Inline views place a subquery in the FROM clause, acting as a temporary named table.
- ORA-01427 error occurs when a single-row operator is used with a multi-row returning subquery.
One-Liner Revision
A subquery nests one SELECT inside another; the inner result feeds the outer condition — use single-row operators for one-value results and IN/ANY/ALL for multiple rows.
Whole Chapter Summary
Chapter Recap
- Oracle Database Architecture: Three-layer design (SGA/PGA memory, background processes, physical files); an instance is memory + processes, while the database adds physical storage.
- SQL*Plus: Oracle's command-line interface for running SQL, PL/SQL, and environment-control commands interactively or via scripts.
- SQL*Plus Buffer Commands: DESCRIBE (inspect table structure), LIST (view buffer), APPEND/CHANGE/INPUT/DEL (edit buffer), CLEAR BUFFER (wipe it) — all for managing the SQL statement before execution.
- Runtime Value Acceptance: Substitution variables (&, &&) and the ACCEPT command allow dynamic, user-input-driven queries without hardcoding values.
- Script Files: .sql files executed via @/START; SAVE, GET, EDIT, and SPOOL manage file-based workflows and output capture.
- SQL Command Categories: DDL (structure), DML (data), DCL (security), TCL (transactions) — each handles a distinct concern; DDL auto-commits, DML does not.
- DDL: CREATE, ALTER, DROP, TRUNCATE, RENAME — build and change the schema; all auto-committed and irreversible.
- DML: SELECT, INSERT, UPDATE, DELETE, MERGE — put data in, change it, and pull it out; reversible with ROLLBACK until COMMIT.
- DCL: GRANT and REVOKE manage who can access what at object and system levels; roles group privileges for easier assignment.
- Joins: INNER (matching rows only), LEFT/RIGHT/FULL OUTER (include unmatched rows with NULLs), CROSS (cartesian product), SELF (table joined to itself for hierarchical data).
- Subqueries: Nested SELECT statements — single-row (=, >), multiple-row (IN/ANY/ALL), correlated (references outer query), inline view (subquery in FROM clause).
Key Commands and Points Sheet
| Command / Concept | Meaning / Key Rule |
|---|
DESCRIBE table | Shows column names, data types, NULL constraints |
LIST / L | Displays current SQL buffer |
APPEND text | Adds text to end of current buffer line |
CHANGE /old/new/ | Find-replace in current buffer line |
INPUT | Inserts new line(s) after current buffer line |
DEL [n] | Deletes line n (or current line) from buffer |
CLEAR BUFFER | Wipes entire SQL buffer |
&var | Prompts for value every run |
&&var | Prompts once, reuses for rest of session |
ACCEPT var PROMPT '...' | Custom prompt with type control |
@file.sql / START file.sql | Executes a script file |
SAVE file.sql | Saves buffer to file |
GET file.sql | Loads file into buffer (does not run) |
SPOOL file / SPOOL OFF | Captures/stops output to a file |
| DDL auto-commit rule | DDL issues implicit COMMIT before and after — no rollback |
| TRUNCATE vs DELETE | TRUNCATE: DDL, fast, no rollback; DELETE: DML, slower, rollback-safe |
| JOIN types | INNER (match only), LEFT/RIGHT/FULL OUTER (include NULLs), CROSS (all combos), SELF (recursive) |
| Subquery operators | Single-row: = > <; Multi-row: IN, ANY, ALL |
| ORA-01427 | Single-row operator used with subquery returning multiple rows → use IN |
Last-Minute Revision Sheet
Rapid Recall
- Oracle architecture → SGA (shared memory) + PGA (private) + background processes (DBWR, LGWR, SMON, PMON) + physical files
- Instance = memory + processes; Database = instance + files
- SQL*Plus → Oracle's CLI; SQL commands end with
;, SQL*Plus commands do not - Buffer editing: LIST → view | APPEND → add end of line | CHANGE → replace | INPUT → new line | DEL → delete | CL BUFF → wipe
- Runtime input →
& (each run) | && (once per session) | ACCEPT (custom prompt) - Script files →
@file.sql runs | SAVE writes buffer | GET loads buffer | SPOOL captures output - DDL → structure (CREATE, ALTER, DROP, TRUNCATE, RENAME) → auto-committed, no rollback
- DML → data (SELECT, INSERT, UPDATE, DELETE, MERGE) → transactional, rollback-safe until COMMIT
- DCL → security (GRANT, REVOKE) → object privileges vs system privileges → roles group privileges
- INNER JOIN → matching only | LEFT/RIGHT OUTER → one side full | FULL OUTER → both sides | CROSS → m × n | SELF → table to itself
- Subquery → inner runs first | single-row uses = | multi-row uses IN/ANY/ALL | correlated → reruns per outer row
- ORA-01427 → fix by switching = to IN
