View

CREATE VIEW view_name [(alias,…)]
AS
שאילתה
[WITH CHECK OPTION]

CREATE VIEW emp_det_vw 
AS 
SELECT employeeID, lastname, firstname, birthdate 
FROM employees 
WHERE Region IS NOT NULL

SELECT * FROM emp_det_vw

CREATE VIEW shipStatusView 
AS 
SELECT o.OrderId,o.ShippedDate,c.ContactName 
FROM Customers c Inner Join Orders o 
ON c.CustomerId=o.CustomerId 
WHERE RequiredDate<ShippedDate

ALTER VIEW view_name [(alias,…)]
AS
שאילתה

CREATE VIEW LOW_PRICED_PRODUCTS 
AS 
SELECT *
FROM Products 
WHERE UnitPrice < 20 
WITH CHECK OPTION

DROP VIEW view_name

use Northwind

-- ex1
CREATE VIEW emp_vw 
AS 
SELECT employeeID, lastname, firstname, [address] 
FROM employees 
WHERE city = 'Tacoma' or city = 'London'

SELECT * FROM emp_vw

-- ex2
CREATE VIEW pro_vw 
AS 
SELECT avg(UnitPrice) as avg_price
FROM Products
WHERE ProductID in (1, 2, 4)

select * from pro_vw

-- ex3
alter view pro_vw
as
SELECT p.ProductName, c.CategoryName, avg(UnitPrice) as avg_price
FROM Products p INNER JOIN Categories c 
On p.CategoryID = c.CategoryID
GROUP BY c.CategoryName, p.ProductName
having avg(UnitPrice) > 25

-- ex4
CREATE VIEW cust_orders_vw
as
SELECT o.CustomerID, c.CompanyName, count(*) as "num of orders"
FROM Orders o INNER JOIN Customers c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID, c.CompanyName

select * from cust_orders_vw

-- ex5
select companyName 
from cust_orders_vw
where [num of orders] > 10

-- ex6
CREATE VIEW Prods
as
select ProductID, ProductName, UnitPrice, (UnitsInStock-UnitsOnOrder) as UnitsLeft
from products
where UnitPrice < 30

select * from Prods

-- ex7
BEGIN TRAN

update products
set ProductName = 'Jiffa'
where ProductID = 52

update products
set UnitPrice = 31
where ProductID = 52

COMMIT

select *
from Prods
where ProductID = 52