Skip to content

SQL-NoSQL Cheat Sheets

SQL





SQL notes

Basic Queries

SELECT col1, col2, col3, ... FROM table1;
  • Commands are not case-sensitive: SELECT or select;
  • ; is not required;
  • Dividing an integer by an integer gives an integer; use floats with decimals.

Add conditions

SELECT col1 FROM table1
WHERE (col4 = "A") AND (col5 = "B" OR col6 = "C");

SELECT col1 FROM table1
WHERE (col4 BETWEEN 10 AND 20);
  • < > = <> <= >= AND OR.

Filter out missing values (or non-missing values)

SELECT col1 FROM table1
WHERE col4 IS NULL;

SELECT col1 FROM table1
WHERE col4 IS NOT NULL;

Set limits

SELECT col1 FROM table1
WHERE (col4 BETWEEN 10 AND 20)
LIMIT 3;
  • BETWEEN is inclusive (like <= and >=);

Find patterns

SELECT col1 FROM table1
WHERE col4 LIKE 'a%';
  • regex patterns like 'a%' for “begins with a”, "%B" for “ends with capital B”;
  • % is a joker.
SELECT col1 FROM table1
WHERE col4 IN ('Germany', "France", 'UK');
  • IN subgroups, limited to a set.

Command hierarchy

SELECT col1, col2, col3, ... FROM table1
WHERE col4 = 1 AND col5 = 2
LIMIT 3
GROUP by ...
HAVING count(*) > 1
ORDER BY col2, col3 DESC;
  • DESC or ascending by default.

Aggregate (two ways)

Opening aggregation:

SELECT COUNT(col1) FROM table1;
  • COUNT (not missing or not null values), SUM, AVG, MIN, MAX.

Closing aggregation:

SELECT col1 FROM table1
HAVING count(col2) > 1;

- count, sum, avg, min, max.

Unique values

SELECT DISTINCT col2 FROM table1;

SELECT COUNT(col1) COUNT(DISTINCT col2) FROM table1;

Compute, transform, create new columns

SELECT col1 + col2 AS col1b FROM table1;
  • + - * /.

Add views

CREATE VIEW view1 AS V1
SELECT col1 FROM table1;
  • Aliases for the headers.

SQLite



MySQL





PostgreSQL





NoSQL