MazoCardsGit Commands Cheat Sheet
50 practical Git command flashcards for commits, branches, inspection, recovery, collaboration, and debugging history.
Programming flashcards
SQL Basics: Queries & Joins
50 SQL flashcards for SELECT, joins, aggregation, CTEs, windows, transactions, constraints, and query plans.
#sql#queries#joins#database#interview
About this deck
This 50-card SQL deck covers the path from SELECT and WHERE through joins, aggregation, subqueries, common table expressions, window functions, transactions, constraints, and EXPLAIN.
Notes identify NULL behavior, outer-join filter mistakes, and dialect differences that often trip up learners moving from toy queries to real databases.
50cards
SQL beginner to intermediatelevel
SQL beginners, analysts, backend developers, and interview learnersaudience
What you will learn
- Select, filter, sort, group, aggregate, and join table data.
- Subqueries, CTEs, set operations, and window functions.
- NULL handling, aliases, conditional expressions, and relationship checks.
- Transactions, constraints, table definitions, indexes, and query plans.
How to study this deck
- Write each query pattern against the same small users and orders schema.
- Predict row counts before running joins and group queries.
- Use EXPLAIN on working queries before trying to optimize them.
Recommended study setup
- Start with about 12 cards per session; the full deck is roughly 5 first-pass packs.
- Type or run one minimal example for each missed card; syntax recall without execution is not enough.
- Use quiz mode after one recall pass; study notes appear only after you answer.
Scope
The deck teaches portable SQL concepts with PostgreSQL-style examples. Exact syntax and behavior can differ across database systems.
Review notes
Query concepts checked against current PostgreSQL documentation and portable SQL behavior on 2026-07-10.
All 50 cards in this deck
| Front | Back | Study note |
|---|---|---|
| SELECT | Chooses which columns to return from a table. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| FROM | Names the table being queried. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| WHERE | Filters rows by a condition. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| DISTINCT | Removes duplicate rows from the result. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| ORDER BY | Sorts the result set. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| LIMIT | Restricts the number of returned rows. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| INNER JOIN | Returns rows with matching values in both joined tables. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| LEFT JOIN | Returns all rows from the left table plus matching right-table rows. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| RIGHT JOIN | Returns all rows from the right table plus matching left-table rows. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| FULL OUTER JOIN | Returns matched rows plus unmatched rows from both tables. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| GROUP BY | Groups rows so aggregate functions can summarize them. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| HAVING | Filters groups after aggregation (unlike WHERE, which filters rows). | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| COUNT(*) | Counts rows in a group or table. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| SUM() / AVG() | Add up or average the values in a column. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| INSERT INTO | Adds new rows to a table. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| UPDATE ... SET | Changes values in existing rows. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| DELETE FROM | Removes rows that match a condition. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| Primary key | A column or set of columns that uniquely identifies a row. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| Foreign key | A column that references the primary key of another table. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| INDEX | A structure that speeds up lookups on one or more columns. | Practice cue: write a small query against users and orders tables; exact syntax can vary slightly by database. |
| Column alias | Renames an output column with AS, such as total AS order_total. | Aliases improve readability and can label computed expressions. |
| Table alias | Gives a table a short name, such as FROM orders AS o. | Qualify columns like o.id when multiple tables share names. |
| AND / OR | Combine filter conditions. | Use parentheses when mixing them so precedence is explicit. |
| IN (...) | Tests whether a value matches any value in a list or subquery. | Useful replacement for a chain of OR equality checks. |
| BETWEEN | Tests an inclusive lower and upper bound. | For timestamps, a half-open range is often safer than BETWEEN whole dates. |
| LIKE | Matches text using % for many characters and _ for one character. | Case sensitivity and alternatives such as ILIKE vary by database. |
| IS NULL | Tests for a missing SQL value. | Do not use = NULL; comparisons with NULL produce unknown. |
| CASE | Returns conditional values inside a query. | Useful for labels, buckets, and conditional aggregates. |
| COALESCE() | Returns the first non-NULL argument. | Often used to provide a display or calculation fallback. |
| NULLIF(a, b) | Returns NULL when a equals b; otherwise returns a. | A common use is avoiding division by zero with NULLIF(denominator, 0). |
| MIN() / MAX() | Return the smallest or largest non-NULL value. | They work with dates and text as well as numbers. |
| Subquery | A query nested inside another SQL statement. | It may return one value, one column, or a table-shaped result. |
| WITH ... AS (CTE) | Names a query result for use by the following statement. | CTEs can make multi-step logic easier to read. |
| UNION | Combines compatible result sets and removes duplicates. | Column counts and compatible types must match. |
| UNION ALL | Combines compatible result sets without removing duplicates. | Usually faster when duplicate removal is unnecessary. |
| Window function | Calculates across related rows without collapsing them into one row per group. | Uses an OVER clause. |
| ROW_NUMBER() | Assigns a sequential number within a window ordering. | Useful for ranking and selecting one row per group. |
| PARTITION BY | Divides rows into groups for a window function. | Unlike GROUP BY, it keeps individual rows in the result. |
| EXISTS | Returns true when a subquery produces at least one row. | Often expresses relationship checks clearly and can stop at the first match. |
| Correlated subquery | References columns from the outer query. | It is logically evaluated per outer row, though the optimizer may transform it. |
| CROSS JOIN | Returns every combination of rows from both inputs. | Row count multiplies, so use intentionally. |
| Self join | Joins a table to itself using different aliases. | Useful for hierarchies, comparisons, and predecessor relationships. |
| Outer join filter pitfall | A right-table condition in WHERE can remove NULL-extended rows from a LEFT JOIN. | Put match conditions in ON when unmatched left rows must remain. |
| BEGIN / START TRANSACTION | Starts a transaction block. | Exact syntax and automatic transaction behavior vary by database. |
| COMMIT / ROLLBACK | Commit makes transaction changes permanent; rollback discards them. | Use transactions for changes that must succeed or fail together. |
| UNIQUE constraint | Prevents duplicate values in the constrained column set. | NULL handling can differ by database. |
| NOT NULL constraint | Requires a column to contain a value. | Use when absence is invalid for the domain. |
| CHECK constraint | Requires inserted or updated rows to satisfy a condition. | It protects rules at the database boundary. |
| CREATE TABLE | Defines a new table, its columns, types, and constraints. | Choose constraints with the data model, not as an afterthought. |
| EXPLAIN | Shows the database query plan. | Use it to understand scans, joins, and index use before guessing at performance fixes. |
Frequently asked questions
Does this SQL deck include joins?
Yes. It covers inner, left, right, full, cross, and self joins plus an outer-join filtering pitfall.
Does it cover advanced SQL?
It reaches CTEs, window functions, transactions, constraints, and EXPLAIN, but not database administration or deep performance tuning.
Will every query work in every database?
Core concepts are portable, but exact syntax and NULL, limit, or transaction behavior may vary by database.
Is the SQL Basics: Queries & Joins flashcard deck free?
Yes. You can study it free in your browser on MazoCards. Create a free account to save progress, track streaks, and sync across devices.
More Programming flashcards
JavaScript Array Methods
50 JavaScript array method flashcards covering transformation, search, mutation, copying methods, iteration, and common pitfalls.
Python Basics: Syntax & Data Types
50 Python basics flashcards covering core syntax, collections, functions, files, exceptions, classes, typing, and everyday tools.
React Hooks Essentials
40 React 19.2 hook flashcards covering state, effects, refs, transitions, actions, external stores, common pitfalls, and when not to use a hook.
CSS Flexbox and Grid
CSS layout flashcards covering Flexbox, Grid, alignment, tracks, gaps, and responsive patterns.
Common TCP and UDP Ports
Common network port flashcards for CompTIA-style networking, sysadmin, and developer review.
Start studying SQL Basics: Queries & Joins
Study these 50 cards free, then create an account to save your progress and build a streak.