VENDAS - Código do Artigo com o prof° Johnes, Rafael e Alan;

bd.sql

CREATE DATABASE vendas;
USE vendas;

CREATE TABLE clientes(
  idcliente INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  nome VARCHAR(50) NOT NULL,
  sexo CHAR(1) NOT NULL,
  nascimento DATE NOT NULL,
  num_dependentes INT(3)
) ENGINE=InnoDB;

CREATE TABLE vendas (
    idvenda INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    idcliente INT(11) NOT NULL,
    valor_total DOUBLE PRECISION(10 , 2 ) NOT NULL,
    CONSTRAINT fk_vendas_clientes FOREIGN KEY (idcliente)
        REFERENCES clientes (idcliente)
) ENGINE=INNODB;

INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Maria', 'F', '1972-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Bruna', 'F', '1988-01-01', 3);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Daiane', 'F', '1968-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('João', 'M', '1989-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Tereza', 'F', '1955-01-01', 2);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Carlos', 'M', '1976-01-01', 2);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Ana', 'F', '1960-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Luisa', 'F', '1959-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Paulo', 'M', '1957-01-01', 1);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Leandro', 'M', '1983-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Carla', 'F', '1942-01-01', 4);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Jaqueline', 'F', '1987-01-01', 2);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Kelly', 'F', '1990-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Sabrina', 'F', '1999-01-01', 0);
INSERT INTO clientes (nome, sexo, nascimento, num_dependentes) VALUES ('Lucia', 'F', '1987-01-01', 0);

INSERT INTO vendas (idcliente, valor_total) VALUES (1, 500);
INSERT INTO vendas (idcliente, valor_total) VALUES (2, 1500);
INSERT INTO vendas (idcliente, valor_total) VALUES (3, 5500);
INSERT INTO vendas (idcliente, valor_total) VALUES (4, 1000);
INSERT INTO vendas (idcliente, valor_total) VALUES (5, 1080);
INSERT INTO vendas (idcliente, valor_total) VALUES (6, 50);
INSERT INTO vendas (idcliente, valor_total) VALUES (7, 150);
INSERT INTO vendas (idcliente, valor_total) VALUES (8, 4500);
INSERT INTO vendas (idcliente, valor_total) VALUES (9, 450);
INSERT INTO vendas (idcliente, valor_total) VALUES (10, 2200);
INSERT INTO vendas (idcliente, valor_total) VALUES (11, 220);
INSERT INTO vendas (idcliente, valor_total) VALUES (12, 280);
INSERT INTO vendas (idcliente, valor_total) VALUES (13, 780);
INSERT INTO vendas (idcliente, valor_total) VALUES (14, 7880);
INSERT INTO vendas (idcliente, valor_total) VALUES (15, 1200);

function.sql

USE `vendas`;
DROP function IF EXISTS `age_group_from`;

DELIMITER $$
USE `vendas`$$
CREATE FUNCTION `age_group_from` (birthday DATE)
RETURNS TEXT
BEGIN
   DECLARE age_group VARCHAR(5); 
   
   SET age_group = CASE
	  WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) >= 40 THEN '≥ 40'
	  WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) BETWEEN 20 AND 39 THEN '20-39'
	  WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) <= 19 THEN '≤ 19'
   END;    
   RETURN age_group;
END$$

DELIMITER ;

query.sql

SELECT
    c.sexo AS 'Sexo',
    AGE_GROUP_FROM(c.nascimento) AS 'Idade',
    IF(c.num_dependentes > 0, 'Sim', 'Não') AS 'Filhos',
    IF(SUM(valor_total) >= 1000, 'Sim', 'Não') AS 'Gasta Muito?'
FROM
    vendas v
        JOIN
    clientes c USING (idcliente)
GROUP BY c.idcliente
ORDER BY c.idcliente;

USE `vendas`;
DROP function IF EXISTS `age_group_from`;

DELIMITER $$
USE `vendas`$$
-- Esta é uma function normal do MySQL. 
-- A função age_group_from, recebe uma data inicial (a data de aniversário de uma pessoa) e 
-- ela é responsável por classificar essa data, em um dos grupos definidos na condição abaixo.
CREATE FUNCTION `age_group_from` (birthday DATE)
-- O retorno desta função será em texto mesmo, é apenas demonstrativo.
RETURNS TEXT
BEGIN
   -- Declaro uma variável do tipo texto, que irá receber o retorno da verificação de data.
   DECLARE age_group VARCHAR(5); 

   -- Esta linha significa que o valor de retorno do CASE será atribuído à variável declarada anteriormente.
   SET age_group = CASE
      /*
      Cada WHEN abaixo representa um grupo de idades. A função TIMESTAMPDIFF recebe a idade passada (recebida pela função) 
      e a compara com a dia de hoje. O resultado é convertido apenas em anos, ou seja a quantidade de anos que se passaram desde a data de aniversário até hoje.
      Com o resultado já convertido, o MySQL verifica em qual intervalo essa idade se encaixa e então retorna o valor de texto, por exemplo '20-39'.
      */
      WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) >= 40 THEN '≥ 40'
      WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) BETWEEN 20 AND 39 THEN '20-39'
      WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) <= 19 THEN '≤ 19'
   END;
   RETURN age_group;
END$$

DELIMITER ;

SELECT
    -- Atribui um apelido (comando 'AS') para a coluna c.idcliente.
    c.idcliente AS 'Id',
    c.sexo,
    -- Chama a função declarada anteriormente, todo o código de verificação poderia ser feito aqui também.
    AGE_GROUP_FROM(c.nascimento) AS 'Idade',
    -- Neste caso queremos dizer com palavras se a pessoa possuí filhos ou não, 
    -- portanto condicionamos o valor da coluna a retornar sim ou não por meio da estrutura de condição IF.
    IF(c.num_dependentes > 0, 'Sim', 'Não') AS 'Filhos', 
    -- O mesmo acontece neste caso, a diferença é que definimos um "limite" de 1.000 para dizer que a pessoa gasta muito ou não
    -- portanto agregamos todos os valores das colunas na tabela de vendas que este cliente comprou e então submetemos a condição de limite.
    IF(SUM(v.valor_total) >= 1000, 'Sim', 'Não') AS 'Total'
FROM
    vendas v
        JOIN
    -- Precisamos das informações dos clientes, aproveitando que a base usa a mesma nomenclatura nas "duas tabelas", 
    -- podemos usar uma abreviação do "clientes c ON c.idcliente = v.idcliente" com o USING.
    clientes c USING (idcliente)
    -- Agrupamos por id de cliente, pois sem isto, cada compra do cliente retornaria uma novo registro, perdendo o sentido querie.
GROUP BY c.idcliente
    -- Ordenamos também por id do cliente do mais antigo ao mais novo.
ORDER BY c.idcliente;