Banco De Dados
Ensaios: Banco De Dados. Pesquise 860.000+ trabalhos acadêmicosPor: yurivsouza • 25/11/2014 • 3.626 Palavras (15 Páginas) • 224 Visualizações
Introdução
A empresa de desenvolvimento software chamada Microsys XYZ S.A está projetando um novo produto a ser lançado. Este novo software SIG – Sistema Integrado de Gerenciamento 1.0 irá atender muitas empresas que necessitam de um sistema que controle vendas, controle estoque, funcionários e outras atividades. As empresas possuem um grande número de informações, sendo assim todas as consultas do banco de dados foram desenvolvidas para obter o melhor desempenho possível. O banco de dados escolhido pela equipe de desenvolvimento foi PostgreSQL.
ETAPA 1
Nesta etapa foram desenvolvidas todas as consultas que serão utilizadas no sistema SIG sendo elas: Consultas de Cliente, Consultas Funcionários, Consultas vendas por Funcionários, Vendas por Produtos e Vendas por Cliente.
1.1 Consultas e Filtros
• Consulta Clientes: Exibir os campos: nome, cidade, UF, idade, gênero. Os registros devem estar ordenados por nome. Filtros da consulta: cidade.
SELECT
nome, cidade, uf, extract(year from age(CURRENT_DATE, datanascimento)) as idade, genero
FROM cliente
WHERE cidade = 'São Paulo'
ORDER BY nome
• Consulta Funcionários: Exibir os campos: nome, cargo, idade, data de admissão, valor da comissão. Os registros devem estar ordenados por cargo. Filtros da consulta: ativo.
SELECT
nome, cargo, extract(year from age(CURRENT_DATE, datanascimento)) as idade, dataadmissao, comissao||'%' as valor_comissao
FROM funcionario
WHERE ativo = true
ORDER BY cargo
• Consulta Vendas por Funcionário: Exibir os campos: nome do funcionário, valor da venda. Os registros devem estar ordenados por valor da venda, sendo os maiores valores exibidos primeiro. Filtros da consulta: dia da venda.
SELECT
f.nome,v.valortotal
FROM venda as v
LEFT JOIN funcionario as f ON v.funcionario_venda = f.idfuncionario
WHERE to_char(v.data, 'DD') = '17'
ORDER BY valorTotal DESC
• Consulta Vendas por Produtos: Exibir os campos: descrição do produto, preço de venda do produto, valor total de vendas para produto. Os registros devem estar ordenados pelo valor total das vendas, sendo os valores maiores nos primeiros registros. Filtros da consulta: mês em que foram realizadas as vendas.
SELECT
*
FROM(SELECT
p.descricao, p.precovenda, sum(p.precovenda) as valorTotalVenda
FROM produto as p
LEFT JOIN itemvenda as iv ON iv.produto_itemvenda = p.idproduto
LEFT JOIN venda as v ON v.idvenda = iv.venda_itemvenda
WHERE to_char(v.data,'MM') = '09'
GROUP BY p.descricao, p.precovenda) as t
ORDER BY valorTotalVenda DESC
• Consulta Vendas por Cliente: Exibir o nome do cliente, cidade, quantidade total de vendas, valor total de vendas. A consulta deve exibir todos os clientes, inclusive aqueles que não possuem registros de venda. Os registros devem estar ordenados por nome e cidade. Filtros da consulta: mês em que foram realizadas as vendas.
SELECT
c.nome, c.cidade, count(v.idvenda) as qtdTotalVendas, v.valortotal
FROM cliente as c
LEFT JOIN venda as v ON v.cliente_venda = c.idcliente
LEFT JOIN itemvenda as iv ON iv.venda_itemvenda = v.idvenda
WHERE to_char(v.data,'MM') = '09' OR v.valortotal IS NULL
GROUP BY c.nome, c.cidade, v.idvenda, v.valortotal
ORDER BY c.cidade
1.2 DML
Elaboração dos comandos DML das tabelas; cliente, funcionário, produto, Venda e Item Venda.
AÇÃO DML
Tabela Cliente
INSERIR TUPLA INSERT INTO cliente (nome, cpf, rg, endereco, cidade, uf, fone, celular, email, datanascimento, genero) VALUES ('Algusto Ferraz', '111.007.068-00','951270341', 'Rua Santa Cruz', 'Limeira', 'SP', '18366-8419','994065588','algusto.ferraz@hotmail.com','19-03-1980','M');
DELETAR TUPLA DELETE FROM cliente WHERE cpf ='111.007.068-00';
ALTERAR TUPLA UPDATE cliente SET nome = 'Vinicius Santos' WHERE cpf ='111.007.068-00';
Tabela Funcionário
INSERIR TUPLA INSERT INTO funcionario (nome, cpf ,rg, ctps, cargo, datanascimento, dataadmissao, dataemissao, ativo,comissao) VALUES ('Anderson Oliveira','3322445566','418757788','4455667788', 'Vendedor de Loja', '25-10-1980', '08-09-2013', '08-09-2013', true, '4.00');
APAGAR TUPLA DELETE FROM funcionario WHERE cpf = '3322445566';
ALTERAR TUPLA UPDATE funcionario SET cargo = 'Vendedor de Atacado',comissao = '10.00' WHERE cpf ='3322445566';
Tabela Produto
INSERIR TUPLA INSERT INTO produto (descricao, precovenda, precocompra, marca, tipo) VALUES ('HD SSD SATA3 2.5" 500GB SAMSUNG ,840 EVO MZ-7TE50W ', '700.00',
...