1. Tech

Your suggestion is on its way!

An email with a link to:

http://databases.about.com/od/careers/l/aaretrieve3.htm

was emailed to:

Thanks for sharing About.com with others!

Retrieving Data with SQL Queries
Part 3: Finishing Touches
 More of this Feature
• Part 1: Introducing SELECT
• Part 2: Restricting Results
 
 Join the Discussion
"Share your questions and  tap the knowledge of hundreds of your peers."
Mike Chapple
 
 Related Resources
• SQL Fundamentals
• Creating Databases and Tables in SQL
• SQL Resources 

In the first two sections of this lesson, we examined basic database queries and more powerful statements that restrict the query results.  Now let's look at two techniques used to enhance the display of query output.

Renaming Columns in Query Results  

All too often, database tables contain cryptic column headings that don't make sense to users outside of a company's IT department. Fortunately, SQL provides a mechanism that allows us to change the headings displayed in query output for enhanced readability.

For example, let's look at a database query that displays the name of every employee of XYZ Corporation along with their annual salary.  Our database simply labels the compensation column "Salary" but doesn't clarify the time period involved.  Here's a query that straightens things out:

SELECT LastName, FirstName, Salary AS 'Annual Salary'
FROM employees

Notice that the third field in the SELECT clause is slightly different from previous examples.  The AS statement modifies the column heading used in the query output.  It's necessary to enclose this heading in single quotes to incorporate the space character.  Here's the output of this query:

EmployeeID LastName FirstName Annual Salary ReportsTo
---------- -------- --------- ------ ---------
1 Smith John 32000 2
2 Scampi Sue 45000 NULL
3 Kendall Tom 29500 2
4 Jones Abraham 35000 2
5 Allen Bill 17250 4
6 Reynolds Allison 19500 4
7 Johnson Katie 21000 3

Removing Duplicate Values from Query Results

For our final challenge, the Human Resources Director would like us to produce a report containing the employee ID numbers of each employee that has subordinates.  We can obtain this information using the following query:

SELECT ReportsTo
FROM employees

That produces the following results:

ReportsTo
---------
2
NULL
2
2
4
4
3

Unfortunately, life's not that simple and there's a catch.  The HR Manager took one look at the query results and asked why employee IDs appeared multiple times.  We're sent back to the drawing board with a request to remove the duplicate values.  This is accomplished by inserting the DISTINCT keyword in the select_list as follows:

SELECT DISTINCT ReportsTo
FROM employees

That produces the following results:

ReportsTo
---------
2
NULL
4
3

The duplicate ID numbers have been eliminated and we're left with the desired report.  Notice that the NULL value appears in the query output.  NULL is considered a unique value and will appear once (and only once) in query output when the DISTINCT keyword is used.

That's it for this lesson.  Now get out there and practice some SQL!

Next page > > Page 1, 2, 3 



 

You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

©2014 About.com. All rights reserved.