SQL and Excel cheat sheet

SELECT name, grade
FROM students;

SELECT * brings all the columns.

SELECT * FROM students
WHERE grade >= 5 AND course = 'Physics';

Combine conditions with AND, OR and NOT.

SELECT * FROM students
WHERE scholarship IS NULL;

NULL can't be compared with =: use IS NULL or IS NOT NULL.

WHERE course IN ('Physics', 'Chemistry')
WHERE grade BETWEEN 7 AND 9
WHERE name LIKE 'An%'

% = any text, _ = a single character.

SELECT * FROM students
ORDER BY grade DESC, name;

ASC (default) or DESC. You can sort by several columns.

SELECT * FROM students
ORDER BY grade DESC
LIMIT 3;

LIMIT always goes last.

SELECT COUNT(*), AVG(grade), MIN(grade), MAX(grade), SUM(credits)
FROM students;

AVG, SUM, MIN and MAX ignore NULLs.

SELECT course, COUNT(*) AS students
FROM students
GROUP BY course;

Every column in the SELECT that isn't an aggregate goes in the GROUP BY.

SELECT course, AVG(grade)
FROM students
GROUP BY course
HAVING AVG(grade) > 7;

WHERE filters rows before grouping; HAVING filters groups after.

UPPER(name)   LOWER(name)
TRIM(name)    REPLACE(text, 'a', 'b')
SUBSTR(text, 1, 4)   first_name || ' ' || last_name

|| joins text.

COALESCE(price, 0)

Returns the first value that isn't NULL.

CAST(amount AS DOUBLE)
CAST(year AS VARCHAR)

Useful when a number is stored as text.

CASE
  WHEN grade >= 9 THEN 'Outstanding'
  WHEN grade >= 5 THEN 'Pass'
  ELSE 'Fail'
END AS result

Conditions are evaluated in order.

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id;

Only the rows that match in both tables remain.

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

LEFT JOIN keeps the whole left table.

SELECT name FROM students
UNION
SELECT name FROM teachers;

UNION removes duplicates; UNION ALL keeps them.

SELECT name FROM students
WHERE grade > (SELECT AVG(grade) FROM students);

A query inside another one, in parentheses.

WITH averages AS (
  SELECT course, AVG(grade) AS average
  FROM students GROUP BY course
)
SELECT * FROM averages WHERE average > 7;

Gives a name to an intermediate result so you can use it like a table.

RANK() OVER (ORDER BY grade DESC)
ROW_NUMBER() OVER (ORDER BY grade DESC)

RANK repeats the place on ties; ROW_NUMBER doesn't.

RANK() OVER (PARTITION BY course ORDER BY grade DESC)

PARTITION BY restarts the calculation in each group.

SUM(sales) OVER (ORDER BY month)
sales - LAG(sales) OVER (ORDER BY month)

LEAD looks at the next row.

=B2*C2   =B2-C2   =B2/C2   =B2^2

Every formula starts with =.

=B2*$G$1

The $ locks the column, the row or both when you copy the formula.

=SUM(B2:B20)   =AVERAGE(B2:B20)
=MAX(B2:B20)   =MIN(B2:B20)
=COUNT(B2:B20) =COUNTA(A2:A20)

COUNT counts numbers; COUNTA counts non-empty cells.

=ROUND(AVERAGE(B2:D2),1)

The second argument is the number of decimals.

=IF(B2>=5,"Pass","Fail")

IF(condition, if true, if false).

=IF(AND(C2>=8,D2>=90%),"Yes","No")

AND requires all conditions; OR, at least one.

=COUNTIF(A2:A9,"Laboratory")
=SUMIF(A2:A9,"Laboratory",B2:B9)
=COUNTIF(B2:B9,">100")

Criteria accept >, <, <> and wildcards (*, ?).

=SUMIFS(C2:C9,A2:A9,"Lab*",B2:B9,">50")

First the range to sum, then range-criteria pairs.

=VLOOKUP(A2,$F$2:$G$20,2,FALSE)

Searches the first column of the table. FALSE = exact match.

=XLOOKUP(A2,$F$2:$F$20,$G$2:$G$20)

Searches in any column and returns from another.

=INDEX($A$2:$A$20,MATCH(E2,$C$2:$C$20,0))

Lets you look up to the left.

=IFERROR(VLOOKUP(A2,$F$2:$G$20,2,FALSE),"Not found")

Replaces #N/A or other errors with a value.

=TRIM(A2)    =PROPER(A2)
=UPPER(A2)   =LOWER(A2)

TRIM removes extra spaces.

=LEFT(A2,3)   =RIGHT(A2,4)
=MID(A2,FIND("-",A2)+1,4)

LEN(A2) returns the number of characters.

=A2&" "&B2
=SUBSTITUTE(A2,".","")
=VALUE(SUBSTITUTE(A2,".",""))

VALUE converts text that looks like a number into a number.