Correção de Scripts SQL: Hardware, Pregos, Móveis e Transportes

-- 1.1 Adicione à tabela hardware a coluna material do tipo VARCHAR(20). Após a adição anterior, insira na tabela hardware um registo à sua escolha.

ALTER TABLE hardware
ADD material VARCHAR(20);
INSERT INTO hardware (IDH, nome, peso, quantidade)
VALUES (1, 'madeira', 50.5, 10);

-- 1.2. Qual o prego mais comprido inserido no sistema? [Nome, Comprimento]
SELECT TOP 1 h.nome, p.comprimento
FROM hardware h, pregos p
WHERE h.IDH = p.IDPr
ORDER BY p.comprimento DESC;

-- 1.3 Quais as alturas dos móveis que usaram igual número de pregos e parafusos no seu fabrico? [IDM, altura]
SELECT m.IDM, m.altura
FROM moveis m, fabricar f
WHERE m.IDM = f.IDM AND f.quant_pregos = f.quant_parafusos
ORDER BY m.altura;

-- 1.4 Quantos pregos de cada comprimento foram usados para fabricar os móveis 'Luis II'? [Comprimento, N_Pregos]
SELECT p.comprimento, f.quant_pregos
FROM pregos p, fabricar f, moveis m
WHERE f.IDPr = p.IDPr
AND m.IDM = f.IDM
AND m.nome LIKE 'LUIS II'
ORDER BY p.comprimento;

-- 1.5. Que pregos nunca foram usados para fabricar móveis? [nome, comprimento]
SELECT h.nome, p.comprimento
FROM pregos p, hardware h
WHERE h.IDH = p.IDPr
AND p.IDPr NOT IN (SELECT DISTINCT IDPr FROM fabricar);

-- 1.6. Crie um procedimento que, dado o código de um móvel, calcule e devolva o seu valor arredondado em euros, sabendo que cada tábua custa 10€, cada parafuso 0,05€ e cada prego 0,01€. Caso o número de tábuas seja NULL deve ser substituído pelo valor 25.

CREATE PROCEDURE valor
@cod_movel INTEGER
AS
BEGIN
DECLARE @valor DECIMAL(10,2), @n_tabuas INTEGER;
SELECT @n_tabuas = n_tabuas
FROM fabricar
WHERE IDM = @cod_movel;
IF @n_tabuas IS NULL SET @n_tabuas = 25;
SELECT @valor = ROUND(SUM(@n_tabuas * 10 + f.quant_pregos * 0.01 + f.quant_parafusos * 0.05), 0)
FROM moveis m, fabricar f
WHERE m.IDM = @cod_movel AND f.IDM = @cod_movel;
RETURN @valor;
END;

-- 1.7. Crie um trigger que, ao inserir um novo registo na tabela fabricar, apresente automaticamente o custo do móvel fabricado. Ex.: Custo = 258€. Assuma que só é inserido 1 registo.

CREATE TRIGGER trg_custo_movel
ON fabricar
AFTER INSERT
AS
BEGIN
DECLARE @id INTEGER, @valor DECIMAL(10,2);
SELECT @id = IDM FROM inserted;
EXEC @valor = valor @id;
PRINT 'Custo = ' + CAST(@valor AS VARCHAR) + '€';
END;

-- 5. Inserção de pelo menos 3 registos em cada tabela
INSERT INTO Armazem (Codigo_Armazem, Nome, Localizacao)
VALUES (1234, 'Grandes Ideias', 'AV RUY LUIS GOMES 23, 2650-129, ENCOSTA DO SOL, AMADORA, LISBOA');

Qual o último transporte realizado?

SELECT E.Nome, T.Codigo_Encomendas, T.Data_Transportar
FROM Empresa E, Transportar T
WHERE T.Data_Transportar IN (SELECT MAX(T2.Data_Transportar) FROM Transportar T2)
AND T.Codigo_Encomendas = E.Nome;

Quantos transportes já se realizaram por cada Empresa?

SELECT E.Codigo_Empresa, E.Nome, COUNT(T.Codigo_Encomendas) AS N_Transportes
FROM Empresa E, Transportar T
WHERE E.Codigo_Empresa = T.Codigo_Encomendas
GROUP BY E.Nome, T.Codigo_Encomendas, E.Codigo_Empresa;

Qual a Empresa que mais pagou por um transporte?

SELECT E.Nome, P.Preco, P.Data_Pagar
FROM Empresa E, Pagar P
WHERE P.Preco IN (SELECT MAX(P2.Preco) FROM Pagar P2)
AND P.Codigo_Empresa = E.Nome;

Quais as empresas que pagaram por mais de 15 transportes nos últimos 30 dias? Ordene-as alfabeticamente.

SELECT DISTINCT E.Nome, COUNT(P.ID_Transportar) AS N_Transportes
FROM Pagar P, Empresa E, Transportar T
WHERE DATEDIFF(DAY, P.Data_Pagar, GETDATE()) < 30
AND E.Codigo_Empresa = P.Codigo_Empresa
AND P.ID_Transportar = T.ID_Transportar
GROUP BY E.Nome
HAVING COUNT(P.ID_Transportar) > 15
ORDER BY E.Nome ASC;

Quais os 2 armazéns com mais encomendas guardadas?

SELECT TOP 2 A.Nome, COUNT(G.Codigo_Armazem) AS Quantidade
FROM Armazem A, Guardar G
WHERE G.Codigo_Armazem = A.Codigo_Armazem AND G.Data_Fim IS NULL
GROUP BY A.Nome
ORDER BY Quantidade DESC;

Quantos tipos de transporte foram definidos por cada empresa no ano de 2020?

SELECT E.Nome, E.Telefone, COUNT(D.Codigo_Empresa) AS Tipo
FROM Empresa E, Definir D
WHERE D.Codigo_Empresa = E.Codigo_Empresa AND YEAR(D.Data_Definir) = 2020
GROUP BY E.Nome, E.Telefone;

Qual o peso total das encomendas transportadas por cada navio no ano de 2020? Ordene por ordem crescente.

SELECT N.Nome, SUM(N.Capacidade) AS Peso
FROM Encomendas E, Navio N, Transportar T
WHERE N.Codigo_Navio = T.Codigo_Navio
AND E.Codigo_Encomendas = T.Codigo_Encomendas
AND YEAR(T.Data_Transportar) = 2020
GROUP BY N.Nome
ORDER BY Peso ASC;

Trigger de Encomendas

USE relatorio_bd_final_2;
GO
CREATE TRIGGER inserirRegistos
ON Encomendas
FOR INSERT
AS
BEGIN
UPDATE Encomendas
SET Peso = i.Peso
FROM Encomendas e
INNER JOIN inserted i ON e.Codigo_Encomendas = i.Codigo_Encomendas
WHERE i.Peso <= 10;
END;

Procedimento de Tempo Médio de Armazenamento

USE relatorio_final_2;
GO
CREATE PROCEDURE encomendaGuardada @nif_empresa INTEGER
AS
BEGIN
DECLARE @tempo FLOAT;
SELECT @tempo = AVG(CAST(DATEDIFF(DAY, G.Data_Inicio, G.Data_Fim) AS FLOAT))
FROM Guardar G, Empresa E
WHERE E.NIF = @nif_empresa AND E.Codigo_Empresa = G.Codigo_Empresa;
IF EXISTS (SELECT 1 FROM Empresa E, Guardar G WHERE E.NIF = @nif_empresa AND E.Codigo_Empresa = G.Codigo_Empresa)
PRINT 'O tempo médio de armazenamento é: ' + CAST(@tempo AS VARCHAR) + ' dias.';
ELSE
RETURN -1;
END;