Database SQLStudy Notes

ACID Properties

The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.

Aggregate Functions in SQL

Learn how COUNT, SUM, AVG, MIN, and MAX summarize sets of rows into single values, and how each handles NULLs.

Common SQL Pitfalls

The everyday SQL mistakes that cause outages, slow queries, and bad data — and how to avoid each one.

Common Table Expressions (CTEs)

A named, temporary result set defined with WITH that improves readability and enables recursive queries.

Constraints and Data Integrity

Explore how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints enforce valid data.

Correlated Subqueries

A subquery that references columns from the outer query and is re-evaluated once for every row processed by the outer query.

CROSS JOIN and Set Operations

Produce a Cartesian product with CROSS JOIN, and combine query results with UNION, UNION ALL, INTERSECT, and EXCEPT.

Database Design and ER Diagrams

Learn how to model real-world data using entities, attributes, and relationships before writing any SQL.

Date and Time Functions in SQL

Work with dates using NOW/CURRENT_DATE, DATE_ADD/DATE_SUB, DATEDIFF, and EXTRACT to filter and calculate time-based data.

DBMS vs RDBMS

Compare general Database Management Systems with Relational Database Management Systems and what makes the relational variant distinct.

DELETE and TRUNCATE

Compare the DELETE and TRUNCATE commands for removing rows, including logging, rollback, and identity reset behavior.

Denormalization

Understand when and why deliberately reintroducing redundancy can improve read performance in real-world systems.

DISTINCT and Basic Operators

Remove duplicate rows with DISTINCT and use comparison, logical, and pattern-matching operators in queries.

Execution Plans

How to read EXPLAIN output to understand and diagnose how the database will run a query.

Filtering with WHERE

Use the WHERE clause to filter rows returned by a query based on specified conditions.

FULL OUTER JOIN

Combine LEFT and RIGHT JOIN behavior, keeping all rows from both tables and NULL-padding unmatched sides.

GROUP BY and HAVING

Understand how GROUP BY partitions rows into groups and how HAVING filters those groups after aggregation, unlike WHERE.

Indexes and How They Work

How database indexes use B-tree structures to speed up lookups, and why they add overhead to writes.

Indexing Strategies and Tradeoffs

How to choose composite index column order, use covering indexes, and know when not to index.

INNER JOIN

Combine rows from two tables where the join condition matches in both, discarding unmatched rows.

The INSERT Statement

Learn how to add new rows to a table using single-row, multi-row, and INSERT...SELECT forms.

Introduction to Databases

Learn what a database is, why software applications need one, and how databases evolved from flat files to modern systems.

Introduction to SQL

Get an overview of SQL, its main statement categories, and how it lets you define, query, and manipulate relational data.

LEFT and RIGHT JOIN

Preserve all rows from one table, NULL-padding columns from the other table when there is no match.