My SQL cheatsheet

There are tons of SQL cheat sheets available online, but creating your own is the only real way to consolidate your knowledge.

As a data user, I’m more interested in how to use data rather than how to create it. That’s why I tend to skip the SQL commands related to data creation, like INSERT or CREATE.

What I really enjoy working with are EAV-style SQL databases—Entity-Attribute-Value models. They offer a flexible structure that’s perfect for handling dynamic, schema-less data, especially in analytical and exploratory contexts.

Here are a few pros and cons I’ve found working with EAV models:

✅ Pros

• Schema flexibility: Add new attributes without altering the schema.
• Great for semi-structured data: Perfect for domains like healthcare, product catalogs, or sensor data.
• Efficient storage: Avoids wide tables with tons of NULL values.
• Easy to extend: You can evolve the data model on the fly.
• User-defined fields support: Excellent for tools where end-users customize their data fields.

❌ Cons

• Complex queries: Filtering and joining across attributes can get messy fast.
• Performance issues: Can be slower due to the high level of normalization.
• Data integrity limitations: Harder to enforce types and constraints.
• Difficult reporting: Pivoting and aggregating require more complex SQL.
• Indexing challenges: It’s tough to optimize queries through indexing since values live in one column.

Code example

CTE

WITH sales_per_make AS (
SELECT car_make,
SUM(sales) AS total_sales_per_make
FROM cars c
JOIN car_sales cs
ON c.id = cs.cars_id
GROUP BY car_make
), # Note the comma
sales_sum AS (
SELECT SUM(sales) AS total_sales
FROM car_sales
) # Note NO comma
SELECT car_make,
total_sales_per_make,
total_sales
FROM sales_per_make, sales_sum ss;

LEFT JOIN

SELECT *
FROM A Keep all rows from A
LEFT JOIN B
ON A.id = B.a_id;

COALESCE

Returns the first non-NULL value.

STRING_AGG()

Combines multiple values into a single cell, separated by a comma or another delimiter.

CASE WHEN

SELECT
s.respondent_id,
MAX(CASE WHEN s.question_code = ‘Q_AGE’ THEN r.response_label END) AS Q_AGE,MAX(CASE WHEN)
MAX(CASE WHEN s.question_code = ‘Q_SEXE’ THEN r.response_label END) AS Q_SEXE
FROM survey s
JOIN responses r ON s.response_code = r.response_code
GROUP BY s.respondent_id;

PROCEDURE

BEGIN
– 1. Declare the string variables to hold parts of the final dynamic SQL
DECLARE strSQLFlat STRING;
DECLARE strSQLTall STRING;
DECLARE strSQLTabulate STRING;
DECLARE strSQLFullCommand STRING;
DECLARE strSQLWhereClause STRING;
– 2. Build the “Tall” part of the SQL (EAV-style, long format data)
SET strSQLTall = CONCAT(
“WITH BPtall AS (”,
“SELECT … FROM …”,– first part: categorical data (joined from response mapping)
“UNION ALL”,
“SELECT … FROM …”,– second part: other data types (numeric, date, text, etc.)
“ORDER BY …)”,
);
– 3. Construction de strSQLFlat (transformation vers format “wide”)
SET strSQLFlat = (
WITH fieldList AS (
SELECT * FROM UNNEST({columns}) AS strVarName
)
SELECT STRING_AGG(…) FROM (…) – logic varies depending on metricType (categorical vs numeric)
);
– 4. Set the WHERE clause (can be custom or dynamic)
SET strSQLWhereClause = “{whereclause}”;
– 5. Compose the final SELECT command using all previously built parts
SET strSQLTabulate = CONCAT(
“SELECT * FROM bpFlat”,
strSQLWhereClause
);
SET strSQLFullCommand = CONCAT(
strSQLTall,
strSQLFlat,
strSQLTabulate
);
– 6. Run the dynamic SQL query
EXECUTE IMMEDIATE strSQLFullCommand;
END;