3

Answers to Exercises: Sort Your Query Results

Question 1

Write a statement to select Employee NationalIDNumber, MaritalStatus, BirthDate and JobTitle, sorted by BirthDate.

SELECT   NationalIDNumber, 
         MaritalStatus, 
         BirthDate, 
         JobTitle
FROM     HumanResources.Employee
ORDER BY BirthDate

Question 2

Write a statement to select Person first and last names ordered by the upper case equivalent of their last name.   Remember:  We discussed UPPER in the previous lesson.

SELECT   FirstName,
         LastName
FROM     Person.Person
ORDER BY Upper(LastName)

Question 3

Select the first two names to appear in a sort of Person LastNames.

SELECT   TOP 2 FirstName,
         LastName
FROM     Person.Person
ORDER BY LastName

Question 4

Select the last three names to appear in a sort of Person LastNames.

 SELECT  TOP 3 FirstName,
         LastName
FROM     Person.Person
ORDER BY LastName DESC
  • Kait says:

    I put the UPPER on the column names in the SELECT statement like you had in the lesson post, and it returned the names in allcaps. But here, you have it in the ORDER BY statement instead, and when I tried the query you have here, it only returned the names as they’re normally capitalized. Is that supposed to happen? I guess I’m not sure exactly what the exercise question wanted returned. Should it have returned the names in allcaps, or just sentence case?

  • Connie says:

    I used DISTINCT so as to not repeat names. Using your example for Question 4, I get Michael Zwilling twice unless I use DISTINCT.

    SELECT TOP 3 FirstName,
    LastName
    FROM Person.Person
    ORDER BY LastName DESC

    Michael Zwilling
    Michael Zwilling
    Jake Zukowski

    vs:

    SELECT distinct TOP 3 FirstName,
    LastName
    FROM Person.Person
    ORDER BY LastName DESC

    Michael Zwilling
    Jake Zukowski
    Judy Zugelder

    • Hi,

      That is a good observation. The reason is that, in the AdventureWorks DB, that same person can be listed multiple times for different purposes. It has to do with the person type. Each entry has a unique BusinessEntityID.

      Kris.

  • >