/*Listing 1*/ SELECT wo.OrderQty , wo.StockedQty , wo.ScrappedQty , sr.Name AS ScrapReason , p.Name AS ProductName , p.ProductID FROM Production.WorkOrder AS wo JOIN Production.Product AS p ON p.ProductID = wo.ProductID JOIN Production.ScrapReason AS sr ON sr.ScrapReasonID = wo.ScrapReasonID WHERE p.ProductID = 904; GO /*Listing 2*/ SELECT soh.OrderDate , sod.OrderQty , sod.LineTotal FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN ( @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99 ); GO /*Listing 3*/ SELECT soh.OrderDate , sod.OrderQty , sod.LineTotal FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID JOIN @IDList AS il ON il.ID = soh.SalesOrderID; GO /*Listing 4*/ DECLARE @LocationName AS NVARCHAR(50); SET @LocationName = 'Paint'; SELECT p.Name AS ProductName , pi.Shelf , l.Name AS LocationName FROM Production.Product AS p JOIN Production.ProductInventory AS pi ON pi.ProductID = p.ProductID JOIN Production.Location AS l ON l.LocationID = pi.LocationID WHERE LTRIM(RTRIM(l.Name)) = @LocationName; GO /*Listing 5*/ CREATE INDEX ProductionLocation ON Production.ProductInventory(LocationID) INCLUDE (Shelf); GO /*Listing 6*/ SELECT pa.PersonName, pa.City, cea.EmailAddress, cs.DueDate FROM dbo.PersonAddress AS pa JOIN dbo.CustomerSales AS cs ON cs.CustomerPersonID = pa.PersonID LEFT JOIN dbo.ContactEmailAddress AS cea ON cea.ContactPersonID = pa.PersonID WHERE pa.City = 'Redmond '; GO /*Listing 6A*/ CREATE VIEW dbo.PersonAddress AS SELECT p.LastName + ', ' + p.FirstName AS PersonName, a.*, at.Name AS AddressTypeName, p.BusinessEntityID AS PersonID FROM Person.Person AS p JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID JOIN Person.Address AS a ON a.AddressID = bea.AddressID JOIN Person.AddressType AS at ON at.AddressTypeID = bea.AddressTypeID; GO CREATE VIEW dbo.ContactEmailAddress AS SELECT ct.Name AS ContactTypeName, p.LastName + ', ' + p.FirstName AS PersonName, ea.EmailAddress, bec.PersonID AS ContactPersonID FROM Person.BusinessEntityContact AS bec JOIN Person.Person AS p ON p.BusinessEntityID = bec.PersonID JOIN Person.ContactType AS ct ON ct.ContactTypeID = bec.ContactTypeID JOIN Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID; GO CREATE VIEW dbo.CustomerSales AS SELECT p.LastName + ', ' + p.FirstName AS PersonName, c.AccountNumber, soh.OrderDate, soh.DueDate, soh.ShipToAddressID, c.PersonID AS CustomerPersonID FROM Sales.Customer AS c JOIN Person.Person AS p ON p.BusinessEntityID = c.PersonID JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID; GO /*Listing 7*/ SELECT p.LastName + ', ' + p.FirstName AS PersonName , a.City , ea.EmailAddress , soh.DueDate FROM Person.Person AS p JOIN Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID JOIN Person.Address AS a ON a.AddressID = bea.AddressID LEFT JOIN Person.BusinessEntityContact AS bec ON bec.PersonID = p.BusinessEntityID JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID WHERE a.City = 'Redmond'; GO /*Listing 8*/ SELECT soh.OrderDate , soh.ShipDate , sod.OrderQty , sod.UnitPrice , p.Name AS ProductName FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID JOIN Production.Product AS p ON p.ProductID = sod.ProductID WHERE p.Name = 'Water Bottle - 30 oz.' AND sod.UnitPrice < $0.0; GO /*Listing 9*/ ALTER TABLE Sales.SalesOrderDetail WITH CHECK ADD CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK ((UnitPrice>=(0.00))); GO /*Listing 10*/ SELECT th.TransactionID , th.ProductID , p.Name FROM Production.TransactionHistory AS th JOIN Production.Product AS p ON p.ProductID = th.ProductID WHERE th.ProductID BETWEEN 400 AND 405; GO /*Listing 11*/ ALTER DATABASE AdventureWorks2014 SET AUTO_UPDATE_STATISTICS OFF; GO BEGIN TRAN UPDATE Production.TransactionHistory SET ProductID = 404 WHERE ProductID NOT BETWEEN 400 AND 405; SELECT th.TransactionID , th.ProductID , p.Name FROM Production.TransactionHistory AS th JOIN Production.Product AS p ON p.ProductID = th.ProductID WHERE th.ProductID BETWEEN 400 AND 405; UPDATE STATISTICS Production.TransactionHistory; SELECT th.TransactionID , th.ProductID , p.Name FROM Production.TransactionHistory AS th JOIN Production.Product AS p ON p.ProductID = th.ProductID WHERE th.ProductID BETWEEN 400 AND 405; ROLLBACK TRAN GO ALTER DATABASE AdventureWorks2014 SET AUTO_UPDATE_STATISTICS ON; GO