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:

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