01-Cria-Banco

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

/* A05 */
/* Modelagem basica */
CLIENTE
NOME CARACTER(30)
CPF NUMERICO(11)
EMAIL CARACTER(30)
TELEFONE CARACTER(30)
SEXO CARACTER(1)
ENDERECO CARACTER(100)
/*PROCESSOS DE MODELAGEM */
/* ADM DE DADOS */
MODELAGEM CONCEITUAL - RASCUNHO 01
MODELAGEM LOGICA - PROGRAMAS COMO BRMODELO
/* AD - DBA */
MODELAGEM FISICA - SCRIPT DO BANCO
/*MODELO FISICO */
/* CRIANDO UM BANCO */
CREATE DATABASE PROJETO;
/* CONECTANDO */
USE PROJETO;
/* CRIANDO A TABELA */
CREATE TABLE CLIENTE(
	NOME VARCHAR(30),
	SEXO CHAR(1),
	EMAIL VARCHAR(30),
	CPF INT(11),
	TELEFONE VARCHAR(30),
	ENDERECO VARCHAR(100)
);
/*VERIFICANDO AS TABELAS */
SHOW TABLES;
/* DESCREVENDO TABELAS */
DESC CLIENTE;
/* A 06 */
/*Tipos de dados */
Todos os banos de dados possuem tipos que devem ser atribuidos aos dados de uma tabela.
Para caracteres literais, temos char e varchar, para números temos int e float, para
objetos como fotos e documentos, temos o blob, para textos extensos, temos o text. 
A disciplina de banco de dados é tão fantástica que ao entendermos o porque das coisas,
podemos iniciar já em modo avançado e um bom exemplo disso são os tipos. Há uma profissão
dentro da área que é a do analista de performance ou tuning, esse profissional é responsável
por analisar o banco de dados e deixá-lo mais rápido. Parece algo avançado, e é! Porém,
com alguas atitudes simples, podemos deixar o banco sem a necessidade de atuação desse profissional.
Cada caracter no banco de dados, vale 1 byte. Sendo assim, se eu entro com o dado JOÃO,
estou entrando com 4 bytes no meu banco. E o que isso tem a ver com a tiagem de tabelas?
O banco de dados funciona como um download de dados da internet. Se baixamos um arquivo de 1 giga,
temos um temo maior que o download de 50 megas, considerando a mesma velocidade de conexão.
Ao tiparmos uma tabela de modo errado ou displicente, vamos aumentar a quantidade de dados que 
será baixada do banco de dados, prolongando assim o tempo de resposta.
Uma comparacao bem didatica é o tipo char e varchar
A palavra var, vem de variant, em ingles, ou seja, que é dinâmica. Logo, vimos que 1 caracter
é igual a 1 byte. Vejamos então a tipagem
varchar(10)
char(10)
entrando a palavra joao
total de bytes varchar(10) = 4 bytes
toal de bytes cahr(10) = 10 bytes
isso ocorre pois o char não varia. Os caracteres restantes serao preenchidos com espaço. 
eles nao ficam vazios. Enquanto que no varchar, o tipo varia conforme o dado.
Entao utilizo sempre o varchar? Não. O charé ligeiramente mais performatico, por nao
ter que gastar tempo variando de tamanho. Entao a regra é utilizar sempre o char quando
sabemos que o numero de caracteres naquela coluna nao vai variar nunca. Por exemplo,
unidade federativa, com dois digitos, sexo com um digito e assim por diante. Vista a diferença
que podemos fazer com uma tipagem correta de tabelas, na próxima aula detalharemos os tipos do mysql
e nos modulos específicos de cada banco, você entenderá os tipos correspondentes no sql server
e no oracle, que mudam muito pouco.
/* A 08 - Inserindo dados e valores nulos */
use projeto;
/* Mostrando as tabelas */
show tables
/* exibindo a estrutura de uma tabela */
desc cliente;
/* FORMA 01 - EXCLUINDO AS COLUNAS */
INSERT INTO CLIENTE VALUES('JOAO','M','JOAO@GMAIL.COM',988638273,'22923110','MAIA LACERDA - ESTACIO - RIO DE JANEIRO - RJ');
INSERT INTO CLIENTE VALUES('CELIA','F','CELIA@GMAIL.COM',541521456,'25078869','RIACHUELO - CENTRO - RIO DE JANEIRO - RJ');
INSERT INTO CLIENTE VALUES('JORGE','M',NULL,885755896,'58748895','OSCAR CURY - BOM RETIRO - PATOS DE MINAS - MG');
/* FORMA 02 - COLOCANDO AS COLUNAS */
INSERT INTO CLIENTE(NOME,SEXO,ENDERECO,TELEFONE,CPF) VALUES('LILIAN','F','SENADOR SOARES - TIJUCA - RIO DE JANEIRO - RJ','947785696',887774856);
/* FORMA 03 - INSERT COMPACTO - SOMENTE MYSQL */
INSERT INTO CLIENTE VALUES('ANA','F','ANA@GLOBO.COM',85548962,'548556985','PRES ANTONIO CARLOS - CENTRO - SAO PAULO - SP'),
 ('CARLA','F','CARLA@TERATI.COM.BR',7745828,'66587458','SAMUEL SILVA - CENTRO - BELO HORIZONTE - MG');
						 
/* A009 - O COMANDO SELECT */
SELECT NOW();
SELECT 'FELIPE MAFRA';
SELECT 'BANCO DE DADOS';
/* ALIAS DE COLUNAS */
SELECT 'FELIPE MAFRA' AS PROFESSOR;
SELECT NOME, SEXO, EMAIL FROM CLIENTE;
SELECT NOME, SEXO, EMAIL, ENDERECO FROM CLIENTE;
SELECT ENDERECO, SEXO, EMAIL FROM CLIENTE;
/* APENAS PARA FINS ACADEMICOS */
SELECT * FROM CLIENTE;
/* A10 - FILTRANDO DADOS COM WHERE E LIKE */
SELECT NOME, TELEFONE FROM CLIENTE;
/* FILTRANDO */
SELECT NOME, SEXO FROM CLIENTE
WHERE SEXO = 'M';
SELECT NOME, ENDERECO FROM CLIENTE
WHERE SEXO = 'M';
SELECT NOME, ENDERECO FROM CLIENTE
WHERE SEXO = 'F';
SELECT NOME, SEXO FROM CLIENTE
WHERE ENDERECO = 'RJ';
/* UTILIZANDO O LIKE */
SELECT NOME, SEXO FROM CLIENTE
WHERE ENDERECO LIKE 'RJ';
/* CARACTER CORINGA % */
SELECT NOME, SEXO FROM CLIENTE
WHERE ENDERECO LIKE '%RJ';
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE ENDERECO LIKE '%RJ';
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE ENDERECO LIKE '%CENTRO%';
/* A11 - OPERADORES LOGICOS */
/* OR - OU */
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE 
SEXO = 'M' OR ENDERECO LIKE '%RJ';
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE
SEXO = 'F' OR ENDERECO LIKE '%ESTACIO';
/* AND - E */
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE 
SEXO = 'M' AND ENDERECO LIKE '%RJ';
SELECT NOME, SEXO, ENDERECO FROM CLIENTE
WHERE
SEXO = 'F' AND ENDERECO LIKE '%ESTACIO';
/* COUNT(*), GROUP BY, PERFORMANCE COM OPERADORES
LOGICOS */
/* CONTANDO OS REGISTROS DE UMA TABELA */
SELECT COUNT(*) FROM CLIENTE;
SELECT COUNT(*) AS "QUANTIDADE DE REG TAB CLIENTE"
FROM CLIENTE;
/* OPERADOR GROUP BY */
SELECT SEXO, COUNT(*) AS "QUANTIDADE"
FROM CLIENTE
GROUP BY SEXO;
/* PERFORMANCE EM OPERADORES LOGICOS */
/* 1 MILHAO DE REGISTROS
PARA CONTAR
SELECT COUNT(*) FROM CLIENTE;
SELECT SEXO, COUNT(*) FROM CLIENTE
GROUP BY SEXO;
SELECT CIDADE, COUNT(*) FROM CLIENTE
GROUP BY CIDADE;
CONDICAO
SEXO = F
CIDADE = RIO DE JANEIRO
SITUACAO - TRATANDO COM OU / OR
70% MULHERES = SEXO = F
30% MORA NO RIO DE JANEIRO
SELECT NOME, SEXO, ENDERECO
FROM CLIENTE
WHERE SEXO = 'F'
OR CIDADE = 'RIO DE JANEIRO';
SITUACAO - TRATANDO COM E / AND
70% MULHERES = SEXO = F
30% MORA NO RIO DE JANEIRO
SELECT NOME, SEXO, ENDERECO
FROM CLIENTE
WHERE CIDADE = 'RIO DE JANEIRO'
AND SEXO = 'F';
/* A13 - FILTRANDO VALORES NULOS */
SELECT NOME, SEXO, ENDERECO
FROM CLIENTE
WHERE EMAIL = NULL;
SELECT NOME, SEXO, ENDERECO
FROM CLIENTE
WHERE EMAIL IS NULL;
SELECT NOME, SEXO, ENDERECO
FROM CLIENTE
WHERE EMAIL IS NOT NULL;
/* A14 - UTILIZANDO O UPDATE PARA ATUALIZAR VALORES */
SELECT NOME, EMAIL
FROM CLIENTE;
UPDATE CLIENTE
SET EMAIL = 'LILIAN@HOTMAIL.COM';
/* UTILIZAR O UPDATE SEMPRE ACOMPANHADO
DA CLAUSULA WHERE */
UPDATE CLIENTE
SET EMAIL = 'JOAO@HOTMAIL.COM'
WHERE NOME = 'JOAO';
UPDATE CLIENTE
SET EMAIL = 'JORGE@GMAIL.COM'
WHERE NOME = 'JORGE';
UPDATE CLIENTE
SET EMAIL = 'ANA@GMAIL.COM'
WHERE NOME = 'ANA';
UPDATE CLIENTE
SET EMAIL = 'CARLA@TERRA.COM.BR'
WHERE NOME = 'CARLA';
/*A15 - DELETANDO REGISTROS COM A CLAUSULA DELETE */
DELETE FROM CLIENTE; -- NUNCA FAÇAM
SELECT COUNT(*) FROM CLIENTE;
DELETE FROM CLIENTE
WHERE NOME = 'ANA';
SELECT COUNT(*) FROM CLIENTE;
/* A16 - ENTRANDO COM MAIS TELEFONES */
UPDATE CLIENTE 
SET TELEFONE = '97865434'
WHERE NOME = 'JOAO';
UPDATE CLIENTE 
SET TELEFONE = '22923110 - 97865434'
WHERE NOME = 'JOAO';
SELECT ENDERECO, COUNT(*) AS "QUANTIDADE"
FROM CLIENTE
GROUP BY ENDERECO;
/* A17 - HISTORIA DA MODELAGEM */
DECADA DE 70, EUA - CRISE DO SOFTWARE
FOI A CRISE QUE ATINGIU NAO SOMENTE A AREA DE TECNOLOGIA
MAS TAMBÉM TODAS AS EMPRESAS QUE UTILIZAVAM DE SISTEMAS
TECNOLOGICOS PARA CONTROLAR SEUS NEGOCIOS.
NAQUELA EPOCA, PROGRAMAR ERA UM PROCESSO ARTESANAL.
BASTAVA OLHAR O CODIGO QUE VOCE SABERIA QUEM O
ESCREVEU, TAO POUCOS ERAM OS PROGRAMADORES EACHTAO ARCAICO ERAM
OS METODOS DE PROGRAMACAO. EM UM PRIMEIRO MOMENTO,
OS PROFISSIONAIS DE TI VIRAM A NECESSIDADE DE SE PROGRAMAR
E INICIARAM SUA CAMINHADA, CONSTRUINDO SISTEMAS QUE ATENDIAM
NUM PRIMEIRO MOMENTO, MAS DEPOIS TRAZIAM INFORMACOES ERRADAS,
PROVOCANDO A FALENCIA DE DIVERSAS EMPRESAS. LEMBRE-SE: INFORMACOES
ERRADAS PODEM LEVAR A SUA EMPRESA A FALENCIA, E FOI ISSO QUE ACONTECEU.
ISSO OCORRIA POIS OS PROFISSIONAIS SABIAM PROGRAMAR MAS ENTENDIAM APENAS
DO NEGOCIO DAS EMPRESAS DAS QUAIS FAZIAM PARTE. SER TER O KNOW HOW
DOS DEMAIS NEGOCIOS, OS PROGRAMADORES PROGRAMAVAM VOLTADOS PARA OS
PROCEDIMENTOS, E ESSE FOI UM ERRO MORTAL.
PETER CHEN, VENDO TODO ESSE CENARIO AFIRMOU: NAO PODEMOS CRIAR BANCOS
DE DADOS VOLTADOS PARA PROCEDIMENTOS EMPRESARIAIS, POIS OS PROCEDIMENTOS
SOFREM INTERFERENCIAS EXTERNAS E PODEM SER ALTERADOS. A UNICA COISA
QUE NAO SOFRE ALTERACAO SAO OS DADOS.
POR EXEMPLO, ARMAZENAMOS NO BANCO A VENDA DE JANEIRO, FEVEREIRO E MARCO
O TOTAL DAS VENDAS E O IMPOSTO PAGO AO GOVERNO. 
FAZEMOS ISSO DURANTE MUITO TEMPO. DE UMA HORA PRA OUTRA O GOVERNO
MUDA A ALIQUOTA DO IMPOSTO. COMO FARÁ SENTIDO OS PAGAMENTOS ARMAZENADOS
ANTERIORMENTE? AQUI NOTA-SE QUE ARMAZENAMOS DADOS E ARMAZENAMOS
INFORMACOES CORRESPONDENTES AOS PROCEDIMENTOS EXTERNOS. DEVEMOS
APENAS ARMAZENAR OS DADOS, POIS ELES SAO SUFICIENTES PARA GERAR
A INFORMACAO.
MODELAR É UMA ARTE, ALGO QUE SE APRENDE COM A PRATICA. E VOCES PODEM
SE PERGUNTAR: MAS SE É ALGO QUE SE APRENDE COM A PRATICA, COMO QUEM
ESTA COMEÇANDO, APRENDE?
ASSIM COMO PETER CHEN CRIOU O MODELO DE ENTIDADE E RELACIONAMENTO, O MER,
FORAM CRIADAS FORMAS NORMAIS, QUE SAO DIRETRIZES PARA APLICAR EM
MODELAGEM DE AMBIENTES OLTP OU AMBIENTES TRANSACIONAIS DE BANCOS
DE DADOS.
HOJE TEMOS MAIS DO QUE 3 FORMAS NORMAIS, MAS AO NORMALIZARMOS UM
BANCO ATE A SUA TERCEIRA FORMA, É O SUFICIENTE PARA TER UM
TRABALHO LIMPO E LIVRE DE ERROS. VEREMOS AS TRES FORMAS NORMAIS,
UMA A UMA, DETALHADAMENTE NAS PROXIMAS AULAS.
/* A18 */
INSERT INTO CLIENTE VALUES('CELIA','F','CELIA@GMAIL.COM',541521456,'58749965','RIACHUELO - CENTRO - RIO DE JANEIRO - RJ');
SELECT SEXO, COUNT(*) AS QUANTIDADE
FROM CLIENTE
GROUP BY SEXO;
/* 1a FORMA NORMAL */
/* 1 - TODO CAMPO VETORIZADO SE TORNARA
OUTRA TABELA
[AZUL,AMARELO,VERDE,LARANJA] - ELEMENTOS DE UM MESMO TIPO
[KA,FIT,FIESTA,CIVIC,UNO] - VETOR DE CARROS
2 - TODO CAMPO MULTIVALORADO SE TORNARÁ
OUTRA TABELA. UM CAMPO DIVISIVEL.
3 - TODA TABELA NECESSITA DE PELO MENOS
UM CAMPO QUE IDENTIFIQUE TODO O RESGISTRO
COMO SENDO ÚNICO - É O QUE CHAMAMOS DE CHAVE PRIMÁRIA
OU PRIMARY KEY*/
/* A19 - CADINALIDADE */
/* QUEM DEFINE A CARDINALIDADE É A REGRA DE NEGOCIOS */
/* PRIMEIRO ALGARISMO - OBRIGATORIEDADE 
0 - NAO OBRIGATORIO
1 - OBRIGATORIO
SEGUNDO ALGARISMO - CARDINALIDADE
1 - MAXIMO DE UM
N - MAIS DE UM
*/
/* MODELAGEM DE CLIENTE */
CREATE DATABASE COMERCIO;
USE COMERCIO;
CREATE TABLE CLIENTE(
	IDCLIENTE INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30) NOT NULL,
	SEXO ENUM('M','F') NOT NULL,
	EMAIL VARCHAR(50) UNIQUE,
	CPF VARCHAR(15) UNIQUE
);
CREATE TABLE TELEFONE(
	IDTELEFONE INT PRIMARY KEY AUTO_INCREMENT,
	TIPO ENUM('COM','RES','CEL'),
	NUMERO VARCHAR(10),
	ID_CLIENTE INT,
	FOREIGN KEY(ID_CLIENTE)
	REFERENCES CLIENTE(IDCLIENTE)
);
CREATE TABLE ENDERECO(
	IDENDERECO INT PRIMARY KEY AUTO_INCREMENT,
	RUA VARCHAR(30) NOT NULL,
	BAIRRO VARCHAR(30) NOT NULL,
	CIDADE VARCHAR(30) NOT NULL,
	ESTADO CHAR(2) NOT NULL,
	ID_CLIENTE INT UNIQUE,
	FOREIGN KEY(ID_CLIENTE)
	REFERENCES CLIENTE(IDCLIENTE)
);
/* FOREIGN KEY - CHAVE ESTRANGEIRA - FK */
/* É A CHAVE PRIMARIA DE UMA TABELA, QUE VAI ATE
OUTRA TABELA FAZER REFERENCIA */
/* EM RELACIONAMENTOS 1 X 1 A CHAVE ESTRANGEIRA
FICA NA TABELA MAIS FRACA */
/* EM RELACIONAMENTOS 1 X N, A CHAVEE
ESTRANGEIRA FICARA SEMPRE NA TABELA N */
/* A20 */
INSERT INTO CLIENTE VALUES(NULL,'JOAO','M','JOAO@IG.COM','98547-6');
INSERT INTO CLIENTE VALUES(NULL,'CARLOS','M','CARLOS@TERA.COM','86664-7');
INSERT INTO CLIENTE VALUES(NULL,'ANA','F','ANA@GLOBO.COM','75658-5');
INSERT INTO CLIENTE VALUES(NULL,'JORGE','M','JORGE@IG.COM','88657-5');
INSERT INTO CLIENTE VALUES(NULL,'CLARA','M',NULL,'99754-7');
INSERT INTO CLIENTE VALUES(NULL,'CELIA','F','JOAO@TERRA.COM','77558-5');
SELECT * FROM CLIENTE;
/* INSERINDO UM REGISTRO ERRADO */
INSERT INTO CLIENTE VALUES(NULL,'ANTONIO','M','JOAO@IG.COM');
/*CORRIGINDO*/
INSERT INTO CLIENTE VALUES(NULL,'ANTONIO','M',NULL,'78558-6');
INSERT INTO CLIENTE VALUES(NULL,'JOAO','M',NULL,'86695-6');
/* ENDERECO */
INSERT INTO ENDERECO VALUES(NULL,'RUA A','CENTRO','B. HORIZONTE','MG',4);
INSERT INTO ENDERECO VALUES(NULL,'RUA B','CENTRO','RIO DE JANEIRO','RJ',1);
INSERT INTO ENDERECO VALUES(NULL,'RUA C','JARDINS','SAO PAULO','SP',3);
INSERT INTO ENDERECO VALUES(NULL,'RUA B','ESTACIO','RIO DE JANEIRO','RJ',8);
INSERT INTO ENDERECO VALUES(NULL,'RUA Z','CRUZEIRO','B. HORIZONTE','MG',2);
INSERT INTO ENDERECO VALUES(NULL,'RUA X','FLAMENGO','RIO DE JANEIRO','RJ',7);
INSERT INTO ENDERECO VALUES(NULL,'RUA Z','CRUZEIRO','B. HORIZONTE','MG',5);
INSERT INTO ENDERECO VALUES(NULL,'RUA X','CENTRO','NITERÓI','RJ',6);
INSERT INTO ENDERECO VALUES(NULL,'RUA U','CENTRO','NITERÓI','RJ',1000);
SELECT * FROM ENDERECO;
/* TELEFONE */
INSERT INTO TELEFONE VALUES(NULL,'CEL','9955331',1);
INSERT INTO TELEFONE VALUES(NULL,'COM','6574565',3);
INSERT INTO TELEFONE VALUES(NULL,'CEL','8864566',2);
INSERT INTO TELEFONE VALUES(NULL,'CEL','5557798',7);
INSERT INTO TELEFONE VALUES(NULL,'COM','6765768',1);
INSERT INTO TELEFONE VALUES(NULL,'RES','5676765',6);
INSERT INTO TELEFONE VALUES(NULL,'CEL','5765547',1);
INSERT INTO TELEFONE VALUES(NULL,'CEL','8865645',7);
INSERT INTO TELEFONE VALUES(NULL,'RES','7555446',7);
INSERT INTO TELEFONE VALUES(NULL,'CEL','5788654',3);
INSERT INTO TELEFONE VALUES(NULL,'CEL','7865644',6);
INSERT INTO TELEFONE VALUES(NULL,'RES','5754644',6);
SELECT * FROM CLIENTE;
SELECT * FROM ENDERECO;
SELECT * FROM TELEFONE;
/* A 21 */
/* SELECAO, PROJECAO E JUNCAO */
/* PROJECAO - TUDO QUE QUEREMOS PROJETAR NA TELA */
SELECT NOW() AS "DATA";
SELECT NOME, NOW() AS "DATA" /* PROJECAO */
FROM CLIENTE;
/* SELECAO - TEORIA DOS CONJUNTOS - WHERE É A CLAUSULA DE SELECAO */
SELECT NOME, SEXO 
FROM CLIENTE;
SELECT NOME, SEXO 
FROM CLIENTE
WHERE SEXO = 'M'; /* SELECAO */
UPDATE CLIENTE
SET SEXO = 'F'
WHERE IDCLIENTE = 5; /*SELECAO */
/* JUNCAO */
/* NOME, SEXO, BAIRRO, CIDADE, DATA */
SELECT NOME, SEXO, BAIRRO, CIDADE, NOW() AS "DATA"
FROM CLIENTE, ENDERECO
WHERE IDCLIENTE = ID_CLIENTE; /* SELECAO - NAO FAÇAM ISSO */
SELECT NOME, SEXO, BAIRRO, CIDADE, NOW() AS "DATA"
FROM CLIENTE, ENDERECO
WHERE IDCLIENTE = ID_CLIENTE
AND BAIRRO = 'CENTRO';
/* JOIN - JUNCAO */
SELECT NOME, SEXO, BAIRRO, CIDADE /* PROJECAO */
FROM CLIENTE
INNER JOIN ENDERECO /*JUNCAO */
ON IDCLIENTE = ID_CLIENTE
WHERE BAIRRO = 'CENTRO'; /* SELECAO */
SELECT CLIENTE.NOME, CLIENTE.SEXO, 
ENDERECO.BAIRRO, ENDERECO.CIDADE, 
TELEFONE.TIPO, TELEFONE.NUMERO
FROM CLIENTE 
INNER JOIN ENDERECO
ON CLIENTE.IDCLIENTE = ENDERECO.ID_CLIENTE
INNER JOIN TELEFONE
ON CLIENTE.IDCLIENTE = TELEFONE.ID_CLIENTE;
SELECT C.NOME, C.SEXO, 
E.BAIRRO, E.CIDADE, 
T.TIPO, T.NUMERO
FROM CLIENTE C
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE;
SELECT C.NOME, C.SEXO, 
E.BAIRRO, E.CIDADE, 
T.TIPO, T.NUMERO
FROM CLIENTE C
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
WHERE SEXO = 'M';
/* A23 Queries - SELECAO - PROJECAO E JUNCAO */
/* PARA UMA CAMPANHA DE MARKETING, O SETOR SOLICITOU UM
RELATÓRIO COM
O NOME, EMAIL E TELEFONE CELULAR 
DOS CLIENTES QUE MORAM NO ESTADO DO RIO DE JANEIRO 
VOCÊ TERÁ QUE PASSAR A QUERY PARA GERAR O RELATORIO PARA
O PROGRAMADOR */
SELECT C.NOME,C.EMAIL,T.NUMERO
FROM CLIENTE C
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE
WHERE TIPO = 'CEL' AND ESTADO = 'RJ';
+---------+----------------+---------+
| NOME | EMAIL | NUMERO |
+---------+----------------+---------+
| JOAO | JOAO@IG.COM | 9955331 |
| JOAO | JOAO@IG.COM | 5765547 |
| ANTONIO | NULL | 5557798 |
| ANTONIO | NULL | 8865645 |
| CELIA | JOAO@TERRA.COM | 7865644 |
+---------+----------------+---------+
/* PARA UMA CAMPANHA DE PRODUTOS DE BELEZA, O COMERCIAL SOLICITOU UM
RELATÓRIO COM O NOME, EMAIL E TELEFONE CELULAR 
 DAS MULHERES QUE MORAM NO ESTADO DE SÃO PAULO 
VOCÊ TERÁ QUE PASSAR A QUERY PARA GERAR O RELATORIO PARA
O PROGRAMADOR */
SELECT NOME, EMAIL, NUMERO
FROM CLIENTE
INNER JOIN TELEFONE T
ON IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON IDCLIENTE = E.ID_CLIENTE
WHERE SEXO = 'F' AND TIPO = 'CEL' AND ESTADO = 'SP';
+------+---------------+---------+
| NOME | EMAIL | NUMERO |
+------+---------------+---------+
| ANA | ANA@GLOBO.COM | 5788654 |
+------+---------------+---------+
/* IFNULL */
SELECT C.NOME AS "CLIENTE",
	 IFNULL(C.EMAIL,'SEM EMAIL') AS "EMAIL",
	 T.NUMERO AS "CELULAR"
FROM CLIENTE C
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE
WHERE TIPO = 'CEL' AND ESTADO = 'RJ';
+---------+----------------+---------+
| NOME | EMAIL | NUMERO |
+---------+----------------+---------+
| JOAO | JOAO@IG.COM | 9955331 |
| JOAO | JOAO@IG.COM | 5765547 |
| ANTONIO | SEM EMAIL | 5557798 |
| ANTONIO | SEM EMAIL | 8865645 |
| CELIA | JOAO@TERRA.COM | 7865644 |
+---------+----------------+---------+
/* A24 - VIEWS OU VISOES */
SELECT C.NOME,C.SEXO,
	 IFNULL(C.EMAIL,'SEM EMAIL') AS "E-MAIL",
	 T.TIPO,
	 T.NUMERO,
 E.BAIRRO,
	 E.CIDADE,
	 E.ESTADO
FROM CLIENTE C
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE;
/* CRIANDO A VIEW */
CREATE VIEW RELATORIO AS
SELECT C.NOME,C.SEXO,
	 IFNULL(C.EMAIL,'SEM EMAIL') AS "E-MAIL",
	 T.TIPO,
	 T.NUMERO,
 E.BAIRRO,
	 E.CIDADE,
	 E.ESTADO
FROM CLIENTE C
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE;
SELECT * FROM RELATORIO;
SELECT * FROM RELATORIO
WHERE SEXO = 'F';
SHOW TABLES;
+--------------------+
| Tables_in_comercio |
+--------------------+
| cliente |
| endereco |
| relatorio |
| telefone |
+--------------------+
/* APAGANDO UMA VIEW */
DROP VIEW RELATORIO;
CREATE VIEW V_RELATORIO AS
SELECT C.NOME,C.SEXO,
	 IFNULL(C.EMAIL,'SEM EMAIL') AS "E-MAIL",
	 T.TIPO,
	 T.NUMERO,
 E.BAIRRO,
	 E.CIDADE,
	 E.ESTADO
FROM CLIENTE C
INNER JOIN TELEFONE T
ON C.IDCLIENTE = T.ID_CLIENTE
INNER JOIN ENDERECO E
ON C.IDCLIENTE = E.ID_CLIENTE;
SELECT NOME, SEXO, CIDADE
FROM V_RELATORIO;
+---------+------+----------------+
| NOME | SEXO | CIDADE |
+---------+------+----------------+
| JOAO | M | RIO DE JANEIRO |
| JOAO | M | RIO DE JANEIRO |
| JOAO | M | RIO DE JANEIRO |
| CARLOS | M | B. HORIZONTE |
| ANA | F | SAO PAULO |
| ANA | F | SAO PAULO |
| CELIA | F | NITERÓI |
| CELIA | F | NITERÓI |
| CELIA | F | NITERÓI |
| ANTONIO | M | RIO DE JANEIRO |
| ANTONIO | M | RIO DE JANEIRO |
| ANTONIO | M | RIO DE JANEIRO |
+---------+------+----------------+
DELETE FROM V_RELATORIO
WHERE NOME = 'JOAO';
Can not delete from join view 'comercio.v_relatorio'
INSERT INTO V_RELATORIO VALUES
('ROMARIO','M',NULL,'RES','99723882','BARRA','RIO DE JANEIRO','RJ');
Can not insert into join view 'comercio.v_relatorio' without fields list
/* A 25 - ORDER BY */
SELECT NOME, SEXO, CPF, CIDADE
FROM CLIENTE
INNER JOIN ENDERECO
ON IDCLIENTE = ID_CLIENTE;
SELECT NOME, SEXO, CPF, CIDADE
FROM CLIENTE
INNER JOIN ENDERECO
ON IDCLIENTE = ID_CLIENTE
ORDER BY NOME;
SELECT NOME, SEXO, CPF, CIDADE
FROM CLIENTE
INNER JOIN ENDERECO
ON IDCLIENTE = ID_CLIENTE
ORDER BY NOME, CPF ASC;
SELECT NOME, SEXO, CPF, CIDADE
FROM CLIENTE
INNER JOIN ENDERECO
ON IDCLIENTE = ID_CLIENTE
ORDER BY 4;
/* A26 - DELIMITADOR E ESTADO DE SERVIDOR */
SELECT * FROM V_RELATORIO
SELECT * FROM V_RELATORIO;
SELECT 'FELIPE', 'MAFRA';
DELIMITER $
SELECT * FROM V_RELATORIO;
STATUS
DELIMITER#
DELIMITER #
/* A27 PROCEDURES */
/* SEMPRE */
DELIMITER $
CREATE PROCEDURE NOME()
BEGIN
		ACAO;
END
$
SELECT 10 + 10 AS "CONTA"$
CREATE PROCEDURE CONTA()
BEGIN
		SELECT 10 + 10 AS "CONTA";
END
$
/* CHAMANDO A PROCEDURE */
CALL CONTA()$
DELIMITER ;
CALL CONTA();
/*PROCEDURES COM PARAMETROS */
DROP PROCEDURE CONTA;
DELIMITER $
CREATE PROCEDURE CONTA(NUMERO1 INT, NUMERO2 INT)
BEGIN
		SELECT NUMERO1 + NUMERO2 AS "CONTA";
END
$
DELIMITER ;
CALL CONTA(100,78);
CALL CONTA(47456,346435);
CALL CONTA(64577,85546);
/* A28 - PROCEDURES COM TABELAS */
CREATE TABLE CURSOS(
	IDCURSO INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30) NOT NULL,
	HORAS INT(3) NOT NULL,
	VALOR FLOAT(10,2) NOT NULL
);
INSERT INTO CURSOS VALUES(NULL,'BANCO DE DADOS FUNDAMENTAL',25,200.00);
SELECT * FROM CURSOS;
/* DELIMITADOR */
DELIMITER #
CREATE PROCEDURE CAD_CURSO(P_NOME VARCHAR(30),P_HORAS INT(3),P_PRECO FLOAT(10,2))
BEGIN
		INSERT INTO CURSOS VALUES(NULL,P_NOME,P_HORAS,P_PRECO);
END
#
DELIMITER ;
STATUS
CALL CAD_CURSO('BI SQL SERVER',35,200.00);
SELECT * FROM CURSOS;
DELIMITER #
CREATE PROCEDURE SEL_CURSO()
BEGIN
		SELECT IDCURSO, NOME, HORAS, VALOR
		FROM CURSOS;
END
#
DELIMITER ;
CALL SEL_CURSO();
/* A29 FUNCOES DE AGREGACAO NUMERICAS */
CREATE TABLE VENDEDORES(
	IDVENDEDOR INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	SEXO CHAR(1),
	JANEIRO FLOAT(10,2),
	FEVEREIRO FLOAT(10,2),
	MARCO FLOAT(10,2)
);
INSERT INTO VENDEDORES VALUES(NULL,'CARLOS','M',76234.78,88346.87,5756.90);
INSERT INTO VENDEDORES VALUES(NULL,'MARIA','F',5865.78,6768.87,4467.90);
INSERT INTO VENDEDORES VALUES(NULL,'ANTONIO','M',78769.78,6685.87,6664.90);
INSERT INTO VENDEDORES VALUES(NULL,'CLARA','F',5779.78,446886.87,8965.90);
INSERT INTO VENDEDORES VALUES(NULL,'ANDERSON','M',676545.78,77544.87,578665.90);
INSERT INTO VENDEDORES VALUES(NULL,'IVONE','F',57789.78,44774.87,68665.90);
INSERT INTO VENDEDORES VALUES(NULL,'JOAO','M',4785.78,66478.87,6887.90);
INSERT INTO VENDEDORES VALUES(NULL,'CELIA','F',89667.78,57654.87,5755.90);
/* MAX - TRAZ O VALOR MAXIMO DE UMA COLUNA */
SELECT MAX(FEVEREIRO) AS MAIOR_FEV
FROM VENDEDORES;
/* MIN - TRAZ O VALOR MINIMO DE UMA COLUNA */
SELECT MIN(FEVEREIRO) AS MENOR_FEV
FROM VENDEDORES;
/* AVG - TRAZ O VALOR MEDIO DE UMA COLUNA */
SELECT AVG(FEVEREIRO) AS MEDIA_FEV
FROM VENDEDORES;
/* VARIAS FUNCOES */
SELECT MAX(JANEIRO) AS MAX_JAN,
 MIN(JANEIRO) AS MIN_JAN,
	 AVG(JANEIRO) AS MEDIA_JAN
	 FROM VENDEDORES;
	 
/*TRUNCATE */
	 
SELECT MAX(JANEIRO) AS MAX_JAN,
 MIN(JANEIRO) AS MIN_JAN,
	 TRUNCATE(AVG(JANEIRO),2) AS MEDIA_JAN
	 FROM VENDEDORES;
/* A30 - AGREGANDO COM SUM() */
SELECT SUM(JANEIRO) AS TOTAL_JAN
FROM VENDEDORES;
SELECT SUM(JANEIRO) AS TOTAL_JAN,
	 SUM(FEVEREIRO) AS TOTAL_FEV,
	 SUM(MARCO) AS TOTAL_MAR
FROM VENDEDORES;
/* VENDAS POR SEXO */
SELECT SEXO, SUM(MARCO) AS TOTAL_MARCO
FROM VENDEDORES
GROUP BY SEXO;
/* A 31 - SUBQUERIES
VENDEDOR QUE VENDEU MENOS EM MARCO
E O SEU NOME 
NOME E O VALOR QUE VENDEU MAIS EM MARCO
QUEM VENDEU
MAIS QUE O VALOR MEDIO DE FEV
*/
SELECT NOME, MIN(MARCO) AS MIN_MARCO
FROM VENDEDORES;
SELECT * FROM VENDEDORES;
SELECT MIN(MARCO) FROM VENDEDORES;
SELECT NOME, MARCO FROM VENDEDORES
WHERE MARCO = (SELECT MIN(MARCO) FROM VENDEDORES);
SELECT NOME, MARCO FROM VENDEDORES
WHERE MARCO = (SELECT MAX(FEVEREIRO) FROM VENDEDORES);
SELECT NOME, MARCO FROM VENDEDORES
WHERE MARCO > (SELECT AVG(MARCO) FROM VENDEDORES);
SELECT NOME, FEVEREIRO FROM VENDEDORES
WHERE FEVEREIRO > (SELECT AVG(FEVEREIRO) FROM VENDEDORES);
SELECT AVG(FEVEREIRO) FROM VENDEDORES;
SELECT NOME, FEVEREIRO FROM VENDEDORES
WHERE FEVEREIRO < (SELECT AVG(FEVEREIRO) FROM VENDEDORES);
/* A32 - OPERACOES EM LINHA */
SELECT * FROM VENDEDORES;
SELECT NOME,
	 JANEIRO,
	 FEVEREIRO,
	 MARCO,
	 (JANEIRO+FEVEREIRO+MARCO) AS "TOTAL",
	 TRUNCATE((JANEIRO+FEVEREIRO+MARCO)/3,2) AS "MEDIA"
	 FROM VENDEDORES;
	 
/* APLICANDO UM % */
SELECT NOME,
	 JANEIRO,
	 FEVEREIRO,
	 MARCO,
	 (JANEIRO+FEVEREIRO+MARCO) AS "TOTAL",
	 (JANEIRO+FEVEREIRO+MARCO) * .25 AS "DESCONTO",
	 TRUNCATE((JANEIRO+FEVEREIRO+MARCO)/3,2) AS "MEDIA"
	 FROM VENDEDORES;
	 
/* A33 - ALTERANDO TABELAS */
CREATE TABLE TABELA(
	COLUNA1 VARCHAR(30),
	COLUNA2 VARCHAR(30),
	COLUNA3 VARCHAR(30)
);
CREATE TABLE TABELA(
	COLUNA1 INT PRIMARY KEY AUTO_INCREMENT
);
--ADICIONANDO UMA PK
ALTER TABLE TABELA 
ADD PRIMARY KEY (COLUNA1);
--ADICIONANDO COLUNA SEM POSICAO. ULTIMA POSICAO
ALTER TABLE TABELA 
ADD COLUNA varchar(30);
ALTER TABLE TABELA 
ADD COLUNA100 INT;
--ADICIONANDO UMA COLUNA COM POSICAO
ALTER TABLE TABELA 
ADD COLUMN COLUNA4 VARCHAR(30) NOT NULL UNIQUE
AFTER COLUNA3;
--MODIFICANDO O TIPO DE UM CAMPO
ALTER TABLE TABELA
 MODIFY COLUNA2 DATE NOT NULL;
--RENOMEANDO O NOME DA TABELA
ALTER TABLE TABELA 
RENAME PESSOA;
CREATE TABLE TIME(
	IDTIME INT PRIMARY KEY AUTO_INCREMENT,
	TIME VARCHAR(30),
	ID_PESSOA VARCHAR(30)
);
--Foreign key
ALTER TABLE TIME 
ADD FOREIGN KEY(ID_PESSOA)
REFERENCES PESSOA(COLUNA1);
/* VERIFICAR AS CHAVES */
SHOW CREATE TABLE TIME;
/* A34 - ORGANIZACAO DE CHAVES - CONSTRAINT (REGRA) */
CREATE TABLE JOGADOR(
	IDJOGADOR INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30)
);
CREATE TABLE TIMES(
	IDTIME INT PRIMARY KEY AUTO_INCREMENT,
	NOMETIME VARCHAR(30),
	ID_JOGADOR INT,
	FOREIGN KEY(ID_JOGADOR)
	REFERENCES JOGADOR(IDJOGADOR)
);
INSERT INTO JOGADOR VALUES(NULL,'GUERRERO');
INSERT INTO TIMES VALUES(NULL,'FLAMENGO',1);
SHOW CREATE TABLE JOGADOR;
SHOW CREATE TABLE TIMES;
/* A35 - ORGANIZANDO CHAVES
 */
SHOW TABLES;
DROP TABLE ENDERECO;
DROP TABLE TELEFONE;
DROP TABLE CLIENTE;
CREATE TABLE CLIENTE(
	IDCLIENTE INT,
	NOME VARCHAR(30) NOT NULL
);
CREATE TABLE TELEFONE(
	IDTELEFONE INT,
	TIPO CHAR(3) NOT NULL,
	NUMERO VARCHAR(10) NOT NULL,
	ID_CLIENTE INT
);
ALTER TABLE CLIENTE ADD CONSTRAINT PK_CLIENTE
PRIMARY KEY(IDCLIENTE);
ALTER TABLE TELEFONE ADD CONSTRAINT FK_CLIENTE_TELEFONE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE);
SHOW CREATE TABLE TELEFONE;
/* DICIONARIO DE DADOS */
SHOW DATABASES;
USE INFORMATION_SCHEMA;
STATUS
SHOW TABLES;
DESC TABLE_CONSTRAINTS;
SELECT CONSTRAINT_SCHEMA AS "BANCO",
	 TABLE_NAME AS "TABELA",
	 CONSTRAINT_NAME AS "NOME REGRA",
	 CONSTRAINT_TYPE AS "TIPO"
	 FROM TABLE_CONSTRAINTS
	 WHERE CONSTRAINT_SCHEMA = 'COMERCIO';
/* APAGANDO CONSTRAINTS */
USE COMERCIO;
ALTER TABLE TELEFONE
DROP FOREIGN KEY FK_CLIENTE_TELEFONE;
ALTER TABLE TELEFONE ADD CONSTRAINT FK_CLIENTE_TELEFONE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE);
/*36 E ACOES DE CONSTRAINTS */
SHOW TABLES;
INSERT INTO CLIENTE VALUES(1,'RICARDO');
INSERT INTO CLIENTE VALUES(2,'CLARA');
INSERT INTO CLIENTE VALUES(3,'JULIO');
INSERT INTO CLIENTE VALUES(4,'ANA');
INSERT INTO TELEFONE VALUES(10,'CEL','37834334',2);
INSERT INTO TELEFONE VALUES(20,'RES','67465656',2);
INSERT INTO TELEFONE VALUES(30,'CEL','88665645',1);
INSERT INTO TELEFONE VALUES(40,'RES','77543546',3);
INSERT INTO TELEFONE VALUES(50,'COM','75454675',1);
INSERT INTO TELEFONE VALUES(60,'CEL','76765455',4);
SELECT NOME, TIPO, NUMERO
FROM CLIENTE
INNER JOIN TELEFONE
ON IDCLIENTE = ID_CLIENTE;
/*ERRO DE REFERENCIA */
DELETE FROM CLIENTE WHERE IDCLIENTE = 1;
/*CORRIGIR ERRO*/
DELETE FROM TELEFONE
WHERE ID_CLIENTE = 1;
DELETE FROM CLIENTE WHERE IDCLIENTE = 1;
/* APAGANDO A FOREIGN KEY */
ALTER TABLE TELEFONE DROP FOREIGN KEY FK_CLIENTE_TELEFONE;
/*RECRIANDO A FK */
ALTER TABLE TELEFONE ADD CONSTRAINT FK_CLIENTE_TELEFONE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE)
ON DELETE CASCADE;
SELECT NOME, TIPO, NUMERO
FROM CLIENTE
INNER JOIN TELEFONE
ON IDCLIENTE = ID_CLIENTE;
DELETE FROM CLIENTE WHERE IDCLIENTE = 2;
ALTER TABLE TELEFONE DROP FOREIGN KEY FK_CLIENTE_TELEFONE;
ALTER TABLE TELEFONE ADD CONSTRAINT FK_CLIENTE_TELEFONE
FOREIGN KEY(ID_CLIENTE) REFERENCES CLIENTE(IDCLIENTE)
ON DELETE SET NULL;
SELECT NOME, TIPO, NUMERO
FROM CLIENTE
INNER JOIN TELEFONE
ON IDCLIENTE = ID_CLIENTE;
SELECT * FROM CLIENTE;
DELETE FROM CLIENTE WHERE IDCLIENTE = 3;
SELECT * FROM TELEFONE;
/* SET NULL - DELETE CASCADE */
/*A37 CORRECAO EX 01 */
CREATE DATABASE PROJETO;
USE PROJETO;
/* A38 MODELO FISICO */
CREATE DATABASE OFICINA;
USE OFICINA;
CREATE TABLE CLIENTE(
	IDCLIENTE INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30) NOT NULL,
	SEXO ENUM('M','F') NOT NULL,
	ID_CARRO INT UNIQUE
);
CREATE TABLE TELEFONE(
	IDTELEFONE INT PRIMARY KEY AUTO_INCREMENT,
	TIPO ENUM('CEL','RES','COM') NOT NULL,
	NUMERO VARCHAR(30) NOT NULL,
	ID_CLIENTE INT 
);
CREATE TABLE MARCA(
	IDMARCA INT PRIMARY KEY AUTO_INCREMENT,
	MARCA VARCHAR(30) UNIQUE
);
CREATE TABLE CARRO(
	IDCARRO INT PRIMARY KEY AUTO_INCREMENT,
	MODELO VARCHAR(30) NOT NULL,
	PLACA VARCHAR(30) NOT NULL UNIQUE,
	ID_MARCA INT
);
CREATE TABLE COR(
	IDCOR INT PRIMARY KEY AUTO_INCREMENT,
	COR VARCHAR(30) UNIQUE
);
CREATE TABLE CARRO_COR(
	ID_CARRO INT,
	ID_COR INT,
	PRIMARY KEY(ID_CARRO,ID_COR)
);
/* CONSTRAINTS */
ALTER TABLE TELEFONE 
ADD CONSTRAINT FK_TELEFONE_CLIENTE
FOREIGN KEY(ID_CLIENTE)
REFERENCES CLIENTE(IDCLIENTE);
ALTER TABLE CLIENTE
ADD CONSTRAINT FK_CLIENTE_CARRO
FOREIGN KEY(ID_CARRO)
REFERENCES CARRO(IDCARRO);
ALTER TABLE CARRO
ADD CONSTRAINT FK_CARRO_MARCA
FOREIGN KEY(ID_MARCA)
REFERENCES MARCA(IDMARCA);
ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_COR
FOREIGN KEY(ID_COR)
REFERENCES COR(IDCOR);
ALTER TABLE CARRO_COR
ADD CONSTRAINT FK_CARRO
FOREIGN KEY(ID_CARRO)
REFERENCES CARRO(IDCARRO);
/* PREENCHER O BANCO COM NO MINIMO 10 CLIENTES */
INSERT INTO CARRO VALUES(NULL,'')
/* A 39 ENTENDENDO TRIGGERS */
/* ESTRUTURA DE UMA TRIGGER */
CREATE TRIGGER NOME
BEFORE/AFTER INSERT/DELETE/UPDATE ON TABELA
FOR EACH ROW (PARA CADA LINHA)
BEGIN -> INICIO
		QUALQUER COMANDO SQL
END -> FIM
CREATE DATABASE AULA40;
USE AULA40;
CREATE TABLE USUARIO(
	IDUSUARIO INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	LOGIN VARCHAR(30),
	SENHA VARCHAR(100)
);
CREATE TABLE BKP_USUARIO(
	IDBACKUP INT PRIMARY KEY AUTO_INCREMENT,
	IDUSUARIO INT,
	NOME VARCHAR(30),
	LOGIN VARCHAR(30)
);
/* CRIANDO A TRIGGER */
DELIMITER $
CREATE TRIGGER BACKUP_USER
BEFORE DELETE ON USUARIO
FOR EACH ROW 
BEGIN
		
	INSERT INTO BKP_USUARIO VALUES
	(NULL,OLD.IDUSUARIO,OLD.NOME,OLD.LOGIN);
END
$
INSERT INTO USUARIO VALUES(NULL,'ANDRADE','ANDRADE2009','HEXACAMPEAO');
SELECT * FROM USUARIO;
DELETE FROM USUARIO WHERE IDUSUARIO = 1;
/* A 41 - COMUNICACAO ENTRE BANCOS */
CREATE DATABASE LOJA;
USE LOJA;
CREATE TABLE PRODUTO(
	IDPRODUTO INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	VALOR FLOAT(10,2)
);
STATUS
CREATE DATABASE BACKUP;
USE BACKUP;
CREATE TABLE BKP_PRODUTO(
	IDBKP INT PRIMARY KEY AUTO_INCREMENT,
	IDPRODUTO
INT,
	NOME VARCHAR(30),
	VALOR FLOAT(10,2)
);
USE LOJA;
STATUS
INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,1000,'TESTE',0.0);
SELECT * FROM BACKUP.BKP_PRODUTO;
DELIMITER $
CREATE TRIGGER BACKUP_PRODUT
BEFORE INSERT ON PRODUTO
FOR EACH ROW
BEGIN
	
	INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,NEW.IDPRODUTO,
	NEW.NOME,NEW.VALOR);
END
$
DELIMITER ;
INSERT INTO PRODUTO VALUES(NULL,'LIVRO MODELAGEM',50.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO BI',80.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO ORACLE',70.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO SQL SERVER',100.00);
SELECT * FROM PRODUTO;
SELECT * FROM BACKUP.BKP_PRODUTO;
DELIMITER $
CREATE TRIGGER BACKUP_PRODUTO_DEL
BEFORE DELETE ON PRODUTO
FOR EACH ROW
BEGIN
	
	INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,OLD.IDPRODUTO,
	OLD.NOME,OLD.VALOR);
END
$
DELIMITER ;
DELETE FROM PRODUTO WHERE IDPRODUTO = 2;
DROP TRIGGER BACKUP_PRODUT;
DELIMITER $
CREATE TRIGGER BACKUP_PRODUTO
AFTER INSERT ON PRODUTO
FOR EACH ROW
BEGIN
	
	INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,NEW.IDPRODUTO,
	NEW.NOME,NEW.VALOR);
END
$
DELIMITER ;
INSERT INTO PRODUTO VALUES(NULL,'LIVRO C#',100.00);
SELECT * FROM PRODUTO;
SELECT * FROM BACKUP.BKP_PRODUTO;
ALTER TABLE BACKUP.BKP_PRODUTO
ADD EVENTO CHAR(1);
DROP TRIGGER BACKUP_PRODUTO_DEL;
DELIMITER $
CREATE TRIGGER BACKUP_PRODUTO_DEL
BEFORE DELETE ON PRODUTO
FOR EACH ROW
BEGIN
	
	INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,OLD.IDPRODUTO,
	OLD.NOME,OLD.VALOR,'D');
END
$
DELIMITER ;
DELETE FROM PRODUTO WHERE IDPRODUTO = 4;
SELECT * FROM BACKUP.BKP_PRODUTO;
/* A 43 - TRIGGER DE AUDITORIA */
DELIMITER ;
DROP DATABASE LOJA;
DROP DATABASE BACKUP;
CREATE DATABASE LOJA;
USE LOJA;
CREATE TABLE PRODUTO(
	IDPRODUTO INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	VALOR FLOAT(10,2)
);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO MODELAGEM',50.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO BI',80.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO ORACLE',70.00);
INSERT INTO PRODUTO VALUES(NULL,'LIVRO SQL SERVER',100.00);
/*QUANDO*/
SELECT NOW();
/*QUEM*/
SELECT CURRENT_USER();
CREATE DATABASE BACKUP;
USE BACKUP;
CREATE TABLE BKP_PRODUTO(
	IDBACKUP INT PRIMARY KEY AUTO_INCREMENT,
	IDPRODUTO INT,
	NOME VARCHAR(30),
	VALOR_ORIGINAL FLOAT(10,2),
	VALOR_ALTERADO FLOAT(10,2),
	DATA DATETIME,
	USUARIO VARCHAR(30),
	EVENTO CHAR(1)
	
);
USE LOJA;
SELECT * FROM PRODUTO;
DELIMITER $
CREATE TRIGGER AUDIT_PROD
AFTER UPDATE ON PRODUTO
FOR EACH ROW
BEGIN
	INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL,OLD.IDPRODUTO,OLD.NOME,
	OLD.VALOR,NEW.VALOR,NOW(),CURRENT_USER(),'U');
	
END
$
DELIMITER ;
UPDATE PRODUTO SET VALOR = 110.00
WHERE IDPRODUTO = 4;
SELECT * FROM PRODUTO;
SELECT * FROM BACKUP.BKP_PRODUTO;
/* A44 - AUTORELACIONAMENTO */
CREATE TABLE CURSOS(
	IDCURSO INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	HORAS INT,
	VALOR FLOAT(10,2),
	ID_PREREQ INT
);
ALTER TABLE CURSOS ADD CONSTRAINT FK_PREREQ
FOREIGN KEY(ID_PREREQ) REFERENCES CURSOS(IDCURSO);
INSERT INTO CURSOS VALUES(NULL,'BD RELACIONAL',20,400.00,NULL);
INSERT INTO CURSOS VALUES(NULL,'BUSINESS INTELLIGENCE',40,800.00,1);
INSERT INTO CURSOS VALUES(NULL,'RELATORIOS AVANCADOS',20,600.00,2);
INSERT INTO CURSOS VALUES(NULL,'LOGICA PROGRAM',20,400.00,NULL);
INSERT INTO CURSOS VALUES(NULL,'RUBY',30,500.00,4);
SELECT * FROM CURSOS;
SELECT NOME, VALOR, HORAS, IFNULL(ID_PREREQ,"SEM REQ") REQUISITO
FROM CURSOS;
/* NOME, VALOR, HORAS E O NOME DO PRE REQUISITO DO CURSO */
SELECT 
C.NOME AS CURSO, 
C.VALOR AS VALOR, 
C.HORAS AS CARGA, 
IFNULL(P.NOME, "---") AS PREREQ
FROM CURSOS C LEFT JOIN CURSOS P
ON P.IDCURSO = C.ID_PREREQ;
/* A 45 - CURSORES */
CREATE DATABASE CURSORES;
USE CURSORES;
CREATE TABLE VENDEDORES(
	IDVENDEDOR INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(50),
	JAN INT,
	FEV INT,
	MAR INT
);
INSERT INTO VENDEDORES VALUES(NULL,'MAFRA',32432,242334,574545);
INSERT INTO VENDEDORES VALUES(NULL,'CLARA',65465,65443,653454);
INSERT INTO VENDEDORES VALUES(NULL,'JOAO',12432,65356,8756);
INSERT INTO VENDEDORES VALUES(NULL,'LILIAN',4567,9676,8765);
INSERT INTO VENDEDORES VALUES(NULL,'ANTONIO',3467,68756,99765);
INSERT INTO VENDEDORES VALUES(NULL,'GLORIA',54786,76889,7098);
SELECT * FROM VENDEDORES;
SELECT NOME, (JAN+FEV+MAR) AS TOTAL FROM VENDEDORES;
SELECT NOME, (JAN+FEV+MAR) AS TOTAL, (JAN+FEV+MAR)/3 AS MEDIA FROM VENDEDORES;
CREATE TABLE VEND_TOTAL(
	NOME VARCHAR(50),
	JAN INT,
	FEV INT,
	MAR INT,
	TOTAL INT,
	MEDIA INT
);
DELIMITER $
CREATE PROCEDURE INSEREDADOS()
BEGIN
		DECLARE FIM INT DEFAULT 0;
		DECLARE VAR1, VAR2, VAR3, VTOTAL, VMEDIA INT;
		DECLARE VNOME VARCHAR(50);
		
		DECLARE REG CURSOR FOR(
			SELECT NOME, JAN, FEV, MAR FROM VENDEDORES
		);
		
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET FIM = 1;
		
		OPEN REG;
		
		REPEAT
		
			FETCH REG INTO VNOME, VAR1, VAR2, VAR3;
			IF NOT FIM THEN
			
				SET VTOTAL = VAR1 + VAR2 + VAR3;
				SET VMEDIA = VTOTAL / 3;
				
				INSERT INTO VEND_TOTAL VALUES(VNOME,VAR1,VAR2,VAR3,VTOTAL,VMEDIA);
				
			END IF;
			
		UNTIL FIM END REPEAT;
		
		CLOSE REG;
END
$
SELECT * FROM VENDEDORES;
SELECT * FROM VEND_TOTAL;
DELIMITER ;
CALL INSEREDADOS();
INSERT INTO VENDEDORES VALUES(NULL,'LETICIA',656,3546,234545);
INSERT INTO VENDEDORES VALUES(NULL,'CELIA',6766,56556,65454);
SELECT * FROM VENDEDORES;
SELECT * FROM VEND_TOTAL;
DELETE FROM VEND_TOTAL;
CALL INSEREDADOS();
/* A 46 TRIGGERS COM VARIAVEIS */
DELIMITER $
CREATE TRIGGER CADTOTAL
BEFORE INSERT ON VENDEDORES
FOR EACH ROW
BEGIN
		DECLARE VTOTAL, VMEDIA INT;
		
		SET VTOTAL := NEW.JAN + NEW.FEV + NEW.MAR;
		SET VMEDIA := (NEW.JAN + NEW.FEV + NEW.MAR)/3;
		
		INSERT INTO VEND_TOTAL VALUES(NEW.NOME,NEW.JAN,NEW.FEV,NEW.MAR,VTOTAL,VMEDIA);
END
$
DELIMITER ;
INSERT INTO VENDEDORES VALUES(NULL,'JULIO',9999,9999,9999);
						 
SELECT * FROM VENDEDORES;
SELECT * FROM VEND_TOTAL;
/* A47 SEGUNDA E TERCEIRAS FORMAS NORMAIS */
/*
	 PRIMEIRA FN
	 
	 ATOMICIDADE - UM CAMPO NAO PODE SER DIVISIVEL
	 UM CAMPO NAO PODE SER VETORIZADO
	 PK CHAVE PRIMARIA
*/
CREATE DATABASE CONSULTORIO;
CREATE TABLE PACIENTE(
	IDPACIENTE INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	SEXO CHAR(1),
	EMAIL VARCHAR(30),
	NASCIMENTO DATE
);
CREATE TABLE MEDICO(
	IDMEDICO INT PRIMARY KEY IDENTITY,
	NOME VARCHAR(30),
	SEXO CAHR(1),
	ESPECIALIDADE VARCHAR(30),
	FUNCIONARIO ENUM('S','N')
);
CREATE TABLE HOSPITAL(
	IDHOSPITAL INT PRIMARY KEY AUTO_INCREMENT,
	NOME VARCHAR(30),
	BAIRRO VARCHAR(30),
	CIDADE VARCHAR(30),
	ESTADO CHAR(2)
);
CREATE TABLE CONSULTA(
	IDCONSULTA INT PRIMARY KEY AUTO_INCREMENT,
	ID_PACIENTE INT,
	ID_MEDICO INT,
	ID_HOSPITAL INT,
	DATA DATETIME,
	DIAGNOSTICO VARCHAR(50)
);
CREATE TABLE INTERNACAO(
	IDINTERNACAO INT PRIMARY KEY AUTO_INCREMENT,
	ENTRADA DATETIME,
	QUARTO INT,
	SAIDA DATETIME,
	OBSERVACOES VARCHAR(50),
	ID_CONSULTA INT UNIQUE	
);