Oracle SQL Interview Questions

Short answer: WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

The rule: If your filter condition references an aggregate function (SUM, COUNT, AVG, MAX, MIN), it must go in HAVING. Everything else belongs in WHERE.

-- WHERE: filters rows before GROUP BY processes them
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
WHERE  hire_date > DATE '2000-01-01'  -- applies to individual rows
GROUP BY department_id;

-- HAVING: filters the results of GROUP BY
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
GROUP BY department_id
HAVING COUNT(*) > 5          -- can only be evaluated after grouping
AND    AVG(salary) > 6000;   -- aggregate function — must use HAVING

-- ERROR: you cannot use aggregate functions in WHERE
SELECT department_id, AVG(salary)
FROM   employees
WHERE  AVG(salary) > 6000     -- ORA-00934: group function not allowed here
GROUP BY department_id;

Using both together:

SELECT department_id,
       COUNT(*)  AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
WHERE  hire_date > DATE '2000-01-01'   -- 1. Filter rows first (no aggregation needed)
GROUP BY department_id                 -- 2. Then group
HAVING COUNT(*) > 3                    -- 3. Then filter groups (aggregation required)
ORDER BY avg_salary DESC;

SQL logical execution order (not the same as written order):

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT / FETCH FIRST

These three commands remove data or structures at different levels:

-- DELETE: removes rows, can be rolled back, can filter
DELETE FROM employees WHERE department_id = 99;
ROLLBACK;  -- employees are restored!

-- TRUNCATE: removes ALL rows, cannot be rolled back
TRUNCATE TABLE temp_staging;
-- ROLLBACK has no effect after TRUNCATE in Oracle

-- DROP: destroys the table entirely
DROP TABLE temp_staging;
-- The table no longer exists — all data, indexes, constraints gone

When to use which:

• Use DELETE when you need to remove specific rows, or need the ability to rollback
• Use TRUNCATE when you need to empty a table completely and speed matters (e.g., reload a staging table)
• Use DROP when you want to remove the table entirely from the schema

Trick question alert: TRUNCATE is often listed as DML by beginners, but it's actually DDL in Oracle and most databases. This matters because DDL auto-commits any pending transaction.

All three assign a number to each row in an ordered set. They differ in how they handle tied values:

-- Three employees with salaries: 9000, 6000, 4800, 4800, 4800
SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM   employees
WHERE  department_id = 60
ORDER BY salary DESC;

-- Result:
-- FIRST_NAME | SALARY | ROW_NUM | RNK | DENSE_RNK
-- -----------|--------|---------|-----|----------
-- Alexander  | 9000   | 1       | 1   | 1
-- Bruce      | 6000   | 2       | 2   | 2
-- David      | 4800   | 3       | 3   | 3   ← tie starts
-- John       | 4800   | 4       | 3   | 3   ← same rank
-- Valli      | 4800   | 5       | 3   | 3   ← same rank
-- Diana      | 4200   | 6       | 6   | 4   ← RNK jumps to 6; DENSE_RNK is 4

When to use which:

• ROW_NUMBER(): when you need unique numbers for every row (pagination, deduplication — WHERE rn = 1)
• RANK(): when ties should get the same rank and you want to communicate "position in a competition" (2nd, 2nd, then 4th — like actual race standings)
• DENSE_RANK(): when you want consecutive rank numbers regardless of ties (often used for "find the Nth highest salary")

Common interview follow-up — find the 3rd highest salary:

-- Using DENSE_RANK (handles ties correctly)
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
)
WHERE dr = 3;
-- Returns the 3rd distinct salary level, even if multiple employees share it

Approach 1: DENSE_RANK() (Most Flexible)

-- Find the 3rd highest salary (change 3 to any N)
SELECT DISTINCT salary AS nth_highest_salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM   employees
)
WHERE dr = 3;

-- Handles ties correctly: if two people share 2nd place,
-- the 3rd distinct salary level is still returned as the 3rd highest

Approach 2: Subquery with ROWNUM (Oracle Classic)

-- Oracle: 2nd highest salary
SELECT MIN(salary) AS second_highest
FROM (
    SELECT DISTINCT salary
    FROM   employees
    ORDER BY salary DESC
    FETCH FIRST 2 ROWS ONLY   -- get top 2 distinct salaries
);
-- MIN of the top 2 = the 2nd highest

Approach 3: Correlated Subquery

-- Find salary where exactly N-1 salaries are higher
-- (replace 2 with N-1 for the Nth highest)
SELECT DISTINCT salary
FROM   employees e1
WHERE  2 = (                           -- 2 = N-1 (for 3rd highest, use 2)
    SELECT COUNT(DISTINCT salary)
    FROM   employees e2
    WHERE  e2.salary > e1.salary       -- count how many distinct salaries are higher
);

Approach 4: FETCH FIRST with OFFSET (SQL Standard)

-- Standard SQL: skip top 2 salaries, get the next distinct one (3rd highest)
SELECT DISTINCT salary
FROM   employees
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;   -- offset = N-1

Interviewer tip: Always clarify — "do you want the Nth distinct salary, or the salary of the employee in the Nth position by salary?" These differ when multiple employees share the same salary.

A correlated subquery is a subquery that references columns from the outer (parent) query. Unlike a regular subquery that runs once, a correlated subquery re-executes for each row of the outer query.

-- Regular subquery: executes once, result is reused
SELECT first_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);  -- runs ONCE

-- Correlated subquery: references outer query's column (e.department_id)
-- Executes once for EACH row in the outer query
SELECT first_name, salary, department_id
FROM   employees e
WHERE  salary > (
    SELECT AVG(salary)
    FROM   employees
    WHERE  department_id = e.department_id   -- e.department_id from outer query ← correlated!
);
-- If there are 107 employees, this inner query runs 107 times

When to use a correlated subquery:

• When the comparison must be row-specific (compare to your own group's average)
• When using EXISTS or NOT EXISTS (most natural form)
• For small tables where performance isn't critical

When to use JOIN instead:

• When joining to get data from another table (always prefer JOIN)
• When the subquery appears in the SELECT clause (use window function instead)
• On large tables where running N subqueries would be slow

-- BAD: correlated subquery in SELECT runs once per employee
SELECT e.first_name,
       (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id)
FROM   employees e;

-- GOOD: single JOIN
SELECT e.first_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- EXISTS (correlated) — appropriate use, hard to express as JOIN
SELECT first_name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM job_history jh WHERE jh.employee_id = e.employee_id
);
-- "Employees who have never changed jobs"

The standard approach uses ROW_NUMBER() to label duplicates, then deletes rows where the row number is greater than 1.

Setup — simulate duplicates:

-- Imagine employees has duplicate rows with same first_name, last_name, salary
-- (employee_id differs — a common scenario after a bad data import)

Method 1: DELETE with ROW_NUMBER() (works in Oracle, PostgreSQL, SQL Server)

-- Step 1: identify duplicates (rows with row_num > 1 are duplicates)
SELECT employee_id, first_name, last_name, salary,
       ROW_NUMBER() OVER (
           PARTITION BY first_name, last_name, salary  -- columns that define "duplicate"
           ORDER BY employee_id                         -- keep the row with the lowest ID
       ) AS row_num
FROM   employees;

-- Step 2: delete duplicates
DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM (
        SELECT employee_id,
               ROW_NUMBER() OVER (
                   PARTITION BY first_name, last_name, salary
                   ORDER BY employee_id
               ) AS row_num
        FROM employees
    )
    WHERE row_num > 1   -- rn = 1 is the keeper; rn > 1 are duplicates
);

Method 2: Oracle ROWID approach

-- Oracle-specific: use ROWID (physical row address) to identify duplicates
DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)                      -- keep the row with the smallest ROWID
    FROM   employees
    GROUP BY first_name, last_name, salary -- group by columns that define a duplicate
);

Method 3: CREATE TABLE AS SELECT (for very large tables)

-- Copy unique rows to a new table, drop original, rename
CREATE TABLE employees_deduped AS
SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY first_name, last_name, salary
               ORDER BY employee_id
           ) AS rn
    FROM employees e
)
WHERE rn = 1;

-- Then swap tables (in a transaction):
DROP TABLE employees;
ALTER TABLE employees_deduped RENAME TO employees;

Interview tip: Always ask "which duplicate should I keep?" The answer determines the ORDER BY in ROW_NUMBER(). Common choices: keep the oldest (lowest ID), the newest (highest ID), or the one with the most complete data.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably, even in the presence of errors, power failures, or concurrent access.

A — Atomicity: "All or nothing"

A transaction either completes entirely or has no effect at all. There is no partial success.

-- Bank transfer: debit one account, credit another
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- If the second UPDATE fails, the ROLLBACK undoes the first UPDATE too.
-- Money is never lost in the middle of a transfer.

C — Consistency: "Rules are always satisfied"

A transaction brings the database from one valid state to another. All constraints, rules, and triggers are enforced. A transaction that would violate a constraint is rolled back.

-- Consistency example: a bank account cannot go negative (CHECK constraint)
ALTER TABLE accounts ADD CONSTRAINT ck_balance CHECK (balance >= 0);
-- If a withdrawal would violate this, the transaction fails
-- and the database stays in a consistent state.

I — Isolation: "Concurrent transactions don't interfere"

Transactions execute as if they were running serially, even when running concurrently. Partial changes from one transaction are not visible to others until committed.

-- Session A reads balance: $1000
-- Session B simultaneously reads balance: $1000
-- Session A deducts $300 (now $700) — not yet committed
-- Session B's read still sees $1000 (isolated from A's uncommitted change)
-- Session A commits: $700
-- Session B now reads $700

Isolation levels (from least to most strict): READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE.

D — Durability: "Committed data survives crashes"

Once a transaction is committed, its changes persist even if the system crashes immediately afterwards. The database uses write-ahead logging (WAL) to guarantee this.

COMMIT;
-- After this line executes successfully, the changes are durable.
-- Even if the power goes out 1 millisecond later, the data is safe.

Summary table:

Clustered Index

A clustered index determines the physical storage order of rows in the table. The rows are stored on disk in the sorted order of the clustered index key. There can only be one clustered index per table (because data can only be physically sorted one way).

In SQL Server and MySQL (InnoDB), the primary key is automatically the clustered index.

Non-Clustered Index

A non-clustered index is a separate data structure that stores the index key values plus a pointer (rowid or bookmark) back to the actual row location in the table. A table can have many non-clustered indexes.

Clustered Index:
┌──────────────────────────────────────────┐
│ Table data is the index — rows are       │
│ physically sorted by employee_id:        │
│  100 | Jennifer | 4400                   │
│  101 | Neena    | 17000                  │
│  102 | Lex      | 17000                  │
│  103 | Alexander| 9000                   │
└──────────────────────────────────────────┘

Non-Clustered Index on last_name:
┌──────────────────────────────────────────┐
│ Separate structure, sorted by last_name: │
│  'Abel' → rowid → points to row 174      │
│  'Ande' → rowid → points to row 166      │
│  'Atkinson' → rowid → points to row 130  │
│  (then follows rowid to get full row)    │
└──────────────────────────────────────────┘

Oracle Note: Oracle does not have "clustered" and "non-clustered" in the SQL Server sense. Oracle B-Tree indexes are all "non-clustered" by default. Oracle's equivalent of a clustered index is an Index-Organized Table (IOT), where the table IS the B-Tree index.

Practical implications:

• For a primary key lookup, a clustered index is fastest because the data is immediately at the leaf node
• For a non-clustered index lookup, the database follows the rowid pointer to the table (an extra I/O step called "table access by rowid")
• A covering index eliminates this extra lookup by including all needed columns in the index itself

The key insight: a customer "ordered every month" means the count of distinct months they ordered in equals 12.

Approach 1: COUNT DISTINCT months (simplest)

SELECT customer_id
FROM   orders
WHERE  order_date >= DATE '2023-01-01'
AND    order_date  < DATE '2024-01-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12;
-- If a customer has orders in all 12 distinct months, include them

Approach 2: Generate all months, then check coverage (more robust)

This approach works even if you want to check for months from a calendar, not just months present in the data:

WITH months_2023 AS (
    -- Generate all 12 months of 2023
    SELECT LEVEL AS month_num
    FROM   DUAL
    CONNECT BY LEVEL <= 12
),
customer_months AS (
    -- Which months did each customer order in?
    SELECT customer_id,
           EXTRACT(MONTH FROM order_date) AS order_month
    FROM   orders
    WHERE  order_date >= DATE '2023-01-01'
    AND    order_date  < DATE '2024-01-01'
    GROUP BY customer_id, EXTRACT(MONTH FROM order_date)
),
customer_coverage AS (
    -- For each customer, count how many of the 12 months they covered
    SELECT cm.customer_id,
           COUNT(*) AS months_covered
    FROM   months_2023 m
    JOIN   customer_months cm ON m.month_num = cm.order_month
    GROUP BY cm.customer_id
)
SELECT customer_id
FROM   customer_coverage
WHERE  months_covered = 12
ORDER BY customer_id;

Extension: add customer details

SELECT c.customer_id, c.customer_name, c.email
FROM   customers c
WHERE  c.customer_id IN (
    SELECT customer_id
    FROM   orders
    WHERE  order_date >= DATE '2023-01-01'
    AND    order_date  < DATE '2024-01-01'
    GROUP BY customer_id
    HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12
)
ORDER BY c.customer_name;

SQL injection is an attack where malicious SQL code is inserted into a query by manipulating user input, causing the database to execute unintended commands — potentially leaking data, bypassing authentication, or destroying data.

Classic example — vulnerable code:

# Python application (DANGEROUS — never do this!)
username = request.form['username']
password = request.form['password']

query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
# If username = "admin' --", the query becomes:
# SELECT * FROM users WHERE username = 'admin' --' AND password = '...'
# The -- comments out the password check — attacker logs in as admin without a password!

-- What the injected query looks like:
SELECT * FROM users
WHERE username = 'admin' --' AND password = 'anything'
-- Everything after -- is a comment — password check is bypassed!

-- An attacker could also do:
-- username = "'; DROP TABLE users; --"
-- → SELECT * FROM users WHERE username = ''; DROP TABLE users; --'

Prevention Method 1: Parameterized Queries / Bind Variables (Best Practice)

# SAFE: parameterized query — user input is never part of the SQL string
cursor.execute(
    "SELECT * FROM users WHERE username = :1 AND password = :2",
    (username, password)   # values bound separately, never interpreted as SQL
)

-- Oracle PL/SQL: use bind variables
EXECUTE IMMEDIATE
    'SELECT * FROM users WHERE username = :uname AND password = :pwd'
    USING v_username, v_password;
-- :uname and :pwd are placeholders — the values cannot contain SQL syntax

Prevention Method 2: Stored Procedures

-- Wrap queries in stored procedures — no dynamic SQL exposed
CREATE PROCEDURE authenticate_user (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2,
    p_valid    OUT NUMBER
)
IS
BEGIN
    SELECT COUNT(*) INTO p_valid
    FROM   users
    WHERE  username = p_username
    AND    password_hash = hash_fn(p_password);   -- static SQL, no injection possible
END;

Prevention Method 3: Input Validation and Least Privilege

-- Principle of least privilege: application connects with a user that has minimal rights
-- Report user: SELECT only on the views they need
GRANT SELECT ON employee_directory TO reporting_app_user;
-- Even if injection occurs, the attacker can't UPDATE, DELETE, or DROP

-- Validate input types in application code:
-- if (not is_integer(user_input)): raise error   -- before it ever reaches the DB

Summary of prevention techniques:

A FULL OUTER JOIN returns all rows from both tables: matching rows from both, non-matching rows from the left (with NULLs for the right), AND non-matching rows from the right (with NULLs for the left).

Think of it as LEFT JOIN + RIGHT JOIN combined.

Visual:

LEFT JOIN:          RIGHT JOIN:         FULL OUTER JOIN:
┌─────────────┐    ┌─────────────┐     ┌─────────────────┐
│ ████ |      │    │      | ████ │     │ ████ |  ████    │
│ ████ | ████ │    │ ████ | ████ │     │ ████ |  ████    │
│ ████ |      │    │      | ████ │     │ ████ |  ████    │
└─────────────┘    └─────────────┘     └─────────────────┘
All left, only     Only right, all left  All left + all right
matched right      matched right

Real-world example: reconciliation report

You have a list of budgeted departments and a list of departments with actual employees. You want to see ALL departments — those with budget but no staff, those with staff but no budget, and those with both:

-- Budget table: departments with approved budget
-- Employees table: departments with actual staff

SELECT COALESCE(b.department_id, e.department_id) AS department_id,
       b.budget_amount,
       COUNT(e.employee_id) AS actual_headcount,
       CASE
           WHEN b.department_id IS NULL THEN 'No Budget Allocated'
           WHEN e.department_id IS NULL THEN 'Budgeted But No Staff'
           ELSE 'Normal'
       END AS status
FROM   department_budgets b
FULL OUTER JOIN employees e ON b.department_id = e.department_id
GROUP BY COALESCE(b.department_id, e.department_id), b.budget_amount, b.department_id, e.department_id;

Other real-world uses:

• Data migration validation: compare old table vs new table, find rows that didn't migrate
• Report comparison: compare two reporting periods side-by-side, including metrics that only appear in one period
• Data integration: merge datasets from two systems where neither is a complete superset of the other

-- Data migration validation
SELECT COALESCE(old.id, new.id) AS id,
       old.value AS old_value,
       new.value AS new_value,
       CASE
           WHEN old.id IS NULL THEN 'Only in NEW'
           WHEN new.id IS NULL THEN 'Only in OLD'
           WHEN old.value != new.value THEN 'Values differ'
           ELSE 'Match'
       END AS status
FROM   old_table old
FULL OUTER JOIN new_table new ON old.id = new.id
WHERE  old.id IS NULL OR new.id IS NULL OR old.value != new.value;
-- Shows only the discrepancies

Oracle note: FULL OUTER JOIN is supported in Oracle 9i+. Older Oracle code may use the (+) syntax for outer joins, but FULL OUTER JOIN is the ANSI standard and preferred.

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. It involves decomposing tables so that each piece of information is stored in exactly one place.

1NF — First Normal Form: "No repeating groups, atomic values"

Rules:

• Each cell contains a single, indivisible value (no lists)
• Each row is unique (has a primary key)
• No repeating columns (like phone1, phone2, phone3)

VIOLATES 1NF (multiple values in one cell):
┌─────────────────────────────────────────────────────┐
│ order_id | customer | products                      │
│----------|----------|-------------------------------|
│ 1001     | Alice    | "Laptop, Mouse, Keyboard"     │  ← multiple values in one cell!
└─────────────────────────────────────────────────────┘

SATISFIES 1NF:
┌─────────────────────────────────────────────────────┐
│ order_id | customer | product_id | product_name    │
│----------|----------|------------|-----------------|
│ 1001     | Alice    | P1         | Laptop          │
│ 1001     | Alice    | P2         | Mouse           │
│ 1001     | Alice    | P3         | Keyboard        │
└─────────────────────────────────────────────────────┘

2NF — Second Normal Form: "No partial dependencies (only for composite keys)"

Rules:

• Must be in 1NF
• Every non-key column must depend on the ENTIRE primary key, not just part of it

(Only relevant when the primary key is composite — multiple columns.)

VIOLATES 2NF (order_id + product_id is the PK, but customer_name depends only on order_id):
┌───────────────────────────────────────────────────────────┐
│ order_id | product_id | customer_name | product_price     │
│ (PK)     | (PK)       | depends only  | depends on both   │
│          |            | on order_id!  |                   │
└───────────────────────────────────────────────────────────┘

SATISFIES 2NF — split into two tables:
orders:     order_id (PK) | customer_name
order_items: order_id (FK) + product_id (FK) | product_price

3NF — Third Normal Form: "No transitive dependencies"

Rules:

• Must be in 2NF
• Non-key columns must depend ONLY on the primary key — not on other non-key columns

VIOLATES 3NF (zip_code → city — zip determines city, transitive dependency):
┌──────────────────────────────────────────────────────────┐
│ employee_id (PK) | name   | zip_code | city             │
│                  |        | ↑         | ↑                │
│                  |        | both depend on employee_id, │
│                  |        | BUT city also depends on     │
│                  |        | zip_code (transitive!)       │
└──────────────────────────────────────────────────────────┘

SATISFIES 3NF — split out the transitive dependency:
employees:  employee_id (PK) | name | zip_code (FK)
zip_codes:  zip_code (PK)    | city

Summary:

Trade-offs: Higher normal forms reduce redundancy but may require more JOINs in queries. Data warehouses and reporting databases often denormalize intentionally for query performance.

A CTE is a named, temporary result set defined using the WITH keyword at the beginning of a query. It can be referenced by name within that query.

A subquery is an unnamed SELECT statement embedded inside another statement (in FROM, WHERE, or SELECT clauses).

Same logic, different forms:

-- Subquery version: logic embedded inline
SELECT first_name, salary
FROM (
    SELECT first_name, salary,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) inner_q
WHERE salary > dept_avg;

-- CTE version: logic named and defined at the top
WITH dept_enriched AS (
    SELECT first_name, salary,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
)
SELECT first_name, salary
FROM   dept_enriched
WHERE  salary > dept_avg;

Key differences:

When to choose CTE:

• The same subquery logic is needed more than once
• The query has multiple steps and benefits from naming each step
• You need recursion (CTEs support recursive queries; subqueries don't)
• You want to improve readability for complex queries

When a subquery is fine:

• Simple, one-off inline query that is only used once
• EXISTS / NOT EXISTS patterns (idiomatic with subqueries)
• Simple scalar subquery in WHERE clause

When to use a temp table instead:

• The intermediate result is very expensive to compute and is referenced many times
• You need to add an index to the intermediate result
• The result persists beyond the current query

-- CTE referenced multiple times (avoids duplicate computation... conceptually)
WITH expensive_calc AS (
    SELECT department_id, COMPLEX_FUNCTION(salary) AS adjusted
    FROM   employees
)
SELECT *
FROM   expensive_calc
WHERE  adjusted > 50000
UNION ALL
SELECT *
FROM   expensive_calc        -- second reference — CTE may re-execute!
WHERE  adjusted < 20000;

-- For truly expensive CTEs used multiple times, use a temp table:
CREATE GLOBAL TEMPORARY TABLE tmp_calc ON COMMIT DELETE ROWS AS ...;

This is one of the most conceptually important distinctions in SQL.

GROUP BY collapses rows:

-- GROUP BY: you lose individual row details — only aggregated results remain
SELECT department_id, COUNT(*), AVG(salary)
FROM   employees
GROUP BY department_id;

-- Result: 1 row per department (original 107 employee rows are gone)
-- DEPARTMENT_ID | COUNT | AVG_SALARY
-- --------------|-------|----------
-- 10            | 1     | 4400
-- 20            | 2     | 9500
-- 60            | 5     | 5760

Window functions preserve rows:

-- OVER(): each employee row is preserved + gains access to aggregate data
SELECT employee_id, first_name, salary, department_id,
       COUNT(*)  OVER (PARTITION BY department_id) AS dept_headcount,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM   employees;

-- Result: all 107 rows preserved, each row enriched with dept stats
-- EMPLOYEE_ID | FIRST_NAME | SALARY | DEPT | DEPT_HEADCOUNT | DEPT_AVG
-- ------------|------------|--------|------|----------------|--------
-- 200         | Jennifer   | 4400   | 10   | 1              | 4400
-- 201         | Michael    | 13000  | 20   | 2              | 9500
-- 202         | Pat        | 6000   | 20   | 2              | 9500
-- 103         | Alexander  | 9000   | 60   | 5              | 5760

Decision guide:

Can you combine them?

Yes! GROUP BY runs first, then window functions can run over the grouped results:

-- GROUP BY first: get one row per dept-year
-- Then window function: running total of headcount by year
SELECT department_id,
       hire_year,
       hires_this_year,
       SUM(hires_this_year) OVER (
           PARTITION BY department_id
           ORDER BY hire_year
       ) AS cumulative_hires
FROM (
    SELECT department_id,
           EXTRACT(YEAR FROM hire_date) AS hire_year,
           COUNT(*) AS hires_this_year
    FROM   employees
    GROUP BY department_id, EXTRACT(YEAR FROM hire_date)
);

NULL never equals anything — not even another NULL. This is the fundamental rule.

In SQL, NULL = NULL evaluates to NULL (not TRUE). Since JOIN conditions require a TRUE match, rows with NULL in the join key will never match any other row.

-- Example: employees.department_id can be NULL
-- departments.department_id is never NULL (it's a PK)

-- INNER JOIN: employees with NULL department_id are EXCLUDED
SELECT e.first_name, d.department_name
FROM   employees   e
JOIN   departments d ON e.department_id = d.department_id;
-- NULL = anything is never TRUE → employee with NULL dept_id is dropped

-- Check: how many employees have NULL department_id?
SELECT COUNT(*) FROM employees WHERE department_id IS NULL;
-- In the HR schema: 1 employee (employee_id = 178)
-- That employee will NOT appear in the INNER JOIN result!

-- LEFT JOIN: employees with NULL department_id ARE included (but dept columns are NULL)
SELECT e.first_name, d.department_name
FROM   employees   e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- The employee with NULL dept_id appears with NULL department_name

NULL in self-joins:

-- Find employees and their managers
SELECT e.first_name AS employee, m.first_name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.employee_id;
-- The CEO has manager_id = NULL → CEO is EXCLUDED from the result!

-- To include the CEO:
SELECT e.first_name AS employee,
       NVL(m.first_name, 'No Manager') AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- CEO appears with manager = 'No Manager'

NULL in WHERE clause:

-- This does NOT find NULLs:
SELECT * FROM employees WHERE commission_pct = NULL;  -- returns 0 rows!
-- NULL = NULL is NULL (not TRUE)

-- CORRECT way to find NULLs:
SELECT * FROM employees WHERE commission_pct IS NULL;

-- CORRECT way to exclude NULLs:
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

NULL in aggregate functions:

-- COUNT(*) includes NULL rows; COUNT(column) excludes NULLs
SELECT COUNT(*) AS total_rows,            -- 107 (all rows)
       COUNT(commission_pct) AS has_comm, -- 35 (only non-NULL commission_pct)
       AVG(commission_pct) AS avg_comm    -- average of 35 values, not 107!
FROM   employees;
-- AVG ignores NULLs — this is usually the right behavior, but be aware of it

Practical tips:

-- Use NVL/COALESCE to handle NULLs in comparisons
SELECT * FROM employees
WHERE NVL(commission_pct, 0) > 0.15;    -- treats NULL as 0

-- Use NVL/COALESCE in aggregations when NULL should mean 0
SELECT AVG(NVL(commission_pct, 0)) AS avg_comm  -- treats NULL as 0 in average
FROM   employees;
-- Result is very different from AVG(commission_pct)!

-- NULLS FIRST / NULLS LAST in ORDER BY
SELECT first_name, commission_pct
FROM   employees
ORDER BY commission_pct DESC NULLS LAST;  -- NULLs appear at the end

Summary of NULL behavior: