/*

Views

Virtual Tables: Creating a View pp. 262
The table upon which a view is based are called base tables.

CREATE VIEW viewname AS SQL expression

*/

USE Northwind


--Create a view of the Order Count Result
--DROP VIEW vwOrderCountResult
CREATE VIEW vwOrderCountResult 
AS 
SELECT Orders.CustomerID, COUNT(*) AS NumberOfOrders
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID AND--Pk=Fk
      Customers.Country = 'USA'
GROUP BY Orders.CustomerID

SELECT *
FROM vwOrderCountResult
WHERE NumberOfOrders > 10


/*
Materialized VIEW

SELECT attribute list INTO viewname
FROM ....

SELECT attribute list INTO #TempTable
FROM ....

*/

SELECT Orders.CustomerID, COUNT(*) AS NumberOfOrders
INTO mvwOrderCountResult
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID AND--Pk=Fk
      Customers.Country = 'USA'
GROUP BY Orders.CustomerID

SELECT *
FROM mvwOrderCountResult
WHERE NumberOfOrders > 10

SELECT *
FROM vwOrderCountResult
WHERE NumberOfOrders > 10

DROP TABLE mvwOrderCountResult

/*
This is a type of materialized view. It is a temporary table
*/
SELECT Orders.CustomerID, COUNT(*) AS NumberOfOrders
INTO #OrderCountResult
FROM Orders, Customers
WHERE Customers.CustomerID=Orders.CustomerID AND--Pk=Fk
      Customers.Country = 'USA'
GROUP BY Orders.CustomerID

SELECT *
FROM #OrderCountResult
SELECT *
FROM mvwOrderCountResult
WHERE NumberOfOrders > 10

SELECT *
FROM vwOrderCountResult
WHERE NumberOfOrders > 10

--Create a view of Andrew's employees
SELECT EmployeeID, Lastname, FirstName, Title, ReportsTo
FROM Employees
WHERE ReportsTo = 2

--DROP VIEW vwFullerEmp
CREATE VIEW vwFullerEmp
AS
SELECT EmployeeID, Lastname, FirstName, Title, ReportsTo
FROM Employees
WHERE ReportsTo = 2

SELECT *
FROM vwFullerEmp

DROP VIEW vwFullerEmp

--Let's say that Margaret got a promotion
UPDATE vwFullerEmp SET Title = 'Sales Manager' WHERE EmployeeID = 4

SELECT * FROM Employees WHERE EmployeeID = 4

UPDATE vwFullerEmp SET Title = 'Sales Representative' WHERE EmployeeID = 4

/*
Views can create a potential security vulnerability.
For example, should the following query be allowed?
*/

UPDATE vwFullerEmp SET ReportsTo = 1 WHERE EmployeeID = 4

--Now Margaret no longer exists in the current view!
SELECT *
FROM vwFullerEmp

--Set it back to the correct value
UPDATE Employees SET ReportsTo = 2 WHERE EmployeeID = 4

--WITH CHECK OPTION

ALTER VIEW vwFullerEmp
AS
SELECT EmployeeID, Lastname, FirstName, Title, ReportsTo
FROM Employees
WHERE ReportsTo = 2
WITH CHECK OPTION

--Try it again
UPDATE vwFullerEmp SET ReportsTo = 1 WHERE EmployeeID = 4

/*
Server: Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies 
WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or 
more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
*/