Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        SQL cheat sheet with checked examples

        Use this SQL cheat sheet to choose clauses, filter orders, group city totals, and check results with a runnable PostgreSQL example and a missing-value check.

        A person practicing on a laptop in a quiet workspace

        A SQL cheat sheet helps you choose clauses for selecting columns, filtering rows, calculating group totals, and sorting results. Use SELECT and FROM to choose data, WHERE to filter individual rows, GROUP BY and HAVING to build and filter summaries, and ORDER BY with LIMIT to control the displayed results. The PostgreSQL exercise below lets you run those core reporting steps and check the returned rows against hand calculations.

        Essential query clauses

        SQL, short for Structured Query Language, is a language for working with data in database tables. A query is an instruction that retrieves or calculates information; every executable example in this article uses PostgreSQL.

        Your task is to report cities whose paid orders meet a sales target. You'll show the recorded sales total, the number of paid orders, and the number of orders with recorded amounts. Keeping both counts makes a missing amount visible instead of letting a plausible total hide the gap.

        An aggregate function calculates one result from several input rows. SUM adds recorded amounts, while COUNT counts rows or nonmissing values, depending on its argument. GROUP BY determines which rows belong together for those calculations.

        Tasks and clause choices

        Choose a clause by deciding whether your task concerns individual rows, grouped results, or the presentation of the output. That decision matters more than memorizing a long list of keywords.

        A task-first SQL reference
        Your taskClause or functionLevelMistake to avoid
        Choose output columns and the sourceSELECT and FROMSource and outputSelecting columns you don't need
        Keep matching ordersWHEREIndividual rowsFiltering city totals before calculating them
        Calculate totals and counts per cityGROUP BY, SUM, COUNTGroups of rowsSelecting an ungrouped, nonaggregated column
        Keep cities meeting a totalHAVINGCalculated groupsConfusing a group condition with an order condition
        Sort returned citiesORDER BYOutputAssuming the database returns a meaningful order
        Restrict the number of returned rowsLIMITOutputExpecting a predictable top set without ORDER BY
        Return unique selected combinationsDISTINCTSelected outputUsing it to hide a query that creates unwanted duplicates

        The written clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, then LIMIT. A useful reasoning sequence is different: choose the source, filter rows, form groups, filter groups, select output, sort, then limit. That sequence is a teaching model, not a promise about the database's physical execution steps.

        WHERE decides whether an order enters the calculation. HAVING decides whether a city's calculated result appears. DISTINCT removes repeated combinations across the selected columns; it doesn't correct an incorrect filter or grouping choice.

        Practice what this post teaches

        A runnable city sales report

        The report below returns cities meeting a paid-sales target while exposing missing amounts. Open a fresh PostgreSQL session and run the complete script in the same connection; its temporary table exists only for that session. Expected result rows appear in comments immediately after each query.

        If you'd like to work on clause selection before running the report, use the SQL course.

        Read the initial target and model calculations before executing the first report. Then follow the threshold-change instruction inside the script without editing the starting rows. Run the final missing-value check to identify the order behind the unequal counts.

        -- Fictional exercise: all amounts are whole rupees.
        -- Initial target: paid sales of at least 2000 per city.
        -- Starting data: six orders; one paid amount is unknown.
        CREATE TEMP TABLE orders (
          order_id integer PRIMARY KEY,
          city text,
          status text,
          amount_inr integer
        );
        
        INSERT INTO orders VALUES
          (1, 'Pune', 'paid', 1200),
          (2, 'Pune', 'paid', 800),
          (3, 'Pune', 'cancelled', 5000),
          (4, 'Jaipur', 'paid', 1500),
          (5, 'Jaipur', 'paid', NULL),
          (6, 'Kochi', 'paid', 600);
        
        -- Model calculations after filtering to paid orders:
        -- Pune: 1200 + 800 = 2000; 2 orders, 2 known amounts.
        -- Jaipur: SUM ignores NULL, giving 1500;
        --         2 orders, 1 known amount.
        -- Kochi: 600; 1 order, 1 known amount.
        -- Only Pune meets the initial target.
        SELECT city,
               SUM(amount_inr) AS sales_inr,
               COUNT(*) AS paid_orders,
               COUNT(amount_inr) AS known_amounts
        FROM orders
        WHERE status = 'paid'
        GROUP BY city
        HAVING SUM(amount_inr) >= 2000
        ORDER BY sales_inr DESC, city ASC;
        
        -- Expected output:
        -- city | sales_inr | paid_orders | known_amounts
        -- Pune | 2000      | 2           | 2
        
        -- Variation target: paid sales of at least 1500 per city.
        -- Change only the HAVING threshold from 2000 to 1500,
        -- as shown in the next query. Keep all rows and other
        -- clauses unchanged. Predict the cities before running it.
        -- Jaipur now meets the target; Kochi still doesn't.
        SELECT city,
               SUM(amount_inr) AS sales_inr,
               COUNT(*) AS paid_orders,
               COUNT(amount_inr) AS known_amounts
        FROM orders
        WHERE status = 'paid'
        GROUP BY city
        HAVING SUM(amount_inr) >= 1500
        ORDER BY sales_inr DESC, city ASC;
        
        -- Expected output:
        -- city   | sales_inr | paid_orders | known_amounts
        -- Pune   | 2000      | 2           | 2
        -- Jaipur | 1500      | 2           | 1
        
        -- Missing-value check: leave the data unchanged.
        -- Run this query to find paid orders with unknown amounts.
        SELECT order_id, city, amount_inr
        FROM orders
        WHERE status = 'paid'
          AND amount_inr IS NULL
        ORDER BY order_id;
        
        -- Expected output (NULL means a missing value):
        -- order_id | city   | amount_inr
        -- 5        | Jaipur | NULL

        Read the report from FROM onward. WHERE excludes the cancelled order, GROUP BY gathers the remaining orders by city, and the aggregate functions calculate each city's figures. HAVING keeps only qualifying groups. AS gives a result column a readable name, such as sales_inr.

        ORDER BY sorts sales from highest to lowest because DESC means descending. The second sort key, city ASC, puts equal totals in ascending city-name order. Explicit tie-breaking matters when you later restrict output with LIMIT.

        A lower sales target

        Lowering the HAVING threshold admits another city without changing which orders enter the calculation.

        Pune remains in the report, Jaipur joins it, and Kochi remains outside it. Compare those predictions with the variation's expected output. If Pune's sales or order count changes, you've changed more than the threshold or you're running against different data.

        Jaipur has two paid orders but only one recorded amount. COUNT(*) counts both rows. COUNT(amount_inr) counts only rows whose amount isn't NULL. NULL represents an unknown or missing value, not a recorded zero.

        Common query mistakes

        Most errors in this report come from filtering at the wrong stage or treating missing values like ordinary numbers. Use the model calculations in the script to distinguish a syntax problem from a query that runs but answers the wrong question.

        • Filtering order amounts instead of city totals: an amount condition in WHERE tests each order separately. That approach can discard orders needed to calculate Pune's combined total. Keep the city-total test in HAVING.
        • Forgetting the paid-status filter: the cancelled order would enter Pune's calculation. Check the report against the paid-only addition preserved in the script.
        • Comparing a missing amount with equality: use IS NULL to test for missing values. Ordinary equality doesn't identify NULL values.
        • Assuming SUM proves completeness: SUM ignores NULL inputs, and a group whose inputs are all NULL produces NULL, not zero.
        • Rerunning table creation in the same session: the temporary table already exists. Open a fresh session for the whole script, or rerun only the report queries against the existing table.

        Matching counts establish only that the selected rows have recorded amounts. They don't prove that every order was loaded, every status is correct, or every amount is accurate. Separate the query's correctness from the source data's completeness.

        Practice and reference limits

        Practice by choosing the clause before typing, then checking the returned rows against a hand-calculated result. For each request, identify the source, row conditions, grouping, group conditions, and required output order.

        When you can explain why the target belongs in HAVING, try SQL interview practice and explain your clause choices aloud or in writing.

        This reference covers single-table reporting, not joins, window functions, or data-changing statements. A short cheat sheet also can't settle an unclear business definition of sales: you still need to establish how cancellations, refunds, and missing amounts should affect the report.

        SQL dialects are database-specific forms of the language. PostgreSQL uses LIMIT for row limiting; SQL Server uses TOP or OFFSET/FETCH for related tasks. Don't assume every database accepts the same syntax unchanged.

        Common questions

        What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping, while HAVING filters groups after aggregate calculations. In the worked report, WHERE keeps paid orders and HAVING keeps cities meeting the sales target.

        What order do SQL query clauses go in? For the query pattern covered here, write SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, then LIMIT. You can omit clauses your task doesn't need, but keep the remaining clauses in that order.

        Why do COUNT(*) and COUNT(column) return different results? COUNT(*) counts rows, while COUNT(column) counts only non-NULL values in that column. Jaipur therefore has two paid orders but only one recorded amount.

        Does SUM treat NULL as zero? No. SUM ignores NULL inputs, and it returns NULL if every input in a group is NULL. A recorded zero is a known value, so it isn't equivalent to a missing amount.

        Can I use this PostgreSQL cheat sheet in SQL Server? The core clause choices are useful in SQL Server, but the complete script isn't portable unchanged. Temporary-table syntax and row-limiting syntax differ, so use examples written for your database.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.