Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Appendix F. Answers to Exercises

Chapter 3

Exercise 1 Solution

SELECT ProductID, Name, ListPrice
FROM Production.Product
ORDER BY Name

Exercise 2 Solution

SELECT Production.ProductSubcategory.Name AS SubCategory
      ,Production.Product.Name AS ProductName
      ,Production.Product.ListPrice
FROM   Production.Product
INNER JOIN Production.ProductSubcategory
ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
ORDER BY SubCategory, ProductName

Exercise 3 Solution

SELECT * FROM Production.Product WHERE ListPrice > 4000

Rowcount = 13

Exercise 4 Solution

SQLCMD /S localhost /E
>1 USE AdventureWorks2008
>2 GO
>1 SELECT ProductCategoryID, Name FROM Production.ProductCategory
>2 GO

Chapter 4

Exercise 1 Solution

Figure F.1. Figure F-1

Exercise 2 Solution

Figure F.2. Figure F-2

Chapter 5

Exercise 1 Solution

SELECT NationalIDNumber
      ,LoginID
      ,JobTitle

,BirthDate
      ,MaritalStatus
      ,HireDate
FROM HumanResources.Employee

Exercise 2 Solution

SELECT NationalIDNumber
      ,LoginID
      ,JobTitle
      ,BirthDate
      ,MaritalStatus
      ,HireDate
      ,DATEDIFF(YY,BirthDate,HireDate) AS AgeAtHire
FROM HumanResources.Employee

Exercise 3 Solution

SELECT Name, ListPrice
FROM Production.Product
WHERE DaysToManufacture >= 3

or

SELECT Name, ListPrice
FROM Production.Product
WHERE DaysToManufacture > 2

Exercise 4 Solution

SELECT TOP 10 ...