| 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. |
|
Answers
|
SELECT Name, SubTotal FROM Vendor INNER JOIN PurchaseOrderHeader ON Vendor.VendorID = PurchaseOrderHeader.VendorID ORDER BY Name |
| 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. |
|
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 |
| 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. |
|
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 |