How to Master SQL Queries, Joins and Aggregates with 74 Flashcards

Most people who "know SQL" can write a SELECT with a WHERE. The gap opens when a query needs a LEFT JOIN instead of an INNER JOIN, when WHERE refuses an aggregate and you have to reach for HAVING, or when = NULL silently returns nothing. The SQL: Queries, Joins & Aggregates deck (code HP2801 on Flip It) packs those exact traps into 74 flashcards, moving from the basic SELECT to subqueries, set operations and DDL.

What the 74 cards cover

The deck follows the order in which a real query is built, so each block prepares the next:

• Foundations: the four sublanguages (DQL, DDL, DML, DCL), keyword case-sensitivity, SELECT *, column and table aliases with AS
• Filtering: WHERE operators (=, !=, <, >, AND, OR, NOT), LIKE with % and _, IN, inclusive BETWEEN, IS NULL / IS NOT NULL
• Shaping results: ORDER BY (default ASC), DESC, DISTINCT, LIMIT vs SQL Server's TOP
• Joins: INNER, LEFT (= LEFT OUTER), RIGHT, FULL OUTER, CROSS JOIN Cartesian product, self-joins with aliases
• Aggregates: COUNT(*) vs COUNT(column), SUM, AVG, MIN, MAX, GROUP BY, HAVING, the full clause order
• Subqueries and sets: scalar and correlated subqueries, EXISTS vs IN, UNION vs UNION ALL and its column rules
• Writing data: INSERT INTO, UPDATE, DELETE, DELETE vs TRUNCATE, and what happens when you forget the WHERE
• Schema: primary and foreign keys, normalization, CREATE / ALTER / DROP TABLE, INT, VARCHAR vs CHAR, DATE vs TIMESTAMP, NOT NULL, UNIQUE vs PRIMARY KEY, DEFAULT

Sample flashcards

How to memorize the joins and aggregates

Draw the join before you flip the card. For the five join cards, sketch two overlapping circles on paper and shade the part each join returns: the intersection for INNER, the whole left circle for LEFT, both circles for FULL, and a grid of every pairing for CROSS. When the flashcard asks for RIGHT JOIN, recall the picture, not the sentence. The self-join card is the exception: draw the same table twice with different aliases.

Learn the clause order as an execution story. The card "SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT" explains three other cards at once: because WHERE runs before GROUP BY, it cannot see an aggregate, which is exactly why HAVING exists and why "Can you use aggregate functions in WHERE?" is answered No. Recite the sequence, then re-derive those answers from it.

Pair every "dangerous" card with its fix. The two omit-the-WHERE cards and the DELETE vs TRUNCATE card are asked in interviews precisely because they cause outages. Answer them out loud with the safe version of the statement (DELETE FROM t WHERE id = 5;) instead of just the definition.

Study the deck in its natural blocks (filtering and NULL, then joins, then aggregates, then subqueries and DDL), then let Flip It's spaced repetition mix all 74 cards. Open the Flip It library, search for deck HP2801 "SQL: Queries, Joins & Aggregates", and start with the NULL cards: they are the shortest and the most often wrong.