A Linguagem SQL: DQL, DML, DDL e DCL

O que é SQL?

SQL (Structured Query Language) é a linguagem padrão para interagir com bancos de dados relacionais. Desenvolvida na IBM nos anos 1970, foi padronizada pelo ANSI em 1986 e ISO em 1987. Apesar de existir um padrão, cada SGBD (Oracle, SQL Server, MySQL) possui extensões próprias.

O SQL é dividido em subcategorias baseadas no tipo de operação:

DQL - Exemplos de SELECT

-- SELECT básico
SELECT * FROM employees;

-- SELECT com colunas específicas
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- SELECT com filtro (WHERE)
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 50
  AND salary > 5000;

-- SELECT com ordenação
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;

-- SELECT com alias
SELECT first_name AS "Nome",
       last_name AS "Sobrenome",
       salary * 12 AS "Salário Anual"
FROM employees;

-- SELECT com DISTINCT (valores únicos)
SELECT DISTINCT department_id
FROM employees
WHERE department_id IS NOT NULL;

-- SELECT com ROWNUM (limitar resultados)
SELECT * FROM employees
WHERE ROWNUM <= 10;

-- Oracle 12c+: FETCH FIRST
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

DML - INSERT, UPDATE, DELETE

-- INSERT: Adicionar novo registro
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Data Science', 1700);

-- INSERT com subconsulta
INSERT INTO employees_backup
SELECT * FROM employees WHERE department_id = 50;

-- INSERT multi-row (Oracle)
INSERT ALL
    INTO regions (region_id, region_name) VALUES (5, 'Oceania')
    INTO regions (region_id, region_name) VALUES (6, 'Antarctica')
SELECT * FROM dual;

-- UPDATE: Modificar registros existentes
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 50;

-- UPDATE com subconsulta
UPDATE employees e
SET salary = (
    SELECT AVG(salary) * 1.05
    FROM employees
    WHERE department_id = e.department_id
)
WHERE performance_rating = 'A';

-- DELETE: Remover registros
DELETE FROM employees
WHERE employee_id = 999;

-- DELETE com subconsulta
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- MERGE: Combina INSERT e UPDATE (UPSERT)
MERGE INTO employees_target t
USING employees_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary, t.last_update = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, salary)
    VALUES (s.employee_id, s.first_name, s.last_name, s.salary);

DDL - CREATE, ALTER, DROP

-- CREATE TABLE
CREATE TABLE projects (
    project_id    NUMBER(10) PRIMARY KEY,
    project_name  VARCHAR2(100) NOT NULL,
    start_date    DATE DEFAULT SYSDATE,
    end_date      DATE,
    budget        NUMBER(12,2),
    status        VARCHAR2(20) DEFAULT 'ACTIVE'
                  CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED'))
);

-- CREATE INDEX
CREATE INDEX idx_projects_status ON projects(status);
CREATE UNIQUE INDEX idx_projects_name ON projects(project_name);

-- CREATE VIEW
CREATE OR REPLACE VIEW vw_active_projects AS
SELECT project_id, project_name, budget
FROM projects
WHERE status = 'ACTIVE';

-- CREATE SEQUENCE
CREATE SEQUENCE seq_project_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

-- ALTER TABLE: Adicionar coluna
ALTER TABLE projects ADD (manager_id NUMBER(10));

-- ALTER TABLE: Modificar coluna
ALTER TABLE projects MODIFY (project_name VARCHAR2(200));

-- ALTER TABLE: Renomear coluna
ALTER TABLE projects RENAME COLUMN budget TO total_budget;

-- ALTER TABLE: Adicionar constraint
ALTER TABLE projects ADD CONSTRAINT fk_project_manager
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id);

-- DROP: Remover objetos (CUIDADO!)
DROP INDEX idx_projects_status;
DROP VIEW vw_active_projects;
DROP SEQUENCE seq_project_id;
DROP TABLE projects CASCADE CONSTRAINTS;  -- Remove com dependências

-- TRUNCATE: Apaga todos os dados (mais rápido que DELETE)
TRUNCATE TABLE logs;  -- Não pode ser revertido com ROLLBACK!

DCL - GRANT e REVOKE

-- GRANT: Conceder privilégios
-- Privilégios de sistema
GRANT CREATE SESSION TO novo_usuario;
GRANT CREATE TABLE, CREATE VIEW TO desenvolvedor;
GRANT DBA TO admin_user;  -- Privilégio poderoso!

-- Privilégios de objeto
GRANT SELECT ON hr.employees TO relatorios_user;
GRANT SELECT, INSERT, UPDATE ON hr.departments TO app_user;
GRANT ALL ON hr.locations TO admin_user;

-- GRANT com WITH GRANT OPTION (pode repassar)
GRANT SELECT ON hr.employees TO gerente WITH GRANT OPTION;

-- GRANT para roles (grupos de privilégios)
CREATE ROLE readonly_role;
GRANT SELECT ON hr.employees TO readonly_role;
GRANT SELECT ON hr.departments TO readonly_role;
GRANT readonly_role TO analista1, analista2;

-- GRANT para PUBLIC (todos os usuários)
GRANT SELECT ON v_public_info TO PUBLIC;

-- REVOKE: Revogar privilégios
REVOKE CREATE TABLE FROM desenvolvedor;
REVOKE SELECT ON hr.employees FROM relatorios_user;
REVOKE readonly_role FROM analista1;

-- Verificar privilégios concedidos
SELECT * FROM user_tab_privs WHERE table_name = 'EMPLOYEES';
SELECT * FROM user_sys_privs;
SELECT * FROM user_role_privs;