SQL - Exercícios Flashcards
Crie esta tabela na base de dados, levando em consideração as seguintes características:
- O nome da tabela deve ser TB_VENDEDORES
- O campo que determina o identificador do vendedor chama-se MATRICULA e deve ser um texto de 5 posições
- O campo do nome do vendedor chama-se NOME, e deve ser um texto de 100 posições
- Temos um campo chamado DATA_ADMISSAO, que representa a data em que o vendedor começou a trabalhar na empresa
- O campo co percentual de comissão deve se chamar PERCENTUAL_COMISSAO, e representa o percentual de comissão que o vendedor ganha sobre cada venda.
CREATE TABLE TB_VENDEDORES
(
MATRICULA VARCHAR2(5),
NOME VARCHAR2(100),
DATA_ADMISSAO DATE,
PERCENTUAL_COMISSAO NUMBER
)
Se quiséssemos excluir a tabela de vendedores (TB_VENDEDORES) qual seria o comando correto?
Drop table TB_VENDEDORES
Durante a sua rotina de trabalho, Fátima recebeu uma demanda de cadastrar diretamente na tabela TB_VENDEDORES no banco de dados.
Matrícula - 00233
Nome: Joao Geraldo da Fonseca
Data da admissão: 01/12/2015
Comissão: 10%
INSERT INTO TB2_VENDEDORES (MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO)
VALUES(‘00233’,’JOAO GERALDO DA FONSECA’,’01/12/2015’,10);
Veja a tabela abaixo:
MATRICULA NOME DATA_ADMISSAO PERCENTUAL_COMISSAO
00400 Maria do Rosario 23/07/2012 0.15
00810 Marcia Almeida 14/12/2016 0.18
00414 Carlos Moreira 13/11/2015 0.14
00934 Juvenildo Martins 09/03/2010 0.20
André, integrante da equipe de Fátima também recebeu uma demanda para cadastrar 4 pessoas vendedoras utilizando SQL. Como seria esse código de cadastro?
INSERT INTO TB2_VENDEDORES (MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO)
VALUES(‘00400’,’MARIA DO ROSARIO’,’23/07/2012’,0.15);
INSERT INTO TB2_VENDEDORES (MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO)
VALUES(‘00810’,’MARCIA ALMEIDA’,’14/12/2016’,0.18);
INSERT INTO TB2_VENDEDORES (MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO)
VALUES(‘00414’,’CARLOS MOREIRA’,’13/11/2015’,0.14);
INSERT INTO TB2_VENDEDORES (MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO)
VALUES(‘00934’,’JUVENILDO MARTINS’,’09/03/2010’,0.20);
Na empresa no qual estamos montando o banco de dados, viu-se uma injustiça nas comissões das pessoas vendedoras. Vendedores(as) com menos tempo de casa estão ganhando mais comissão que vendedores(as) mais antigos(as). Logo, foi tomada uma decisão: vendedores(as) mais antigos não podem ter comissões menores que vendedores(as) que foram admitidos(as) recentemente. E os(as) que tem comissões altas, não podem perder o direito que possuem. Faça este acerto na base de dados.
UPDATE TB2_VENDEDORES SET PERCENTUAL_COMISSAO = 0.18 WHERE MATRICULA IN (‘00400’,’00414’,’00233’);
Sabemos que não podemos ter duas pessoas vendedoras com a mesma matrícula. Para evitar esse problema, que tal criar uma chave primária para a tabela de vendedores usando este campo como critério?
ALTER TABLE TB2_VENDEDORES ADD CONSTRAINT PK_MATRICULA PRIMARY KEY (MATRICULA);
Nossa empresa é multinacional, e surgiu a necessidade de incluir duas pessoas vendedoras da filial americana, ou seja, do exterior, cujas datas de admissão vieram no padrão americano. Além disso, também será necessário incluir duas pessoas vendedoras da filial brasileira.
Os dados que precisamos inserir na tabela de vendedores são os seguintes:
MATRICULA NOME DATA_ADMISSAO PERCENTUAL_COMISSAO
00265 Jonh Wayne 03/27/2019 0.12
00777 Katy Peterson 02/04/2020 0.10
00342 Rodrigo Almeida 18/01/2022 0.09
00729 Patricia Martins 02/01/2022 0.09
Sabendo que Jonh e Katy são vendedores(as) da filial americana e Rodrigo e Patricia são da filial brasileira, como podemos inseri-los no banco de dados?
INSERT INTO TB_VENDEDORES (
MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO
) VALUES (
‘00265’,’Jonh Wayne’,TO_DATE(‘03/27/2019’,’MM/DD/YYYY’), 0.12
);
INSERT INTO TB_VENDEDORES (
MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO
) VALUES (
‘00777’,’Katy Peterson’,TO_DATE(‘02/04/2020’,’MM/DD/YYYY’), 0.10
);
INSERT INTO TB_VENDEDORES (
MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO
) VALUES (
‘00342’,’Rodrigo Almeida’,’18/01/2022’, 0.09
);
INSERT INTO TB_VENDEDORES (
MATRICULA, NOME, DATA_ADMISSAO, PERCENTUAL_COMISSAO
) VALUES (
‘00729’,’Patricia Martins’,’02/01/2022’, 0.09
);
Surgiu uma demanda para listar vendedores que têm comissões menores que 0,17.
Como poderia ser esse comando SQL?
SELECT * FROM TB2_VENDEDORES WHERE PERCENTUAL_COMISSAO < 0.17
Qual comando SQL podemos executar para mapear as pessoas vendedoras mais novas da empresa, que foram admitidas de 2020 em diante?
SELECT * FROM TB2_VENDEDORES WHERE TO_CHAR(DATA_ADMISSAO,’YYYY’) >=’2020’
Quais os vendedores que possuem comissões maiores que 0,19 ou menores que 0,15?
SELECT * FROM TB2_VENDEDORES WHERE PERCENTUAL_COMISSAO < 0.15 OR PERCENTUAL_COMISSAO > 0.19;
Analise o texto abaixo e transforme-o numa expressão lógica:
Se o(a) aluno(a) tiver 18 anos ou mais ou for formado em Inglês, ele(a) pode ir para a Escola de Estudos Astronômicos. Ou se ele(a) tiver menos de 18 anos e for formado em Alemão, então deve ir para a Escola de Estudos Supersônicos.
O aluno tem 17 anos e é formado em Inglês. Esta expressão terá o resultado verdadeiro ou falso?
(17 >= 18 OR “INGLÊS” = “INGLÊS”) OR (17 < 18 AND “INGLÊS” = “ALEMÃO”)
(Falso OR Verdadeiro) OR (Verdadeiro AND Falso)
(Verdadeiro) OR (Falso)
(Verdadeiro)
Descreve o relacionamento entre a tabela Tabela_de_Vendedores e Tabela_de_Produtos?
FK_Notas_Fiscais1(Matricula),
FK_Itens_Notas_Fiscais2(Numero),
FK_Itens_Notas_Fiscais1(Codigo_do_Produto)
Alternativa correta! A FK_Notas_Fiscais1(Matricula) é a chave estrangeira que liga o campo Matricula, da tabela de vendedores (Tabela_de_Vendedores), com a tabela de cabeçalho de notas fiscais (Notas_Fiscais). Depois, a FK_Itens_Notas_Fiscais2(Numero) é a chave estrangeira que liga a tabela de cabeçalho de notas fiscais com a de itens de notas fiscais (Itens_Notas_Fiscais). Finalmente, a FK_Itens_Notas_Fiscais1(Codigo_do-Produto) liga a tabela de produtos (Tabela_de_Produtos) com a de itens de notas. Assim, a tabela de vendedores e de produtos são relacionadas.
Quero listar somente o nome e a idade das pessoas do cadastro.
SELECT NOME, IDADE FROM CADASTRO
Quero listar somente o nome e a idade das pessoas do cadastro que estejam morando em MANAUS.
SELECT NOME, IDADE FROM CADASTRO WHERE CIDADE = ‘MANAUS’
Quero listar somente o nome e a idade das pessoas do cadastro que tenham mais de 25 anos.
SELECT NOME, IDADE FROM CADASTRO WHERE IDADE > 25
Se o(a) aluno(a) tiver 18 anos ou mais ou for formado(a) em Inglês ele(a) pode ir para a Escola de Estudos Astronômicos. Ou se ele(a) tiver menos de 18 anos e for formado em Alemão então deve ir para a Escola de Estudos Supersônicos.
Qual seria a seleção que deve ser feita para achar os(as) alunos(as) que vão estudar na Escola de Estudos Supersônicos?
SELECT NOME FROM ALUNOS WHERE IDADE < 18 AND FORMAÇÃO = ‘ALEMÃO’
Na nossa tabela de produtos (TABELA_DE_PRODUTOS_FONTE), execute uma consulta para obter o número de sabores existentes. Especifique o SQL que você executou para obter o resultado e a resposta propriamente dita.
SELECT DISTINCT SABOR FROM TABELA_DE_PRODUTOS_FONTE;
Qual é o comando SQL para listar as 10 primeiras vendas do dia 01/10/2017?
(Tabela NOTAS_FISCAIS)
SELECT * FROM NOTAS_FISCAIS WHERE DATA_VENDA = TO_DATE(‘2017-10-01’,’YYYY-MM-DD’) AND ROWNUM <= 10;
Utilizando duas queries SQL, qual foi a maior venda do produto Linha Refrescante - 1 Litro - Morango/Limao, em quantidade?
Utilize as tabelas TABELA_DE_PRODUTOS para achar o código e a tabela IT
SELECT * FROM TABELA_DE_PRODUTOS
WHERE nome_do_produto = ‘Linha Refrescante - 1 Litro - Morango/Limao’
—————————————————————-
SELECT * FROM ITENS_NOTAS_FISCAIS
WHERE codigo_do_produto = ‘1101035’
ORDER BY QUANTIDADE DESC
Cada item da nota fiscal representa uma venda de um determinado produto. Vimos na atividade anterior que temos várias vendas com quantidade igual a 99 litros para o produto 1101035.
Quantas vendas foram feitas com quantidade igual a 99 litros para o produto 1101035?
Utilize a tabela ITENS_NOTAS_FISCAIS
SELECT COUNT(*) FROM ITENS_NOTAS_FISCAIS WHERE codigo_do_produto = ‘1101035’ AND quantidade = 99;
Vamos voltar aos itens das notas fiscais. As duas atividades anteriores olharam as vendas do produto 1101035, mas nossa empresa vendeu mais produtos. Verifique as quantidades totais de vendas de cada produto e ordene da maior para a menor.
Utilize a tabela ITENS_NOTAS_FISCAIS
SELECT CODIGO_DO_PRODUTO, SUM(QUANTIDADE) FROM ITENS_NOTAS_FISCAIS
GROUP BY CODIGO_DO_PRODUTO ORDER BY SUM(QUANTIDADE) DESC;
Vimos os produtos mais vendidos na atividade anterior. Agora, liste somente os produtos que venderam mais que 394000 unidades.
Utilize a tabela ITENS_NOTAS_FISCAIS
SELECT CODIGO_DO_PRODUTO, SUM(QUANTIDADE) FROM ITENS_NOTAS_FISCAIS
GROUP BY CODIGO_DO_PRODUTO HAVING SUM(QUANTIDADE) > 394000
ORDER BY SUM(QUANTIDADE) DESC;
Para cada cliente, temos seus limites de crédito mensais. Liste somente os nomes dos clientes e os classifique por:
Acima ou igual a 150.000 de limite de crédito - Clientes grandes
Entre 150.000 e 110.000 de limite de crédito - Clientes médios
Menores que 110.000 de limite de crédito - Clientes pequenos
SELECT NOME,
(CASE WHEN LIMITE_DE_CREDITO >= 150000 THEN ‘Clientes grandes’
WHEN LIMITE_DE_CREDITO < 150000 AND LIMITE_DE_CREDITO >= 110000 THEN ‘Clientes medios’
ELSE ‘Clientes pequenos’
END)
FROM TABELA_DE_CLIENTES
Na atividade onde pretendíamos obter os produtos que venderam mais que 394000 litros, executamos esta consulta:
SELECT CODIGO_DO_PRODUTO, SUM(QUANTIDADE) FROM ITENS_NOTAS_FISCAIS
GROUP BY CODIGO_DO_PRODUTO HAVING SUM(QUANTIDADE) > 394000
ORDER BY SUM(QUANTIDADE) DESC;Copiar código
Mas quero que na resposta desta consulta apareça não somente o código do produto, mas também o nome do produto. Como fazer?
SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO, SUM(INF.QUANTIDADE) FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO HAVING SUM(INF.QUANTIDADE) > 394000
ORDER BY SUM(INF.QUANTIDADE) DESC;
Qual seria a consulta para listar apenas a cidade que não teve nenhum candidato votado?
SELECT A1.CIDADE FROM CIDADE A1
LEFT JOIN CANDIDATO A2
ON A1.CIDADE = A2.CIDADE
WHERE A2.CIDADE IS NULL
OU
SELECT CI.CIDADE FROM CANDIDATO CA RIGHT JOIN CIDADE CI ON CA.CIDADE = CI.CIDADE WHERE CA.CIDADE IS NULL;
Sabendo que queremos ver todos os registros de TAB2 e todos de TAB1, o que deve estar no lugar de XXXXX?
SELECT * FROM
TAB1 XXXXX TAB2 ON TAB1.COD = TAB2.COD
FULL JOIN
Quando eu quero listar todos os registros, listando os repetidos, qual UNION eu devo usar?
UNION ALL
Liste os sabores dos produtos que são selecionados nesta consulta.
SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO, SUM(INF.QUANTIDADE) FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO HAVING SUM(INF.QUANTIDADE) > 394000
ORDER BY SUM(INF.QUANTIDADE) DESC;
SELECT DISTINCT SABOR FROM TABELA_DE_PRODUTOS A1 WHERE CODIGO_DO_PRODUTO IN (
SELECT INF.CODIGO_DO_PRODUTO FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO HAVING SUM(INF.QUANTIDADE) > 394000 );
Redesenhe esta consulta usando subconsultas.
SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO, SUM(INF.QUANTIDADE) FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO HAVING SUM(INF.QUANTIDADE) > 394000
ORDER BY SUM(INF.QUANTIDADE) DESC;
SELECT SC.CODIGO_DO_PRODUTO, SC.NOME_DO_PRODUTO, SC.QUANTIDADE_TOTAL
FROM
(SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO, SUM(INF.QUANTIDADE) AS QUANTIDADE_TOTAL FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO) SC
WHERE SC.QUANTIDADE_TOTAL > 394000
ORDER BY SC.QUANTIDADE_TOTAL DESC
Redesenhe esta consulta, criando uma visão para a lista de quantidades totais por produto e aplicando a condição e ordenação sobre essa mesma visão.
SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO, SUM(INF.QUANTIDADE) FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO HAVING SUM(INF.QUANTIDADE) > 394000
ORDER BY SUM(INF.QUANTIDADE) DESC;
CREATE VIEW VW_QUANTIDADE_PRODUTOS AS SELECT INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO,
SUM(INF.QUANTIDADE) AS QUANTIDADE_TOTAL FROM ITENS_NOTAS_FISCAIS INF
INNER JOIN TABELA_DE_PRODUTOS TP
ON INF.CODIGO_DO_PRODUTO = TP.CODIGO_DO_PRODUTO
GROUP BY INF.CODIGO_DO_PRODUTO, TP.NOME_DO_PRODUTO;
SELECT * FROM VW_QUANTIDADE_PRODUTOS
WHERE QUANTIDADE_TOTAL > 394000
ORDER BY QUANTIDADE_TOTAL DESC
Como seria a função para retirar somente a palavra RIO deste texto.
CIDADE DO RIO DE JANEIRO
SUBSTR(‘CIDADE DO RIO DE JANEIRO’, 11, 3)
Como eu poderia calcular o número de dias que uma pessoa viveu desde o seu nascimento?
SELECT (SYSDATE - DATA_DE_NASCIMENTO) FROM TABELA_DE_CLIENTES;
Como seria uma consulta que retorne o nome do cliente e sua data de nascimento por extenso?
select NOME, to_char(DATA_DE_NASCIMENTO,’dd “de” MONTH “de” YYYY’) from TABELA_DE_CLIENTES
SELECT (SYSDATE - DATA_DE_NASCIMENTO) FROM TABELA_DE_CLIENTES;
Obtemos o número de dias que uma pessoa viveu desde o seu nascimento. Porém, essa consulta resulta em um número decimal, porque SYSDATE é expresso em horas/minutos/segundos e a parte decimal representa as horas desde a última meia noite.
Qual função devo acrescentar para arredondar o valor para o número de dias realmente vividos pela pessoa?
SELECT FLOOR(SYSDATE - DATA_DE_NASCIMENTO) FROM TABELA_DE_CLIENTES;
Na tabela de notas fiscais, temos o valor do imposto. Já na tabela de itens, temos a quantidade e o faturamento. Calcule o valor do imposto pago no ano de 2016, arredondando para o menor inteiro.
SELECT TO_CHAR(A1.DATA_VENDA,’YYYY’) AS ANO, FLOOR(SUM(A2.FATURAMENTOA1.IMPOSTO)) FROM NOTAS_FISCAIS A1
LEFT JOIN (SELECT NUMERO,SUM(QUANTIDADEPRECO) AS FATURAMENTO FROM ITENS_NOTAS_FISCAIS GROUP BY NUMERO) A2
ON A1.NUMERO = A2.NUMERO
WHERE TO_CHAR(A1.DATA_VENDA,’YYYY’) = ‘2016’
GROUP BY TO_CHAR(A1.DATA_VENDA,’YYYY’);
Queremos construir um SQL cujo resultado seja, para cada cliente:
O cliente NOME DO CLIENTE comprou QUANTIDADE no ano de ANO
Faça isso somente para o ano de 2016.
SELECT ‘O cliente ‘ || TC.NOME || ‘ comprou ‘ ||
TO_CHAR(ROUND(SUM(INF.QUANTIDADE * INF.PRECO),2)) || ‘ no ano de ‘ || TO_CHAR(DATA_VENDA, ‘YYYY’) AS SENTENCA
FROM NOTAS_FISCAIS NF
INNER JOIN ITENS_NOTAS_FISCAIS INF ON NF.NUMERO = INF.NUMERO
INNER JOIN TABELA_DE_CLIENTES TC ON NF.CPF = TC.CPF
WHERE TO_CHAR(DATA_VENDA, ‘YYYY’) = ‘2016’
GROUP BY TC.NOME, TO_CHAR(DATA_VENDA, ‘YYYY’)
Complemente este relatório, listando somente os que tiveram vendas inválidas e calculando a diferença entre o limite de venda máximo e o realizado, em percentuais.
SELECT
TC.CPF, TC.NOME, TC.VOLUME_DE_COMPRA, TV.MES_ANO, TV.QUANTIDADE_TOTAL,
(CASE WHEN TC.VOLUME_DE_COMPRA >= TV.QUANTIDADE_TOTAL THEN ‘VENDAS VÁLIDAS’
ELSE ‘VENDAS INVÁLIDAS’ END) AS RESULTADO
FROM
TABELA_DE_CLIENTES TC
INNER JOIN
(SELECT
NF.CPF,
TO_CHAR(NF.DATA_VENDA,’MM-YYYY’) AS MES_ANO,
SUM(INF.QUANTIDADE) AS QUANTIDADE_TOTAL
FROM
NOTAS_FISCAIS NF
INNER JOIN
ITENS_NOTAS_FISCAIS INF
ON NF.NUMERO = INF.NUMERO
GROUP BY
CPF, TO_CHAR(NF.DATA_VENDA,’MM-YYYY’)) TV
ON TV.CPF = TC.CPF
WHERE TV.MES_ANO = ‘02-2015’;
SELECT
TC.CPF, TC.NOME, TC.VOLUME_DE_COMPRA, TV.MES_ANO, TV.QUANTIDADE_TOTAL,
(CASE WHEN TC.VOLUME_DE_COMPRA >= TV.QUANTIDADE_TOTAL THEN ‘VENDAS VÁLIDAS’
ELSE ‘VENDAS INVÁLIDAS’ END) AS RESULTADO,
ROUND((1 - (TC.VOLUME_DE_COMPRA/TV.QUANTIDADE_TOTAL)) * 100,2)
FROM
TABELA_DE_CLIENTES TC
INNER JOIN
(SELECT
NF.CPF,
TO_CHAR(NF.DATA_VENDA,’MM-YYYY’) AS MES_ANO,
SUM(INF.QUANTIDADE) AS QUANTIDADE_TOTAL
FROM
NOTAS_FISCAIS NF
INNER JOIN
ITENS_NOTAS_FISCAIS INF
ON NF.NUMERO = INF.NUMERO
GROUP BY
CPF, TO_CHAR(NF.DATA_VENDA,’MM-YYYY’)) TV
ON TV.CPF = TC.CPF
WHERE TV.MES_ANO = ‘02-2015’
AND (TC.VOLUME_DE_COMPRA - TV.QUANTIDADE_TOTAL) < 0
Criar um relatório que verifique quais clientes ultrapassaram o volume total de vendas em um determinado mês.
Tabelas: TABELA_DE_CLIENTES , NOTAS_FISCAIS,ITENS_NOTAS_FISCAIS
SELECT
TC.CPF, TC.NOME, TC.VOLUME_DE_COMPRA, TV.MES_ANO, TV.QUANTIDADE_TOTAL,
(CASE WHEN TC.VOLUME_DE_COMPRA >= TV.QUANTIDADE_TOTAL THEN ‘VENDAS VÁLIDAS’
ELSE ‘VENDAS INVÁLIDAS’ END) AS RESULTADO
FROM
TABELA_DE_CLIENTES TC
INNER JOIN
(SELECT
NF.CPF,
TO_CHAR(NF.DATA_VENDA,’MM-YYYY’) AS MES_ANO,
SUM(INF.QUANTIDADE) AS QUANTIDADE_TOTAL
FROM
NOTAS_FISCAIS NF
INNER JOIN
ITENS_NOTAS_FISCAIS INF
ON NF.NUMERO = INF.NUMERO
GROUP BY
CPF, TO_CHAR(NF.DATA_VENDA,’MM-YYYY’)) TV
ON TV.CPF = TC.CPF
WHERE TV.MES_ANO = ‘02-2015’;
Crie uma consulta para saber o ranking de vendas por tamanho
TABELA_DE_PRODUTOS, NOTAS_FISCAIS, ITENS_NOTAS_FISCAIS
SELECT
TAMANHO,
SUM(VL_VENDA) AS VL_VENDA
FROM TABELA_DE_PRODUTOS TAB
LEFT JOIN (SELECT
V1.NUMERO,
V1.DATA_VENDA,
TO_CHAR(V1.DATA_VENDA,’YYYYMM’) AS ANOMES_VENDA,
CPF AS CD_VENDEDOR,
CODIGO_DO_PRODUTO AS CD_PRODUTO,
QUANTIDADE AS VL_VENDA
FROM NOTAS_FISCAIS V1
LEFT JOIN ITENS_NOTAS_FISCAIS V2
ON V1.NUMERO = V2.NUMERO
WHERE EXTRACT(YEAR FROM DATA_VENDA) = ‘2016’) VEN
ON TAB.CODIGO_DO_PRODUTO = VEN.CD_PRODUTO
Crie um consulta de relatório para ver o ranking de tamanho de produtos vendidos e o percentual de participação
TABELA_DE_PRODUTOS, NOTAS_FISCAIS, ITENS_NOTAS_FISCAIS
SELECT
consulta_relatorio.tamanho,
consulta_relatorio.ano,
consulta_relatorio.quantidade_total,
round((consulta_relatorio.quantidade_total / consulta_relatorio.quantidade_geral) * 100, 2) AS percentual_participacao
FROM
(
SELECT
tp.tamanho,
EXTRACT(YEAR FROM nf.data_venda) AS ano,
SUM(inf.quantidade) AS quantidade_total,
(
SELECT
total_ano.quantidade_geral
FROM
(
SELECT
EXTRACT(YEAR FROM nf.data_venda) AS ano,
SUM(inf.quantidade) AS quantidade_geral
FROM
notas_fiscais nf
INNER JOIN itens_notas_fiscais inf ON nf.numero = inf.numero
WHERE
EXTRACT(YEAR FROM nf.data_venda) = 2016
GROUP BY
EXTRACT(YEAR FROM nf.data_venda)
) total_ano
) AS quantidade_geral
FROM
tabela_de_produtos tp
INNER JOIN itens_notas_fiscais inf ON tp.codigo_do_produto = inf.codigo_do_produto
INNER JOIN notas_fiscais nf ON inf.numero = nf.numero
WHERE
EXTRACT(YEAR FROM nf.data_venda) = 2016
GROUP BY
tp.tamanho,
EXTRACT(YEAR FROM nf.data_venda)
ORDER BY
SUM(inf.quantidade) DESC
) consulta_relatorio;
Temos várias empresas, que são divididas em departamentos. Cada departamento possui funcionários associado a ele. Um funcionário não pode estar em mais de um departamento. Por outro lado, um departamento controla vários projetos. Por último, como a empresa oferece plano de saúde, um funcionário pode ter vários dependentes.
Quem seriam as entidades deste modelo?
Empresa, departamentos, funcionários, projetos e dependentes.
Temos várias empresas, que são divididas em departamentos. Cada departamento possui funcionários associado a ele. Um funcionário não pode estar em mais de um departamento. Por outro lado, um departamento controla vários projetos. Por último, como a empresa oferece plano de saúde, um funcionário pode ter vários dependentes.
Quem seriam os relacionamentos deste modelo?
Divididas, Possui, Controla, Pode ter Vários.
Temos várias empresas, que são divididas em departamentos. Cada departamento possui funcionários associado a ele. Um funcionário não pode estar em mais de um departamento. Por outro lado, um departamento controla vários projetos. Por último, como a empresa oferece plano de saúde, um funcionário pode ter vários dependentes.
Construa o diagrama de entidades e relacionamentos usando uma notação muito simples: retângulos para as entidades e losangos para relacionamentos. Não represente a cardinalidade.
Temos várias empresas, que são divididas em departamentos. Cada departamento possui funcionários associado a ele. Um funcionário não pode estar em mais de um departamento. Por outro lado, um departamento controla vários projetos. Por último, como a empresa oferece plano de saúde, um funcionário pode ter vários dependentes.
Nele, já sabemos que:
EMPRESA - Divididas - DEPARTAMENTO
FUNCIONÁRIO - Pode ter vários - DEPENDENTES
DEPARTAMENTO - Possui - FUNCIONÁRIOS
DEPARTAMENTO - Controla - PROJETOS
Ache as cardinalidades dessas 4 relações.
EMPRESA - Divididas - DEPARTAMENTO - 1:N
Uma empresa sem departamentos não existiria, e ela pode ter de 1 a vários departamentos ligados a ela.
FUNCIONÁRIO - Pode ter vários - DEPENDENTES - 0:N
Um funcionário pode não ter dependentes. Mas claro, se tiver dependentes eles podem não se reduzir a apenas 1, por isso é uma relação 0 para N.
DEPARTAMENTO - Possui - FUNCIONÁRIOS - 1:N
Um funcionário pode trabalhar somente em um departamento, e um departamento não tem sentido sem um funcionário.
DEPARTAMENTO - Controla - PROJETOS - 1:N
Esta pode ser a resposta que pode gerar várias dúvidas. Um departamento pode existir sem projeto? Nada no texto fala sobre isso. Logo, 0:N ou 1:N poderiam ser aceitos.
Temos várias empresas, que são divididas em departamentos. Cada departamento possui funcionários associado a ele. Um funcionário não pode estar em mais de um departamento. Por outro lado, um departamento controla vários projetos. Por último, como a empresa oferece plano de saúde, um funcionário pode ter vários dependentes.
Para simplificar, teremos para todas as entidades os atributos CÓDIGO e NOME.
Durante as entrevistas com os usuários, vimos que precisamos de outros dados importantes como:
Funcionário - Data de admissão na empresa.
Dependente - Tipo de dependência (se é esposo/a ou filha/o). Também é preciso saber a idade do dependente.
Projeto - Orçamento do projeto (valor financeiro), data do início do projeto e horas trabalhadas.
Departamento - Cidade onde o departamento tem seu escritório.
Projete as tabelas e os relacionamentos do banco de dados que irá gerenciar o mini-mundo que está sendo modelado.
Nesta representação, sugira também os tipos para cada campo. Levando em consideração que o CÓDIGO e o NOME das entidades serão representados como texto.
A vendedora de matrícula 00400 pediu demissão, logo precisamos excluí-la da base de dados. Como podemos fazer para realizar essa exclusão?
DELETE TB2_VENDEDORES WHERE MATRICULA = ‘00400’;
