Module 1.2 - Essential SQL

CREATE TABLE - Creating structures

The command CREATE TABLE is part of the DDL (Data Definition Language), the subset of SQL responsible for defining the database structure. With it, you create tables and define columns, data types, and constraints. It's the first step before any operation on data.

Most commonly used data types

TEXT: Variable-length text. Ideal for names, emails, descriptions.
INT / INTEGER: Whole numbers. Ideal for quantities, manual IDs, and counters.
NUMERIC(p,s): Decimal number with precision. Ideal for monetary values. Example: NUMERIC(10,2) = up to 10 digits, 2 decimal places.
SERIAL: Auto-incrementing integer. Used as an automatic primary key.
TIMESTAMP: Date and time. Ideal for recording when something happened.
BOOLEAN: True or false. Ideal for flags (active, verified, etc.).

Constraints

When creating a table, you define constraints directly on the columns: PRIMARY KEY identifies the record, NOT NULL prevents empty values, UNIQUE prevents duplicates, REFERENCES creates a foreign key and DEFAULT defines a default value.

CREATE TABLE cliente (
  id SERIAL PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);

CREATE TABLE pedido (
  id SERIAL PRIMARY KEY,
  cliente_id INT REFERENCES cliente(id),
  total NUMERIC(10,2) NOT NULL,
  criado_em TIMESTAMP DEFAULT NOW()
);

INSERT and UPDATE - Manipulating data

After creating the structure, you need to populate the tables. The INSERT adds new records and the UPDATE modifies existing records. Together with DELETE, make up the operations CRUD (Create, Read, Update, Delete) — the foundation of any application.

DML Operations

INSERT INTO ... VALUES: Adds one or more rows. You specify the columns and their corresponding values.
Multiple INSERT: Insert multiple rows in a single command by separating the value groups with commas.
UPDATE ... SET ... WHERE: Modifies column values in records that meet the WHERE condition.

Be careful with UPDATE without WHERE

One UPDATE without a clause WHERE modifies all the table records. Always include WHERE to limit the scope. Before running an UPDATE, run a SELECT with the same WHERE to confirm which records will be affected.

-- Inserindo um registro
INSERT INTO cliente (nome, email)
VALUES ('Ana Silva', 'ana@example.com');

-- Inserindo multiplos registros de uma vez
INSERT INTO cliente (nome, email)
VALUES ('Carlos Lima', 'carlos@example.com'),
       ('Maria Santos', 'maria@example.com');

-- Atualizando um registro especifico
UPDATE cliente
SET email = 'ana.silva@example.com'
WHERE id = 1;

SELECT and WHERE - Querying data

O SELECT is the most commonly used SQL command. It reads data from tables and returns results filtered, sorted, and limited as you need. The clause WHERE filters which rows should appear in the result.

Anatomy of a SELECT

SELECT columns: Which columns to return. Use * for all of them (avoid in production).
FROM table: Which table to retrieve the data from.
WHERE condition: Filters rows. Accepts operators such as =, !=, >, <, LIKE, IS NULL, IN.
ORDER BY: Sorts the result. ASC (default) or DESC.
LIMIT: Limits the number of rows returned.

Logical Operators

Combine conditions with AND (both must be true), OR (at least one must be true) and NOT (negates the condition). Use parentheses to control precedence when combining AND and OR.

-- Selecionar colunas especificas com filtro
SELECT nome, email
FROM cliente
WHERE email IS NOT NULL;

-- Filtrar, ordenar e limitar
SELECT *
FROM pedido
WHERE total > 100
ORDER BY criado_em DESC
LIMIT 10;

-- Combinando condicoes com AND e operadores
SELECT nome
FROM cliente
WHERE email LIKE '%example.com'
  AND nome != 'Ana Silva';

JOINs - Combining tables

In the relational model, data is distributed across multiple tables. The JOIN combines rows from two or more tables based on a relationship condition (usually foreign key = primary key). This is what gives SQL real power.

Types of JOIN

INNER JOIN: Returns only rows that have a match in both tables. If a customer has no order, they don't appear.
LEFT JOIN: Returns all rows from the left table, even without a match on the right. Columns from the right are NULL.
RIGHT JOIN: The inverse of LEFT. All rows from the right, even without a match on the left.
FULL JOIN: Returns all rows from both sides, with NULL where there is no match.

Aliases make everything more readable

Use aliases (nicknames) for tables: FROM cliente c lets you reference it as c.nome instead of cliente.nome. This is especially useful when you join several tables and the columns have ambiguous names.

-- INNER JOIN: clientes que TEM pedidos
SELECT c.nome, p.total, p.criado_em
FROM cliente c
INNER JOIN pedido p ON p.cliente_id = c.id
ORDER BY p.total DESC;

-- LEFT JOIN: TODOS os clientes, mesmo sem pedidos
-- COALESCE substitui NULL por 0
SELECT c.nome, COALESCE(SUM(p.total), 0) AS total_gasto
FROM cliente c
LEFT JOIN pedido p ON p.cliente_id = c.id
GROUP BY c.nome;

GROUP BY and Aggregations - Summarizing Data

Aggregate functions transform many rows into a single summarized result. The GROUP BY groups rows that share a common value, and aggregate functions operate on each group. That's how you generate reports and statistics directly in SQL.

Aggregate functions

COUNT(*): Counts the number of rows in the group.
SUM(column): Adds the values in the column for the group.
AVG(column): Calculates the average of the values.
MIN(column) / MAX(column): Returns the smallest/largest value.
HAVING: Filters groups after aggregation (the WHERE clause for groups). Unlike WHERE, which filters rows before of the grouping.

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Example: "show customers who spent more than R$500" uses HAVING, because the total is calculated by SUM after GROUP BY.

-- Resumo por cliente: contagem, soma e media
SELECT cliente_id,
       COUNT(*) AS num_pedidos,
       SUM(total) AS total,
       AVG(total) AS media
FROM pedido
GROUP BY cliente_id
HAVING SUM(total) > 500;

DELETE and Security - Removing Carefully

O DELETE permanently deletes records. It’s a powerful and dangerous command: without WHERE, it deletes everything of the table. In practice, many applications prefer the soft delete, which marks the record as removed instead of actually deleting it.

Removal Strategies

Hard delete: DELETE FROM tabela WHERE ... Permanently removes data. Use for temporary data or when regulations require it.
Soft delete: Add a column deletado_em TIMESTAMP. Instead of deleting, use UPDATE and mark the date. The data stays in the database for auditing.
TRUNCATE: Removes ALL records at once, faster than DELETE, but without WHERE. Used for complete cleanups.

DO

Always use WHERE in DELETE
Run SELECT first to confirm
Prefer soft deletes in production
Use transactions (BEGIN/ROLLBACK)

DO NOT DO

DELETE without WHERE
TRUNCATE on production tables
Deleting without a recent backup
Ignoring foreign keys

-- SEGURO: sempre com WHERE
DELETE FROM pedido WHERE id = 5;

-- SOFT DELETE (recomendado)
-- Passo 1: adicione a coluna de controle
ALTER TABLE cliente ADD COLUMN deletado_em TIMESTAMP;

-- Passo 2: em vez de deletar, marque a data
UPDATE cliente SET deletado_em = NOW() WHERE id = 3;

-- Nas consultas, filtre os "deletados":
-- SELECT * FROM cliente WHERE deletado_em IS NULL;

Essential SQL