SQL na prática: pessoa e automóvel

CREATE TABLE pessoa (
    id INT AUTO_INCREMENT,
    nome VARCHAR(100),
    profissao VARCHAR(45),
    cnh VARCHAR(1),
    PRIMARY KEY(id)
);

CREATE TABLE automovel (
    id INT AUTO_INCREMENT,
    carro_moto VARCHAR(10),
    modelo VARCHAR(45),
    preco DECIMAL(10,2),
    id_pessoa INT,               -- precisa existir antes do FOREIGN KEY
    PRIMARY KEY(id),
    FOREIGN KEY(id_pessoa) REFERENCES pessoa(id)
);

INSERT INTO pessoa (nome, profissao, cnh) VALUES
('paulo', 'maestro', 'S'),
('henrique', 'medico', 'N');

INSERT INTO automovel (carro_moto, modelo, preco, id_pessoa) VALUES
('moto', 'XT', 30000.00, 5),
('carro', 'civic', 45000.00, 5);

SELECT nome AS nome_completo,
       CASE WHEN cnh = 'S' THEN 'Sim' ELSE 'Não' END AS possui_cnh
FROM pessoa;

SELECT valor,
    CASE
        WHEN valor % 2 = 0 THEN 'Par'
        ELSE CASE
            WHEN valor % 5 = 0 THEN 'Múltiplo de 5'
            WHEN valor % 3 = 0 THEN 'Múltiplo de 3'
            ELSE 'Ímpar'
        END
    END AS analise
FROM numero;

SELECT valor FROM numero
UNION
SELECT valor FROM numero_par;
-- 1,2,3,4,5,6,7,8,9,10 (sem duplicar o que já é comum)

SELECT valor FROM numero
INTERSECT
SELECT valor FROM numero_par;
-- só os valores que existem nas duas tabelas

SELECT valor FROM numero
EXCEPT
SELECT valor FROM numero_par;
-- os valores de "numero" que NÃO aparecem em "numero_par"

SELECT pessoa.nome, automovel.modelo
FROM pessoa
JOIN automovel ON automovel.id_pessoa = pessoa.id;

SELECT pessoa.nome, automovel.modelo
FROM pessoa
LEFT JOIN automovel ON automovel.id_pessoa = pessoa.id;
-- quem não tem carro aparece com modelo = NULL

SELECT nome FROM pessoa p
WHERE NOT EXISTS (
    SELECT 1 FROM pessoa_automovel pa WHERE pa.nome_pessoa = p.nome
);

SELECT pessoa.nome,
       SUM(automovel.preco) AS total_garagem
FROM pessoa
JOIN automovel ON automovel.id_pessoa = pessoa.id
GROUP BY pessoa.id, pessoa.nome
ORDER BY total_garagem DESC;

SELECT nome_pessoa, modelo, preco, COUNT(*) AS qtd
FROM pessoa_automovel
GROUP BY nome_pessoa, modelo, preco
HAVING COUNT(*) > 1;

WITH info_temp(numero, quantidade, media) AS (
    SELECT numero, COUNT(numero) AS qtd, AVG(nota) AS media
    FROM exercicio
    GROUP BY numero
)
SELECT numero, quantidade, media
FROM info_temp
WHERE media > 6
ORDER BY quantidade DESC;

INSERT INTO calculado(exercicio, quantidade, media)
WITH info_temp(numero, quantidade, media) AS (
    SELECT numero, COUNT(numero), AVG(nota)
    FROM exercicio GROUP BY numero
)
SELECT numero, quantidade, media FROM info_temp WHERE media > 6;

WITH info_temp(numero, quantidade, media) AS (
    SELECT numero, COUNT(numero), AVG(nota)
    FROM exercicio GROUP BY numero
)
UPDATE calculado
INNER JOIN info_temp ON info_temp.numero = calculado.exercicio
SET calculado.quantidade = info_temp.quantidade,
    calculado.media = info_temp.media;

UPDATE pessoa_automovel
SET preco = (SELECT preco FROM automovel WHERE id = 1)
WHERE modelo = (SELECT modelo FROM automovel WHERE id = 1)
  AND nome_pessoa = (
      SELECT nome FROM pessoa WHERE id = (SELECT id_pessoa FROM automovel WHERE id = 1)
  );

CREATE INDEX idx_modelo ON pessoa_automovel (modelo);

-- conferir que foi criado
SHOW INDEX FROM pessoa_automovel;

-- remover
DROP INDEX idx_modelo ON pessoa_automovel;

EXPLAIN ANALYZE SELECT valor FROM numero_par WHERE valor > 100;

CREATE VIEW view_aluno AS
SELECT nome, ra
FROM aluno
ORDER BY nome;

SELECT * FROM view_aluno;
DESC view_aluno;   -- mostra só as colunas nome e ra

UPDATE view_aluno SET ra = '999' WHERE nome = 'Danilo';
-- isso muda a linha na tabela "aluno" de verdade, não só na view

DROP VIEW view_aluno;

CREATE TRIGGER <nome trigger>
<BEFORE ou AFTER> <INSERT ou UPDATE ou DELETE>
ON <tabela>
FOR EACH ROW
BEGIN
    <comandos>
END;

DELIMITER $$
CREATE TRIGGER trigger_before_insert_salario
BEFORE INSERT ON salario
FOR EACH ROW
BEGIN
    IF NEW.remuneracao < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Remuneração deve ser positiva';
    END IF;
END $$
DELIMITER ;

INSERT INTO salario(nome_funcionario, remuneracao) VALUES('Alberto', -1000.00);
-- Error Code: 1644. Remuneração deve ser positiva

DELIMITER $$
CREATE TRIGGER trigger_after_insert_salario
AFTER INSERT ON salario
FOR EACH ROW
BEGIN
    INSERT INTO log_salario(id_salario, nome_funcionario, remuneracao_anterior,
                            remuneracao_nova, datahora, usuario)
    VALUES (NEW.id, NEW.nome_funcionario, 0.0, NEW.remuneracao,
            CURRENT_TIMESTAMP(), USER());
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_before_update_salario
BEFORE UPDATE ON salario
FOR EACH ROW
BEGIN
    IF OLD.remuneracao > NEW.remuneracao THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Não pode diminuir o salário do funcionário';
    END IF;
END $$
DELIMITER ;

UPDATE salario SET remuneracao = 500 WHERE id = 1;
-- Error Code: 1644. Não pode diminuir o salário do funcionário

DELIMITER $$
CREATE TRIGGER trigger_after_update_salario
AFTER UPDATE ON salario
FOR EACH ROW
BEGIN
    DECLARE salario_antigo DECIMAL(9,2);
    SET salario_antigo = (SELECT remuneracao_nova
                          FROM log_salario
                          WHERE nome_funcionario = NEW.nome_funcionario
                          ORDER BY id
                          LIMIT 1);
    INSERT INTO log_salario(id_salario, nome_funcionario, remuneracao_anterior, remuneracao_nova, datahora, usuario)
    VALUES (NEW.id, NEW.nome_funcionario, salario_antigo, NEW.remuneracao, CURRENT_TIMESTAMP(), USER());
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_before_delete_salario
BEFORE DELETE ON salario
FOR EACH ROW
BEGIN
    DECLARE media_salario DECIMAL(9,2);
    DECLARE maximo_salario DECIMAL(9,2);
    SET media_salario = (SELECT AVG(remuneracao) FROM salario);
    SET maximo_salario = (SELECT MAX(remuneracao) FROM salario);
    IF OLD.remuneracao < (media_salario/2) OR OLD.remuneracao = maximo_salario THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Não pode demitir quem recebe menos que metade da média, nem o dono (maior salário)';
    END IF;
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trigger_after_delete_salario
AFTER DELETE ON salario
FOR EACH ROW
BEGIN
    <comandos>
END $$
DELIMITER ;

SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_before_insert_salario;

DELIMITER $$
CREATE FUNCTION <nome função>(<parâmetros>)
RETURNS <tipo retorno>
<tipo de função>
BEGIN
    <comandos>
    RETURN <retorno>;
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION verificar_salario_2000(salario DECIMAL(9,2))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE resultado VARCHAR(100);
    IF salario >= 2000 THEN SET resultado = 'Maior ou igual a 2000';
    ELSE SET resultado = 'Menor que 2000';
    END IF;
    RETURN resultado;
END$$
DELIMITER ;

SELECT *, verificar_salario_2000(remuneracao) FROM salario;

DELIMITER $$
CREATE FUNCTION verificar_salario_media(salario DECIMAL(9,2))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE media DECIMAL(9,2);
    DECLARE resultado VARCHAR(100);
    SET media = (SELECT AVG(remuneracao) FROM salario);
    IF salario > media THEN SET resultado = 'Salário maior do que a média';
    ELSE SET resultado = 'Salário menor ou igual do que a media';
    END IF;
    RETURN resultado;
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION calcular_total_salario(salario DECIMAL(9,2), meses INT)
RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
    DECLARE resultado DECIMAL(9,2);
    SET resultado = salario * meses;
    RETURN resultado;
END$$
DELIMITER ;

SELECT nome_funcionario, calcular_total_salario(remuneracao, 8) FROM salario;
-- Alberto: 8000.00 · Bruno: 12000.00 · Clóvis: 16000.00 · Danilo: 40000.00

DROP FUNCTION verificar_salario_2000;

DELIMITER $$
CREATE PROCEDURE <nome>(<IN | OUT | INOUT> <parâmetro> <tipo>, ...)
BEGIN
    <comandos>
END$$
DELIMITER ;

CALL <nome>(<argumentos>);
DROP PROCEDURE <nome>;

DELIMITER $$
CREATE PROCEDURE selecionar_1_disciplina(IN p_id INT)
BEGIN
    SELECT * FROM disciplina WHERE id = p_id;
END$$
DELIMITER ;

CALL selecionar_1_disciplina(3);

DELIMITER $$
CREATE PROCEDURE contar_automoveis(OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total FROM automovel;
END$$
DELIMITER ;

CALL contar_automoveis(@total);
SELECT @total;

DELIMITER $$
CREATE PROCEDURE dobrar(INOUT p_numero INT)
BEGIN
    SET p_numero = p_numero * 2;
END$$
DELIMITER ;

SET @n = 7;
CALL dobrar(@n);
SELECT @n;   -- 14

DELIMITER $$
CREATE PROCEDURE contagem_regressiva(IN p_inicio INT)
BEGIN
    WHILE p_inicio > 0 DO
        SELECT p_inicio;
        SET p_inicio = p_inicio - 1;
    END WHILE;
END$$

CREATE PROCEDURE contagem_regressiva_repeat(IN p_inicio INT)
BEGIN
    REPEAT
        SELECT p_inicio;
        SET p_inicio = p_inicio - 1;
    UNTIL p_inicio <= 0
    END REPEAT;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE gerenciar_automovel(IN p_acao VARCHAR(10), IN p_id INT)
BEGIN
    IF p_acao = 'EXCLUIR' THEN
        IF NOT EXISTS (SELECT 1 FROM automovel WHERE id = p_id) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Erro - Id não existe';
        END IF;
        DELETE FROM automovel WHERE id = p_id;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Erro - Ação desconhecida';
    END IF;
END$$
DELIMITER ;

CALL gerenciar_automovel('EXCLUIR', 99);
-- Error Code: 1644. Erro - Id não existe