Chapter 8 Exercises


Exercise 1

Create a list of vendors and the subtotal amounts for their purchase orders, sorted by vendor names. This list should include the vendor name and the subtotal amount for all vendors who have purchase orders recorded in the PurchaseOrderHeader table.

image from book

Answers

 SELECT Name, SubTotal  FROM Vendor INNER JOIN PurchaseOrderHeader  ON Vendor.VendorID = PurchaseOrderHeader.VendorID  ORDER BY Name 

Exercise 2

In Chapter 7, exercise 3; you wrote a query to return the average pay rate for employees by gender for their managers. Extend this query and replace the ManagerID with the manager's name. Concatenate the manager's first and last name, separated with a space, from the employee table. Create an alias for this value called ManagerName. Create an alias to refer to the employee table used to obtain the managers' names called Managers.

image from book

Answers

 SELECT  Managers.FirstName + ‘ ‘ + Managers.LastName AS ManagerName , Employee.Gender, AVG(Employee.BaseRate) FROM Employee INNER JOIN Employee AS Managers ON Employee.ManagerID = Managers.EmployeeID GROUP BY  Managers.FirstName + ‘ ‘ + Managers.LastName , Employee.Gender WITH ROLLUP 

Exercise 3

Write a query that returns a list of product subcategories and related products that don't have any sales order detail records. Include two columns including the subcategory name labeled SubCategoryName and the product name labeled ProductName.

image from book

Answers

 SELECT  ProductSubCategory.Name AS SubCategoryName , Product.Name AS ProductName FROM ProductSubCategory INNER JOIN Product ON ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID LEFT OUTER JOIN SalesOrderDetail ON Product.ProductID = SalesOrderDetail.ProductID WHERE SalesOrderDetail.ProductID IS NULL 




Beginning Transact-SQL with SQL Server 2000 and 2005
Beginning Transact-SQL With SQL Server 2000 and 2005
ISBN: 076457955X
EAN: 2147483647
Year: 2006
Pages: 131
Authors: Paul Turley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net