SQL Cheat Sheet

SQL Cheat Sheet

This SQL cheat sheet—part of our Complete Guide to SQL—provides a quick reference for common SQL operations and functions, adapted to work with the Classic Models database.

The SQL query examples provided in the cheat sheet use the table names: productlines, products, orderdetails, employees, customers, orders, offices, and payments as shown in the database diagram at the bottom of this page. This structure represents a model car business, so the examples have been tailored to fit this context.


Have the Dataquest SQL Cheat Sheet at your fingertips when you need it!

Table of Contents

Selection Queries
Selection Queries

Select, Order By, Distinct

Aggregate Functions
Aggregate Functions

Sum, Avg, ROUND, Min, Max, Group By, HAVING, COUNT

String Functions
String Functions

Upper, Lower, Length, SUBSTR, Concatenation

Conditional Queries
Conditional Queries

Case, COALESCE, Cast

Combine Data
Combine Data

Union, Union All, Except, Intersect

Window Functions
Window Functions

Partition BY, Order By, PARTITION BY AND ORDER BY

Ranking Functions
Ranking Functions

DENSE_RANK, RANK, Row Number, NTile

Joins
Joins

Inner Join, Left Join, Right Join, Cross Join, JOIN MULTIPLE, JOIN SELF

Subqueries
Subqueries

SELECT, FROM, WHERE, IN, EXISTS, Correlated Subquery, =, CTE

SQLite and PostgreSQL
SQLite and PostgreSQL

SQLite-specific commands, 
PostgreSQL-specific commands

Selection Queries

Selection Queries

    Clause

    How to use

    Explained

    Select

    SELECT * 
      FROM products;

    Display all columns from products table.

    SELECT productName, 
           buyPrice
      FROM products;

    Display only productName and buyPrice columns from products table.

    Order By

    SELECT productName, 
           buyPrice
      FROM products
     ORDER BY buyPrice DESC;

    Sort the selected columns by buyPrice in descending order.

    SELECT productName, 
           buyPrice

      FROM products

     ORDER BY productName ASC;

    Sort the selected columns by productName in ascending order.

    SELECT orderNumber, 
           customerNumber,
           orderDate
      FROM orders

     ORDER BY customerNumber ASC, orderDate DESC;

    Sorts the data by customerNumber and then by orderDate within each customerNumber.

    Distinct

    SELECT DISTINCT productLine
      FROM products;

    Retrieve unique values from productLine in products table.

    SELECT country,
           DISTINCT city
      FROM customers
     ORDER BY country, city;

    Retrieve unique combinations of city and country where customers are located, sorted by country and then city.

    Aggregate Functions

    Aggregate Functions

      Clause

      How to use

      Explained

      SUM

      SELECT SUM(quantityOrdered * priceEach) AS total_sales
        FROM orderdetails;

      Calculates the total sales from the orderdetails table.

      AVG

      SELECT AVG(buyPrice) AS average_price
        FROM products;

      Averages the buyPrice values in products.

      ROUND

      SELECT ROUND(AVG(buyPrice), 2) AS average_price
        FROM products;

      Rounds the average of buyPrice to two decimal places.

      MIN

      SELECT MIN(buyPrice) AS lowest_price

        FROM products;

      Finds the minimum value in the buyPrice column of products.

      MAX

      SELECT MAX(buyPrice) AS highest_price
        FROM products;

      Rounds the average of buyPrice to two decimal places.

      COUNT

      SELECT COUNT(*) AS total_orders
        FROM orders;

      Counts the total number of rows in orders.


      GROUP BY

      SELECT productLine, 
             AVG(buyPrice) AS avg_price
        FROM products
       GROUP BY productLine;

      Groups rows by productLine and calculates the average price for each product line.

      SELECT productLine, 
             AVG(buyPrice) AS avg_price
        FROM products
       WHERE buyPrice > 100
       GROUP BY productLine;

      Groups rows by productLine for products with price over 100 and calculates the average price for each product line.

      SELECT customerNumber, 
             COUNT(orderNumber) AS order_count
        FROM orders
       WHERE orderDate >= '2023-01-01'
       GROUP BY customerNumber
       ORDER BY order_count DESC;

      Groups orders by customerNumber, counts the number of orders for each customer in 2023 and beyond, and sorts the results by the order count in descending order. This shows which customers placed the most orders in 2023.

      HAVING

      SELECT productLine, 
             AVG(buyPrice) AS avg_price
        FROM products
       GROUP BY productLine
      HAVING AVG(buyPrice) > 50;

      Filters product lines to only include those with average price greater than 50.

      COUNT

      SELECT COUNT(*) AS total_products
        FROM products;

      Counts the total number of rows in the products table, returning the total number of products. This includes all rows, regardless of NULL values in any columns.

      SELECT COUNT(reportsTo) AS employees_with_manager
 
        FROM employees;

      Counts the number of non-null values in the reportsTo column of the employees table, showing how many employees have a manager assigned. COUNT ignores NULL values, so employees without a manager (e.g., the president) are not included in this count.

      Note COUNT(*) includes all rows, while COUNT(column_name) excludes NULL values in the specified column.
      String Functions

      String Functions

        Clause

        How to use

        Explained

        UPPER

        SELECT UPPER(productName) AS uppercase_name
          FROM products;

        Converts the productName column values to uppercase.

        LOWER

        SELECT LOWER(productName) AS lowercase_name
          FROM products;

        Converts the productName column values to lowercase.

        LENGTH

        SELECT productName, 
               LENGTH(productName) AS name_length
          FROM products;

        Calculates the length of each value in the productName column.

        SUBSTR

        SELECT productLine,
               SUBSTR(productLine, 1, 3) AS product_category 
          FROM products;

        Extracts the first three characters from the productLine column. SUBSTR extracts a substring from a given string. It can be used to extract characters from the beginning, end, or any position within the string.

        SELECT productCode,
               SUBSTR(productCode, -4) AS product_id 
          FROM products;

        Extracts the last four characters from the productCode column.

        Concat (using ||)

        SELECT firstName || ' ' || lastName AS full_name
          FROM employees;

        Concatenates firstName and lastName with a space in between.

        SELECT firstName || '.' || lastName || '@modelcars.com' 
                   AS email_address
        
 FROM employees;

        Creates an email address by concatenating first name, last name, and a domain.

        Conditional Queries

        Conditional Queries

          Clause

          How to use

          Explained

          CASE

          SELECT productName, 
                 buyPrice,
                 CASE
                 WHEN buyPrice < 50 THEN 'Budget'
                 WHEN buyPrice BETWEEN 50 AND 100 THEN 'Mid'
                 ELSE 'Premium'
                 END AS price_category
           FROM products;

          Categorizes the buyPrice values into 'Budget', 'Mid', and 'Premium' categories.

          SELECT orderNumber, 
                 orderDate,
                 CASE 
                 WHEN CAST(strftime('%m', orderDate) AS INTEGER)  
                      BETWEEN 3 AND 5 THEN 'Spring Sale'
                 WHEN CAST(strftime('%m', orderDate) AS INTEGER) 
                      BETWEEN 6 AND 8 THEN 'Summer Sale'
                 WHEN CAST(strftime('%m', orderDate) AS INTEGER)  
                      BETWEEN 9 AND 11 THEN 'Fall Sale'
                 ELSE 'Winter Sale'
                 END AS sale_season
            FROM orders;

          Categorizes orders into different sale seasons based on the order date.

          Coalesce

          SELECT productName,
                 COALESCE(productDescription, 
                          'No description available'
                 ) AS product_description
            FROM products;

          Returns 'No description available' if productDescription is NULL.

          SELECT employeeNumber, 
                 firstName, 
                 lastName,
                 COALESCE(extension, 
                          email, 
                          'No contact information'
                 ) AS contact_info
            FROM employees;

          Returns the first non-null value among extension, email, or 'No contact information'.

          Cast

          SELECT orderNumber, 
                 CAST(orderDate AS DATE) AS order_day
            FROM orders;

          Converts the orderDate to DATE type.

          Combine Data

          Combine Data

            Clause

            How to use

            Explained

            Union

            SELECT productName
              FROM products
             WHERE productLine = 'Classic Cars'
            
            UNION
            
            SELECT productName
              FROM products
             WHERE productLine = 'Vintage Cars';

            Combines the product names from Classic Cars and Vintage Cars product lines, removing duplicates.

            Union All

            SELECT productName 
              FROM products
             WHERE productLine = 'Classic Cars'
            
            UNION ALL
            
            SELECT productName 
              FROM products
             WHERE productLine = 'Vintage Cars';

            Combines the product names from Classic Cars and Vintage Cars product lines without removing duplicates.

            Except

            SELECT productCode, productName
              FROM products
            
            EXCEPT
            
            SELECT productCode, productName
              FROM products
             WHERE productLine = 'Classic Cars';

            Returns products EXCEPT the Classic Cars product line, demonstrating how EXCEPT removes rows from the first result that appear in the second result.

            Intersect

            SELECT customerNumber, customerName
              FROM customers
             WHERE country = 'USA'
            
            INTERSECT
            
            SELECT customerNumber, customerName
              FROM customers
             WHERE creditLimit > 100000;

            Returns customers who are both located in the USA AND have a credit limit over 100,000. This query demonstrates how INTERSECT finds common rows between two result sets.

            Note EXCEPT and INTERSECT are not supported in all SQL databases. These examples use PostgreSQL syntax.
            Window Functions

            Window Functions

              Note SQLite does not support window functions natively. The following examples use PostgreSQL syntax and require PostgreSQL or a SQLite extension.

              Clause

              How to use

              Explained

              PARTITION BY

              SELECT employeeNumber, 
                     officeCode, 
                     extension, 
                     AVG(LENGTH(extension)) OVER (
                         PARTITION BY officeCode
                     ) AS avg_extension_length
                FROM employees;

              Calculates the average extension length within each office. The PARTITION BY clause divides the data into partitions based on the officeCode column.

              Order By

              SELECT employeeNumber, 
                     officeCode, 
                     extension, 
                     SUM(LENGTH(extension)) OVER (
                         ORDER BY LENGTH(extension) DESC
                     ) AS running_total_length
                FROM employees;

              Calculates a running total of extension lengths ordered by length in descending order.

              PARTITION BY AND ORDER BY

              SELECT employeeNumber, 
                     officeCode, 
                     extension, 
                     SUM(LENGTH(extension)) OVER (
                         PARTITION BY officeCode
                         ORDER BY LENGTH(extension) DESC
                     ) AS running_total_length
                FROM employees;

              Calculates a running total of extension lengths within each office, ordered by length.

              Ranking Functions

              Ranking Functions

                Note SQLite does not support ranking functions natively. The following examples use PostgreSQL syntax and require PostgreSQL or a SQLite extension.

                Clause

                How to use

                Explained

                Dense Rank

                SELECT productCode, 
                       productName, 
                       buyPrice,
                       DENSE_RANK() OVER (
                           ORDER BY buyPrice DESC
                       ) AS price_rank
                  FROM products;

                Ranks products based on buyPrice in descending order. Differs from RANK by handling ties differently (no gaps in ranking).

                RANK

                SELECT employeeNumber, 
                       officeCode, 
                       extension,
                       RANK() OVER (
                           PARTITION BY officeCode 
                           ORDER BY LENGTH(extension) DESC
                       ) AS extension_rank_in_office
                  FROM employees;

                Ranks employees within each office based on their extension length. Differs from DENSE_RANK by leaving gaps in ranking when there are ties.

                ROW NUMBER

                SELECT orderNumber, 
                       orderDate,
                       customerNumber,
                       ROW_NUMBER() OVER (
                           ORDER BY orderDate, customerNumber
                       ) AS order_number
                  FROM orders;

                Assigns a unique row number to each order based on orderDate and customerNumber.

                Joins

                  Clause

                  How to use

                  Explained

                  INNER JOIN

                  SELECT o.orderNumber, 
                         o.orderDate, 
                         c.customerName
                    FROM orders AS o
                   INNER JOIN customers AS c 
                      ON o.customerNumber = c.customerNumber;

                  Joins orders and customers tables, returning only matching rows. This is the default join type when JOIN is used without specifying LEFT, RIGHT, or FULL.

                  LEFT JOIN

                  SELECT p.productCode, 
                         p.productName, 
                         od.orderNumber
                    FROM products AS p
                    LEFT JOIN orderdetails AS od 
                      ON p.productCode = od.productCode;

                  Joins products and orderdetails tables, returning all products and their orders (if any).

                  RIGHT JOIN

                  SELECT e.employeeNumber, 
                         e.lastName, 
                         o.officeCode
                    FROM offices AS o
                   RIGHT JOIN employees AS e 
                      ON o.officeCode = e.officeCode;

                  Joins offices and employees tables, returning all employees and their offices (if any).

                  CROSS JOIN

                  SELECT p.productName, 
                         pl.textDescription
                    FROM products AS p
                   CROSS JOIN productlines AS pl;

                  Returns all possible combinations of products and product line descriptions.

                  join multiple

                  SELECT o.orderNumber, 
                         c.customerName, 
                         p.productName
                    FROM orders AS o
                    JOIN customers AS c 
                      ON o.customerNumber = c.customerNumber
                    JOIN orderdetails AS od 
                      ON o.orderNumber = od.orderNumber
                    JOIN products p 
                      ON od.productCode = p.productCode;

                  Joins four tables: orders, customers, orderdetails, and products.

                  SELF JOIN

                  SELECT e1.firstName || ' ' || e1.lastName AS employee, 
                         e2.firstName || ' ' || e2.lastName AS manager
                    FROM employees AS e1
                    LEFT JOIN employees AS e2 
                      ON e1.reportsTo = e2.employeeNumber;

                  Self-join example listing employees and their respective managers.

                  Subqueries

                  Subqueries

                    Clause

                    How to use

                    Explained

                    Subquery in SELECT

                    SELECT productName,
                           buyPrice,
                           (SELECT AVG(buyPrice) 
                              FROM products) AS avg_price
                      FROM products;

                    Includes a subquery that calculates the average price for all products.

                    Subquery in FROM

                    SELECT productLine,
                           avg_price
                      FROM (
                            SELECT productLine,
                                   AVG(buyPrice) AS avg_price
                              FROM products
                             GROUP BY productLine
                           ) AS line_averages
                     WHERE avg_price < 100;

                    Finds product lines with an average price greater than 100 using a subquery.

                    Subquery in WHERE

                    SELECT productName, 
                           buyPrice
                      FROM products p1
                     WHERE p1.buyPrice < (
                           SELECT AVG(p2.buyPrice)
                             FROM products p2
                            WHERE p1.productLine = p2.productLine
                           )
                     ORDER BY productLine, 
                              buyPrice DESC;

                    This query selects products that are more expensive than the average price in their respective product line, ordered by product line and price in descending order.

                    Subquery with EXISTS

                    SELECT customerName
                      FROM customers c
                     WHERE EXISTS (
                           SELECT 1
                             FROM orders o
                            WHERE o.customerNumber = c.customerNumber
                              AND o.orderDate >= '2023-01-01'
                           );

                    Finds products that are more expensive than the average price in their product line.

                    =

                    SELECT orderNumber, 
                           orderDate, 
                           totalAmount
                      FROM orders
                     WHERE customerNumber = (
                           SELECT customerNumber
                             FROM customers
                            WHERE customerName = 'Petit Auto'
                           )
                     ORDER BY orderDate DESC;
                    

                    This query selects all orders for a specific customer named ‘Petit Auto’, ordered by date from most recent to oldest.

                    CTE

                    WITH order_totals AS (
                         SELECT orderNumber, 
                                SUM(quantityOrdered * priceEach
                                ) AS total_amount
                           FROM orderdetails
                          GROUP BY orderNumber
                    )
                    SELECT o.orderNumber, 
                           o.orderDate, 
                           ot.total_amount
                      FROM orders AS o
                      JOIN order_totals AS ot 
                        ON o.orderNumber = ot.orderNumber
                     ORDER BY ot.total_amount DESC;

                    This query calculates the total amount for each order using a common table expression CTE and then joins the orders table with the CTE to display order details with total amounts, ordered by total amount in descending order.

                    SQLite and PostgreSQL

                    SQLite and PostgreSQL

                      SQLite Commands

                      .tables

                      Lists all tables in the current database.

                      .schema table_name

                      Shows the schema for the specified table.

                      .mode column
                      .headers on

                      Sets output to column mode with headers for better readability.

                      .open filename

                      Opens a new or existing database file.

                      .save filename

                      Saves the current database to a file.

                      .quit

                      Exits the SQLite prompt.

                      Note SQLite doesn’t have a built-in user management system like PostgreSQL, so commands related to user management are not applicable.

                      PostgreSQL Commands

                      \l

                      Lists all databases.

                      \c database_name

                      Connects to a specific database.

                      \dt

                      Lists all tables in the current database.

                      \d table_name

                      Describes the specified table.

                      \du

                      Lists all roles/users.

                      \timing

                      Toggles display of query execution time.

                      \e

                      Opens the last command in an editor.

                      \i filename

                      Executes commands from a file.

                      \q

                      Exits the PostgreSQL interactive terminal.

                      Classic Models Database Diagram

                        Classic Models Database Diagram