-- SBD-2006-SQLM4
-- Disciplina de Bases de Dados
-- Licenciatura em Matemática e Ciências de Computação (4º Ano)
-- Licenciatura em Ciências de Computação (3º Ano)

-- Modelo Relacional de Dados
-- Folha de Exercícios SBD-2006-SQLM1
--
-- Introdução ao SQL
-- Criação e Manipulação de Vistas.
-- Definição de permissões.
-- Procedimentos armazenados - stored procedures.
-- Gatilhos - triggers.
-- Exemplos de programação em Transact-SQL


-- Exercícios resolvidos utilizando Transact-SQL do Microsoft SQL Server

-- Base de Dados
USE NORTHWIND


-- CRIAÇÂO E MANIPULAÇÂO DE VISTAS (VIEWS)
--
-- Criação de uma vista através de uma query simples. 
-- Visualização de parte da informação mantida na tabela "Customers".
-- Lista com a informação dos clientes dos países 'Mexico','France'
-- e'Brazil'

CREATE VIEW ClientesMFB
AS 
	SELECT  *
 		FROM Customers AS CL
		WHERE CL.Country IN ('Mexico','France','Brazil')


-- Alteração de uma vista.

ALTER VIEW ClientesMFB
AS 
	SELECT  CustomerID, CompanyName, Phone
 		FROM Customers AS CL
 		WHERE CL.Country IN ('Mexico','France','Brazil')


-- Remoção de uma vista do sistema.

DROP VIEW ClientesMFB


-- Interrogação de dados a partir de uma vista.

SELECT *
	FROM ClientesMFB
	WHERE CompanyName LIKE 'B%'


-- Criação de uma vista para fornecimento de uma lista com os endereços
-- dos clientes e fornecedores da Northwind.

CREATE VIEW "Lista de Endereços CF"
AS
	SELECT  'C' AS "Tipo",CustomerID AS "Código", CompanyName, ContactName, Address, City, Phone
 		FROM Customers AS CL
 		WHERE CL.Country IN ('Mexico','France','Brazil')
	UNION
	SELECT  'S',CONVERT(CHAR(5),SupplierID), CompanyName, ContactName, Address, City, Phone
 		FROM Suppliers AS SU
 		WHERE SU.Country IN ('Mexico','France','Brazil')


-- Interrogação de dados a partir da vista "Lista de Endereços CF" com
-- aplicação de critérios de filtragem e de ordenação.
 
SELECT *
	FROM [Lista de Endereços CF]
	WHERE Tipo = 'C'
	ORDER BY CompanyName ASC


-- Remoção da vista "Lista de Endereços CF" do sistema.

DROP VIEW "Lista de Endereços CF"


-- Criação de uma vista para obtenção de uma lista com os nomes dos
-- 10 produtos mais encomendados e respectivas quantidades encomendadas.

CREATE VIEW "Top10 Products"
AS
	SELECT TOP 10 ProductName, SUM(Quantity) AS Quantity
		FROM [Order Details] AS OD INNER JOIN Products AS PR
			ON OD.ProductId = PR.ProductId
		GROUP BY ProductName
		ORDER BY SUM(Quantity) DESC

SELECT *
	FROM [Top10 Products]


-- Remoção da vista "Top10 Products" do sistema.

DROP VIEW "Top10 Products"



-- DEFINIÇÂO DE PERMISSÔES.
-- Autorização a um perfil de utilização para a realização das 
-- operações de criação de bases de dados, criação de tabelas, 
-- criação de vistas e criação de funções. 
-- Obs.: Assumiu-se queosperfis e utilizadores utilizadosnos exercícios
-- já estavamcriados no sistema.
 
GRANT CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE FUNCTION
      TO "NorthManager"

-- Negação da autorização a dois utilizadores e a um perfil
-- de utilização para a realização das operações de 
-- criação de bases de dados, criação de tabelas, 
-- criação de vistas e criação de funções. 

DENY  CREATE DATABASE, CREATE TABLE, CREATE VIEW
      TO pedroreis, analima, “NorthManager”

-- Atribuição de privilégios de consulta e de inserção sobre a 
-- tabela “Orders” ao perfil de utilização “public”.
GRANT SELECT, INSERT
	ON Orders
	TO public

-- Negação dos privilégios de inserção, actualização e remoção
-- sobre a tabela “Customers” ao utilizador “pedroreis”.
DENY  INSERT, UPDATE, DELETE
      ON Customers
      TO pedroreis

-- Revogação dos privilégios de inserção sobre a tabela “Customers” ao 
-- utilizador “pedroreis”.
REVOKE INSERT
      ON Customers
      TO pedroreis

-- Negação de qualquer privilégio ao perfil de utilização “NorthManager” 
-- para acesso à tabela “Orders”.
DENY ALL
     ON Orders
     TO “NorthManager”

-- Visualização das permissões definidas sobre a tabela “Orders”.
EXECUTE sp_helprotect 'Orders'


-- PROCEDIMENTOS ARMAZENADOS
-- Criação do procedimento "ProdutosClienteData".
-- Lista dos produtos encomendados pelo Cliente Y no dia X.
CREATE PROCEDURE "ProdutosClienteData" 
	@CodCli NCHAR(5), @DataEnc DateTime 
AS
	SELECT ProductId
		FROM Orders AS EN INNER JOIN [Order Details] AS LE
			ON EN.OrderId = LE.OrderId
		WHERE EN.CustomerId = @CodCli AND EN.OrderDate = @DataEnc

-- Execução do procedimento "ProdutosClienteData"
EXECUTE "ProdutosClienteData" 'VINET', '1996-07-04'
EXECUTE "ProdutosClienteData" 'ALFKI', '1996-07-04'

-- Alteração do procedimento "ProdutosClienteData"
ALTER PROCEDURE "ProdutosClienteData" 
	@CodCli NCHAR(5), @DataEnc1 DateTime, @DataEnc2 DateTime 
AS
	SELECT ProductId
		FROM Orders AS EN INNER JOIN [Order Details] AS LE
			ON EN.OrderId = LE.OrderId
		WHERE EN.CustomerId = @CodCli AND 
		      EN.OrderDate BETWEEN @DataEnc1 AND @DataEnc2

EXECUTE "ProdutosClienteData" 'VINET', '1996-07-04', '1996-07-04'


DROP PROCEDURE "ProdutosFornecedorData" 

-- Lista dos produtos encomendados no dia X que são fornecidos pelo fornecedor Y.
CREATE PROCEDURE "ProdutosFornecedorData" 
	@CodFor INT, @DataEnc DateTime 
AS
	SELECT  PR.ProductId, PR.ProductName
		FROM Orders AS EN INNER JOIN [Order Details] AS LE
			ON EN.OrderId = LE.OrderId
			INNER JOIN Products AS PR
				ON LE.ProductId = PR.ProductId
		WHERE PR.SupplierId = @CodFor AND 
		      EN.OrderDate = @DataEnc

EXECUTE "ProdutosFornecedorData" 5, '1996-07-04'


DROP TABLE Audit

CREATE TABLE Audit
(
	DataReg DATETIME PRIMARY KEY,
	Operação VARCHAR(10) NOT NULL,
	Registo VARCHAR(50) NOT NULL,
	Tabela VARCHAR(20) NOT NULL
)


-- CRIAÇÂO E MANIPULAÇÂO DE TRIGGERS
-- Criaçãodo gatilho "registaInserções".
CREATE TRIGGER registaInserções ON [dbo].[Customers] 
FOR INSERT
AS
	DECLARE @Código AS VARCHAR(50)
	SELECT @Código=INSERTED.CustomerID FROM INSERTED
	INSERT INTO Audit
		VALUES (GETDATE(),'INSERT',@Código,'Customers')


-- Remoção do gatilho "registaInserções"
DROP TRIGGER registaInserções

INSERT INTO Customers (CustomerId, CompanyName)
	VALUES ('XPTO1','Nome do XPTO1')

INSERT INTO Customers (CustomerId, CompanyName)
	VALUES ('XPTO2','Nome do XPTO2')
INSERT INTO Customers (CustomerId, CompanyName)
	VALUES ('XPTO3','Nome do XPTO3')
INSERT INTO Customers (CustomerId, CompanyName)
	VALUES ('XPTO4','Nome do XPTO4')

SELECT *
	FROM Customers

SELECT *
	FROM Audit


-- Criação do gatilho "registaRemoções"
CREATE TRIGGER registaRemoções ON [dbo].[Customers] 
FOR DELETE
AS
	DECLARE @Código AS VARCHAR(50)
	SELECT @Código=DELETED.CustomerID FROM DELETED
	INSERT INTO Audit
		VALUES (GETDATE(),'DELETE',@Código,'Customers')

-- Remoção do gatilho "registaRemoções"
DROP TRIGGER registaRemoções 

DELETE Customers 
	WHERE CustomerId = 'XPTO1'

DELETE Customers 
	WHERE CustomerId LIKE 'XPTO%'


SELECT *
	FROM Audit


-- Criação do gatilho "registaInserçãoFornecedor" 

CREATE TRIGGER registaInserçãoFornecedor ON [dbo].[Suppliers] 
FOR INSERT
AS
	DECLARE @Código AS VARCHAR(50)
	SELECT @Código=INSERTED.SupplierID FROM INSERTED
	INSERT INTO Audit
		VALUES (GETDATE(),'INSERT',@Código,'Suppliers')

-- Remoção do gatilho "registaInserçãoFornecedor" 
DROP TRIGGER registaInserçãoFornecedor

DELETE Audit

SELECT * FROM Audit

SELECT * FROM Suppliers

INSERT INTO Suppliers
	(CompanyName)
	VALUES ('FXPTO1 -Bananas e Portáteis Lda')
INSERT INTO Suppliers
	(CompanyName)
	VALUES ('FXPTO2 - Café da Rosinha')



CREATE TRIGGER "registaRemoçãoFornecedor" ON [dbo].[Suppliers] 
FOR DELETE
AS
	DECLARE @Código AS VARCHAR(50)
	SELECT @Código=DELETED.SupplierID FROM DELETED
	INSERT INTO Audit
		VALUES (GETDATE(),'DELETE',@Código,'Suppliers')

DELETE Suppliers
	WHERE CompanyName = 'FXPTO2 - Café da Rosinha'


-- Manipulação de variáveis e controlo de ciclos com a instrução WHILE
-- Procedimento que apresenta as datas e os nomes dos sete dias seguintes
-- à data actual. 

ALTER PROCEDURE seteDiasSeguintes
AS
DECLARE @contador int, @vardata datetime
SET @contador = 0
PRINT 'Início da Contagem'
SET @vardata = getdate()
PRINT @vardata
WHILE @contador <= 500
   BEGIN
      PRINT @contador
      PRINT convert(varchar(11),@vardata) + ' - ' +   
            convert(varchar(15),datename(weekday,@vardata))
      SET @vardata = @vardata + 1
      SET @contador = @contador + 1
   END
PRINT 'Fim ' + convert(varchar(2), @contador)

-- Execução do procedimento.
EXECUTE seteDiasSeguintes


CREATE PROCEDURE geraTempo
AS
DECLARE @Tempo DATETIME
DECLARE @TempoLimite DATETIME
SELECT TOP 1 @Tempo = Data
	FROM ParqueCereais.dbo.RegistosEntrada
	ORDER BY  Data ASC
SET @TempoLimite = getdate()  + 365 * 10

WHILE @Tempo  < @TempoLimite
BEGIN
PRINT @tempo
INSERT INTO ParqueCereaisDW.dbo.[DM-Tempo]
	VALUES (@Tempo, 
		DATENAME(dw,@Tempo), 
		DATEPART(ww,@Tempo),
		DATEPART(mm,@Tempo),
		DATEPART(qq,@Tempo),
		DATEPART(yy,@Tempo))
SET @Tempo = @Tempo + 1
END


GO


--
-- Fim da Ficha de Exercícios
-- Sistemas de Bases de Dados - SQL
-- SBD-2006-SQLM4