SQL Complete Study Guide | From Zero to Advanced with Java

SQL
Complete Study Guide
From Zero to Advanced — with Java Integration

For: College Students • IT Professionals • Career Changers

Page 1
SQL Complete Study Guide | From Zero to Advanced with Java

Chapter 1 — What is SQL?

SQL (Structured Query Language) is the universal language for communicating with relational
databases. Whether you are a student learning your first database concepts, an IT professional
managing enterprise systems, or someone transitioning into tech, SQL is one of the most
valuable skills you can learn.

Think of a database like a highly organized filing cabinet. Each drawer is a table, each folder is
a row of data, and each label on the folder is a column. SQL is how you open the right drawer,
find the right folder, and read, add, edit, or remove information — all at high speed, across
millions of records.

📌 Why SQL Matters

SQL is used in virtually every application that stores data — banking systems, hospital records, e-
commerce platforms, social media feeds, mobile apps, and more. Knowing SQL makes you valuable
in software development, data analysis, business intelligence, and database administration.

1.1 Relational Databases — The Foundation

A relational database organizes data into tables (also called relations). Each table has rows
(records) and columns (fields). Tables can be linked to each other using keys, allowing complex
data relationships without duplicating information.

Popular relational database systems that use SQL:

• MySQL — Free, open-source, very popular for web applications
• PostgreSQL — Advanced open-source database with powerful features
• Microsoft SQL Server — Enterprise-grade, widely used in corporate environments
• Oracle Database — Large enterprise systems, heavily used in banking and healthcare
• SQLite — Lightweight, file-based, built into Android and iOS apps
• H2 Database — In-memory database popular for Java testing

1.2 How SQL Fits with Java

Java applications connect to SQL databases using JDBC (Java Database Connectivity) — a
standard API built into Java. Modern Java projects also use frameworks like Hibernate or Spring
Data JPA, which layer on top of JDBC to simplify database interactions. You will learn raw SQL
first, then see how every concept maps to Java code throughout this guide.

💡 Tip for Beginners

You do not need to memorize everything at once. Read through each section, try the examples in a
tool like DB Browser for SQLite or MySQL Workbench, then come back when you are ready to go
deeper. Learning SQL is iterative.

Page 2
SQL Complete Study Guide | From Zero to Advanced with Java

Page 3
SQL Complete Study Guide | From Zero to Advanced with Java

Chapter 2 — Data Types

Every column in a SQL table must have a data type — it tells the database what kind of
information is stored there. Choosing the right data type improves performance, saves storage
space, and prevents bad data from being entered.

2.1 Common SQL Data Types

Data Type Description & Example

INT / INTEGER Whole numbers. Example: age = 25, quantity =
100
BIGINT Very large whole numbers. Example: social
security numbers, large IDs
DECIMAL(p,s) / NUMERIC Exact decimals. DECIMAL(10,2) = up to 10 digits,
2 after decimal. Good for money.
FLOAT / DOUBLE Approximate decimals. Good for scientific data,
not for money.
VARCHAR(n) Variable-length text up to n characters.
VARCHAR(100) for names.
CHAR(n) Fixed-length text. CHAR(2) for US state codes
like 'CA', 'TX'.
TEXT Long text with no set limit. Good for descriptions,
notes.
DATE Calendar date. Format: YYYY-MM-DD. Example:
'2024-06-15'
TIME Time of day. Format: HH:MM:SS. Example:
'14:30:00'
DATETIME / TIMESTAMP Date and time together. TIMESTAMP also tracks
time zones.
BOOLEAN True or False. Some databases use TINYINT(1)
or BIT instead.
BLOB Binary Large Object — stores images, files, or
other raw binary data.

2.2 Choosing the Right Type

A very common mistake is storing numbers as text (VARCHAR) or using FLOAT for currency.
Here are the rules of thumb:

Page 4
SQL Complete Study Guide | From Zero to Advanced with Java

• Money / financial values → always use DECIMAL, never FLOAT (floating point errors
can cause rounding issues)
• Unique IDs → use INT with AUTO_INCREMENT or BIGINT for large-scale systems
• Short fixed codes (country codes, status flags) → CHAR
• Names, emails, descriptions → VARCHAR with a reasonable limit
• Dates you need to compare or sort → DATE or DATETIME, never store as plain text

⚠️Common Pitfall
Storing dates as VARCHAR like '06/15/2024' makes sorting and date math impossible. Always use
DATE or DATETIME so the database can properly compare and calculate date differences.

Page 5
SQL Complete Study Guide | From Zero to Advanced with Java

Chapter 3 — DDL: Defining Your Database Structure

DDL stands for Data Definition Language. These are the SQL commands used to CREATE,
ALTER, and DROP the structure of your database — tables, indexes, constraints, and more.
DDL commands change the schema (the blueprint), not the actual data.

3.1 CREATE TABLE

The CREATE TABLE statement builds a new table and defines all of its columns and
constraints.

-- Creating an employees table

CREATE TABLE employees (
employee_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
hire_date DATE NOT NULL,
salary DECIMAL(10,2) NOT NULL DEFAULT 0.00,
department_id INT,
PRIMARY KEY (employee_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Breaking down the key parts:

• NOT NULL — this column must always have a value, it cannot be left empty
• AUTO_INCREMENT — the database automatically assigns the next number (1, 2, 3...)
for new rows
• UNIQUE — no two rows can have the same value in this column
• DEFAULT 0.00 — if no salary is provided when inserting, it defaults to zero
• PRIMARY KEY — uniquely identifies every row in this table
• FOREIGN KEY — links this column to the primary key of another table

3.2 PRIMARY KEY and FOREIGN KEY

These two constraint types are the backbone of relational databases.

🔑 Primary Key
A Primary Key is a column (or combination of columns) that uniquely identifies every row. No two rows
can share a primary key value. There is only one primary key per table. It is always NOT NULL.

Page 6
SQL Complete Study Guide | From Zero to Advanced with Java

🔗 Foreign Key
A Foreign Key is a column in one table that references the Primary Key of another table. It creates a
relationship between tables and enforces referential integrity — you cannot insert a department_id
that doesn't exist in the departments table.

3.3 ALTER TABLE

ALTER TABLE lets you modify an existing table — add columns, change types, drop columns,
or add constraints — without recreating it from scratch.

-- Add a new column

ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20);

-- Change a column's data type

ALTER TABLE employees MODIFY COLUMN phone_number VARCHAR(15);

-- Rename a column (MySQL 8+ / PostgreSQL syntax)

ALTER TABLE employees RENAME COLUMN phone_number TO mobile;

-- Drop a column
ALTER TABLE employees DROP COLUMN mobile;

-- Add a constraint after the fact

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

3.4 DROP and TRUNCATE

-- DROP TABLE: permanently removes the table AND all its data
DROP TABLE employees;

-- TRUNCATE TABLE: removes all rows but keeps the table structure
-- Much faster than DELETE with no WHERE clause
TRUNCATE TABLE employees;

-- DROP DATABASE: removes an entire database

DROP DATABASE company_db;

⚠️Warning
DROP and TRUNCATE cannot be undone unless you are inside a transaction. Always double-check
before running these commands in a production environment. TRUNCATE also resets
AUTO_INCREMENT counters.

Page 7
SQL Complete Study Guide | From Zero to Advanced with Java

Chapter 4 — DML: Working with Data

DML stands for Data Manipulation Language. These are the commands you will use every
single day: SELECT, INSERT, UPDATE, and DELETE. They operate on the actual data inside
tables, not the table structure itself.

4.1 INSERT — Adding Data

-- Insert a single row (specifying columns is best practice)
INSERT INTO employees (first_name, last_name, email, hire_date, salary,
department_id)
VALUES ('Alice', 'Johnson', 'alice@[Link]', '2023-03-15', 72000.00, 2);

-- Insert multiple rows at once (much faster than one by one)

INSERT INTO employees (first_name, last_name, email, hire_date, salary,
department_id)
VALUES
('Bob', 'Smith', 'bob@[Link]', '2022-07-01', 65000.00, 1),
('Carol', 'White', 'carol@[Link]', '2023-11-20', 80000.00, 3),
('David', 'Brown', 'david@[Link]', '2021-05-10', 95000.00, 2);

4.2 SELECT — Reading Data

SELECT is by far the most used SQL command. It retrieves data from one or more tables.

-- Select all columns from a table

SELECT * FROM employees;

-- Select specific columns (preferred — avoids unnecessary data transfer)

SELECT first_name, last_name, salary FROM employees;

-- Rename columns in the output using aliases

SELECT first_name AS 'First Name', salary AS 'Annual Salary' FROM employees;

-- Filter rows with WHERE

SELECT * FROM employees WHERE salary > 70000;

-- Multiple conditions
SELECT * FROM employees WHERE salary > 70000 AND department_id = 2;

-- Sort results
SELECT first_name, salary FROM employees ORDER BY salary DESC;

Page 8
SQL Complete Study Guide | From Zero to Advanced with Java

-- Limit number of results

SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;

4.3 WHERE Clause — Filtering Data

The WHERE clause filters which rows are returned or affected. It supports many operators:

Operator Meaning & Example

= Equals. WHERE salary = 70000
!= or <> Not equals. WHERE department_id != 3
> and < Greater than / less than. WHERE salary > 50000
>= and <= Greater than or equal / less than or equal.
WHERE age <= 65
BETWEEN...AND Within a range (inclusive). WHERE salary
BETWEEN 50000 AND 90000
IN (...) Matches any value in a list. WHERE
department_id IN (1, 2, 4)
NOT IN (...) Does not match any value in a list. WHERE status
NOT IN ('deleted', 'archived')
LIKE Pattern matching. WHERE email LIKE
'%@[Link]'
IS NULL Value is empty/missing. WHERE manager_id IS
NULL
IS NOT NULL Value exists. WHERE phone IS NOT NULL

-- LIKE patterns: % matches any number of characters, _ matches exactly one

SELECT * FROM employees WHERE last_name LIKE 'S%'; -- starts with S
SELECT * FROM employees WHERE first_name LIKE '_ob'; -- 3 letters, ends in 'ob'
SELECT * FROM employees WHERE email LIKE '%gmail%'; -- contains 'gmail'

4.4 UPDATE — Modifying Data

-- Update a single row (always use WHERE to target specific rows!)
UPDATE employees SET salary = 75000.00 WHERE employee_id = 1;

-- Update multiple columns at once

UPDATE employees
SET salary = 80000.00, department_id = 3
WHERE employee_id = 5;

Page 9
SQL Complete Study Guide | From Zero to Advanced with Java

-- Update all rows in a department (use carefully!)

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 2;

⚠️Always Use WHERE with UPDATE and DELETE

If you run UPDATE employees SET salary = 0 without a WHERE clause, you will zero out every
salary in the table. Before running any UPDATE or DELETE, first run a SELECT with the same
WHERE clause to confirm which rows you are targeting.

4.5 DELETE — Removing Data

-- Delete a specific row
DELETE FROM employees WHERE employee_id = 7;

-- Delete rows matching a condition

DELETE FROM employees WHERE hire_date < '2010-01-01';

-- Delete all rows (TRUNCATE is faster for this, but both work)
DELETE FROM employees;

Chapter 5 — JOINs: Combining Tables

One of the most powerful features of relational databases is the ability to combine data from
multiple tables using JOINs. Instead of storing everything in one giant table, well-designed
databases split data into related tables and join them when needed.

Imagine you have two tables: employees (employee_id, name, department_id) and departments
(department_id, department_name). A JOIN lets you show each employee alongside their
department name in a single query result.

5.1 INNER JOIN

Returns only the rows where there is a matching value in BOTH tables. Non-matching rows
from either table are excluded.

-- Get each employee's name and their department name

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Result: Only employees who belong to an existing department appear.

Page 10
SQL Complete Study Guide | From Zero to Advanced with Java

-- Employees with NULL department_id are excluded.

-- Departments with no employees are excluded.

5.2 LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from the LEFT table, plus matching rows from the RIGHT table. If no match
exists in the right table, the columns from that table will show NULL.

-- Show all employees, including those with no department

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Employees without a department will show: first_name, last_name, NULL

5.3 RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN. Returns ALL rows from the RIGHT table, plus matching rows
from the LEFT table.

-- Show all departments, including those with no employees

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- Departments with no employees will show: NULL, NULL, department_name

5.4 FULL OUTER JOIN

Returns ALL rows from BOTH tables, with NULLs where there is no match on either side. Note:
MySQL does not support FULL OUTER JOIN directly — use a UNION of LEFT and RIGHT
JOINs.

-- PostgreSQL / SQL Server syntax:

SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- MySQL workaround using UNION:

SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name

Page 11
SQL Complete Study Guide | From Zero to Advanced with Java

FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

5.5 SELF JOIN

A table joined to itself. Useful for hierarchical data, like finding an employee's manager (who is
also in the employees table).

-- employees table has a 'manager_id' column pointing to another employee_id

SELECT e.first_name AS Employee, m.first_name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

5.6 Joining Multiple Tables

-- Join three tables: employees, departments, and locations
SELECT e.first_name, e.last_name, d.department_name, [Link]
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE [Link] = 'New York'
ORDER BY e.last_name;

Chapter 6 — Aggregate Functions & GROUP BY

Aggregate functions perform calculations on a set of rows and return a single result. Combined
with GROUP BY, they answer questions like 'What is the average salary per department?' or
'How many orders were placed each month?'

6.1 Core Aggregate Functions

Function What It Does

COUNT(*) Counts total rows in a group
COUNT(column) Counts non-NULL values in that column
COUNT(DISTINCT col) Counts unique non-NULL values
SUM(column) Adds up all numeric values in a column
AVG(column) Calculates the average of numeric values
MIN(column) Returns the smallest value
MAX(column) Returns the largest value

Page 12
SQL Complete Study Guide | From Zero to Advanced with Java

Function What It Does

GROUP_CONCAT(col) Concatenates string values from a group
(MySQL)

-- Basic aggregate examples

SELECT COUNT(*) AS total_employees FROM employees;
SELECT AVG(salary) AS avg_salary FROM employees;
SELECT MAX(salary) AS highest_salary FROM employees;
SELECT MIN(hire_date) AS earliest_hire FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;

6.2 GROUP BY
GROUP BY groups rows with the same value in specified columns, then applies aggregate
functions to each group.

-- Average salary and employee count per department

SELECT department_id,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

-- Group by multiple columns

SELECT department_id, YEAR(hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY department_id, YEAR(hire_date)
ORDER BY department_id, hire_year;

6.3 HAVING — Filtering Groups

WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER the GROUP
BY calculation. This is a key distinction.

-- Only show departments with more than 5 employees

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Only show departments where average salary exceeds 80,000

SELECT department_id, AVG(salary) AS avg_sal

Page 13
SQL Complete Study Guide | From Zero to Advanced with Java

FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000
ORDER BY avg_sal DESC;

-- Combining WHERE and HAVING

-- WHERE runs first (filters rows), then GROUP BY, then HAVING (filters groups)
SELECT department_id, COUNT(*) AS count, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date >= '2020-01-01' -- only recent hires
GROUP BY department_id
HAVING COUNT(*) >= 3 -- only depts with 3+ recent hires
ORDER BY avg_sal DESC;

Chapter 7 — Subqueries
A subquery is a SELECT statement nested inside another SQL statement. They allow you to
use the result of one query as input to another — essential for complex data retrieval.

7.1 Scalar Subquery

Returns a single value (one row, one column). Can be used wherever a single value is
expected.

-- Find employees earning above the company average salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- The inner query runs first: SELECT AVG(salary) FROM employees → e.g., 72000
-- Then the outer query: WHERE salary > 72000

7.2 IN Subquery
Returns a list of values and uses IN to check membership.

-- Find employees in departments located in 'New York'

SELECT first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments

Page 14
SQL Complete Study Guide | From Zero to Advanced with Java

WHERE location = 'New York'

);

7.3 EXISTS Subquery

Tests whether a subquery returns any rows at all. Faster than IN for large datasets when you
only care if data exists.

-- Find departments that have at least one employee

SELECT department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
);

7.4 Correlated Subquery

A subquery that references a column from the outer query. It runs once for each row in the outer
query.

-- Find employees who earn more than the average in THEIR department
SELECT first_name, last_name, salary, department_id
FROM employees e_outer
WHERE salary > (
SELECT AVG(salary)
FROM employees e_inner
WHERE e_inner.department_id = e_outer.department_id
);

7.5 Subquery in FROM (Derived Table)

A subquery in the FROM clause creates a temporary table you can query against. Called a
'derived table' or 'inline view'.

-- Find departments whose average salary is above the grand average

SELECT dept_stats.department_id, dept_stats.avg_sal
FROM (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS dept_stats

Page 15
SQL Complete Study Guide | From Zero to Advanced with Java

WHERE dept_stats.avg_sal > (SELECT AVG(salary) FROM employees);

Chapter 8 — Window Functions (Advanced)

Window functions perform calculations across a set of rows related to the current row, without
collapsing them into a single group like GROUP BY does. They are extremely powerful for
analytics, rankings, and running totals. Available in MySQL 8+, PostgreSQL, SQL Server, and
Oracle.

📌 Window Functions vs GROUP BY

GROUP BY collapses multiple rows into one. Window functions add a calculated column to each row
while keeping all rows visible. Both are useful but serve different purposes.

8.1 Syntax
function_name(column) OVER (
PARTITION BY column -- like GROUP BY, divides rows into groups
ORDER BY column -- determines row order within each partition
ROWS/RANGE frame -- optional: defines the window frame
)

8.2 Ranking Functions

-- ROW_NUMBER: unique sequential number per partition
SELECT first_name, salary, department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
row_num
FROM employees;

-- RANK: same salary gets the same rank, but gaps appear (1,1,3)
SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK: same as RANK but no gaps (1,1,2)

SELECT first_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- NTILE(n): divides rows into n buckets (e.g., salary quartiles)

SELECT first_name, salary,
NTILE(4) OVER (ORDER BY salary) AS salary_quartile

Page 16
SQL Complete Study Guide | From Zero to Advanced with Java

FROM employees;

8.3 Value Functions

-- LAG: access a value from a previous row
SELECT hire_date,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS previous_hire_date
FROM employees;

-- LEAD: access a value from a following row

SELECT hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire_date
FROM employees;

-- FIRST_VALUE / LAST_VALUE: first or last value in the window

SELECT first_name, salary,
FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS highest_salary
FROM employees;

8.4 Aggregate Window Functions

-- Running total of salary (cumulative sum)
SELECT first_name, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- Moving average within each department

SELECT first_name, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

Chapter 9 — CTEs: Common Table Expressions

A CTE (Common Table Expression) is a named temporary result set defined within a single
SQL statement using the WITH keyword. CTEs make complex queries much more readable by
breaking them into named, reusable building blocks — like creating a temporary view just for
that query.

9.1 Basic CTE

-- Without CTE (hard to read):

Page 17
SQL Complete Study Guide | From Zero to Advanced with Java

SELECT * FROM (SELECT department_id, AVG(salary) avg_sal

FROM employees GROUP BY department_id) x
WHERE x.avg_sal > 70000;

-- With CTE (much clearer):

WITH dept_averages AS (
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
)
SELECT * FROM dept_averages WHERE avg_sal > 70000;

9.2 Multiple CTEs

WITH
high_earners AS (
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 90000
),
dept_info AS (
SELECT d.department_id, d.department_name, [Link]
FROM departments d
JOIN locations l ON d.location_id = l.location_id
)
SELECT h.first_name, [Link], d.department_name, [Link]
FROM high_earners h
JOIN employees e ON h.employee_id = e.employee_id
JOIN dept_info d ON e.department_id = d.department_id;

9.3 Recursive CTEs

Recursive CTEs can reference themselves, making them perfect for hierarchical data like org
charts or category trees.

-- Build an org chart: find all reports under a manager

WITH RECURSIVE org_chart AS (
-- Anchor: start with the top-level manager
SELECT employee_id, first_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

Page 18
SQL Complete Study Guide | From Zero to Advanced with Java

-- Recursive: join each employee to their manager

SELECT e.employee_id, e.first_name, e.manager_id, [Link] + 1
FROM employees e
INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT employee_id, first_name, level FROM org_chart ORDER BY level;

Chapter 10 — Indexes & Query Performance

An index is a data structure that speeds up data retrieval. Without indexes, a database must
scan every single row (a full table scan) to find matching records. With the right indexes, it can
jump directly to the relevant data.

📌 The Library Analogy

Think of an index like the index at the back of a textbook. Instead of reading every page to find 'foreign
key', you go to the index, find 'foreign key → page 142', and jump there. A database index works the
same way — it maps values to their physical storage location.

10.1 Creating and Dropping Indexes

-- Single-column index (speeds up queries filtering by last_name)
CREATE INDEX idx_last_name ON employees(last_name);

-- Composite index (speeds up queries filtering by both columns)

CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- Unique index (enforces uniqueness AND speeds up lookups)

CREATE UNIQUE INDEX idx_email ON employees(email);

-- Drop an index
DROP INDEX idx_last_name ON employees; -- MySQL
DROP INDEX idx_last_name; -- PostgreSQL

10.2 When Indexes Help and When They Don't

Indexes HELP when... Indexes DON'T help when...

Columns used in WHERE, JOIN ON, or ORDER Full table scans are faster (very small tables)
BY
Columns with high cardinality (many unique Low cardinality columns like boolean flags
values)

Page 19
SQL Complete Study Guide | From Zero to Advanced with Java

Indexes HELP when... Indexes DON'T help when...

Frequently queried read-heavy tables Tables with very frequent
INSERT/UPDATE/DELETE
Foreign key columns Columns rarely used in WHERE or JOIN
conditions

10.3 EXPLAIN / Query Execution Plan

Use EXPLAIN to see how the database executes a query — whether it uses indexes, does full
table scans, or how many rows it examines.

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

-- Key things to look at in the output:

-- type: 'ALL' = full table scan (slow), 'ref' or 'range' = using index (fast)
-- key: which index was used (NULL means no index used)
-- rows: estimated number of rows examined
-- Extra: 'Using index', 'Using filesort', 'Using temporary' etc.

Chapter 11 — Transactions & ACID Properties

A transaction is a group of SQL statements that are treated as a single unit. Either all of them
succeed together, or none of them are applied. This is critical for data integrity in scenarios like
bank transfers, order processing, and inventory management.

11.1 ACID Properties

Property Meaning
Atomicity All operations in a transaction succeed, or none
do. 'All or nothing.'
Consistency A transaction brings the database from one valid
state to another valid state.
Isolation Transactions run independently — concurrent
transactions don't interfere.
Durability Once a transaction commits, it is permanently
saved, even if the system crashes.

11.2 Transaction Commands

Page 20
SQL Complete Study Guide | From Zero to Advanced with Java

-- START TRANSACTION begins the transaction

START TRANSACTION;

-- Example: bank transfer — deduct from account A, add to account B

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- If both succeeded, commit (permanently save)

COMMIT;

-- If something went wrong, rollback (undo everything)

ROLLBACK;

-- SAVEPOINT: create a named checkpoint within a transaction

SAVEPOINT before_update;
UPDATE employees SET salary = 999 WHERE department_id = 3;
ROLLBACK TO before_update; -- undo only back to the savepoint
COMMIT;

💡 Auto-commit
By default, most SQL databases run in auto-commit mode — every single statement is automatically
committed. When you use START TRANSACTION, you take manual control. In Java with JDBC, set
[Link](false) to manage transactions in code.

Chapter 12 — Views
A view is a saved SELECT query that acts like a virtual table. Users and applications can query
a view just like a regular table. Views are used to simplify complex queries, restrict access to
sensitive data, and present data in specific formats.

12.1 Creating and Using Views

-- Create a view that joins employees and departments
CREATE VIEW employee_details AS
SELECT e.employee_id, e.first_name, e.last_name,
[Link], d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Now query it like a table

SELECT * FROM employee_details WHERE salary > 80000;

Page 21
SQL Complete Study Guide | From Zero to Advanced with Java

-- Update or replace a view

CREATE OR REPLACE VIEW employee_details AS
SELECT e.employee_id, e.first_name, e.last_name, [Link],
[Link], d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Drop a view
DROP VIEW employee_details;

12.2 Updatable Views

Some views can be used in INSERT, UPDATE, and DELETE statements if they are based on a
single table, include the primary key, and don't use GROUP BY, DISTINCT, or aggregate
functions. Complex joins and aggregates make views read-only.

Chapter 13 — Stored Procedures & Functions

Stored procedures and functions are named, reusable blocks of SQL code stored in the
database. They reduce code duplication, improve performance by reducing network round trips,
and allow you to encapsulate complex business logic.

13.1 Stored Procedures

-- Create a stored procedure to give raises to a department
DELIMITER //
CREATE PROCEDURE give_raise(
IN dept_id INT,
IN raise_pct DECIMAL(5,2)
)
BEGIN
UPDATE employees
SET salary = salary * (1 + raise_pct / 100)
WHERE department_id = dept_id;
SELECT ROW_COUNT() AS employees_updated;
END //
DELIMITER ;

-- Call the procedure

CALL give_raise(2, 5.00); -- Give dept 2 a 5% raise

Page 22
SQL Complete Study Guide | From Zero to Advanced with Java

13.2 Stored Functions

Functions return a single value and can be used in SELECT statements, unlike stored
procedures.

DELIMITER //
CREATE FUNCTION get_annual_salary(emp_id INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
DECLARE ann_salary DECIMAL(12,2);
SELECT salary INTO ann_salary
FROM employees WHERE employee_id = emp_id;
RETURN ann_salary;
END //
DELIMITER ;

-- Use in a SELECT
SELECT first_name, get_annual_salary(employee_id) AS annual
FROM employees WHERE department_id = 2;

Chapter 14 — Connecting SQL to Java with JDBC

JDBC (Java Database Connectivity) is the standard Java API for connecting to relational
databases. It is part of the Java SE standard library. You write SQL queries in Java strings and
execute them through a connection object. Every SQL concept from this guide maps directly to
JDBC operations.

14.1 Setup — Maven Dependencies

Add the JDBC driver for your database to your [Link]:

<!-- MySQL -->

<dependency>
<groupId>[Link]</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.3.0</version>
</dependency>

<!-- PostgreSQL -->

<dependency>
<groupId>[Link]</groupId>
<artifactId>postgresql</artifactId>

Page 23
SQL Complete Study Guide | From Zero to Advanced with Java

<version>42.7.1</version>
</dependency>

<!-- H2 (in-memory, great for testing) -->

<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>

14.2 Basic Connection and Query

import [Link].*;

public class DatabaseExample {

private static final String URL = "jdbc:mysql://localhost:3306/company_db";
private static final String USER = "root";
private static final String PASS = "yourpassword";

public static void main(String[] args) {

// try-with-resources automatically closes the connection
try (Connection conn = [Link](URL, USER, PASS);
Statement stmt = [Link]();
ResultSet rs = [Link](
"SELECT first_name, last_name, salary FROM employees")) {

while ([Link]()) {
String firstName = [Link]("first_name");
String lastName = [Link]("last_name");
double salary = [Link]("salary");
[Link]("%s %s: $%.2f%n", firstName, lastName, salary);
}
} catch (SQLException e) {
[Link]();
}
}
}

14.3 PreparedStatement — Preventing SQL Injection

Always use PreparedStatement instead of Statement when user input is involved. Building SQL
strings with string concatenation creates SQL injection vulnerabilities — a critical security risk.

Page 24
SQL Complete Study Guide | From Zero to Advanced with Java

// DANGEROUS — never do this!

String query = "SELECT * FROM users WHERE email = '" + userInput + "'";

// SAFE — use PreparedStatement with parameter placeholders

String sql = "SELECT * FROM employees WHERE department_id = ? AND salary > ?";

try (Connection conn = [Link](URL, USER, PASS);

PreparedStatement pstmt = [Link](sql)) {

[Link](1, 2); // Set first ? = department_id 2

[Link](2, 70000.0); // Set second ? = salary threshold

try (ResultSet rs = [Link]()) {

while ([Link]()) {
[Link]([Link]("first_name") + " " +
[Link]("last_name"));
}
}
} catch (SQLException e) {
[Link]();
}

14.4 INSERT, UPDATE, DELETE with JDBC

// INSERT with PreparedStatement
String insertSql = "INSERT INTO employees (first_name, last_name, email, hire_date,
salary) "+
"VALUES (?, ?, ?, ?, ?)";

try (Connection conn = [Link](URL, USER, PASS);

PreparedStatement pstmt = [Link](insertSql,
Statement.RETURN_GENERATED_KEYS)) {

[Link](1, "Alice");
[Link](2, "Johnson");
[Link](3, "alice@[Link]");
[Link](4, [Link]("2024-01-15"));
[Link](5, 72000.00);

int rowsInserted = [Link]();

[Link]("Rows inserted: " + rowsInserted);

// Get the auto-generated primary key

try (ResultSet keys = [Link]()) {

Page 25
SQL Complete Study Guide | From Zero to Advanced with Java

if ([Link]()) {
[Link]("New employee ID: " + [Link](1));
}
}
} catch (SQLException e) { [Link](); }

14.5 Transactions in Java JDBC

try (Connection conn = [Link](URL, USER, PASS)) {
// Disable auto-commit to manage the transaction manually
[Link](false);

try {
// Transfer $500 from account 1 to account 2
PreparedStatement deduct = [Link](
"UPDATE accounts SET balance = balance - ? WHERE account_id = ?");
[Link](1, 500.0);
[Link](2, 1);
[Link]();

PreparedStatement add = [Link](

"UPDATE accounts SET balance = balance + ? WHERE account_id = ?");
[Link](1, 500.0);
[Link](2, 2);
[Link]();

[Link](); // Both succeeded — commit

[Link]("Transfer complete.");

} catch (SQLException e) {
[Link](); // Something failed — undo everything
[Link]("Transfer failed. Rolled back.");
throw e;
}
} catch (SQLException e) { [Link](); }

14.6 Connection Pooling with HikariCP

In real applications, creating a new database connection for every request is very slow.
Connection pools maintain a set of open connections that are reused. HikariCP is the industry-
standard connection pool for Java.

// Maven dependency:

Page 26
SQL Complete Study Guide | From Zero to Advanced with Java

// <dependency><groupId>[Link]</groupId>
// <artifactId>HikariCP</artifactId><version>5.1.0</version></dependency>

import [Link];
import [Link];

HikariConfig config = new HikariConfig();

[Link]("jdbc:mysql://localhost:3306/company_db");
[Link]("root");
[Link]("yourpassword");
[Link](10); // Max 10 concurrent connections
[Link](2); // Keep at least 2 connections open
[Link](30000); // 30 second timeout

HikariDataSource dataSource = new HikariDataSource(config);

// Use [Link]() instead of [Link]()

try (Connection conn = [Link]()) {
// ... execute queries ...
}

Chapter 15 — Spring Data JPA (Modern Java

Approach)
While JDBC gives you full control, most modern Java applications use an ORM (Object-
Relational Mapper) to map database tables to Java classes automatically. Spring Data JPA is
the most popular approach, built on top of Hibernate.

📌 JDBC vs JPA
JDBC: You write SQL manually. Full control, more boilerplate code. Great for complex queries and
when performance is critical. JPA/Hibernate: The framework generates SQL from Java annotations.
Less code, easier CRUD operations, but complex queries still need custom SQL or JPQL.

15.1 Entity Class — Mapping a Table to Java

import [Link].*;

@Entity
@Table(name = "employees")
public class Employee {

Page 27
SQL Complete Study Guide | From Zero to Advanced with Java

@Id
@GeneratedValue(strategy = [Link])
private Long employeeId;

@Column(name = "first_name", nullable = false, length = 50)

private String firstName;

@Column(name = "last_name", nullable = false, length = 50)

private String lastName;

@Column(unique = true)
private String email;

@Column(precision = 10, scale = 2)

private BigDecimal salary;

@ManyToOne
@JoinColumn(name = "department_id")
private Department department;

// Getters, setters, constructors...

}

15.2 Repository Interface

import [Link];
import [Link];
import [Link];
import [Link];

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

// Spring generates the SQL from the method name automatically!

List<Employee> findByLastName(String lastName);
List<Employee> findBySalaryGreaterThan(BigDecimal salary);
List<Employee> findByDepartment_DepartmentName(String deptName);

// Custom JPQL query (object-oriented SQL)

@Query("SELECT e FROM Employee e WHERE [Link] > :minSalary",
"AND [Link] = :deptName")
List<Employee> findHighEarnersInDept(BigDecimal minSalary, String deptName);

// Native SQL query when you need raw SQL

Page 28
SQL Complete Study Guide | From Zero to Advanced with Java

@Query(value = "SELECT * FROM employees WHERE hire_date > ?",

nativeQuery = true)
List<Employee> findRecentHires([Link] cutoffDate);
}

Chapter 16 — SQL Quick Reference Cheat Sheet

DDL Commands
CREATE TABLE t (col type constraints); -- Create new table
ALTER TABLE t ADD col type; -- Add column
ALTER TABLE t MODIFY col newtype; -- Change column type
ALTER TABLE t DROP col; -- Remove column
DROP TABLE t; -- Delete table permanently
TRUNCATE TABLE t; -- Delete all rows, keep structure

DML Commands
INSERT INTO t (c1, c2) VALUES (v1, v2); -- Add row
SELECT c1, c2 FROM t WHERE cond; -- Read data
UPDATE t SET c1 = v1 WHERE cond; -- Modify data
DELETE FROM t WHERE cond; -- Remove rows

Joins
INNER JOIN t2 ON [Link] = [Link] -- Matching rows only
LEFT JOIN t2 ON [Link] = [Link] -- All from left + matches
RIGHT JOIN t2 ON [Link] = [Link] -- All from right + matches
FULL OUTER JOIN t2 ON [Link] = [Link] -- All from both sides

Aggregates & Grouping

SELECT col, COUNT(*), AVG(sal), MAX(sal) FROM t GROUP BY col;
HAVING COUNT(*) > 5 -- Filter groups (after GROUP BY)

Window Functions
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC)
RANK() OVER (ORDER BY sal DESC)
SUM(sal) OVER (PARTITION BY dept)
LAG(col, 1) OVER (ORDER BY date) -- previous row value
LEAD(col, 1) OVER (ORDER BY date) -- next row value

Page 29
SQL Complete Study Guide | From Zero to Advanced with Java

Useful Clauses & Keywords

ORDER BY col [ASC|DESC] -- Sort results
LIMIT n -- Return first n rows
OFFSET n -- Skip first n rows (pagination)
DISTINCT -- Remove duplicates
BETWEEN v1 AND v2 -- Range filter (inclusive)
IN (v1, v2, v3) -- Match any value in list
LIKE 'pattern%' -- Pattern matching
IS NULL / IS NOT NULL -- Check for null values
CASE WHEN cond THEN val ELSE other END -- Conditional expression
COALESCE(val1, val2, ...) -- First non-null value
IFNULL(val, default) -- Replace null with default

Transactions
START TRANSACTION; -- Begin transaction
COMMIT; -- Save permanently
ROLLBACK; -- Undo everything
SAVEPOINT name; -- Checkpoint within a transaction
ROLLBACK TO name; -- Undo back to savepoint

Chapter 17 — Next Steps & Practice Resources

You now have a comprehensive foundation in SQL and its integration with Java. Here is a
recommended path for continued learning:

17.1 Hands-On Practice

• SQLZoo ([Link]) — Interactive SQL exercises in your browser, no installation
needed
• LeetCode Database section — Real interview problems categorized by difficulty
• HackerRank SQL — Structured challenges with instant feedback
• Mode Analytics SQL Tutorial — Real-world datasets with progressively harder
challenges
• [Link] — PostgreSQL-specific exercises with solutions and explanations

17.2 Tools to Install

• MySQL Workbench — Free GUI for MySQL: write and run SQL, view table structures,
manage connections

Page 30
SQL Complete Study Guide | From Zero to Advanced with Java

• DBeaver — Universal database GUI supporting MySQL, PostgreSQL, SQLite, and

dozens more
• DB Browser for SQLite — Lightweight tool, great for beginners, no server required
• DataGrip (JetBrains) — Professional IDE for SQL, excellent with Java/Spring projects

17.3 Java Projects to Build

• Student grade tracker — CREATE, INSERT, SELECT, UPDATE with a simple Java
console app using JDBC
• Inventory management system — Practice JOINs, transactions, and stored procedures
• REST API with Spring Boot + JPA — Build a full backend connected to MySQL or
PostgreSQL
• Reporting dashboard — Practice GROUP BY, window functions, and complex CTEs

17.4 Topics to Explore Next

• Database normalization (1NF, 2NF, 3NF, BCNF) — How to design tables correctly to
avoid redundancy
• Database design patterns — Star schema, snowflake schema for data warehousing
• NoSQL databases (MongoDB, Redis, Cassandra) — When NOT to use a relational
database
• Database replication and sharding — How large-scale systems handle millions of users
• Spring Boot full-stack applications — Connect everything you have learned

🎯 Final Tip
The fastest way to learn SQL is to work with real data on real problems. Find a dataset you care about
— sports statistics, music, movies, your own finances — import it into MySQL or SQLite, and start
asking questions. Every question becomes a query.

Page 31