Last active
May 13, 2016 19:32
-
-
Save brunoocasali/bb6bf3e323cc8a6bde6ebf1ea9f8c4ca to your computer and use it in GitHub Desktop.
VENDAS - Código do Artigo com o prof° Johnes, Rafael e Alan;
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Author
brunoocasali
commented
May 13, 2016
•
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment