Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
Jhonatan Pinheiro
Loading page...
The 20 SQL commands you use every day — from SELECT to aggregations, with ready-to-copy examples.
If you are starting out with databases, these are the 20 commands you use every day. Each one comes with a practical example for you to copy and test. We go from SELECT to aggregations — the foundation of everything.
The most used command: it picks which columns to bring from a table. Use * for all of them (avoid it in production).
-- All columns
SELECT * FROM clientes;
-- Only what you need (recommended)
SELECT nome, email FROM clientes;Brings back only the rows that satisfy a condition.
SELECT nome, cidade
FROM clientes
WHERE cidade = 'São Paulo';Sorts the result. ASC (ascending, the default) or DESC (descending).
SELECT nome, criado_em
FROM clientes
ORDER BY criado_em DESC;Returns only N rows. On SQL Server it is TOP; on Oracle, FETCH FIRST.
-- PostgreSQL / MySQL / SQLite
SELECT * FROM produtos ORDER BY preco DESC LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM produtos ORDER BY preco DESC;Eliminates repeated rows from the result.
SELECT DISTINCT cidade FROM clientes;Adds new rows. Always list the columns.
INSERT INTO clientes (nome, email, cidade)
VALUES ('Ana Souza', 'ana@email.com', 'Campinas');Changes existing rows. Never forget the WHERE — without it, you update the entire table!
UPDATE clientes
SET cidade = 'Rio de Janeiro'
WHERE id = 42;Removes rows. Same as UPDATE: without WHERE, it wipes everything.
DELETE FROM clientes WHERE id = 42;Defines the structure: columns, types and constraints.
CREATE TABLE clientes (
id INTEGER PRIMARY KEY,
nome VARCHAR(120) NOT NULL,
email VARCHAR(200) UNIQUE,
cidade VARCHAR(80),
criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Adds, removes or changes columns of an existing table.
ALTER TABLE clientes ADD COLUMN telefone VARCHAR(20);
ALTER TABLE clientes DROP COLUMN telefone;Deletes the whole table (structure + data). Irreversible.
DROP TABLE clientes;
-- Only if it exists (avoids an error)
DROP TABLE IF EXISTS clientes;Choosing the right type saves space and avoids bugs.
-- Numeric: INTEGER, BIGINT, DECIMAL(10,2)
-- Text: VARCHAR(n), TEXT
-- Date/time: DATE, TIMESTAMP
-- Boolean: BOOLEAN
CREATE TABLE pedidos (
id BIGINT,
total DECIMAL(10,2),
pago BOOLEAN,
data DATE
);Identifies each row uniquely. Every table should have one.
CREATE TABLE produtos (
id INTEGER PRIMARY KEY,
nome VARCHAR(120)
);Join several conditions in the WHERE. Use parentheses to make it clear.
SELECT * FROM produtos
WHERE (categoria = 'livros' OR categoria = 'games')
AND preco < 100
AND NOT esgotado;Powerful filtering shortcuts: a list of values, a range, and pattern matching.
-- List
SELECT * FROM clientes WHERE cidade IN ('SP', 'RJ', 'MG');
-- Range
SELECT * FROM produtos WHERE preco BETWEEN 50 AND 150;
-- Pattern (% = any text)
SELECT * FROM clientes WHERE email LIKE '%@gmail.com';NULL means 'absence of value'. Never compare it with = NULL — use IS NULL.
SELECT * FROM clientes WHERE telefone IS NULL;
SELECT * FROM clientes WHERE telefone IS NOT NULL;They summarise many rows into a single value.
SELECT
COUNT(*) AS total_pedidos,
SUM(total) AS faturamento,
AVG(total) AS ticket_medio,
MIN(total) AS menor,
MAX(total) AS maior
FROM pedidos;Groups rows so you can aggregate by category. Here: revenue by city.
SELECT cidade, COUNT(*) AS pedidos, SUM(total) AS faturamento
FROM pedidos
GROUP BY cidade
ORDER BY faturamento DESC;Renames columns and tables to keep the result (and the query) readable.
SELECT p.nome AS produto, p.preco AS valor
FROM produtos AS p;Document your queries. -- for one line, /* */ for a block.
-- This is a single-line comment
SELECT nome FROM clientes; -- it also works at the end
/* Comment
spanning several lines */