Learn SQL From Zero to Expert

The SQL SELECT statement retrieves rows and columns from one or more database tables, optionally filtered, sorted, and limited.

The SQL WHERE clause filters rows returned by a query, including only rows that satisfy a specified condition.

The SQL GROUP BY clause groups rows with the same values in specified columns into summary rows, typically used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

SQL JOINs combine rows from two or more tables based on a related column, enabling queries that span multiple tables in a relational database.

A SQL subquery is a query nested inside another SQL statement, used to compute values, filter rows, or define temporary datasets for the outer query.

The SQL HAVING clause filters groups produced by GROUP BY, allowing conditions on aggregate functions like COUNT, SUM, and AVG that cannot be used in WHERE.

SQL aggregate functions perform calculations on a set of rows and return a single summary value, including COUNT, SUM, AVG, MIN, and MAX.

NULL in SQL represents the absence of a value or unknown data, and it behaves differently from zero or empty string — NULL is not equal to anything, including itself.

The SQL CASE WHEN expression evaluates a list of conditions and returns a value for the first true condition, functioning as SQL's if-then-else logic.

PostgreSQL supports all standard SQL JOIN types plus LATERAL joins, which allow subqueries in the FROM clause to reference columns from preceding table references.

MySQL implements all standard SQL JOIN types with its own optimizer that uses hash joins, block nested loop joins, and index-based nested loops for performance.

MySQL GROUP BY aggregates rows with identical values and, in strict mode (ONLY_FULL_GROUP_BY), requires all non-aggregated SELECT columns to be in the GROUP BY clause.

SQL window functions perform calculations across a set of table rows related to the current row without collapsing rows into groups, unlike GROUP BY.

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword that exists only for the duration of a single SQL statement.

PostgreSQL window functions compute values across a 'window' of rows related to the current row using OVER(), enabling analytics without collapsing rows.

PostgreSQL CTEs support recursive queries, writeable CTE operations (INSERT/UPDATE/DELETE), and explicit MATERIALIZED/NOT MATERIALIZED hints for performance control.

A database index is a data structure that improves the speed of data retrieval operations by creating a sorted reference to one or more columns of a table.

SQL query optimization is the process of rewriting queries and adjusting database settings to minimize execution time and resource consumption.

PostgreSQL supports multiple index types including B-tree, Hash, GiST, GIN, BRIN, and SP-GiST, each optimized for different data types and query patterns.

MySQL uses B-tree indexes by default (InnoDB), with additional support for FULLTEXT, SPATIAL, and Hash indexes (Memory engine only).

Database normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by decomposing tables according to normal form rules.

A SQL transaction is a sequence of one or more SQL statements executed as a single atomic unit, ensuring all changes either fully commit or fully roll back.