Exercises

Exercise 1

Using Query Analyzer or SQL Server Management Studio, write a query to return Employee records from the AdventureWorks2000 database. Include only the FirstName, LastName, and EmailAddress columns in the result set. Execute this query and view the results.

Answers

 SELECT FirstName, LastName, EmailAddress  FROM Employee 

This query should return approximately 290 employee records.

Exercise 2

Return Employee records from the AdventureWorks2000 database. Combine the FirstName and LastName columns separated by a space, to return an aliased column called FullName. Return only the FullName and Title columns. Sort the results by the LastName and then FirstName columns in ascending order.

Answers

 SELECT FirstName + ' ' + LastName AS FullName, Title  FROM Employee ORDER BY LastName, FirstName 

There are multiple techniques for defining a column alias. This query returns two columns: FullName and Title. Note that the ORDER BY clause may contain columns that are not included in the result set.

Exercise 3

Return Product records that have a DiscontinuedDate value greater than or equal to December 4, 2002. Include the Name and ListPrice columns.

Answers

 SELECT Name, DiscontinuedDate FROM Product WHERE DiscontinuedDate >= '12-4-2002' 

Multiple date formats may be used. I chose to use the standard U.S. short date. Others include the ANSI and European standard formats.

This query returns 495 rows.

Exercise 4

Return a list of Department records including all columns.

Include only departments that have a Name value ending with the word Control. These records must also have a Name column value starting with the word Production. In addition to these records, include records that have a GroupName value ending in the word Assurance.

Sort these records by the Name column in reverse alphabetical order.

Answers

 SELECT * FROM Department WHERE (Name Like '%Control' AND Name Like 'Production%') OR  (GroupName Like '%Assurance') ORDER BY Name Desc 

The WHERE expression contains three criteria. Since the first two statements are enclosed in parentheses, these are processed before the third statement. The AND operator is restrictive and will only return the one record where the Name is Production Control.

The OR statement makes the result additive: results are added where the GroupName column ends with the text Assurance. All together, this query returns three rows.