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"));