SQL Basics

What is SQL — and Why Does a Java Developer Need It?

SQL (Structured Query Language) is the declarative language used to interact with every relational database — PostgreSQL, MySQL, Oracle, SQL Server, H2. You describe what data you want; the database engine decides how to retrieve it. It's not a programming language — there are no loops, no variables in standard SQL, no control flow. That's a deliberate design: the optimizer can choose the fastest execution plan without being constrained by the order the developer wrote the operations in.

Java ORMs like JPA and Hibernate generate SQL automatically. This is not a reason to skip learning SQL — it's a reason to learn it deliberately, because when a query is slow or returns the wrong data, the first debugging step is always "what SQL did the ORM actually send to the database?" You can't read that output, understand the execution plan, or fix the problem without understanding SQL.

/*
 * BEFORE JPA existed — you wrote SQL directly via JDBC.
 * This still happens for complex queries, migrations, and analytics.
 */
SELECT
    o.id,
    c.email,
    SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'PENDING'
  AND o.created_at >= NOW() - INTERVAL '24 HOURS'
GROUP BY o.id, c.email
HAVING SUM(oi.quantity * oi.unit_price) > 100
ORDER BY total DESC;

/*
 * JPA with Hibernate generates SQL automatically from entity queries.
 * Enable logging to see what it actually sends — this is the first tool
 * for debugging unexpected results or slow queries.
 */

# application.properties — see the SQL JPA generates
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace  # shows parameter values too

Defining Schema: DDL

The schema lives in version-controlled migration files (Flyway, Liquibase) in production — not written by hand in a SQL console. Understanding DDL is essential for writing those migrations and for understanding what JPA's ddl-auto=create generates (which you should never use in production).

-- An e-commerce schema: three tables, two foreign keys, essential constraints

CREATE TABLE customers (
    id          BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  -- standard SQL:2003, preferred over AUTO_INCREMENT
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(200) NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()   -- TIMESTAMPTZ: timestamp WITH time zone — always prefer this over TIMESTAMP
);

CREATE TABLE products (
    id          BIGINT          PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku         VARCHAR(50)     NOT NULL UNIQUE,
    name        VARCHAR(255)    NOT NULL,
    price       NUMERIC(12, 2)  NOT NULL CHECK (price >= 0),   -- NUMERIC not FLOAT for money
    stock       INTEGER         NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    id          BIGINT       PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT       NOT NULL REFERENCES customers(id),
    status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING'
                    CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    order_id    BIGINT         NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  BIGINT         NOT NULL REFERENCES products(id),
    quantity    INTEGER        NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),  -- snapshot at purchase time
    PRIMARY KEY (order_id, product_id)
);

-- Indexes for the columns you'll filter and join on
CREATE INDEX idx_orders_customer   ON orders(customer_id);
CREATE INDEX idx_orders_status      ON orders(status);
CREATE INDEX idx_order_items_product ON order_items(product_id);

Modifying Tables Safely

-- Add a column (safe — existing rows get NULL or the DEFAULT)
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);

-- Adding a NOT NULL column to a large table: ALWAYS add nullable first,
-- backfill data, THEN add the constraint. Adding NOT NULL without a default
-- on a table with existing rows will fail or lock the table entirely.
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);                         -- step 1: nullable
UPDATE customers SET phone = 'UNKNOWN' WHERE phone IS NULL;               -- step 2: backfill
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;                      -- step 3: constrain

-- Drop a column (PostgreSQL 15+ can do this online; older versions lock)
ALTER TABLE customers DROP COLUMN phone;

-- Add index without locking reads (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_customers_email ON customers(email);
-- Standard CREATE INDEX locks writes for the duration — use CONCURRENTLY in production

Reading and Writing Data: DML

INSERT

-- Insert a single row, explicit columns (always list columns — never omit them)
INSERT INTO customers (email, full_name)
VALUES ('ada@example.com', 'Ada Lovelace')
RETURNING id;   -- PostgreSQL: get the generated ID back in the same statement

-- Batch insert (one statement, many rows — dramatically faster than N individual INSERTs)
INSERT INTO products (sku, name, price) VALUES
    ('SKU-001', 'Laptop Stand', 49.99),
    ('SKU-002', 'USB-C Hub',    29.99),
    ('SKU-003', 'Keyboard',     89.99);

-- Upsert: insert or update if the unique key already exists
-- PostgreSQL:
INSERT INTO products (sku, name, price)
VALUES ('SKU-001', 'Laptop Stand Pro', 59.99)
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price;

-- MySQL / MariaDB:
INSERT INTO products (sku, name, price)
VALUES ('SKU-001', 'Laptop Stand Pro', 59.99)
ON DUPLICATE KEY UPDATE
    name  = VALUES(name),
    price = VALUES(price);

SELECT — The Most Important Statement

-- Always select specific columns — never SELECT * in application code
SELECT id, email, full_name FROM customers WHERE id = 42;

-- The six clauses, in the order SQL executes them (not the order you write them):
-- 1. FROM + JOINs  → determines the working set
-- 2. WHERE         → filters rows from that set
-- 3. GROUP BY      → collapses rows into groups
-- 4. HAVING        → filters groups (like WHERE, but after grouping)
-- 5. SELECT        → picks and computes the output columns
-- 6. ORDER BY / LIMIT → sorts and truncates the final result

-- Filtering
SELECT id, email FROM customers
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'   -- range on indexed column: uses the index
  AND email LIKE 'ada%';            -- trailing wildcard: uses the index. Leading %: does not.

-- NULL handling — NULL is not equal to anything, including itself
WHERE phone IS NULL       -- correct
WHERE phone = NULL        -- always false, even when phone IS NULL

-- Pagination: OFFSET has a performance problem at scale (the database
-- scans and discards all rows up to the offset). Keyset pagination
-- avoids this by filtering on the last seen ID instead.
-- OFFSET pagination (simple, slow at high page numbers):
SELECT id, email FROM customers ORDER BY id LIMIT 20 OFFSET 100;

-- Keyset pagination (efficient regardless of depth):
SELECT id, email FROM customers
WHERE id > 120   -- last ID seen on the previous page
ORDER BY id LIMIT 20;

UPDATE and DELETE

-- Always use a WHERE clause. Always.
UPDATE products
SET price = price * 1.10, updated_at = NOW()
WHERE id = 42;

-- Before running a destructive UPDATE or DELETE in production:
-- 1. Run a SELECT with the same WHERE clause and verify the count
-- 2. Wrap in a transaction
-- 3. COMMIT only after verifying the result
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'CANCELLED' AND created_at < '2025-01-01';
-- verify the number before proceeding
DELETE FROM orders WHERE status = 'CANCELLED' AND created_at < '2025-01-01';
ROLLBACK;  -- or COMMIT once you're sure

-- Soft delete: preserve history, filter instead of delete
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

UPDATE orders SET deleted_at = NOW() WHERE id = 9001;

SELECT * FROM orders WHERE deleted_at IS NULL;   -- your "active" view

JOINs: Combining Tables

JOINs are the most important concept in relational SQL after SELECT. Every ORM relationship ( @ManyToOne, @OneToMany) maps to a JOIN at the database level.

-- Working dataset for the examples below

-- customers
┌────┬──────────────────────┐
│ id │ email                │
├────┼──────────────────────┤
│  1 │ ada@example.com      │
│  2 │ alan@example.com     │
│  3 │ grace@example.com    │  -- has no orders
└────┴──────────────────────┘

-- orders
┌────┬─────────────┬──────────┐
│ id │ customer_id │ status   │
├────┼─────────────┼──────────┤
│ 101│      1      │ PAID     │
│ 102│      1      │ PENDING  │
│ 103│      2      │ SHIPPED  │
│ 104│      9      │ PAID     │  -- orphaned: customer 9 doesn't exist
└────┴─────────────┴──────────┘

-- INNER JOIN: only rows that match on both sides
SELECT c.email, o.id AS order_id, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Result: Ada (2 rows), Alan (1 row). Grace omitted (no orders). Order 104 omitted (no customer).

-- LEFT JOIN: all rows from the left table, NULLs where no match on right
SELECT c.email, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Result: Ada (2 rows), Alan (1 row), Grace (1 row with NULL order_id).
-- Use LEFT JOIN when you need all left-side records regardless of matches.

-- Finding customers with no orders at all (anti-join pattern)
SELECT c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- Grace

-- Multiple JOINs: orders with their items and product names
SELECT
    o.id         AS order_id,
    c.email,
    p.name       AS product,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers   c  ON c.id  = o.customer_id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products    p  ON p.id  = oi.product_id
WHERE o.status = 'PAID'
ORDER BY o.id, p.name;

Aggregation: GROUP BY, HAVING, and Window Functions

GROUP BY and Aggregate Functions

-- Order totals per customer
SELECT
    c.email,
    COUNT(o.id)                                      AS order_count,
    SUM(oi.quantity * oi.unit_price)                  AS lifetime_value,
    MAX(o.created_at)                                 AS last_order_date
FROM customers c
JOIN orders o      ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status != 'CANCELLED'        -- WHERE filters rows BEFORE aggregation
GROUP BY c.id, c.email
HAVING SUM(oi.quantity * oi.unit_price) > 1000   -- HAVING filters AFTER aggregation
ORDER BY lifetime_value DESC;

-- Most popular products (by total units sold)
SELECT
    p.sku,
    p.name,
    SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o       ON o.id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY p.id, p.sku, p.name
ORDER BY units_sold DESC
LIMIT 10;

CTEs — Readable Complex Queries

-- Same query as above, written with a CTE for clarity
WITH paid_item_totals AS (
    SELECT
        oi.product_id,
        SUM(oi.quantity) AS units_sold
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.status = 'PAID'
    GROUP BY oi.product_id
)
SELECT
    p.sku,
    p.name,
    pit.units_sold
FROM products p
JOIN paid_item_totals pit ON pit.product_id = p.id
ORDER BY pit.units_sold DESC
LIMIT 10;

-- CTEs don't change performance (they're rewritten by the optimizer in most databases),
-- but they make complex queries dramatically easier to read and debug.

Window Functions — Ranking Without Losing Rows

Window functions compute across related rows without collapsing them the way GROUP BY does. The canonical e-commerce use case: rank products by revenue within each category.

-- Top 3 products by revenue per category (window function)
WITH product_revenue AS (
    SELECT
        p.id,
        p.name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON oi.product_id = p.id
    JOIN orders o       ON o.id = oi.order_id AND o.status = 'PAID'
    GROUP BY p.id, p.name, p.category
),
ranked AS (
    SELECT
        *,
        RANK() OVER (
            PARTITION BY category   -- rank within each category, not globally
            ORDER BY revenue DESC
        ) AS rank_in_category
    FROM product_revenue
)
SELECT category, name, revenue, rank_in_category
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

-- RANK vs DENSE_RANK vs ROW_NUMBER for ties:
-- RANK():       1, 2, 2, 4   (gap after ties)
-- DENSE_RANK(): 1, 2, 2, 3   (no gap)
-- ROW_NUMBER(): 1, 2, 3, 4   (unique, arbitrary for ties)

Indexes and Query Performance

An index is a data structure (usually a B-tree) maintained alongside a table that allows the database to find rows matching a condition without scanning every row. The trade-off: faster reads, slower writes (the index must be updated on INSERT/UPDATE/DELETE), and more storage.

When to add an index

-- Index candidates: columns in WHERE, JOIN ON, and ORDER BY clauses
-- that appear in queries run frequently or on large tables.

-- Single column
CREATE INDEX idx_orders_status ON orders(status);

-- Composite: useful when you always filter on both columns together.
-- Column order matters: put the most selective column first.
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- This serves: WHERE status = 'PAID' AND created_at > '...'
-- But NOT: WHERE created_at > '...' alone (leading column must appear)

-- Partial index: only index the rows you actually query
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'PENDING';
-- Smaller, faster than a full index if PENDING is a tiny fraction of all orders

Reading EXPLAIN output

-- Add EXPLAIN ANALYZE before any slow query to see the execution plan
EXPLAIN ANALYZE
SELECT id, status FROM orders WHERE customer_id = 42 AND status = 'PENDING';

-- Output tells you:
-- Seq Scan     → full table scan. Fine for small tables, bad for large ones.
-- Index Scan   → uses an index. Usually what you want.
-- Index Only Scan → best: all needed columns are in the index itself.
-- rows=N       → how many rows the planner estimated (vs actual=N).
-- cost=N.N     → relative cost estimate. Higher is slower.

-- The three most common reasons a query ignores an existing index:
-- 1. Function applied to the column: YEAR(created_at) = 2026 → no index
--    Fix: created_at >= '2026-01-01' AND created_at < '2027-01-01'
-- 2. Leading wildcard: name LIKE '%laptop' → no index
--    Fix: use full-text search for contains queries
-- 3. Type mismatch: WHERE id = '42' when id is BIGINT → implicit cast, no index
--    Fix: match the column's type exactly

SQL Injection — Why Parameterized Queries Are Non-Negotiable

SQL injection is the most exploited database vulnerability. It happens when user-supplied input is concatenated directly into a SQL string, allowing the input to change the structure of the query.

// VULNERABLE — string concatenation with user input
String email = request.getParameter("email");
String sql = "SELECT * FROM customers WHERE email = '" + email + "'";

// If email = "' OR '1'='1", the query becomes:
// SELECT * FROM customers WHERE email = '' OR '1'='1'
// This returns every row in the table.

// If email = "'; DROP TABLE customers; --", the query becomes two statements.
// The database executes both. Your table is gone.

// CORRECT — parameterized query (PreparedStatement)
// The ? is a placeholder. The value is sent separately from the SQL structure.
// No matter what the user types, it can never change the structure of the query.
String sql = "SELECT id, email FROM customers WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, email);   // the value is bound, not concatenated
    ResultSet rs = ps.executeQuery();
}

// JPA / Spring Data protects you automatically when you use parameters:
// SAFE — parameter binding
@Query("SELECT c FROM Customer c WHERE c.email = :email")
Optional<Customer> findByEmail(@Param("email") String email);

// DANGEROUS even with JPA — never concatenate into JPQL
String jpql = "FROM Customer WHERE email = '" + email + "'";  // ✗
em.createQuery(jpql);

Best Practices and Common Pitfalls

Interview Questions