SQL Cheatsheet

Here's a cheat sheet to quickly review all SQL queries before an interview.

TABLE Queries

CREATE TABLE table_name (column1 datatype, column2 datatype, ... );

DROP TABLE table_name;

ALTER TABLE table_name ADD column_name datatype;

INSERT INTO table_name (column1, column2)
VALUES (value_or_expr1, value_or_expr2);

SELECT Queries

SELECT * FROM table_name;

SELECT DISTINCT column_name1, column_name2 FROM table_name;

SELECT column_name1 FROM table_name ORDER BY column_name2 ASC / DESC; /* (by default it is ascending if you don't write DESC) */

SELECT column_name1 FROM table_name WHERE column_name2 BETWEEN x AND y;
SELECT column_name1 FROM table_name WHERE column_name2 NOT BETWEEN x AND y;

SELECT column_name1 FROM table_name WHERE column_name1 LIKE "%XYZ%";
SELECT column_name1 FROM table_name WHERE column_name1 NOT LIKE "%XYZ%";

SELECT column_name FROM table_name ORDER BY column_name1 LIMIT number_of_rows OFFSET num_offset; /* The LIMIT will reduce the number of rows to return, and the optional OFFSET will specify where to begin counting the number rows from. */

SELECT table1_column, table2_column, …
FROM table1
INNER/LEFT/RIGHT/FULL JOIN JOIN table2 
    ON table1.id = table2.matching_id
WHERE condition(s)
ORDER BY table1_column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM table_name
WHERE constraint_expression
GROUP BY column_name

AGG_FUNC -> COUNT(*), COUNT(column), MIN(column), MAX(column), AVG(column), SUM(column)

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

A complex SQL SELECT query combining joins, aggregation, filtering, sorting, and pagination would look something like this.

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

I hope this helps you with interview prep as it does to me. I find https://sqlbolt.com/ super helpful if you want to learn SQL queries in depth. Thanks!