SQL For Data Analysis

deck is a focused introduction to the SQL skills you'll use every day as a data analyst. The cards walk you through the building blocks of querying data, from basic SELECT statements and filtering with WHERE and HAVING, to sorting with ORDER BY, paging through results with LIMIT and OFFSET, and handling missing values with NULL checks and COALESCE. It's the kind of foundational knowledge that pays off in every database you touch, whether you're pulling reports in MySQL, PostgreSQL, or another dialect.

It's a great fit if you're new to SQL or preparing for a data analyst interview, bootcamp, or your first analytics role. Even if you've written queries before, the cards are useful for shoring up small but important details, like how COUNT(*) differs from COUNT(column) or what default sort ORDER BY applies. Working through them is also a handy way to build confidence before sitting down at a real database and exploring on your own.

Because the concepts build on each other, try reviewing a few cards at a time rather than cramming everything in one sitting. Spacing your practice over several days helps the syntax stick, and pairing the flashcards with hands-on exercises against a sample database will deepen your understanding even further. Don't worry about getting every card right on the first pass — repeated review is exactly how these patterns become second nature.

SQL, or Structured Query Language, is the standard language for querying and manipulating relational data. At its core, every data-retrieval query begins with the SELECT statement, which pulls rows from one or more tables. The supporting clauses give you precise control over which rows appear and how they are ordered. WHERE filters individual rows before any grouping occurs, while the related HAVING clause filters aggregated groups after GROUP BY has done its work, making it the right choice for conditions on totals, averages, or counts. The DISTINCT keyword removes duplicate rows so the result set contains only unique values, and ORDER BY sorts the output; by default this is ascending, but you can reverse it with the DESC keyword. To paginate through results, LIMIT sets the maximum number of rows returned while OFFSET specifies how many rows to skip before output begins — for example, LIMIT 10 OFFSET 20 yields rows 21 through 30.

Aliases give temporary names to columns or tables, making queries easier to read and letting you reference computed expressions elsewhere in the same query. You can write either SELECT column AS alias_name or simply SELECT column alias_name, and the same applies to tables in the FROM clause. A critical concept for understanding why some references work and others do not is the order of execution of a SQL query: the engine processes FROM and JOIN first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT/OFFSET. Because SELECT runs after WHERE and GROUP BY but before ORDER BY, a column alias declared in SELECT is unavailable in WHERE or GROUP BY but perfectly legal in ORDER BY.

No treatment of SELECT is complete without addressing NULL, the special marker representing the absence of any value, distinct from zero and from the empty string. To test for NULL, use IS NULL or IS NOT NULL; equality comparisons such as = NULL are never true because NULL is not comparable to anything, including itself. The COALESCE function returns the first non-NULL argument from its list, giving you a clean way to substitute a default value for missing data, and aggregate functions like SUM silently ignore NULLs by default. COUNT behaves specially: COUNT(*) counts every row including those with NULL values, while COUNT(column) counts only rows where that specific column is non-NULL.

Joins are how you combine rows from multiple related tables, and choosing the right join type determines which rows appear in the output. An INNER JOIN returns only rows with matches in both tables, making it the strictest and most common form. A LEFT JOIN preserves every row from the left table and adds matched columns from the right, filling unmatched right-side columns with NULL. A RIGHT JOIN mirrors that behavior on the right side, and a FULL OUTER JOIN keeps all rows from both sides, again using NULLs where no match exists. A CROSS JOIN produces the Cartesian product — every row of one table paired with every row of the other — which is useful for combinatorial generation but can quickly produce enormous result sets.

Two special join forms deserve attention. A self join uses the same table twice with different aliases, allowing you to compare rows within that table, for example finding employees whose manager also works in the same department. A NATURAL JOIN automatically joins on every pair of columns that share a name, which is convenient but risky because adding a new identically named column later silently changes the join semantics. Most of the time you should explicitly state the join condition using ON, which accepts any arbitrary expression. The USING (col) shorthand is appropriate when both tables have a single identically named join column and you want exactly one output column for that key rather than two.

The ON clause specifies the predicate used to match rows between the two tables and behaves much like a WHERE clause for the join itself. A derived table — a subquery used in the FROM clause with an alias — can also be joined like an ordinary table, which is useful for pre-aggregating data before joining. Remember that joins participate in the FROM phase of query execution, so they are processed before WHERE and the rest of the pipeline.

Aggregation compresses many rows into summary values. The standard aggregate functions are COUNT, SUM, AVG, MIN, and MAX; COUNT totals the number of rows (or non-NULL values in a specific column), SUM and AVG work on numeric columns, and MIN and MAX work across most comparable types. Aggregates ignore NULL values by default, which is usually what you want but can occasionally mislead — for example, the average of a column with many missing entries may not represent the population you intended to summarize.

The GROUP BY clause collapses rows that share values in the listed columns into single output rows, which is the prerequisite for using aggregate functions meaningfully on each group. Once rows have been grouped, the HAVING clause filters those groups, in contrast to WHERE which filters individual rows before grouping. This is why you can reference an aggregate like SUM(amount) in HAVING but not in WHERE. When no GROUP BY is present, HAVING still works: the entire result set is treated as a single implicit group, so HAVING can filter on aggregate calculations of all rows together. COUNT has subtle variants worth noting — COUNT(*) counts every row, COUNT(column) counts only non-NULL values in that column, and COUNT DISTINCT counts unique non-NULL values.

For richer summaries, SQL provides GROUPING SETS, ROLLUP, and CUBE. GROUPING SETS let you compute multiple groupings in a single query — equivalent to taking the UNION of several GROUP BY queries, but more efficient. ROLLUP produces hierarchical subtotals (such as year, then year+month, then a grand total), while CUBE generates every possible combination of the grouped columns. Together, these constructs support cross-tabulation and multi-level reporting without writing multiple separate queries.

Window functions perform calculations across a set of rows that are related to the current row, but unlike aggregates they do not collapse the result set — every input row still appears in the output. The OVER clause controls how the window is defined and accepts three optional components: a PARTITION BY that divides the result into independent groups, an ORDER BY that defines the sequence within each partition, and a frame clause that further restricts which rows participate. A typical use case is computing a running total with SUM(amount) OVER (ORDER BY date), possibly inside a PARTITION BY to restart the running total for each group such as each customer.

Ranking and positional functions are among the most useful window features. ROW_NUMBER() assigns a unique sequential integer to each row in its partition with no ties, while RANK() and DENSE_RANK() handle ties differently: RANK leaves gaps after ties (1, 2, 2, 4) while DENSE_RANK does not (1, 2, 2, 3). NTILE(n) divides rows into n buckets as evenly as possible, useful for percentile analysis. For comparing adjacent rows, LAG(column, 1) retrieves the value from the previous row in the partition and LEAD(column, 1) retrieves the value from the next row — both returning NULL when there is no neighbor. FIRST_VALUE returns the value from the first row in the window frame, and the related PERCENT_RANK gives the relative rank as a fraction between 0 and 1.

Window frames add fine-grained control over exactly which rows are included in the calculation. The clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a frame running from the start of the partition to the current row, while ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING creates a centered sliding window of three rows. The distinction between ROWS and RANGE is subtle but important: ROWS counts physical row positions, while RANGE treats rows with equal ORDER BY values as a single peer group, so RANGE-based frames include all rows tied on the ordering key even if that is more than the literal row count specified.

A subquery is a query nested inside another query — in SELECT, FROM, WHERE, or HAVING — and provides a way to compute a value or set of values that the outer query then uses. A non-correlated subquery is independent of the outer query and runs once, with its result then used by the outer query. A correlated subquery, by contrast, references columns from the outer query and is re-evaluated for each outer row, which makes it more powerful but potentially expensive. A scalar subquery is the special case that returns exactly one row and one column, allowing it to be used wherever a single value is expected.

The EXISTS operator checks whether a subquery returns at least one row, returning TRUE or FALSE (and never NULL). It is usually the most efficient choice for "is there any matching row?" questions, particularly when the subquery is correlated. IN compares a value against a list or subquery result, returning TRUE if there is any match. While IN and EXISTS overlap in capability, EXISTS is typically faster for correlated existence checks because it can short-circuit on the first match rather than materializing the full list. As a rule, prefer JOINs for combining columns from related tables and reach for subqueries when you need existence checks, scalar values, or results logically independent of the joined columns.

Common Table Expressions (CTEs) name a temporary result set with the WITH keyword and can be referenced multiple times in the same query using the form WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name. CTEs often improve readability over nested subqueries, and a CTE defined in a WITH clause can reference any earlier CTE in the same clause. A recursive CTE references itself to traverse hierarchies, generate series, or walk graphs; the typical structure uses UNION ALL to combine an anchor member with the recursive member. For combining result sets across queries, set operations provide union-like behavior: UNION combines rows and removes duplicates, UNION ALL keeps duplicates and is therefore faster, INTERSECT returns rows present in both queries, and EXCEPT (called MINUS in some engines) returns rows from the first query that are not in the second. All queries combined with these operators must have the same number of columns in the same order with compatible data types.

Beyond the basic comparison operators, SQL offers specialized tools for working with strings, ranges, and conditional logic. The LIKE operator performs pattern matching on string columns using wildcards: the percent sign (%) matches any sequence of characters and the underscore (_) matches a single character. To match a literal percent sign or underscore, escape it with a chosen delimiter such as LIKE '%\%%' ESCAPE '\'. LIKE is case-sensitive in most engines, while PostgreSQL's ILIKE provides case-insensitive matching. For richer text search, full-text features such as MySQL's MATCH ... AGAINST and PostgreSQL's tsvector/tsquery support word-based matching over text columns.

The BETWEEN operator tests whether a value falls within an inclusive range — both endpoints are included — and IN (val1, val2, ...) tests membership in a list of values. The quantifier-style form = ANY(a, b, c) is functionally equivalent to IN, but = ALL is stricter, requiring the value to match every item in the list. The CASE expression brings conditional logic to SQL, working much like an if/else chain: the simple form compares one expression against several values (CASE x WHEN val THEN result ...), while the searched form evaluates independent Boolean conditions (CASE WHEN condition THEN result ...). CASE is evaluated top to bottom and returns the result for the first match.

Several functions help you handle NULLs and convert between data types. COALESCE(x, y, z) returns the first non-NULL argument, providing a portable default-value mechanism; the SQL Server–specific ISNULL is similar but takes exactly two arguments. NULLIF(a, b) returns NULL when its two arguments are equal and otherwise returns the first argument, useful for guarding against division by zero. For type conversion, CAST(x AS INT) is the ANSI-standard portable syntax, while SQL Server's CONVERT adds a style argument for date and format conversions; some engines also perform implicit conversion in expressions like SELECT 1 + '2', where MySQL coerces the string to a number and returns 3. For date and time manipulation, EXTRACT pulls a subfield such as year or month from a date/time value, DATE_TRUNC truncates a value to a specified precision, NOW() returns the current timestamp including time, and CURRENT_DATE returns only the date portion. DATE stores year, month, and day; TIMESTAMP additionally stores hour, minute, second, and often fractional seconds and time zone information. Adding an INTERVAL such as INTERVAL '7 days' is portable, while adding a raw number of days depends on engine-specific arithmetic. Finally, PIVOT rotates rows into columns for cross-tabulation (often using CASE with GROUP BY), and UNPIVOT does the reverse, converting columns back into rows.

Indexes are data structures — typically B-trees — that speed up lookups at the cost of additional storage and slower writes. A clustered index determines the physical order of data in the table, so a table can have at most one; non-clustered indexes are separate structures containing pointers back to the data rows, and a table may have many. A covering index includes every column needed by a query, allowing the database to satisfy the request without touching the underlying table at all. The trade-off appears on writes: every INSERT, UPDATE, and DELETE must maintain the indexes alongside the data, so heavily indexed tables pay a price on write-heavy workloads.

Keys enforce the structural integrity of your data. A primary key uniquely identifies each row in a table, cannot be NULL, and there can be only one per table. A unique key also enforces uniqueness but allows NULLs (typically one per column in many engines) and you can declare several per table. A foreign key links a column to the primary key (or unique key) of another table, enforcing referential integrity by rejecting values that have no matching parent. When uniqueness requires more than one column, a composite key spans two or more columns and treats their combination as the identifier. Views and materialized views offer different read-side trade-offs: a view is a stored query re-executed each time it is referenced, while a materialized view stores the result physically and must be refreshed on a schedule or on demand, trading freshness for query speed.

Understanding query performance requires reading query plans. The execution plan is the sequence of steps the database engine uses to run a query, including join order, index usage, and aggregation strategy. In PostgreSQL, EXPLAIN shows the planned steps and EXPLAIN ANALYZE actually executes the query and reports real timings and row counts. A sequential scan reads every row of the table, used when no suitable index exists or when the table is small enough that scanning is faster; an index seek locates qualifying rows directly via the B-tree and is efficient for selective predicates, while an index scan walks an entire index range — still smaller than the table but not as targeted as a seek.

Data modification statements also have important distinctions. UPDATE modifies existing rows and DELETE removes them; DELETE can use WHERE, fires triggers, and is fully logged. TRUNCATE drops all rows quickly, cannot use WHERE, cannot be rolled back in some engines, and resets identity counters. UPSERT inserts a new row or, if a row with the same key already exists, updates the existing one — PostgreSQL uses INSERT ... ON CONFLICT DO NOTHING or DO UPDATE, MySQL uses INSERT IGNORE or ON DUPLICATE KEY UPDATE. Transactions group statements so they either all succeed (COMMIT) or all fail (ROLLBACK), and reliable transaction systems guarantee the ACID properties: Atomicity, Consistency, Isolation, and Durability. The four standard isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE — trade concurrency against anomalies such as dirty reads (seeing uncommitted data), non-repeatable reads (the same row returning different values within one transaction), and phantom reads (range queries returning different row sets). When two transactions each hold a lock the other needs, a deadlock occurs; the database detects the cycle and aborts one transaction so the other can proceed.