Banco de Dados - Estudo de Caso Lan House do Pereira

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

CENTRO ESTADUAL DE EDUCAÇÃO TECNOLÓGICA PAULA SOUZA
FACULDADE DE TECNOLOGIA DE MAUÁ
GABRYEL SILVA
QUESTIONÁRIO 1 – BANCO DE DADOS
MAUÁ-SP
2018
SUMÁRIO
(1) QUESTIONÁRIO 1 – BANCO DE DADOS......................................
(2) ESTUDO DE CASO 1 – LAN HOUSE DO PEREIRA........................
(2.1) TABELAS....................................................................................
(2.2) ATRIBUTOS................................................................................
(2.3) TABELAS E ATRIBUTOS.........................................................
QUESTIONÁRIO 1 – BANCO DE DADOS
(1) Explique: 
a) Modelo Conceitual 
R: Visão inicial das regras sem limitações tecnológicas ou de implementação.
b) Modelo Lógico 
R: Abstrai as possíveis técnicas de Banco de Dados a serem usadas. Ex: Banco de dados hierárquico ou relacional.
c) Modelo Físico 
R: É a modelagem que leva em consideração as limitações físicas do SGBD e dos programas que acessam o mesmo.
(2) O que é uma entidade?
É a representação de um objetivo do mundo real ou abstrato em Banco de Dados. 
(3) O que são atributos? 
São as características que compõem a entidade Ex: Nome, Cidade, Cor, Sexo;
(4) O que são registros? 
É um conjunto de atributos que contém informações comuns a um determinado elemento.
(5) O que são arquivos? 
É um conjunto de registros.
(6) O que é uma chave? 
É um atributo principal que identifica um registro.
(7) Quais os tipos de chave que podemos ter?
Simples, Composta e Virtual. 
(8) Quais as características que uma chave deve apresentar? 
Ela deve ser única, obrigatória e imutável.
(9) O que é dependência funcional? 
É a característica de um atributo depender de outro para existir ou representar uma informação.
(10) Explique o que é Normalização de dados. 
Normalização de dados é uma técnica que consiste em fazer uma limpeza nos dados eliminando inconsistências, duplicidades, e garante uma integridade e confiabilidade dos dados.
(11) Explique de forma breve o que acontece na 1FN, 2FN e 3FN.
1FN: Criação de uma estrutura BIDIMENSIONAL e identificação de uma chave
2FN: Separa do arquivo informações que não tem dependência funcional com a chave do arquivo
3FN: Elimina atributos multivalorados e atributos calculado.
(12) Até onde devemos normalizar um arquivo? 
Até a terceira norma.
(13) Quais são as regras práticas para a normalização? 
Usar o bom senso na hora de normatizar tendo cuidado com a fragmentação dos dados e desempenho do SGBD na recuperação dos dados.
ESTUDO DE CASO – LAN HOUSE DO PEREIRA
(14) Considere o estudo de caso abaixo: 
A Lan House disponibiliza acesso a internet durante 24h por dia, 7 dias por semana. Seus donos desejam implementar um sistema para registro de usuários, controle de reservas, e registrar as utilizações de equipamentos além de extrair relatórios gerenciais. Entre os requisitos que devem ser atendidos pelo sistema temos:
> Emitir uma listagem de usuários com código, nome, idade, sexo, endereço, telefone, email ordenado por endereço.
> Emitir uma listagem de equipamentos disponíveis na lan house com código, CPU, memória, disco, acessórios (Joystick, mouse ótico, fone de ouvido, etc.)
> Criar e manter uma agenda de reservas com usuário (código), nome, telefone, data de reserva, hora da reserva, em ordem decrescente de data da reserva.
> Emitir um histórico de utilização com data, equipamento, usuário (código e nome), hora de inicio, hora final, tempo utilizado, valor cobrado ordenado por data e nome de usuário.
> Emitir um extrato de utilização com data, equipamento, hora de início, hora final, tempo utilizado, valor cobrado ordenado por data
> Emitir um histórico de utilização por período (manhã, tarde, noite) com total de usuários por período e média de tempo utilizado.
> Listar os maiores usuários (em relação ao tempo total utilizado) por ano com: usuário
(código), nome, idade, telefone, email, quantidade de utilizações, tempo total.
> Gerar estatísticas de usuários no ano, mostrando o total de usuários para cada mês e o percentual do mês em relação ao total do ano.
> Criar um modelo normalizado até a 3FN, identificado as tabelas e atributos de cada tabela, definir qual ou quais são atributos chaves e quais os seus tipos (Número, texto, data).
(2.1) TABELAS
(1) Usuário
(2) Equipamento
(3) Reserva
(4) Utilização
(2.2) ATRIBUTOS
Usuário: (CHAVE) CODIGO_USUARIO, NOME, IDADE, SEXO, RUA/ AVENIDA, NUMERO, BAIRRO, COMPLEMENTO,CIDADE,EMAIL
Equipamento: (CHAVE) CODIGO_EQUIPAMENTO, CPU, MEMORIA, DISCO, JOYSTICK, MOUSE, FONE_DE_OUVIDO
Reserva: (CHAVE) CODIGO_RESERVA, (CHAVE) CODIGO_USUARIO, DATA_RESERVA
Utilização: (CHAVE) CODIGO_UTILIZAÇÃO, (CHAVE) CODIGO_USUARIO, (CHAVE) EQUIPAMENTO, HORARIO_INICIO, HORARIO_FINAL. 
(2.3) TABELAS E ATRIBUTOS
(1) USUARIO
(2) EQUIPAMENTO
 (3) RESERVA
(4) UTILIZAÇÃO
Exercício – SQL
Exercicio 01 – Aula 04.
a) Criar a seguinte tabela:
Placa 		Char(7)
Modelo	varchar(20)
Fabricante	varchar(20)
Ano		int
Cor		varchar(20)
Combustível	varchar(20)
Preço		numeric(10,2)
Chave primária: placa
Inserir 10 registros
Selecionar todos os veículos com modelo iniciado pela letra ‘F’
Selecionar todos os veículos de ano entre 2000 e 2008
Selecionar todos de combustível FLEX
Selecionar todos do fabricante GM com ano superior a 2001;
Qual a média de preço dos veículos;
Qual o valor do veículo mais caro;
Qual o valor do veículo mais barato;
Acrescentar um aumento de 10% para todos os veículos do fabricante FIAT;
Quantos veículos prata temos no cadastro?
Qual o somatório dos preços?
Excluir do cadastro todos os veículos com ano inferior a 1985;
Selecionar todos dos fabricantes: GM, FORD e TOYOTA;
Listar todos os fabricantes, sem duplicidade.
CREATE TABLE CARRO
 
 --A)
(PLACA CHAR(7),
 MODELO	VARCHAR(20),
 FABRICANTE	VARCHAR(20), 
 ANO INT,
 COR	VARCHAR(20),
 COMBUSTÍVEL	VARCHAR(20),
 PREÇO	NUMERIC(10 ,2),
 
 CONSTRAINT PK_PLACA PRIMARY KEY(PLACA));
 
 -- LISTAR A ESTRUTURA TABELA 
 
 SELECT *FROM CARRO;
 
 --INSERIR REGISTRO
 
 
 INSERT INTO CARRO VALUES
 ('EFR6796','FOX','VOLKSWAGEN',2013,'PRETO', 'FLEX',30000.00);
 
 --B)
 INSERT INTO CARRO VALUES
 ('ABC0012','CORSA','GM',2000,'PRATA','GASOLINA',10000.00),
 ('BCD1234','CLS', 'MERCEDES', 2017,'VERMELHO', 'FLEX', 75000.00),
 ('EFG5678', 'GOL','VOLKSWAGEN', 2009, 'AZUL','FLEX', 15000.00),
 ('SDF4566', 'UNO' ,'FIAT',2008,'ROSA´','GASOLINA', 12000.00),
 ('AFR4496','FUSCA','VOLKSWAGEN',1973,'PRETO', 'GASOLINA',6000.00),
 ('CBC0012','CLIO','RENAULT',2001,'VERDE','GASOLINA',8000.00),
 ('JLD1254','VECTRA', 'GM', 2012,'AMARELO', 'FLEX', 25000.00),
 ('MJG5678', 'MERIVA','GM', 2007, 'BRANCO','GASIOLINA', 7000.00),
 ('GAI4576', 'KA' ,'FORD',2015,'VERMELHO´','FLEX', 18000.00);
 
 
 --DELETAR REGISTROS
 
 DELETE FROM CARRO;
 
 --C) SELECIONAR VEICULOS INICIADO COM A LETRA F
 SELECT * FROM CARRO WHERE modelo LIKE 'F%'
 
 --D)SELECIONAR TODOS ODS VEICULOS DE ANO 2000 E 2008
 SELECT * FROM CARRO WHERE ANO BETWEEN 2000 AND 2008;
 
 --E)SELECIONAR TODOS OS COMBUSTIVEL FLEX
 SELECT * FROM CARRO WHERE COMBUSTÍVEL IN ('FLEX');
 
 --F) SELECIONAR TODOS OS FABRICANTES GM COM O ANO SUPERIOR A 2001
 SELECT * FROM CARRO WHERE FABRICANTE = 'GM' AND ANO > 2001;
 
 
 
 -- EXCLUIR DO CADASTRO TODOS OS VEICULOS COM ANO INFERIOR A 1985;
DELETE FROM CARRO WHERE ano < 1985;
-- SELECIONAR TODOS OS FABRICANTES :GM,FORD E TOYOTA
SELECT * FROM CARRO WHERE FABRICANTE IN ('GM','FORD','TOYOTA'); 
 
 -- LISTAR TODOS OS FABRICANTES SEM DUPLICIDADE
 SELECT DISTINCT FABRICANTE FROM CARRO;
 
-- G) QUAL É A MEDIA DE PREÇO DOS VEÍCULOS
SELECT AVG (PREÇO) AS 'MÉDIA'FROM CARRO;
--H) QUAL VALOR DO VEÍCULO MAIS CARO
SELECT MAX (PREÇO) AS 'MAIS CARO' FROM CARRO;
--I) QUAL VALOR DO VEÍCULO MAIS BARATO
SELECT MIN (PREÇO) AS 'MAIS BARATO' FROM CARRO;
--J) ACRESCENTAR UM AUMENTO DE 10% PARA TODOS OS VEÍCULOS
DO FABRICANTE FIAT
UPDATE CARRO SET PREÇO =PREÇO *1.10 WHERE FABRICANTE= 'FIAT';
 
 --K) QUANTOS VEICULOS PRATA TEMOS NO CADASTRO
 SELECT COUNT(*) AS 'PRATA' FROM CARRO WHERE COR = 'PRATA';
 
 
Exercicio 02 – Aula 04.
Produtos
*Codprod	int
Descrição	varchar(20)
Preco		numeric(9,2)
Qtde		int
Cor		char(20)
Chave primária: codprod
Criar a tabela;
Inserir 10 registros;
Quantos produtos temos no cadastro?
Selecionar os produtos com preço entre 10 e 50 reais.
Qual o valor do produto mais caro?
Qual o valor do produto mais barato?
Qual a média de preços?
Quantos produtos vermelhos temos no cadastro?
Atualizar o preço dos produtos com preço abaixo de 10.00 acrescentando 15%;
Atualizar o preço dos produtos com preço acima de 500.00 com um desconto de 5%
Excluir todos os produtos com qtde abaixo de 5;
Listar as cores sem duplicidade
Selecionar todos os produtos de cor Azul, vermelho, branco e preto.
Qual o somatório das quantidades?
Listar todos os produtos que tem descrição iniciado em ‘A’.
CREATE TABLE PROD
 (CODPROD INT,
 DESCRIÇÃO VARCHAR(20),
 PREÇO NUMERIC(9,2),
 QTDE INT,
 COR CHAR(20),
 CONSTRAINT PK_CODPROD PRIMARY KEY(CODPROD));
 
 -- LISTAR A ESTRUTURA TABELA 
 
 SELECT * FROM PROD;
 
 --INSERIR REGISTRO
 
 INSERT INTO PROD VALUES
 (1, 'CAMISETA', 40.00, 12,'AMARELA'),
 (2, 'CALÇA ', 99.00, 5, 'JEANS'),
 (3,' VESTIDO', 150.00,6, 'ESTAMPADOS'),
 (4,'SAIA', 60.00,10,'AZUIS'),
 (5,' JAQUETAS', 200.00, 15, 'PRETA'),
 (6,' JAQUETAS', 200.00, 10, 'BRANCA'),
 (7, 'CAMISETA', 40.00, 8,'ROSA'),
 (8, 'CALÇA ', 99.00, 2, 'VERMELHA'),
 (9,' VESTIDO', 150.00,6, 'VERDE'),
 (10,'MEIAS',5.00,25,'MARROM');
 
 DELETE FROM PROD;
 
 --EXCLUIR TABELA E REGISTROS
DROP TABLE PROD;
 
 --SELECIONAR OS PRODUTOS COM PREÇO ENTRE 10,00 E 50,00
 SELECT *FROM PROD WHERE PREÇO BETWEEN 10.00 AND 50.00;
 
 -- EXCLUIR TODOS OS PRRODUTOS ABAIXO DE 6,00
 SELECT * FROM PROD WHERE PREÇO < 6.00;
 
 -- LISTAR AS CORES SEM DUPLICIDADE
SELECT DISTINCT COR FROM PROD;
-- SELECIONAR TODOS OS PRODUTOS DE COR AZUL, VERMELHO,BRANCO,E PRETO;
 SELECT * FROM PROD WHERE COR IN ('AZUIS', 'VERMELHA', 'BRANCA','PRETA');
 
 -- LISTAR TODOS OS PRODUTOS QUE TEM DESCRIÇÃO INICIADO COM A LETRA A
 SELECT * FROM PROD WHERE DESCRIÇÃO LIKE 'C%';
 
 --QUANTOS PRODUTOS TEM NO CADASTRO
 SELECT COUNT(*) AS QTDE FROM PROD;
 
 --QUAL O VALOR DO PRODUTO MAIS CARO
 SELECT MAX (PREÇO )AS ' MAIS CARO'FROM PROD;
 
 --QUAL O VALOR DO PRODUTO MAIS BARATO
 SELECT MIN(PREÇO)AS 'MAIS BARATO'FROM PROD;
 
 -- QUAL A MÉDIA DOS PREÇOS
 SELECT AVG (PREÇO) AS ' MEDIA PREÇO' FROM PROD;
 
 --QUANTOS PRODUTOS VERMELHOS TEMOS NO CADASTRO 
 SELECT COUNT(*) AS 'VERMELHA' FROM PROD WHERE COR = 'VERMELHA';
 
 --ATUALIZAR OS PREÇOS DOS PRODUTOS COM PREÇO ABAIXO DE 10 REAIS ACRESCENTANDO 15%
 UPDATE PROD SET PREÇO=PREÇO * 1.15 
 WHERE PREÇO<10; 
 
 UPDATE PROD SET PREÇO=PREÇO *0.95
 WHERE PREÇO >500;
 
 
Exercicio 03 – Aula 05.
Inserir 5 registros
Selecionar código do pedido, nome do produto e qtde.
Selecionar todos os produtos com preço entre 10 e 70.
Qual a média de idade dos clientes
Qual a média de preços dos produtos
Quantos pedidos temos no cadastro?
Selecionar todos os pedidos do cliente 2, informando código do pedido, data e nome do cliente;
Selecionar todos os itens do pedido 1, mostrar código do produto, nome do produto e qtde.
Selecionar todos os clientes que moram em Santo André, SBC, SCS ou Mauá.
--A)
CREATE TABLE CLIENTES2 
(CODCLI INT,
NOME VARCHAR(30),
ENDER VARCHAR(40),
BAIRRO VARCHAR(20),
CIDADE VARCHAR(20),
IDADE INT,
CONSTRAINT PK_CLI2 PRIMARY KEY (CODCLI));
CREATE TABLE PRODUTOS2
(CODPROD INT,
DESCRICAO VARCHAR(20),
PRECO NUMERIC(9,2),
CONSTRAINT PK_PROD PRIMARY KEY (CODPROD));
CREATE TABLE PEDIDOS2
(CODPED INT,
CODCLI INT,
DATA DATETIME,
CONSTRAINT PK_PED PRIMARY KEY (CODPED));
CREATE TABLE ITENSPEDIDOS
(CODPED INT,
CODPROD INT,
QTDE INT, 
CONSTRAINT PK_ITENS PRIMARY KEY (CODPED,CODPROD),
CONSTRAINT FK_ITENS FOREIGN KEY (CODPED)
 REFERENCES PEDIDOS2 (CODPED),
CONSTRAINT FK_ITENS FOREIGN KEY (CODPROD)
 REFERENCES PRODUTOS2 (CODPROD));
 
 --LISTAR AS TABELAS
 SELECT * FROM CLIENTES2;
 SELECT * FROM PRODUTOS2;
 SELECT * FROM PEDIDOS2; 
 SELECT * FROM ITENSPEDIDOS; 
--B)Inserir 5 registros
INSERT INTO CLIENTES2 VALUES
(1,'MARCOS','NEVADA,280','VILA AMERICA', 'RIBEIRAO',12),
(2,'MARIA','ALASCA,95','CENTREVILLE', 'SCS',61),
(3,'ALE','EUA,170','SAO JORGE', 'SBC',19),
(4,'MICHELLE','ARAPONGA,52','ZAIRA', 'MAUA',32),
(5,'FLAVIO','PIRELLI,1463','VILA HOMERO THON', 'SANTO ANDRE',25);
INSERT INTO PRODUTOS2 VALUES
(100,'COXINHA',3.99),
(200,'PASTEL',4.49),
(300,'KIBE',5.49),
(400,'REFRIGERANTE',6.00),
(500,'SUCOS',8.00);
INSERT INTO PEDIDOS2 VALUES
(12345,1,20180322),
(56842,3,20180707),
(38929,2,20121212),
(41002,4,21001118),
(69512,5,20010831);
INSERT INTO ITENSPEDIDOS VALUES
(69512,400,2),
(41002,300,6),
(12345,100,8),
(38929,200,20),
(56842,500,12);
--c)	Selecionar código do pedido, nome do produto e qtde.
SELECT CODPED FROM PEDIDOS2;
SELECT DESCRICAO FROM PRODUTOS2;
SELECT QTDE FROM ITENSPEDIDOS; 
--d) Selecionar todos os produtos com preço entre 10 e 70.
SELECT *FROM PRODUTOS2 WHERE PRECO BETWEEN 10 AND 70;
--e)Qual a média de idade dos clientes
SELECT AVG (IDADE) AS 'MEDIA IDADE' FROM CLIENTES2;
--f)	Qual a média de preços dos produtos
SELECT AVG (PRECO) AS 'PREÇOS' FROM PRODUTOS2;
--g)	Quantos pedidos temos no cadastro?
SELECT COUNT (*) AS 'TOTAL DE PEDIDOS' FROM PEDIDOS2;
--j)	Selecionar todos os clientes que moram em Santo André, SBC, SCS ou Mauá.
SELECT * FROM CLIENTES2 WHERE CIDADE IN ('SANTO ANDRE','MAUA','SBC', 'SCS');
--------------CONFERIR TABELAS DE PEDIDOS E ITENS DE PEDIDOS....
Exercício 04 – Aula 06
Inserir 5 registros.
Selecionar todos os produtos com preço acima da média.
Selecionar todos os produtos com preço igual ao produto mais caro.
Selecionar todos os registros da Agenda que contenham mais de 2 serviços agendados;
Selecionar todos os serviços com preço inferior à média de preços.
Quantos funcionários trabalham no salão?
Quantos produtos temos cadastrados?
Quantos registros temos agendados para a data mais recente?
Mostrar Data Agendada, Serviço, cargo do funcionário e horário.
CREATE TABLE CLIENT
(CODCLI    INT,
NOME    VARCHAR(30),
ENDEREÇO    VARCHAR (40),
FONE    CHAR (09),
CONSTRAINT PK_CLI2 PRIMARY KEY (CODCLI));
--LISTAR ESTRUTURA DA TABELA
SELECT * FROM CLIENT;
--b)INSERIR CINCO REGISTROS
INSERT INTO CLIENT VALUES
(1, 'OBAMA', 'AV.BARÃO, 765', 43211000),
(2, 'MICHELLE', 'AV.BRASIL,425', 45774511),
(3, 'WALDIR', 'AV. RIO BRANCO, 12', 34211000),
(4, 'TAINA', 'RUA ALASCA, 170',29884808),
(5, 'EMERSON', 'RUA GIOVANNI, 14631',25122018);
CREATE TABLE PRODUTO
(CODPROD  INT,
DESCRIÇÃO    VARCHAR (20),
PREÇO NUMERIC(9,2),
QTDE_EST    INT,
CONSTRAINT PK_PROD PRIMARY KEY (CODPROD));
--LISTAR ESTRUTURA DA TABELA
SELECT * FROM PRODUTO;
--b)INSERIR CINCO REGISTROS
INSERT INTO PRODUTO VALUES
(100,'AMACIANTE', 5.00, 2),
(200, 'SABÃO LÍQUIDO', 7.75,30),
(300, 'PRETINHO PARA PNEUS', 25.00, 10),
(400, 'DETERGENTE', 1.15, 30),
(500, 'VANISH', 25.00,100);
--C)SELECIONAR TODOS OS PRODUTOS ACIMA DA MÉDIA
SELECT * FROM PRODUTO
WHERE PREÇO >(SELECT AVG (PREÇO) FROM PRODUTO);
--d) SELECIONAR TODOS OS PRODUTOS COM PREÇO IGUAL AO PRODUTO MAIS CARO
 SELECT * FROM PRODUTO
WHERE PREÇO =(SELECT MAX (PREÇO) FROM PRODUTO);
CREATE TABLE SERVIÇO
(CODSERV  INT,
DESCRIÇÃO    VARCHAR (20),
PREÇO NUMERIC(7,2),
CONSTRAINT PK_SERV PRIMARY KEY (CODSERV));
INSERT INTO SERVIÇO VALUES
(110,'LIMPEZA', 80.00),
(220,'SECAGEM', 30.00),
(330,'POLIMENTO', 200.00),
(440, 'CRISTALIZAÇÃO', 300.00),
(550, 'ALINHAMENTO', 1500.00);
SELECT * FROM SERVIÇO;
--f) SELECIONAR TODOS OS SERVIÇOS COM PREÇO INFERIOR A MÉDIA DOS PREÇOS
SELECT * FROM SERVIÇO 
WHERE PREÇO <(SELECT AVG (PREÇO) FROM SERVIÇO);
CREATE TABLE FUNCIONÁRIOS
(CODFUNC    INT,
CARGO    CHAR (20),
FONE    CHAR(9),
CONSTRAINT PK_FUNC PRIMARY KEY (CODFUNC));
INSERT INTO FUNCIONÁRIOS VALUES
(1201, 'PRESIDENTE', 32111000),
(2102, 'DIRETOR', 35774511),
(3001, 'SUPERVISOR', 934211000),
(4004, 'GERENTE', 920884808),
(5005, 'OPERADOR',915122018);
SELECT * FROM FUNCIONÁRIOS;
--g)QUANTOS FUNCIONÁRIOS TRABALHAM NO SALÃO
SELECT COUNT (*) AS 'QTDE FUNCIONÁRIOS' FROM FUNCIONÁRIOS
--H) QUANTOS PRODUTOS TEMOS NO CADASTRO
SELECT COUNT (*) AS 'PRODUTO' FROM PRODUTO
CREATE TABLE AGENDA1
(CODAGD    INT,
DATA    DATETIME,
CLIENTE    INT,
CONSTRAINT PK_AGD PRIMARY KEY (CODAGD),
CONSTRAINT FK_CLI FOREIGN KEY (CLIENTE)
 REFERENCES CLIENT (CODCLI));
 
 
INSERT INTO AGENDA1 VALUES
(707, '20180330', 1),
(808, '20190330', 2),
(909, '20341225', 3),
(1010,'20560101', 4),
(2020, '20350216',5);
SELECT * FROM AGENDA1;
--I) QUANTOS REGISTROS TEMOS AGENDADOS PARA A DATA MAIS RECENTE
SELECT * FROM AGENDA1 
WHERE DATA = (SELECT MIN (DATA) FROM AGENDA1);
DROP TABLE AGENDA1 
Exercício 05 – Aula 06
Criar as tabelas x relacionamentos
Inserir 5 registros em cada tabela;
Selecionar todos os usuários;
Selecionar todos os filmes locados pelo usuário 2;
Qual é o tempo de maior duração dos filmes?
Qual é o tempo de menor duração dos filmes?
Qual a média de duração dos filmes?
Quantos filmes temos com duração acima de 95 min?
Quantos filmes temos com duração acima da média?
Quantos gêneros temos no cadastro?
Listar todos os filmes que iniciam pela letra “A”.
Listar o código do gênero (Filmes_gen) sem duplicidade.
Selecionar todos os filmes com duração entre 95 e 120 min.
Quantos filmes temos do gênero 2?
CREATE TABLE USUARIO
 (CODUSE INT,
 NOME VARCHAR(40),
 EMAIL VARCHAR(35),
 DATANASC DATETIME,
 CONSTRAINT PK_CODUSE PRIMARY KEY (CODUSE));
 
 SELECT *FROM USUARIO
 
 INSERT INTO USUARIO VALUES
 (001,'MICHELLE','MICHELLE@FATEC.COM','20151212'),
 (002,'MARCOS','MARCOS@FATEC.COM','20001018'),
 (003,'MARCELO','MARCELO@FATEC.COM','20181105'),
 (004,'THAINA','THAINA@FATEC.COM','19721210'),
 (005,'ALE','ALE@FATEC.COM','19601231');
 
 --DELETE FROM USUARIO;
 --DROP TABLE USUARIO;
 
 
 
CREATE TABLE FILMES
 (CODFILME INT,
 NOME VARCHAR(40),
 DURAÇÃO INT,
 CONSTRAINT PK_CODFILME PRIMARY KEY (CODFILME));
 
 SELECT *FROM FILMES
 
 INSERT INTO FILMES VALUES
 (5001,'GHOST',140),
 (5002,'TODO_MUNDO_EM_PANICO',160),
 (5003,'AMATTEROFFAITH',240),
 (5004,'SENHOR_DOS_ANÉIS',210),
 (5005,'MADAGASCAR',190);
 
 
 CREATE TABLE GENERO
 (CODGEN INT,
 DESCRIÇÃO VARCHAR(20),
 CONSTRAINT PK_CODGENE PRIMARY KEY (CODGEN));
 
 SELECT *FROM GENERO
 
 INSERT INTO GENERO VALUES
 (31,'ANIMAÇÃO'),
 (32,'ROMANCE'),
 (33,'COMEDIA'),
 (34,'FANTASIA'),
 (35,'ESPIRITUALIDADE');
 
 
CREATE TABLE USUARIOFILME
 (USUARIO INT,
 CODFILME INT,
 CONSTRAINT FK_USU1 FOREIGN KEY (USUARIO)
 REFERENCES USUARIO (CODUSE),
 CONSTRAINT FK_FILM1 FOREIGN KEY (CODFILME)
 REFERENCES FILMES (CODFILME));
 
 SELECT * FROM USUARIOFILME
 
 
 
 CREATE TABLE FILMESGENERO
 (CODFILME INT,
 CODGEN INT,
 CONSTRAINT FK_FILM2 FOREIGN KEY (CODFILME)
 REFERENCES FILMES (CODFILME),
 CONSTRAINT FK_CODGENE2 FOREIGN KEY (CODGEN)
 REFERENCES GENERO (CODGEN));
 
 SELECT * FROM FILMESGENERO
 
 
 --C)SELECIONAR TODOS OS USUARIOS
 SELECT * FROM USUARIO; 
 
 --E)QUAL É O TEMPO DE MAIOR DURAÇÃO DOS FILMES
 SELECT MAX (DURAÇÃO) AS 'MAIOR DURAÇÃO' FROM FILMES;
 
 --F) QUAL É O TEMPO DE MENOR DURAÇÃO DOS FILMES
 SELECT MIN (DURAÇÃO) AS 'MENOR DURAÇÃO' FROM FILMES;
 
 
 
 --G)QUAL É A MÉDIA SW DURAÇÃO DOS FILMES
 SELECT AVG (DURAÇÃO) AS 'MEDIA DE DURAÇÃO' FROM FILMES;
 
 
 
 
 ------------------------------------------------------------- incompleto!!!!!!
 --H)QUANTOS FILMES TEMOS COM DURAÇÃO ACIMA DE 95 MINUTOS
 SELECT COUNT (*) AS 'QUANTIDADE FILMES ACIMA DE 95 MINUTOS' FROM FILMES;
 SELECT * FROM FILMES WHERE DURAÇÃO>95;
 
 --I) QUANTOS FILMES TEMOS COM DURAÇÃO ACIMA DA MEDIA
 SELECT * FROM FILMES WHERE DURAÇÃO> (SELECT AVG (DURAÇÃO) FROM FILMES));
 
 
Exercicio 06 – Aula 06 
 
Inserir 5 registros em cada tabela.
Selecionar todos os clientes com idade acima da média.
Selecionar todos os clientes com idade entre 20 e 50 anos.
Selecionar todos os clientes que moram em Santo André, Mauá ou SBC.
Quantos pedidos existem para o cliente 2?
Qual o número do Pedido mais recente?
Quantos Clientes residem em Mauá?
Quantos clientes estão cadastrados?
Qual a maior idade dos clientes?
Listar todas as cidades que temos clientes, sem duplicidade.
Atualizar o valor total dos pedidos com 10% de desconto, para todos da transportadora 01;
Selecionar todos os clientes com nome iniciado pela letra “E”;
Excluir todos os pedidos da transportadora 02.
-- a) CRIAR TABELAS E RELACIONAMENTOS:
 
-- CRIAR TABELA CLIENTES
CREATE TABLE CLIENTES (
CODCLI INT,
NOME VARCHAR(30),
ENDERECO VARCHAR(40),
CIDADE VARCHAR(30),
FONE CHAR(9),
IDADE INT,
CONSTRAINT PK_CLIENTES PRIMARY KEY(CODCLI));
 
-- CRIAR TABELA TRANSPORTADORA
CREATE TABLE TRANSPORTADORA (
CODIGO INT,
NOME VARCHAR(20),
CONSTRAINT PK_TRANSPORTADORA PRIMARY KEY(CODIGO));
 
-- CRIAR TABELA PEDIDOS
CREATE TABLE PEDIDOS (
NUMPED INT,
CLIENTE INT,
DATA_ENTREGA DATETIME,
TRANSPORTADORA INT,
VALOR_TOTAL NUMERIC(10,2),
CONSTRAINT PK_PEDIDOS PRIMARY KEY(NUMPED),
CONSTRAINT FK_CLIENTES FOREIGN KEY(CLIENTE)
REFERENCES CLIENTES(CODCLI),
CONSTRAINT FK_TRANSPORTADORA FOREIGN KEY(TRANSPORTADORA)
REFERENCES TRANSPORTADORA(CODIGO));
 
-- b) Inserir 5 registros em cada tabela.
INSERT INTO CLIENTES VALUES
(1,'Lucas Mendes','Rua Dr. João Carlos de Azevedo, 877','Mauá','987654321',24),
(2,'Leonardo Zaninete','Rua Vice Presidente Francisco, 429','Mauá','987654321',20),
(3,'Paulo','Rua Manoel Godoy, 30','SBC','987654321',32),
(4,'Hugo','Rua Joaquim Queiroz, 229','Santo André','987654321',21),
(5,'Jhon','Rua Castelo Branco, 1440','Ribeirão Pires','987654321',30);
 
INSERT INTO TRANSPORTADORA VALUES
(1,'Mega Transporte'),
(2,'ABC Transportes'),
(3,'Alpha Transportes'),
(4,'Ômega Transportes'),
(5,'TransAuto');
 
INSERT INTO PEDIDOS VALUES
(1,1,'20180406',1,'100.00'),
(2,2,'20180407',3,'200.00'),
(3,1,'20180407',2,'250.00'),
(4,3,'20180407',4,'150.00'),
(5,5,'20180407',5,'300.00');
 
--c) Selecionar todos os clientes com idade acima da média.
SELECT * FROM CLIENTES WHERE IDADE > (SELECT AVG(IDADE) FROM CLIENTES);
 
--d) Selecionar todos os clientes com idade entre 20 e 50 anos.
SELECT * FROM CLIENTES WHERE IDADE BETWEEN 20 AND 50;
 
--e) Selecionar todos os clientes que moram em Santo André, Mauá ou SBC.
SELECT * FROM CLIENTES WHERE CIDADE IN ('Santo André','Mauá','SBC');
 
--f) Quantos pedidos existem para o cliente 2?
Select COUNT(*) AS QTD_PEDIDOS from PEDIDOS where CLIENTE = 2;
 
--g) Qual o número do Pedido mais recente?
SELECT * FROM PEDIDOS WHERE DATA_ENTREGA = (SELECT MIN(DATA_ENTREGA) FROM PEDIDOS);
 
--h) Quantos Clientes residem em Mauá?
SELECT COUNT(*) AS 'RESIDENTES DE MAUÁ' FROM CLIENTES WHERE CIDADE='MAUÁ';
 
--i) Quantos clientes estão cadastrados?
SELECT COUNT(*) AS QTD_CADASTROS FROM CLIENTES;
 
--j) Qual a maior idade dos clientes?
SELECT MAX(IDADE) AS MAIOR_IDADE FROM CLIENTES;
 
--k) Listar todas as cidades que temos clientes, sem duplicidade.
SELECT DISTINCT(CIDADE) FROM CLIENTES;
 
--l) Atualizar o valor total dos pedidos com 10% de desconto, para todos da transportadora
01;
UPDATE PEDIDOS SET VALOR_TOTAL = VALOR_TOTAL - (VALOR_TOTAL* 0.1) WHERE TRANSPORTADORA = 1;
 
--m) Selecionar todos os clientes com nome iniciado pela letra “E”;
SELECT * FROM CLIENTES WHERE NOME LIKE 'E%';
 
--n) Excluir todos os pedidos da transportadora 02.
DELETE FROM PEDIDOS WHERE TRANSPORTADORA=2;