BD II – SQL – Exercícios

Baseado no diagrama e nos dados pede-se:

1. Mostre o número de fornecedores cadastrados;

 SELECT COUNT(*) FROM FORNECEDOR;

2. Mostrar a soma, média, valor máximo e valor mínimo dos produtos cadastrados;

SELECT SUM(PROVLRUNITARIO),
AVG(PROVLRUNITARIO),
MAX(PROVLRUNITARIO),
MIN(PROVLRUNITARIO) FROM PRODUTO;

3. Mostrar todos os clientes que não tem telefone cadastrado;

SELECT * FROM CLIENTE
WHERE CLITELEFONE IS NULL;

4. Mostrar a quantidade de produtos classificados e ordenados por fornecedor;

SELECT FORNOME, COUNT(*) FROM FORNECEDOR
JOIN PRODUTO ON PRODUTO.IDFORNECEDOR = FORNECEDOR.ID
GROUP BY FORNOME

5. Mostrar a soma dos valores dos produtos classificados por fornecedor;

SELECT FORNOME, SUM(PROVLRUNITARIO) FROM FORNECEDOR
JOIN PRODUTO ON PRODUTO.IDFORNECEDOR = FORNECEDOR.ID
GROUP BY FORNOME

6. O número de  notas fiscais emitidas por cliente ordenados pelo nome do cliente;

 SELECT CLINOME,COUNT(*) FROM NOTAFISCAL
JOIN CLIENTE ON CLIENTE.ID = NOTAFISCAL.IDCLIENTE
GROUP BY CLINOME

7. O valor total vendido classificados por nota fiscal;

SELECT NOTAFISCAL.ID,SUM(QUANTIDADE*VALORUNITARIO) FROM NOTADETALHE
JOIN NOTAFISCAL ON NOTAFISCAL.ID = NOTADETALHE.IDNOTAFISCAL
GROUP BY NOTAFISCAL.ID
ORDER BY NOTAFISCAL.ID

8. O valor total vendido classificado por cliente ordenado pelo nome do cliente;

SELECT CLINOME,SUM(QUANTIDADE*VALORUNITARIO) FROM NOTADETALHE
JOIN NOTAFISCAL ON NOTAFISCAL.ID = NOTADETALHE.IDNOTAFISCAL
JOIN CLIENTE ON CLIENTE.ID = NOTAFISCAL.IDCLIENTE
GROUP BY CLINOME
ORDER BY CLINOME

9. O valor total vendido classificado por cliente ordenado pelo nome do cliente mas somente as vendas maiores que R$20,00;

 SELECT CLINOME, SUM(VALORUNITARIO * QUANTIDADE )
FROM NOTADETALHE
JOIN NOTAFISCAL ON NOTAFISCAL.ID = NOTADETALHE.IDNOTAFISCAL
JOIN CLIENTE ON CLIENTE.ID = NOTAFISCAL.IDCLIENTE
GROUP BY CLINOME
HAVING SUM(VALORUNITARIO * QUANTIDADE) > 20
ORDER BY CLINOME

10. O valor total vendido classificado por cliente ordenado pelo nome do cliente mas somente as vendas maiores que R$20,00 e somente vendas entre os anos de 2011 e 2012.

SELECT CLINOME, SUM(VALORUNITARIO * QUANTIDADE )
FROM NOTADETALHE
JOIN NOTAFISCAL ON NOTAFISCAL.ID = NOTADETALHE.IDNOTAFISCAL
JOIN CLIENTE ON CLIENTE.ID = NOTAFISCAL.IDCLIENTE
WHERE NOTAFISCAL.NOTAEMISSAO BETWEEN ’01/01/2011′ AND ’31/12/2012′
GROUP BY CLINOME
HAVING SUM(VALORUNITARIO * QUANTIDADE) > 20
ORDER BY CLINOME


CREATE TABLE FORNECEDOR (
 ID INTEGER PRIMARY KEY,
 FORNOME VARCHAR(100) NOT NULL
);

CREATE TABLE PRODUTO (
 ID INTEGER PRIMARY KEY,
 PRONOME VARCHAR(100) NOT NULL,
 PROVLRUNITARIO NUMERIC(10,2) NOT NULL,
 IDFORNECEDOR INTEGER NOT NULL,
 FOREIGN KEY(IDFORNECEDOR) REFERENCES FORNECEDOR (ID)
);

CREATE TABLE CLIENTE (
 ID INTEGER PRIMARY KEY,
 CLINOME VARCHAR(100) NOT NULL,
 CLIENDERECO VARCHAR(40),
 CLITELEFONE VARCHAR(14)
);

CREATE TABLE NOTAFISCAL (
 ID INTEGER PRIMARY KEY,
 NOTAEMISSAO DATE NOT NULL,
 IDCLIENTE INTEGER NOT NULL,
 FOREIGN KEY(IDCLIENTE) REFERENCES CLIENTE (ID)
);

CREATE TABLE NOTADETALHE (
 IDNOTAFISCAL INTEGER NOT NULL,
 SEQUENCIA INTEGER NOT NULL,
 QUANTIDADE NUMERIC(8,2) NOT NULL,
 VALORUNITARIO NUMERIC(10,2) NOT NULL,
 IDPRODUTO INTEGER NOT NULL,
 PRIMARY KEY(IDNOTAFISCAL,SEQUENCIA),
 FOREIGN KEY(IDNOTAFISCAL) REFERENCES NOTAFISCAL (ID),
 FOREIGN KEY(IDPRODUTO) REFERENCES PRODUTO (ID)
);

INSERT INTO FORNECEDOR (ID,FORNOME) VALUES (20,'FORNECEDOR FOR#1');
INSERT INTO FORNECEDOR (ID,FORNOME) VALUES (30,'FORNECEDOR FOR#2');
INSERT INTO FORNECEDOR (ID,FORNOME) VALUES (40,'FORNECEDOR FOR#3');
INSERT INTO FORNECEDOR (ID,FORNOME) VALUES (50,'FORNECEDOR FOR#4');
INSERT INTO FORNECEDOR (ID,FORNOME) VALUES (60,'FORNECEDOR FOR#5');

INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (100,'PRODUTO PRO#1',20,10.55);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (101,'PRODUTO PRO#2',20,2.30);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (102,'PRODUTO PRO#3',30,15.10);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (103,'PRODUTO PRO#4',20,98.00);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (104,'PRODUTO PRO#5',40,100.00);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (106,'PRODUTO PRO#6',20,101.50);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (107,'PRODUTO PRO#7',60,25.66);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (108,'PRODUTO PRO#8',50,30.31);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (109,'PRODUTO PRO#90',40,60.66);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (110,'PRODUTO PRO#80',20,70.13);
INSERT INTO PRODUTO(ID,PRONOME,IDFORNECEDOR,PROVLRUNITARIO) VALUES (111,'PRODUTO PRO#108',30,82.30);

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (108,'CLIENTE CLI#1',NULL,NULL);

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (109,'CLIENTE CLI#2','RUA SAMARA 118','3248-6090');

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (110,'CLIENTE CLI#3',NULL,'3242-2525');

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (111,'CLIENTE CLI#4',NULL,NULL);

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (112,'CLIENTE CLI#5','RUA BITUVAS 110',NULL);

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (113,'CLIENTE CLI#6','RUA DOS MORCEGOS','9141-2626');

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (114,'CLIENTE CLI#7','AV. 7 SETEMBRO 77','3247-7777');

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (115,'CLIENTE CLI#8','AV. 4 JULHO 1973',NULL);

INSERT INTO CLIENTE(ID,CLINOME,CLIENDERECO,CLITELEFONE)
     VALUES (116,'CLIENTE CLI#9',NULL,NULL);

INSERT INTO NOTAFISCAL(ID,NOTAEMISSAO,IDCLIENTE)
     VALUES (300,'12/12/2003',108);
INSERT INTO NOTAFISCAL(ID,NOTAEMISSAO,IDCLIENTE)
     VALUES (400,'15/02/2002',112);
INSERT INTO NOTAFISCAL(ID,NOTAEMISSAO,IDCLIENTE)
     VALUES (500,'05/02/2011',115);
INSERT INTO NOTAFISCAL(ID,NOTAEMISSAO,IDCLIENTE)
     VALUES (600,'03/06/2011',112);
INSERT INTO NOTAFISCAL(ID,NOTAEMISSAO,IDCLIENTE)
     VALUES (701,'04/09/2012',114);

INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (300,1,2.0,20.53,111);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (300,2,3.0,19.90,110);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (300,3,1.0,19.90,101);

INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (400,1,2.0,20.53,109);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (400,2,3.0,19.90,103);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (400,3,1.0,19.90,104);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO)
       VALUES (400,5,1.0,35.00,106);

INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (500,1,1.0,10.53,102);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (500,2,5.0,19.90,109);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (500,3,6.0,19.90,110);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (500,4,2.5,21.35,102);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (500,5,8.0,35.00,106);

INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,1,2.0,10.55,100);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,2,3.0,17.90,101);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,3,7.0,15.50,110);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,4,3.5,21.35,103);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,5,1.0,36.00,107);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (600,6,1.0,36.00,107);

INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (701,1,2.0,10.55,100);
INSERT INTO NOTADETALHE(IDNOTAFISCAL,SEQUENCIA,QUANTIDADE,VALORUNITARIO,IDPRODUTO) VALUES
       (701,2,3.0,17.90,101);

 

Esse post foi publicado em BD II - Banco de Dados II. Bookmark o link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s