use AdventureWorks2012 ;

---------------------------------------------------------------------------------
-- Abschnitt 10.1.1
create view Person.PersonMail as
	select p.Firstname, p.LastName, m.Emailaddress
		from Person.Person as p left join
			Person.EmailAddress as m on
				p.BusinessEntityID = m.BusinessEntityID ;

select * from Person.PersonMail ;

update Person.PersonMail
	set Emailaddress = 'a.leonetti@adventure-works.com'
	-- select * from Person.PersonMail
	where FirstName = 'A.' and
		LastName = 'Leonetti' ;

update Person.PersonMail
	set FirstName = 'Alexandra',
		Emailaddress = 'alex.wright@adventure-works.com'
	-- select * from Person.PersonMail
	where FirstName = 'A.' and
		LastName = 'Wright';

alter view Person.PersonMail as
	select p.Title, p.Firstname, p.LastName, m.Emailaddress
		from Person.Person as p left join
			Person.EmailAddress as m on
				p.BusinessEntityID = m.BusinessEntityID ;

create view Production.ProductOverview as
	select p.ProductID, p.Name,
			( select sum ( OrderQty ) 
				from Sales.SalesOrderDetail
				where ProductID = p.ProductID ) as Sales ,
			( select sum ( OnOrderQty ) 
				from 	Purchasing.ProductVendor
				where ProductID = p.ProductID ) as OnOrder ,
			( select sum ( StockedQty ) 
				from Production.WorkOrder
				where ProductID = p.ProductID ) as Stocked ,
			( select sum ( ScrappedQty )
				from Production.WorkOrder 
				where ProductID = p.ProductID ) as Scrapped ,
			( select sum ( Quantity )
				from Production.ProductInventory
				where ProductID = p.ProductID ) as Shelved
		from Production.Product as p ;

update Production.ProductOverview
	set OnOrder += 1
	where ProductID = 999 ;

create view Sales.SalesOverview with schemabinding as
	select sr.Name as Name, sr.SalesReasonID,
			hr.ModifiedDate, hr. SalesOrderID
		from Sales.SalesOrderHeaderSalesReason as hr inner join
			Sales.SalesReason as sr on
				hr.SalesReasonID = sr.SalesReasonID ;

create unique clustered index uq_SalesOverview on 	
	Sales.SalesOverview ( SalesOrderID , SalesReasonID );

create index ix_uq_SalesOverview on 
	Sales.SalesOverview ( Name, ModifiedDate ) ;

select * 
	from Sales.SalesOverview
	where Name in ( 'Price', 'Review', 'Quality' ) and
		ModifiedDate = '20060527' ;
go
