Gabarito Lista em SQL

Prévia do material em texto

Considerando o esquema do BD “Fornecedores, Peças e Projetos”, responda as questões do exercício a seguir:
Esquema do BD Fornecedores, Peças e Projetos
FORNECEDORES(cod, nome, status, cidade)
PEÇAS(cod, nome, cor, peso, cidade) OBS: O peso é cadastrado em gramas.
PROJETOS(cod, nome, cidade)
REMESSA_FORNECIDA(codForn, codProj, CodPeça, quantidade)
Exemplo de tuplas das tabelas:
Em FORNECEDORES (F1, Maria, 20, Duque de Caxias)
Em PEÇAS (P1, Porca, Vermelho, 12.0, Rio de Janeiro)
Em PROJETOS (J1, Monitor, Nova Iguaçú)
Em REMESSA_FORNECIDA (F1, P3, J5, 255)
Exercícios:
1. Utilizando a instrução CREATE TABLE, criar as tabelas do esquema “Fornecedores, Peças e Projetos” que 
você ainda não criou em sala de aula.
 Professor Flávio 
Mendonça
1
CREATE TABLE Fornecedores 
( cod VARCHAR(10) NOT NULL,
 nome VARCHAR(50) NOT NULL,
 status INT NOT NULL,
 cidade VARCHAR(20) NOT NULL,
 PRIMARY KEY (cod) );
CREATE TABLE Pecas
( cod VARCHAR(10) NOT NULL,
 nome VARCHAR(30) NOT NULL,
 cor VARCHAR(20) NOT NULL,
 peso DECIMAL(5,2)NOT NULL,
cidade VARCHAR(30) NOT NULL,
PRIMARY KEY (cod) );
CREATE TABLE Projetos
( cod VARCHAR(10) NOT NULL,
 nome VARCHAR(30) NOT NULL,
 cidade VARCHAR(20) NOT NULL,
 PRIMARY KEY (cod) );
CREATE TABLE Remessa_Fornecida
( codForn VARCHAR(10) NOT 
NULL,
 codProj VARCHAR(10) NOT 
NULL,
 codPeca VARCHAR(10) NOT 
NULL,
 quantidade INT NOT NULL,
 PRIMARY KEY (codForn, codProj, codPeça),
 FOREIGN KEY codForn REFERENCES 
Fornecedores(cod),
 FOREIGN KEY codProj REFERENCES 
Projetos(cod),
 FOREIGN KEY codPeca REFERENCES 
Pecas(cod) );
2. Inserir a seguinte tupla na tabela FORNECEDORES:
(F10, João, Niterói)
INSERT INTO Fornecedores
VALUES (‘F10’,’João’, 30,‘Niterói’);
3. Inserir a seguinte tupla na tabela Peça:
cod = P34,
nome =Arruela
peso = 9.35
INSERT INTO Pecas(cod, nome, peso)
VALUES (‘P34’,’Arruela’, 9.35);
4. Alterar a cor de todas as peças Vermelhas para Laranja.
UPDATE Pecas
SET cor = ‘Laranja’
WHERE cor=’Vermelha’;
5. Eliminar todos os projetos para os quais não existem remessas cadastradas.
DELETE FROM Projeto
WHERE cod NOT IN (SELECT codProj FROM Remessa_Fornecida);
6. Eliminar todas as remessas dos fornecedores que são da cidade de Niterói.
DELETE FROM Remessa_Fornecida
WHERE codForn IN (SELECT cod FROM Fornecedores WHERE cidade=’Niteroi’);
7. Utilizando a instrução SELECT, escreva as seguintes consultas:
a. Obter todos os fornecedores de porcas;
SELECT Fornecedores.Cod, Fornecedores.Nome
FROM Peças INNER JOIN (Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod 
= Remessa_Fornecida.CodForn) ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Peças.Nome)="porca"));
b. Obter o nome e a cor das peças dos fornecedores de Duque que Caxias;
SELECT Peças.Nome, Peças.Cor, Fornecedores.Cidade
FROM Peças INNER JOIN (Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod 
= Remessa_Fornecida.CodForn) ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Fornecedores.Cidade)="duque de caxias"));
c. Para cada remessa, obter o nome da peça, sua cor e a quantidade fornecida;
SELECT Peças.Nome, Peças.Cor, Remessa_Fornecida.Quantidade
FROM Peças INNER JOIN Remessa_Fornecida ON Peças.Cod = Remessa_Fornecida.CodPeça;
d. Obter o nome dos fornecedores e a quantidade total de peças fornecidas por cada um;
SELECT Fornecedores.Nome, Sum(Remessa_Fornecida.Quantidade) AS SomaDeQuantidade
FROM Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod = 
Remessa_Fornecida.CodForn
GROUP BY Fornecedores.Nome;
e. Obter as cidades de peças que armazenam mais de cinco peças vermelhas;
SELECT Peças.Cidade, Remessa_Fornecida.Quantidade
FROM Peças INNER JOIN Remessa_Fornecida ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Remessa_Fornecida.Quantidade)>5) AND ((Peças.Cor)="vermelha"));
f. Obter o número de fornecedores do Rio de Janeiro que possuem status maior do que 20;
SELECT Count(Fornecedores.Cod) AS NúmeroDeFornecedores
FROM Fornecedores
WHERE (((Fornecedores.Cidade)="rio de janeiro") AND ((Fornecedores.Status)>20));
g. Obter todas as peças que pesam mais de 25 gramas e são fornecidas pelos fornecedores da cidade de 
Niterói ou Duque de Caxias;
SELECT Peças.Cod
FROM Peças INNER JOIN (Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod 
= Remessa_Fornecida.CodForn) ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Peças.Peso)>25) AND ((Fornecedores.Cidade)="niterói" Or 
(Fornecedores.Cidade)="duque de caxias"));
h. Obter a cor e a cidade das peças que não são do Rio de Janeiro e tem peso inferior a 40 gramas;
SELECT Peças.Cor, Peças.Cidade, Peças.Peso
FROM Peças INNER JOIN (Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod 
= Remessa_Fornecida.CodForn) ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Peças.Cidade)<>"rio de janeiro") AND ((Peças.Peso)<40));
i. Para todas as peças, obter o número da peça, o nome e seu peso em quilos;
SELECT Peças.Cod, Peças.Nome, [Peças]![Peso]*0.001 AS Peso_quilo
FROM Peças;
j. Obter o número total de fornecedores;
SELECT Count(Fornecedores.Cod) AS QuantidadeForn
FROM Fornecedores;
k. Obter as quantidades máximas e mínimas de remessas da peça P13;
SELECT Min(Remessa_Fornecida.Quantidade) AS Quantidade_Min, Max
(Remessa_Fornecida.Quantidade) AS Quantidade_Max
FROM Remessa_Fornecida;
l. Obter o nome dos fornecedores que fornecem pelo menos uma peça de cor azul.
SELECT Fornecedores.Nome
FROM Peças INNER JOIN (Fornecedores INNER JOIN Remessa_Fornecida ON Fornecedores.Cod 
= Remessa_Fornecida.CodForn) ON Peças.Cod = Remessa_Fornecida.CodPeça
WHERE (((Peças.Cor)="azul"));