SQL & Database Fundamentals —
Complete Study Notes
Study Notes • Concepts, examples, practical guidance and revision points
Chapter 1: Database Fundamentals
Relational Model
A relational database stores data in tables. Rows represent records and columns represent attributes.
Primary keys uniquely identify rows; foreign keys represent relationships between tables.
Transactions
A transaction groups related operations. ACID describes atomicity, consistency, isolation and durability.
Chapter 2: SELECT and Filtering
Retrieving Data
SELECT chooses columns; FROM chooses the source table. WHERE filters rows.
SELECT name, age FROM users WHERE age >= 18;
Sorting and Limiting
ORDER BY sorts results. LIMIT can restrict the number of returned rows, depending on the database
system.
Chapter 3: INSERT UPDATE DELETE
Data Changes
INSERT creates rows, UPDATE modifies existing rows, and DELETE removes rows.
Always use a carefully written WHERE condition when updating or deleting specific records.
Data Integrity
Constraints such as NOT NULL, UNIQUE, CHECK and FOREIGN KEY protect data quality.
Chapter 4: Joins
INNER JOIN
Returns rows where the join condition matches in both tables.
LEFT JOIN
Keeps every row from the left table and adds matching right-side data when available.
Join Discipline
Join on keys or well-defined relationships. Unexpected duplicate rows often indicate an incorrect join
condition.
Chapter 5: Aggregation
Aggregate Functions
COUNT, SUM, AVG, MIN and MAX summarize values.
SELECT department, COUNT(*) FROM employees GROUP BY department;
HAVING
WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
Chapter 6: Subqueries and CTEs
Subqueries
A subquery can provide a value or temporary result used by an outer query.
CTEs
A common table expression introduced with WITH can make multi-step queries easier to read and
maintain.
Chapter 7: Indexes
Purpose
Indexes help databases locate rows efficiently without scanning an entire table in many cases.
Trade-offs
Indexes consume storage and can slow inserts and updates. Index columns used frequently for filtering,
joins or ordering after measuring workload needs.
Chapter 8: Normalization
Why Normalize
Normalization reduces unnecessary duplication and helps prevent insert, update and delete anomalies.
Practical Balance
Highly normalized schemas are not always the final performance design. Real systems may use
carefully chosen denormalization after profiling.
Chapter 9: Transactions and Concurrency
Isolation
Concurrent operations can interact in ways that cause anomalies. Isolation levels control what one
transaction can observe from another.
Locks
Databases may use locks or other concurrency mechanisms to maintain consistency. Long transactions
can reduce throughput.
Chapter 10: SQL Best Practices
Security
Use parameterized queries rather than concatenating untrusted input into SQL. This helps prevent SQL
injection.
Performance
Select only needed columns, filter early, inspect query plans, index deliberately and avoid unnecessary
repeated queries.
Quick Revision Checklist
• Review the key definitions before attempting exercises.
• Practice the examples without looking at the answer.
• Focus on understanding why a technique works, not only memorizing syntax.
• Build a small project to connect the concepts.
• Revisit weak topics after a few days using active recall.