Basic SQL: 200 Essential Commands

This is the foundation of SQL — the commands you will use every day, on any relational database. Each item comes with a short explanation and an example ready to copy and test.

The examples use PostgreSQL as the reference. Where a database does it differently (MySQL, SQL Server, Oracle, Firebird), the variation is noted in the example itself.

A study tip: do not memorise. Run each command, change a piece of it and see what happens. That is how it sticks.

Contents

• 1–15 · SELECT fundamentals
• 16–35 · Filtering with WHERE
• 36–45 · Sorting, limits and pagination
• 46–60 · Operators, conversions and conditionals
• 61–85 · Text functions
• 86–105 · Numeric functions
• 106–130 · Dates and times
• 131–145 · NULL, conversions and default values
• 146–163 · Aggregations and GROUP BY
• 164–178 · Inserting, updating and deleting
• 179–200 · Creating tables: types and constraints

SELECT fundamentals

Every query starts here: choosing the source of the data and what to bring back.

1. SELECT — choose columns

Brings back only the columns you asked for. It is the most used command in SQL.

SELECT nome, email
FROM clientes;

2. SELECT * — every column

Brings everything. Great for exploring, bad in production: it loads data you do not use.

SELECT * FROM clientes;

3. AS — a column alias

Renames the column in the result. It makes reports and APIs far more readable.

SELECT nome AS cliente, criado_em AS cadastro
FROM clientes;

4. Table alias

Shortens the table's name. It becomes essential once you start using JOIN.

SELECT cli.nome, cli.email
FROM clientes AS cli;

5. DISTINCT — remove duplicates

Eliminates repeated rows from the result.

SELECT DISTINCT cidade FROM clientes;

6. DISTINCT on several columns

It is the combination of all the listed columns that has to be unique.

SELECT DISTINCT cidade, estado FROM clientes;

7. SELECT without FROM

Useful for testing expressions and functions with no table involved. (On Oracle, use FROM dual.)

SELECT 2 + 2 AS resultado;

-- Oracle
SELECT 2 + 2 AS resultado FROM dual;

8. Calculated columns

You can do arithmetic straight in the SELECT and give the result a name.

SELECT nome, preco, preco * 0.9 AS preco_com_desconto
FROM produtos;

9. Concatenate columns

Joins text together. The standard is ||; MySQL and SQL Server use CONCAT().

-- SQL standard / PostgreSQL / Oracle / Firebird
SELECT nome || ' <' || email || '>' AS contato FROM clientes;

-- MySQL / SQL Server
SELECT CONCAT(nome, ' <', email, '>') AS contato FROM clientes;

10. Schema-qualified name

The object's full name: schema.table. It avoids ambiguity when there are tables with the same name.

SELECT * FROM vendas.pedidos;

11. Quoting identifiers

Need a name with a space or a capital letter? Use double quotes (brackets on SQL Server, backticks on MySQL).

SELECT "Nome Completo" FROM clientes;   -- standard
SELECT [Nome Completo] FROM clientes;   -- SQL Server

12. Single-line comment

Everything after -- is ignored. Use it to explain why the query exists.

-- Active customers in the southern region
SELECT * FROM clientes WHERE ativo = true;

13. Block comment

Comments out several lines at once with /* */. Handy for disabling passages temporarily.

/* Monthly report
   Author: the data team */
SELECT * FROM pedidos;

14. Logical order of execution

The database does not read from top to bottom: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That is why you cannot use a SELECT alias in the WHERE.

-- Error: 'total' does not exist yet in the WHERE
-- SELECT preco * qtd AS total FROM itens WHERE total > 100;

-- Right
SELECT preco * qtd AS total FROM itens WHERE preco * qtd > 100;

15. Semicolon

Ends the statement. Mandatory when you send several statements at once.

SELECT 1;
SELECT 2;

Filtering with WHERE

Filtering well is what separates a fast query from one that scans the whole table.

16. WHERE — equality

Brings back only the rows where the condition is true.

SELECT * FROM clientes WHERE cidade = 'Campinas';

17. Not equal to

<> is the SQL standard; != works on most databases.

SELECT * FROM pedidos WHERE status <> 'cancelado';

18. Greater and less than

The comparators >, <, >= and <= work with numbers, text and dates.

SELECT * FROM produtos WHERE preco >= 100;

19. AND — every condition

It only returns the row if all the conditions are true.

SELECT * FROM produtos
WHERE preco >= 100 AND categoria = 'games';

20. OR — any condition

It returns the row if at least one condition is true.

SELECT * FROM clientes
WHERE cidade = 'Campinas' OR cidade = 'Sumaré';

21. NOT — negate

Inverts the condition.

SELECT * FROM produtos WHERE NOT esgotado;

22. Parentheses and precedence

AND is evaluated before OR. Without parentheses the result is usually not what you expected.

-- Wrong: brings expensive games OR any book
SELECT * FROM produtos WHERE categoria = 'games' AND preco > 100 OR categoria = 'livros';

-- Right
SELECT * FROM produtos WHERE (categoria = 'games' OR categoria = 'livros') AND preco > 100;

23. IN — a list of values

A shortcut for several ORs over the same column.

SELECT * FROM clientes WHERE estado IN ('SP', 'RJ', 'MG');

24. NOT IN — outside the list

Careful: if the list contains a NULL, the result comes back empty. Prefer NOT EXISTS in that case.

SELECT * FROM pedidos WHERE status NOT IN ('cancelado', 'estornado');

25. BETWEEN — a range

It includes both ends. Equivalent to >= a AND <= b.

SELECT * FROM produtos WHERE preco BETWEEN 50 AND 150;

26. NOT BETWEEN

Everything outside the range.

SELECT * FROM produtos WHERE preco NOT BETWEEN 50 AND 150;

27. LIKE with %

% matches any sequence of characters, including an empty one.

SELECT * FROM clientes WHERE email LIKE '%@gmail.com';

28. LIKE with _

_ matches exactly one character.

-- Plates in the ABC-1234 format
SELECT * FROM veiculos WHERE placa LIKE '___-____';

29. Case-insensitive search

PostgreSQL has ILIKE; on the others, normalise with UPPER() on both sides.

-- PostgreSQL
SELECT * FROM clientes WHERE nome ILIKE '%silva%';

-- Portable
SELECT * FROM clientes WHERE UPPER(nome) LIKE UPPER('%silva%');

30. ESCAPE in LIKE

To search for a literal % or _, define an escape character.

-- Looks for discounts containing '50%'
SELECT * FROM promocoes WHERE texto LIKE '%50!%%' ESCAPE '!';

31. IS NULL

NULL is the absence of a value. Never use = NULL: the comparison is never true.

SELECT * FROM clientes WHERE telefone IS NULL;

32. IS NOT NULL

Only the rows that have a value filled in.

SELECT * FROM clientes WHERE telefone IS NOT NULL;

33. EXISTS — is there a related row?

True if the subquery returns at least one row. It is usually faster than IN with many rows.

SELECT cli.nome FROM clientes cli
WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

34. Filter by date

Compare against date literals. For ranges, prefer >= start AND < end (it avoids losing the last day's hours).

SELECT * FROM pedidos
WHERE criado_em >= DATE '2026-01-01'
  AND criado_em <  DATE '2026-02-01';

35. Filter a boolean

On databases with a boolean type, the column is already the condition.

SELECT * FROM clientes WHERE ativo;
SELECT * FROM clientes WHERE NOT ativo;

-- MySQL / Oracle / Firebird (0 and 1)
SELECT * FROM clientes WHERE ativo = 1;

Sorting, limits and pagination

Without ORDER BY there is no guaranteed order — even if it looks sorted in your tests.

36. ORDER BY — sort

Sorts the result. Ascending (ASC) is the default.

SELECT nome FROM clientes ORDER BY nome;

37. ORDER BY DESC

Descending order: most recent, largest value, and so on.

SELECT * FROM pedidos ORDER BY criado_em DESC;

38. Sort by several columns

It applies them in sequence: a tie on the first is broken by the second.

SELECT * FROM clientes ORDER BY estado ASC, nome ASC;

39. Sort by position

ORDER BY 2 sorts by the second column of the SELECT. Practical in quick queries, fragile in code.

SELECT cidade, COUNT(*) FROM clientes GROUP BY cidade ORDER BY 2 DESC;

40. NULLS FIRST / NULLS LAST

Controls where the nulls appear (PostgreSQL, Oracle, Firebird).

SELECT * FROM clientes ORDER BY telefone NULLS LAST;

41. LIMIT — limit the rows

Returns at most N rows (PostgreSQL, MySQL, SQLite).

SELECT * FROM produtos ORDER BY preco DESC LIMIT 10;

42. OFFSET — skip rows

Skips the first N. The basis of simple pagination.

SELECT * FROM produtos ORDER BY id LIMIT 10 OFFSET 20;

43. TOP — SQL Server

The equivalent of LIMIT on SQL Server.

SELECT TOP 10 * FROM produtos ORDER BY preco DESC;

44. FETCH FIRST — the SQL standard

The standard syntax, accepted by Oracle 12c+, PostgreSQL, SQL Server 2012+ and Firebird 3+.

SELECT * FROM produtos
ORDER BY preco DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

45. Pagination by page

Page N of size T: skip (N-1)*T. Always with a stable ORDER BY, otherwise items repeat between pages.

-- Page 3, 20 per page
SELECT * FROM produtos ORDER BY id LIMIT 20 OFFSET 40;

Operators, conversions and conditionals

The pieces you combine inside SELECT, WHERE and ORDER BY.

46. Arithmetic operators

Addition, subtraction, multiplication and division work as you expect.

SELECT preco + frete AS total, qtd * preco AS subtotal FROM itens;

47. Integer vs decimal division

Dividing two integers can truncate the result. Convert first if you want decimal places.

SELECT 7 / 2;                    -- 3 on PostgreSQL/Oracle
SELECT 7 / 2.0;                  -- 3.5
SELECT CAST(7 AS DECIMAL) / 2;   -- 3.5

48. Modulo (the remainder of a division)

% on most databases, MOD() in the standard and on Oracle.

SELECT 10 % 3 AS resto;      -- 1
SELECT MOD(10, 3) AS resto;  -- 1

49. Power

Raises a number to an exponent.

SELECT POWER(2, 10) AS resultado;  -- 1024

50. Operator precedence

Multiplication and division come before addition and subtraction. Parentheses settle any doubt.

SELECT 2 + 3 * 4 AS sem_parenteses,   -- 14
       (2 + 3) * 4 AS com_parenteses; -- 20

51. CAST — convert a type

An explicit, portable conversion between types.

SELECT CAST('2026-01-15' AS DATE) AS data,
       CAST(preco AS INTEGER)     AS preco_inteiro
FROM produtos;

52. The :: operator (PostgreSQL)

PostgreSQL's shortcut for CAST.

SELECT '42'::INTEGER, criado_em::DATE FROM pedidos;

53. Simple CASE

Compares one expression against fixed values, like a switch.

SELECT nome,
  CASE status
    WHEN 'P' THEN 'Pendente'
    WHEN 'A' THEN 'Aprovado'
    ELSE 'Outro'
  END AS situacao
FROM pedidos;

54. Searched CASE

Each branch has its own condition — more flexible than the simple CASE.

SELECT nome,
  CASE
    WHEN preco < 50  THEN 'barato'
    WHEN preco < 200 THEN 'médio'
    ELSE 'caro'
  END AS faixa
FROM produtos;

55. COALESCE — the first non-null

Returns the first argument that is not NULL. Ideal for default values.

SELECT nome, COALESCE(apelido, nome, 'sem nome') AS exibicao FROM clientes;

56. NULLIF — becomes NULL if equal

Returns NULL when the two values are the same. A classic for avoiding division by zero.

SELECT total / NULLIF(quantidade, 0) AS preco_medio FROM pedidos;

57. GREATEST and LEAST

The largest and smallest among several values on the same row (not to be confused with MAX/MIN, which work across rows).

SELECT GREATEST(preco_site, preco_loja) AS maior,
       LEAST(preco_site, preco_loja)    AS menor
FROM produtos;

58. Comparing text

The comparison follows the database's collation — which defines accents and capitalisation.

SELECT * FROM clientes WHERE nome > 'M' ORDER BY nome;

59. Booleans on each database

PostgreSQL has a native BOOLEAN; MySQL uses TINYINT(1); older Oracle and Firebird use CHAR(1) or SMALLINT.

-- PostgreSQL
SELECT * FROM clientes WHERE ativo = TRUE;

-- MySQL / Oracle / Firebird
SELECT * FROM clientes WHERE ativo = 1;

60. An expression in ORDER BY

You can sort by a calculation, not only by columns.

SELECT nome, preco, estoque
FROM produtos
ORDER BY preco * estoque DESC;

Text functions

Cleaning, formatting and comparing strings is half the work with real-world data.

61. UPPER — uppercase

Converts the whole text to uppercase.

SELECT UPPER(nome) FROM clientes;

62. LOWER — lowercase

Converts to lowercase. Useful for normalising emails before comparing them.

SELECT LOWER(email) FROM clientes;

63. INITCAP — capital initial

Puts the initial of each word in uppercase (PostgreSQL and Oracle).

SELECT INITCAP('maria da silva');  -- Maria Da Silva

64. LENGTH — size

Counts characters. On SQL Server it is called LEN.

SELECT nome, LENGTH(nome) AS tamanho FROM clientes;

-- SQL Server
SELECT nome, LEN(nome) FROM clientes;

65. TRIM — remove spaces

Takes spaces off both ends. Essential in imported data.

SELECT TRIM('  texto  ') AS limpo;

66. LTRIM and RTRIM

They remove spaces only from the left or only from the right.

SELECT LTRIM('  abc'), RTRIM('abc  ');

67. TRIM of a character

You can remove any character, not only a space.

SELECT TRIM(BOTH '0' FROM '000123000');  -- 123

68. SUBSTRING — cut out

Extracts a piece from a position onwards (counting starts at 1).

SELECT SUBSTRING(cpf FROM 1 FOR 3) AS inicio FROM clientes;

-- MySQL / SQL Server
SELECT SUBSTRING(cpf, 1, 3) FROM clientes;

69. LEFT — the first characters

Takes the first N characters.

SELECT LEFT(nome, 1) AS inicial FROM clientes;

70. RIGHT — the last characters

Takes the last N characters.

SELECT RIGHT(telefone, 4) AS final FROM clientes;

71. REPLACE — substitute

Swaps every occurrence of a passage for another.

SELECT REPLACE(telefone, '-', '') AS so_digitos FROM clientes;

72. POSITION — find a position

Returns where a passage starts (0 if it is not found). INSTR on Oracle/MySQL, CHARINDEX on SQL Server.

SELECT POSITION('@' IN email) AS pos FROM clientes;

-- SQL Server
SELECT CHARINDEX('@', email) FROM clientes;

73. LPAD — pad on the left

Fills the text out to a fixed size. A classic for codes with leading zeros.

SELECT LPAD(CAST(id AS VARCHAR), 6, '0') AS codigo FROM pedidos;  -- 000042

74. RPAD — pad on the right

The same idea, filling from the right-hand side.

SELECT RPAD(nome, 20, '.') AS coluna_fixa FROM clientes;

75. CONCAT_WS — join with a separator

Concatenates while ignoring NULLs, putting a separator between the values.

SELECT CONCAT_WS(', ', cidade, estado, pais) AS endereco FROM clientes;

76. REVERSE — reverse

Reverses the order of the characters.

SELECT REVERSE('abcdef');  -- fedcba

77. REPEAT — repeat

Repeats a piece of text N times (REPLICATE on SQL Server).

SELECT REPEAT('-', 20) AS linha;

78. SPLIT_PART — split by a delimiter

Takes the Nth part of a text separated by a delimiter (PostgreSQL).

SELECT SPLIT_PART(email, '@', 2) AS dominio FROM clientes;

79. Extract the email domain

The classic combination of SUBSTRING with POSITION, portable across databases.

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS dominio
FROM clientes;

80. STRING_AGG — join rows

Concatenates values from several rows into a single piece of text. GROUP_CONCAT on MySQL, LISTAGG on Oracle.

SELECT cliente_id, STRING_AGG(produto, ', ') AS itens
FROM pedidos GROUP BY cliente_id;

81. TO_CHAR — format as text

Converts a number or a date into text with a mask (PostgreSQL, Oracle).

SELECT TO_CHAR(total, 'FM999G999D00') AS valor FROM pedidos;

82. ASCII and CHR

Converts a character into a code and back (CHAR on SQL Server/MySQL).

SELECT ASCII('A') AS codigo, CHR(65) AS letra;

83. TRANSLATE — swap character by character

Replaces several characters at once, one by one. Great for removing punctuation.

SELECT TRANSLATE(cpf, '.-', '') AS so_digitos FROM clientes;

84. Searching with a regular expression

Filters that LIKE cannot handle. ~ on PostgreSQL, REGEXP on MySQL/Oracle.

-- PostgreSQL: digits only
SELECT * FROM clientes WHERE telefone ~ '^[0-9]+$';

-- MySQL
SELECT * FROM clientes WHERE telefone REGEXP '^[0-9]+$';

85. Count occurrences in a text

A portable trick: compare the length before and after removing the character.

SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1 AS qtd_tags
FROM produtos;

Numeric functions

Rounding and precision look like details until they become a difference in the financial report.

86. ROUND — round

Rounds to the given number of decimal places.

SELECT ROUND(1234.5678, 2) AS valor;  -- 1234.57

87. CEIL — round up

The smallest integer greater than or equal to the value (CEILING on SQL Server).

SELECT CEIL(4.1) AS resultado;  -- 5

88. FLOOR — round down

The largest integer less than or equal to the value.

SELECT FLOOR(4.9) AS resultado;  -- 4

89. TRUNC — truncate

Cuts off the decimal places without rounding.

SELECT TRUNC(4.99, 1) AS resultado;  -- 4.9

90. ABS — absolute value

Removes the number's sign.

SELECT ABS(-42) AS resultado;  -- 42

91. SIGN — the sign

Returns -1, 0 or 1 according to the number's sign.

SELECT SIGN(saldo) AS situacao FROM contas;

92. SQRT — square root

The square root of the value.

SELECT SQRT(144) AS resultado;  -- 12

93. EXP and LN

The natural exponential and the natural logarithm — the basis of growth calculations.

SELECT EXP(1) AS euler, LN(EXP(1)) AS um;

94. LOG — logarithm

A logarithm in any base.

SELECT LOG(10, 1000) AS resultado;  -- 3

95. RANDOM — a random number

Generates a value between 0 and 1 (RAND() on MySQL/SQL Server). Useful for sampling.

SELECT * FROM clientes ORDER BY RANDOM() LIMIT 10;

96. Percentage

Multiply by 100 and round. Watch out for division by zero.

SELECT ROUND(100.0 * aprovados / NULLIF(total, 0), 1) AS pct FROM metricas;

97. Discounted value

Calculated straight in the SELECT, with no application needed.

SELECT preco, ROUND(preco * (1 - desconto / 100.0), 2) AS final FROM produtos;

98. Monetary rounding

Use DECIMAL/NUMERIC for money. With FLOAT you accumulate floating-point error.

SELECT CAST(total AS DECIMAL(12,2)) AS valor FROM pedidos;

99. DECIMAL vs FLOAT

DECIMAL is exact (money); FLOAT is approximate (measurements, science).

SELECT 0.1 + 0.2 = 0.3 AS float_confia;             -- it may come back false
SELECT CAST(0.1 AS DECIMAL(2,1)) + CAST(0.2 AS DECIMAL(2,1));

100. Text to number

Convert before comparing or adding; text sorts '10' before '9'.

SELECT CAST('42' AS INTEGER) + 8 AS resultado;

101. Number to text

An explicit conversion avoids a concatenation error.

SELECT 'Pedido #' || CAST(id AS VARCHAR) AS titulo FROM pedidos;

102. An average with no division error

Multiply by 1.0 (or convert) so as not to fall into integer division.

SELECT SUM(total) * 1.0 / COUNT(*) AS ticket_medio FROM pedidos;

103. Bands with WIDTH_BUCKET

Distributes values into equally sized bands — a histogram straight in SQL (PostgreSQL, Oracle).

SELECT WIDTH_BUCKET(preco, 0, 1000, 10) AS faixa, COUNT(*)
FROM produtos GROUP BY 1 ORDER BY 1;

104. Generate a sequence of numbers

generate_series (PostgreSQL) creates rows on demand — great for calendars and testing.

SELECT * FROM generate_series(1, 10) AS num;

105. Sum with a conditional sign

Ins and outs in the same column, added according to the type.

SELECT SUM(CASE WHEN tipo = 'saida' THEN -valor ELSE valor END) AS saldo
FROM movimentos;

Dates and times

Dates are the type that generates the most bugs: time zones, month ends and range comparison.

106. CURRENT_DATE — today's date

The server's date, with no time.

SELECT CURRENT_DATE;

-- SQL Server
SELECT CAST(GETDATE() AS DATE);

107. CURRENT_TIMESTAMP — right now

The server's date and time. NOW() on PostgreSQL/MySQL, SYSDATE on Oracle.

SELECT CURRENT_TIMESTAMP;
SELECT NOW();       -- PostgreSQL / MySQL
SELECT SYSDATE FROM dual;  -- Oracle

108. Date literal

Use the ISO format YYYY-MM-DD — it is the only one with no ambiguity across databases and locales.

SELECT * FROM pedidos WHERE criado_em >= DATE '2026-01-01';

109. EXTRACT — take part of a date

Isolates the year, month, day, hour, minute or second.

SELECT EXTRACT(YEAR FROM criado_em) AS ano,
       EXTRACT(MONTH FROM criado_em) AS mes
FROM pedidos;

110. DATE_PART

The same idea as EXTRACT, with function syntax (PostgreSQL).

SELECT DATE_PART('dow', criado_em) AS dia_semana FROM pedidos;

111. DATEPART — SQL Server

SQL Server's version for extracting parts of a date.

SELECT DATEPART(YEAR, criado_em) AS ano FROM pedidos;

112. YEAR, MONTH, DAY — MySQL

Direct shortcuts on MySQL and on SQL Server.

SELECT YEAR(criado_em), MONTH(criado_em), DAY(criado_em) FROM pedidos;

113. DATE_TRUNC — truncate

Zeroes out the smaller parts: it groups by day, month or year without losing the date type.

SELECT DATE_TRUNC('month', criado_em) AS mes, COUNT(*)
FROM pedidos GROUP BY 1 ORDER BY 1;

114. Add days

With INTERVAL in the standard; each database has its own shortcut.

SELECT CURRENT_DATE + INTERVAL '7 days';      -- PostgreSQL
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);   -- MySQL
SELECT DATEADD(DAY, 7, GETDATE());            -- SQL Server

115. Subtract days

The same logic with the sign flipped.

SELECT CURRENT_DATE - INTERVAL '30 days' AS trinta_dias_atras;

116. Add months

Mind the month end: 31/01 + 1 month usually becomes 28/02.

SELECT CURRENT_DATE + INTERVAL '1 month';
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;  -- Oracle

117. The difference between dates

On PostgreSQL the subtraction returns an interval; on the others there is a dedicated function.

SELECT entrega - pedido AS dias FROM pedidos;             -- PostgreSQL
SELECT DATEDIFF(entrega, pedido) FROM pedidos;            -- MySQL
SELECT DATEDIFF(DAY, pedido, entrega) FROM pedidos;       -- SQL Server

118. AGE — a readable difference

Returns the difference in years/months/days (PostgreSQL).

SELECT AGE(CURRENT_DATE, nascimento) AS idade FROM clientes;

119. Calculate the age in years

A portable trick: the difference in years, adjusted if the birthday has not happened yet.

SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, nascimento)) AS idade
FROM clientes;

120. The first day of the month

DATE_TRUNC solves it with no string hackery.

SELECT DATE_TRUNC('month', CURRENT_DATE) AS primeiro_dia;

121. The last day of the month

The first day of the next month minus one day. LAST_DAY() on MySQL/Oracle.

SELECT DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day';
SELECT LAST_DAY(CURRENT_DATE);  -- MySQL / Oracle

122. Day of the week

Returns the day's number (the base varies: 0 or 1, Sunday or Monday — check on your database).

SELECT EXTRACT(DOW FROM criado_em) AS dia FROM pedidos;   -- 0 = Sunday
SELECT DAYOFWEEK(criado_em) FROM pedidos;                 -- MySQL: 1 = Sunday

123. The name of the month and the day

Formatting by mask.

SELECT TO_CHAR(criado_em, 'Month') AS mes,
       TO_CHAR(criado_em, 'Day')   AS dia
FROM pedidos;

124. Format a date as text

Each database has its own formatting function.

SELECT TO_CHAR(criado_em, 'DD/MM/YYYY');             -- PostgreSQL / Oracle
SELECT DATE_FORMAT(criado_em, '%d/%m/%Y');           -- MySQL
SELECT FORMAT(criado_em, 'dd/MM/yyyy');              -- SQL Server

125. Text to date

Give the input mask so you do not depend on the server's locale.

SELECT TO_DATE('15/01/2026', 'DD/MM/YYYY');          -- PostgreSQL / Oracle
SELECT STR_TO_DATE('15/01/2026', '%d/%m/%Y');        -- MySQL

126. Time zone

Converts a timestamp into another time zone. Always store in UTC and convert on display.

SELECT criado_em AT TIME ZONE 'UTC' AT TIME ZONE 'America/Sao_Paulo'
FROM pedidos;

127. Unix timestamp

Converts between the epoch (seconds since 1970) and a date.

SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS epoch;
SELECT TO_TIMESTAMP(1767225600) AS data;

128. Filter the current month

Prefer a range with >= and <: that way the column's index keeps being used.

SELECT * FROM pedidos
WHERE criado_em >= DATE_TRUNC('month', CURRENT_DATE)
  AND criado_em <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

129. Filter the last 30 days

A rolling window, heavily used in dashboards.

SELECT * FROM pedidos WHERE criado_em >= CURRENT_DATE - INTERVAL '30 days';

130. Avoid a function on the filtered column

WHERE YEAR(column) = 2026 prevents the index being used. Rewrite it as a range.

-- Slow
SELECT * FROM pedidos WHERE EXTRACT(YEAR FROM criado_em) = 2026;

-- Fast (uses the index)
SELECT * FROM pedidos
WHERE criado_em >= DATE '2026-01-01' AND criado_em < DATE '2027-01-01';

NULL, conversions and default values

NULL is neither zero nor an empty string: it is 'unknown'. That changes the whole logic.

131. What NULL is

The absence of a value. Any comparison with NULL results in 'unknown', not in true or false.

SELECT NULL = NULL AS comparacao;  -- NULL, not TRUE

132. NULL in operations

Any arithmetic with NULL results in NULL. Handle it first with COALESCE.

SELECT 10 + NULL AS resultado;                    -- NULL
SELECT 10 + COALESCE(NULL, 0) AS tratado;         -- 10

133. NULL in concatenation

In standard SQL, concatenating with NULL nullifies everything. MySQL's CONCAT() ignores it.

SELECT 'Olá ' || NULL AS resultado;               -- NULL
SELECT 'Olá ' || COALESCE(nome, 'visitante') FROM clientes;

134. NULL in aggregations

COUNT(column), SUM and AVG ignore NULL. COUNT(*), on the other hand, counts the row regardless.

SELECT COUNT(*) AS linhas, COUNT(telefone) AS com_telefone FROM clientes;

135. IFNULL / ISNULL / NVL

Database-specific versions of the two-argument COALESCE.

SELECT IFNULL(telefone, 'sem')  FROM clientes;  -- MySQL
SELECT ISNULL(telefone, 'sem')  FROM clientes;  -- SQL Server
SELECT NVL(telefone, 'sem')     FROM clientes;  -- Oracle

136. COALESCE in a chain

Tests several fields in order and returns the first one that is filled in.

SELECT COALESCE(celular, telefone, email, 'sem contato') AS contato FROM clientes;

137. Division by zero

NULLIF turns the zero into NULL, and the result becomes NULL instead of an error.

SELECT receita / NULLIF(pedidos, 0) AS ticket FROM metricas;

138. Sorting while handling NULL

COALESCE inside the ORDER BY defines where the nulls land.

SELECT * FROM produtos ORDER BY COALESCE(desconto, 0) DESC;

139. CASE to classify NULL

Makes it explicit what to do with missing data in the report.

SELECT nome,
  CASE WHEN telefone IS NULL THEN 'sem contato' ELSE telefone END AS contato
FROM clientes;

140. Implicit conversion

The database sometimes converts types on its own — and that drops the index and hides bugs. Be explicit.

-- Avoid
SELECT * FROM pedidos WHERE id = '42';

-- Prefer
SELECT * FROM pedidos WHERE id = 42;

141. TRY_CAST — a safe conversion

Returns NULL instead of an error when the value does not convert (SQL Server, PostgreSQL 16+).

SELECT TRY_CAST(codigo AS INTEGER) AS numero FROM importacao;

142. DEFAULT on columns

The value used when the INSERT does not provide the column.

CREATE TABLE pedidos (
  id INTEGER PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pendente',
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

143. NOT NULL

Stops the column being left empty. A business rule guaranteed by the database, not by the application.

ALTER TABLE clientes ALTER COLUMN email SET NOT NULL;

144. An empty string is not NULL

On nearly every database they are different things — on Oracle, an empty string becomes NULL.

SELECT * FROM clientes WHERE telefone = '';        -- empty
SELECT * FROM clientes WHERE telefone IS NULL;     -- absent

145. Count the nulls

Combines COUNT(*) with COUNT(column) to measure the data's completeness.

SELECT COUNT(*) - COUNT(telefone) AS sem_telefone FROM clientes;

Aggregations and GROUP BY

Turning thousands of rows into one number: the heart of every report.

146. COUNT(*) — count rows

Counts every row, including those with null columns.

SELECT COUNT(*) AS total FROM clientes;

147. COUNT(column)

Counts only the rows where the column is not NULL.

SELECT COUNT(telefone) AS com_telefone FROM clientes;

148. COUNT(DISTINCT)

Counts different values — how many unique customers bought something, for instance.

SELECT COUNT(DISTINCT cliente_id) AS clientes FROM pedidos;

149. SUM — add up

Adds up the values of a numeric column.

SELECT SUM(total) AS faturamento FROM pedidos;

150. AVG — average

The arithmetic mean, ignoring nulls.

SELECT ROUND(AVG(total), 2) AS ticket_medio FROM pedidos;

151. MIN and MAX

The smallest and largest value. It works with numbers, text and dates.

SELECT MIN(criado_em) AS primeiro, MAX(criado_em) AS ultimo FROM pedidos;

152. GROUP BY — group

Calculates the aggregation per category instead of for the whole table.

SELECT cidade, COUNT(*) AS clientes
FROM clientes GROUP BY cidade;

153. GROUP BY with several columns

It creates a group for each distinct combination of the columns.

SELECT estado, cidade, COUNT(*)
FROM clientes GROUP BY estado, cidade;

154. The GROUP BY rule

Every column in the SELECT that is not inside an aggregation has to be in the GROUP BY.

-- Error: nome is neither aggregated nor grouped
-- SELECT cidade, nome, COUNT(*) FROM clientes GROUP BY cidade;

SELECT cidade, COUNT(*) FROM clientes GROUP BY cidade;

155. HAVING — filter groups

It is the WHERE of groups: it runs after the aggregation.

SELECT cidade, COUNT(*) AS total
FROM clientes GROUP BY cidade
HAVING COUNT(*) > 10;

156. WHERE vs HAVING

WHERE filters rows before grouping (faster); HAVING filters the result of the aggregation.

SELECT cidade, SUM(total) AS faturamento
FROM pedidos
WHERE status = 'pago'          -- before grouping
GROUP BY cidade
HAVING SUM(total) > 10000;     -- after grouping

157. Sort by the aggregation

A ranking straight away: group, sum and sort by the result.

SELECT cidade, SUM(total) AS faturamento
FROM pedidos GROUP BY cidade
ORDER BY faturamento DESC LIMIT 10;

158. Aggregate with an expression

You can group by a calculation, such as the month of the date.

SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total)
FROM pedidos GROUP BY 1 ORDER BY 1;

159. Conditional counting

A CASE inside the SUM counts only what matters — several metrics in a single query.

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'pago' THEN 1 ELSE 0 END) AS pagos,
  SUM(CASE WHEN status = 'cancelado' THEN 1 ELSE 0 END) AS cancelados
FROM pedidos;

160. FILTER — a filter per aggregation

A cleaner version of conditional counting (PostgreSQL, SQLite).

SELECT COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'pago') AS pagos
FROM pedidos;

161. Percentage of the total

Divides the group's sum by the overall sum.

SELECT cidade,
  ROUND(100.0 * SUM(total) / (SELECT SUM(total) FROM pedidos), 1) AS pct
FROM pedidos GROUP BY cidade ORDER BY pct DESC;

162. A simple pivot with CASE

Turns rows into columns — months side by side, for example.

SELECT produto,
  SUM(CASE WHEN mes = 1 THEN qtd ELSE 0 END) AS jan,
  SUM(CASE WHEN mes = 2 THEN qtd ELSE 0 END) AS fev
FROM vendas GROUP BY produto;

163. Aggregate text per group

Joins the group's values into a readable list.

SELECT pedido_id, STRING_AGG(produto, ', ' ORDER BY produto) AS itens
FROM itens_pedido GROUP BY pedido_id;

Inserting, updating and deleting

Commands that change data. Every one of them deserves a checking SELECT first.

164. INSERT — insert one row

Always list the columns: if the table changes, your query keeps working.

INSERT INTO clientes (nome, email, cidade)
VALUES ('Ana Souza', 'ana@email.com', 'Campinas');

165. INSERT of several rows

Far faster than one INSERT per row: it is a single trip to the database.

INSERT INTO clientes (nome, email) VALUES
  ('Ana', 'ana@email.com'),
  ('Bruno', 'bruno@email.com'),
  ('Carla', 'carla@email.com');

166. INSERT ... SELECT

Copies data from a query into another table. The basis of loading and archiving.

INSERT INTO clientes_inativos (id, nome, email)
SELECT id, nome, email FROM clientes WHERE ultimo_acesso < CURRENT_DATE - INTERVAL '1 year';

167. INSERT with DEFAULT

Lets the database fill the column with the default value.

INSERT INTO pedidos (cliente_id, status) VALUES (1, DEFAULT);

168. RETURNING — give back what was inserted

Returns the recorded row, with the generated id, without needing a SELECT afterwards (PostgreSQL, Oracle, Firebird).

INSERT INTO clientes (nome) VALUES ('Ana') RETURNING id, criado_em;

169. UPDATE — update

Never forget the WHERE. Without it, you update the entire table.

UPDATE clientes SET cidade = 'Rio de Janeiro' WHERE id = 42;

170. UPDATE of several columns

Separate the assignments with commas.

UPDATE produtos SET preco = 99.90, atualizado_em = CURRENT_TIMESTAMP WHERE id = 7;

171. UPDATE with an expression

The new value can be calculated from the current one.

UPDATE produtos SET preco = preco * 1.10 WHERE categoria = 'games';

172. UPDATE with a subquery

Fetches the new value from another table.

UPDATE pedidos ped
SET cliente_nome = (SELECT nome FROM clientes cli WHERE cli.id = ped.cliente_id);

173. UPDATE ... FROM

Updates using a JOIN — more readable and faster than a subquery (PostgreSQL, SQL Server).

UPDATE pedidos ped
SET cliente_nome = cli.nome
FROM clientes cli
WHERE cli.id = ped.cliente_id;

174. DELETE — delete rows

The same rule as UPDATE: without a WHERE, it deletes everything.

DELETE FROM clientes WHERE id = 42;

175. DELETE with a subquery

Deletes based on another table.

DELETE FROM pedidos
WHERE cliente_id IN (SELECT id FROM clientes WHERE bloqueado);

176. TRUNCATE — empty the table

Far faster than DELETE with no WHERE, but it does not fire triggers and cannot always be undone.

TRUNCATE TABLE log_acessos;

177. UPSERT — insert or update

Writes if it does not exist, updates if it does. Each database has its own syntax.

-- PostgreSQL / SQLite
INSERT INTO metricas (dia, acessos) VALUES (CURRENT_DATE, 1)
ON CONFLICT (dia) DO UPDATE SET acessos = metricas.acessos + 1;

-- MySQL
INSERT INTO metricas (dia, acessos) VALUES (CURRENT_DATE, 1)
ON DUPLICATE KEY UPDATE acessos = acessos + 1;

178. Test before executing

Run the WHERE in a SELECT first. Two seconds that save a backup restore.

-- 1) Check what will be affected
SELECT * FROM clientes WHERE cidade = 'Campinas';

-- 2) Only then execute
UPDATE clientes SET ativo = false WHERE cidade = 'Campinas';

Creating tables: types and constraints

Modelling well at the start avoids months of workarounds later. A constraint in the database is worth more than validation in the application.

179. CREATE TABLE

Defines the table's columns, types and rules.

CREATE TABLE clientes (
  id INTEGER PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  email VARCHAR(200) UNIQUE,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

180. Integer types

SMALLINT, INTEGER and BIGINT change the range and the space taken. The id of a large table deserves BIGINT.

CREATE TABLE eventos (
  id BIGINT,
  tentativas SMALLINT
);

181. Decimal types

DECIMAL(p, s) is exact: p digits in total, s after the decimal point. Use it for money.

CREATE TABLE pedidos (total DECIMAL(12,2));

182. Text types

VARCHAR(n) limits the size; TEXT is unrestricted. Set a limit when the size is a business rule.

CREATE TABLE posts (
  titulo VARCHAR(200) NOT NULL,
  conteudo TEXT
);

183. Date and time types

DATE (date only), TIME (time only), TIMESTAMP (both). Prefer a timestamp with a time zone.

CREATE TABLE agenda (
  dia DATE,
  inicio TIME,
  criado_em TIMESTAMP WITH TIME ZONE
);

184. Boolean

BOOLEAN on PostgreSQL; MySQL uses TINYINT(1); older Oracle and Firebird, CHAR(1) or SMALLINT.

CREATE TABLE clientes (ativo BOOLEAN DEFAULT TRUE);

185. UUID

A globally unique identifier. Good for distributed systems; it takes more space than an integer.

CREATE TABLE sessoes (id UUID PRIMARY KEY);

186. A JSON column

Stores a variable structure inside a column. On PostgreSQL prefer JSONB (indexable).

CREATE TABLE configuracoes (
  id INTEGER PRIMARY KEY,
  dados JSONB NOT NULL DEFAULT '{}'
);

187. NOT NULL

Guarantees the column always has a value.

CREATE TABLE clientes (nome VARCHAR(120) NOT NULL);

188. DEFAULT

The value assumed when the INSERT omits the column.

CREATE TABLE pedidos (status VARCHAR(20) NOT NULL DEFAULT 'pendente');

189. PRIMARY KEY

Identifies each row uniquely and creates an index automatically.

CREATE TABLE produtos (id INTEGER PRIMARY KEY, nome VARCHAR(120));

190. Composite primary key

Two or more columns together identify the row. Common in link tables.

CREATE TABLE pedido_produto (
  pedido_id INTEGER,
  produto_id INTEGER,
  PRIMARY KEY (pedido_id, produto_id)
);

191. Auto-incrementing id

Each database has its own way of generating the next id.

-- PostgreSQL
CREATE TABLE a (id SERIAL PRIMARY KEY);
CREATE TABLE b (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- MySQL
CREATE TABLE c (id INT AUTO_INCREMENT PRIMARY KEY);

-- SQL Server
CREATE TABLE d (id INT IDENTITY(1,1) PRIMARY KEY);

192. UNIQUE

Prevents repeated values in the column. One email per customer, for instance.

ALTER TABLE clientes ADD CONSTRAINT uk_clientes_email UNIQUE (email);

193. CHECK

Validates the value in the database itself — the rule applies to any application that writes there.

ALTER TABLE produtos ADD CONSTRAINT ck_preco_positivo CHECK (preco > 0);

194. FOREIGN KEY

Guarantees the value exists in the referenced table. It is what keeps the model's integrity.

ALTER TABLE pedidos
ADD CONSTRAINT fk_pedidos_cliente FOREIGN KEY (cliente_id) REFERENCES clientes (id);

195. ON DELETE CASCADE

Deletes the children along with the parent. Powerful and dangerous: confirm that it is what you want.

ALTER TABLE itens
ADD CONSTRAINT fk_itens_pedido FOREIGN KEY (pedido_id)
REFERENCES pedidos (id) ON DELETE CASCADE;

196. ALTER TABLE — add a column

Adds a column to an existing table. With a DEFAULT on a large table, check the locking first.

ALTER TABLE clientes ADD COLUMN telefone VARCHAR(20);

197. ALTER TABLE — remove a column

Removes the column and its data. There is no way back without a backup.

ALTER TABLE clientes DROP COLUMN telefone;

198. Rename a table and a column

Renaming is cheap, but it breaks everything that references the old name.

ALTER TABLE clientes RENAME TO customers;
ALTER TABLE customers RENAME COLUMN nome TO name;

199. DROP TABLE

Deletes the structure and the data. IF EXISTS avoids an error when the table no longer exists.

DROP TABLE IF EXISTS log_temporario;

200. CREATE TABLE AS SELECT

Creates a table already filled with the result of a query. Great for a snapshot before a migration.

CREATE TABLE clientes_backup AS
SELECT * FROM clientes;