SQL Cheatsheet


  • CREATE TABLE <table_name> (<col_name> <col_type>, ...);
  • INSERT INTO <table_name> (col_name, ...) VALUES (col_value);
    • If supplying a value for every column, (col_name, ...) can be omitted
  • SELECT <col_name>, ... FROM <table_name>;
  • ALTER TABLE <table_name> ...
    • ... RENAME TO <new_table_name>;
    • ... RENAME COLUMN <col_name> TO <new_col_name>;
    • ... ADD COLUMN <col_name> <col_type>;
    • ... DROP COLUMN <col_name>;
  • DELETE FROM <table_name>; :insert sirens:
  • UPDATE <table_name> SET <col_name> = <new_val>, ...;


  • WHERE: for filtering
    • SELECT name FROM users WHERE age > 18;
    • Accepts =, <, <=, >, >=, IS NULL, IS NOT NULL
    • Logical operators AND and OR can be used to combine filters
  • IN: for filtering from a list
    • SELECT * FROM users WHERE country_code IN ("CA", "US", "MX");
  • LIKE: for very simple string matching
    • % stands for 0 or more characters
    • _ stands for 1 character
    • SELECT name FROM products WHERE name LIKE "%banana%"; (name contains "banana")
  • LIMIT: puts a cap on the number of records returned
    • SELECT * FROM users WHERE country_code = "US" LIMIT 50;
  • AS: for single time column renaming
    • SELECT employee_id AS id FROM employees;
    • Useful when calculating new values in a query
  • IIF: ternary function
    • SELECT IIF(quantity < 10, "Low Stock", "In Stock") AS stock FROM inventory;
  • BETWEEN: for checking ranges
    • SELECT * FROM users WHERE age BETWEEN 18 AND 65;
  • DISTINCT: for removing duplicates in results
    • SELECT DISTINCT region FROM users;
  • ORDER BY: for sorting records
    • SELECT * FROM users ORDER BY age;
    • By default sorts by ASC, but can also specify DESC
    • Must come before LIMIT
  • GROUP BY: combines rows into records with matching values
    • SELECT user_id, sum(amount) FROM transactions GROUP BY user_id;
  • HAVING: WHERE but for groups instead of individual records
    • SELECT sum(amount) AS balance FROM transactions GROUP BY user_id HAVING balance > 20;


  • PRIMARY KEY (implies NOT NULL)
  • FOREIGN KEY ties a column to values from another table
    • CREATE TABLE users(id PRIMARY KEY, country_code TEXT, FOREIGN KEY (country_code) REFERENCES countries(code)

SQLite Data Types

  • NULL
  • REAL - as in numbers. AKA float
  • TEXT - aka strings
  • BLOB - Binary Large Object
  • BOOLEAN - Technically represented as 0/1, like C does


  • count: returns an integer count of the number of results that were returned, instead of the actual results
    • SELECT count(*) FROM users;
  • sum: returns an integer sum of the selected records
    • SELECT sum(amount) FROM transactions WHERE user_id=2;
  • max: returns the single largest value from the selected records
    • SELECT max(amount) FROM transactions;
  • min: returns the single smallest value from the selected records
    • SELECT min(amount) FROM transactions;
  • avg: returns the average value from the selected records
    • SELECT avg(age) from users;
  • round(<value>, <precision>): round a value, where <precision> is the number of digits past the decimal point
    • SELECT round(avg(age)) from users;


Insert queries into other queries by surrounding them with parentheses.

SELECT id, song_name, artist_id
FROM songs
WHERE artist_id IN (
    SELECT id
    FROM artists
    WHERE artist_name LIKE 'Rick%'


  • Inner Join: Match each record from the first table with a record from the second table, but only the ones that have a match. This is also the default join.
    • SELECT * FROM users INNER JOIN countries ON users.country_code = countries.code;
  • Left Join: Match each record from the first table with a record from the second table, and return every record from the first table.