Banco de dados 02 LISTA FEITA

Prévia do material em texto

<p>Banco de dados 2 (aula 02)</p><p>-- Tabela Tipo</p><p>CREATE TABLE Tipo (</p><p>CodTipo INT PRIMARY KEY,</p><p>NomeTipo VARCHAR(45)</p><p>);</p><p>-- Tabela Produto</p><p>CREATE TABLE Produto (</p><p>CodProduto INT PRIMARY KEY,</p><p>CodTipo INT,</p><p>Preco DECIMAL(10,2),</p><p>Estoque INT,</p><p>FOREIGN KEY (CodTipo) REFERENCES Tipo(CodTipo)</p><p>);</p><p>-- Tabela Venda</p><p>CREATE TABLE Venda (</p><p>CodVenda INT PRIMARY KEY,</p><p>CodProduto INT,</p><p>CodUsuario INT,</p><p>Quantidade INT,</p><p>FOREIGN KEY (CodProduto) REFERENCES Produto(CodProduto),</p><p>FOREIGN KEY (CodUsuario) REFERENCES Usuario(CodUsuario)</p><p>);</p><p>-- Tabela Usuario</p><p>CREATE TABLE Usuario (</p><p>CodUsuario INT PRIMARY KEY,</p><p>NomeUsuario VARCHAR(45)</p><p>);</p><p>--3. Registre algumas informações aleatórias no banco, para exemplo:</p><p>-- Inserir alguns dados na tabela Tipo</p><p>INSERT INTO Tipo (CodTipo, NomeTipo) VALUES</p><p>(1, 'Eletrônicos'),</p><p>(2, 'Roupas'),</p><p>(3, 'Alimentos');</p><p>-- Inserir alguns dados na tabela Produto</p><p>INSERT INTO Produto (CodProduto, CodTipo, Preco, Estoque) VALUES</p><p>(101, 1, 1500.00, 20),</p><p>(102, 2, 100.00, 50),</p><p>(103, 3, 5.00, 100);</p><p>-- Inserir alguns dados na tabela Usuario</p><p>INSERT INTO Usuario (CodUsuario, NomeUsuario) VALUES</p><p>(201, 'João'),</p><p>(202, 'Maria'),</p><p>(203, 'Pedro');</p><p>--4. Crie uma função que receba o código do produto e ela retorne a quantidade de</p><p>estoque:</p><p>CREATE FUNCTION GetEstoque(@CodProduto INT)</p><p>RETURNS INT</p><p>AS</p><p>BEGIN</p><p>DECLARE @QuantidadeEstoque INT;</p><p>SELECT @QuantidadeEstoque = Estoque FROM Produto WHERE CodProduto = @CodProduto;</p><p>RETURN @QuantidadeEstoque;</p><p>END;</p><p>--5. Crie uma função que receba o código do produto e retorne o nome do tipo do</p><p>mesmo:</p><p>CREATE FUNCTION GetTipoNome(@CodProduto INT)</p><p>RETURNS VARCHAR(45)</p><p>AS</p><p>BEGIN</p><p>DECLARE @NomeTipo VARCHAR(45);</p><p>SELECT @NomeTipo = NomeTipo FROM Tipo t INNER JOIN Produto p ON t.CodTipo =</p><p>p.CodTipo WHERE p.CodProduto = @CodProduto;</p><p>RETURN @NomeTipo;</p><p>END;</p><p>--6. Crie uma função que receba o código do produto e mostre o resultado da equação</p><p>Preço x Estoque:</p><p>CREATE FUNCTION CalcularValorEstoque(@CodProduto INT)</p><p>RETURNS DECIMAL(10,2)</p><p>AS</p><p>BEGIN</p><p>DECLARE @ValorEstoque DECIMAL(10,2);</p><p>SELECT @ValorEstoque = Preco * Estoque FROM Produto WHERE CodProduto =</p><p>@CodProduto;</p><p>RETURN @ValorEstoque;</p><p>END;</p><p>--7. Crie uma função que receba o código da venda e mostre a quantidade vendida:</p><p>CREATE FUNCTION GetQuantidadeVendida(@CodVenda INT)</p><p>RETURNS INT</p><p>AS</p><p>BEGIN</p><p>DECLARE @Quantidade INT;</p><p>SELECT @Quantidade = Quantidade FROM Venda WHERE CodVenda = @CodVenda;</p><p>RETURN @Quantidade;</p><p>END;</p><p>--8. Crie uma função que receba o código da venda e mostre a quantidade vendida x</p><p>preço do produto:</p><p>CREATE FUNCTION CalcularTotalVenda(@CodVenda INT)</p><p>RETURNS DECIMAL(10,2)</p><p>AS</p><p>BEGIN</p><p>DECLARE @Total DECIMAL(10,2);</p><p>SELECT @Total = Quantidade * Preco</p><p>FROM Venda v</p><p>INNER JOIN Produto p ON v.CodProduto = p.CodProduto</p><p>WHERE CodVenda = @CodVenda;</p><p>RETURN @Total;</p><p>END;</p><p>--9. Crie uma função que receba o código do usuário e mostre quantos produtos ele já</p><p>comprou:</p><p>CREATE FUNCTION ContarProdutosComprados(@CodUsuario INT)</p><p>RETURNS INT</p><p>AS</p><p>BEGIN</p><p>DECLARE @Contagem INT;</p><p>SELECT @Contagem = COUNT(*) FROM Venda WHERE CodUsuario = @CodUsuario;</p><p>RETURN @Contagem;</p><p>END;</p><p>--10. Crie uma função que receba o código do produto e mostre a média da quantidade</p><p>vendida:</p><p>CREATE FUNCTION CalcularMediaQuantidadeVendida(@CodProduto INT)</p><p>RETURNS DECIMAL(10,2)</p><p>AS</p><p>BEGIN</p><p>DECLARE @Media DECIMAL(10,2);</p><p>SELECT @Media = AVG(Quantidade) FROM Venda WHERE CodProduto = @CodProduto;</p><p>RETURN @Media;</p><p>END;</p><p>--11. Crie uma função que receba o código do usuário e verifique se ele já gastou</p><p>mais de 200 reais em compras:</p><p>CREATE FUNCTION VerificarGastos(@CodUsuario INT)</p><p>RETURNS INT</p><p>AS</p><p>BEGIN</p><p>DECLARE @Gastos DECIMAL(10,2);</p><p>SELECT @Gastos = SUM(Quantidade * Preco)</p><p>FROM Venda v</p><p>INNER JOIN Produto p ON v.CodProduto = p.CodProduto</p><p>WHERE CodUsuario = @CodUsuario;</p><p>IF @Gastos > 200</p><p>RETURN 1;</p><p>ELSE</p><p>RETURN 0;</p><p>-- Instrução de retorno adicional para garantir conformidade</p><p>RETURN 0;</p><p>END;</p><p>--12. Crie uma função que receba o código do produto, código do usuário e</p><p>quantidade:</p><p>CREATE PROCEDURE ProcessarVenda</p><p>@CodProduto INT,</p><p>@CodUsuario INT,</p><p>@Quantidade INT</p><p>AS</p><p>BEGIN</p><p>DECLARE @Status INT;</p><p>-- Verificar se o produto existe</p><p>IF NOT EXISTS (SELECT 1 FROM Produto WHERE CodProduto = @CodProduto)</p><p>BEGIN</p><p>SET @Status = 4; -- Produto não cadastrado</p><p>RETURN @Status;</p><p>END</p><p>-- Verificar se o usuário existe</p><p>IF NOT EXISTS (SELECT 1 FROM Usuario WHERE CodUsuario = @CodUsuario)</p><p>BEGIN</p><p>SET @Status = 3; -- Usuário não cadastrado</p><p>RETURN @Status;</p><p>END</p><p>-- Verificar se há estoque disponível</p><p>DECLARE @EstoqueAtual INT;</p><p>SELECT @EstoqueAtual = Estoque FROM Produto WHERE CodProduto = @CodProduto;</p><p>IF @Quantidade <= @EstoqueAtual</p><p>BEGIN</p><p>-- Atualizar estoque</p><p>UPDATE Produto SET Estoque = @EstoqueAtual - @Quantidade WHERE CodProduto =</p><p>@CodProduto;</p><p>-- Inserir informações na tabela Venda</p><p>INSERT INTO Venda (CodProduto, CodUsuario, Quantidade) VALUES (@CodProduto,</p><p>@CodUsuario, @Quantidade);</p><p>SET @Status = 1; -- Venda processada com sucesso</p><p>END</p><p>ELSE</p><p>BEGIN</p><p>SET @Status = 2; -- Estoque insuficiente</p><p>END</p><p>RETURN @Status;</p><p>END;</p>