1. Technology

Your suggestion is on its way!

An email with a link to:

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

was emailed to:

Thanks for sharing About.com with others!

Retrieving Data with SQL Queries
Part 2: Restricting Query Results
 More of this Feature
• Part 1: Introducing SELECT
• Part 3: Finishing Touches
 
 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 part of this feature, we looked at the general form of the SELECT statement and a simple query that retrieved all of the information contained within a table.  Let's go a step further and look at some queries that restrict the information retrieved. 

Retrieving Selected Columns from a Table  

Our last example produced a report for the Director of Human Resources that contained all of the salary and reporting information for every employee of XYZ Corporation.  There are several mid-level managers within the department that also require access to reporting information as part of their duties.  These managers do not need access to salary information, so we'd like to provide them with a report containing limited information from the database -- each employee's name, ID number and the ID number of their manager.

Here's a SQL SELECT statement that accomplishes the desired result:

SELECT EmployeeID, LastName, FirstName, ReportsTo
FROM employees

This query looks somewhat different from the previous one.  Notice that the asterisk wildcard has been replaced with a list of the column names we would like to include in our query results.  The Salary column is omitted to satisfy privacy concerns by limiting the information provided to mid-level managers.  Here's the output of this query:

EmployeeID LastName FirstName ReportsTo
---------- -------- --------- ---------
1 Smith John 2
2 Scampi Sue NULL
3 Kendall Tom 2
4 Jones Abraham 2
5 Allen Bill 4
6 Reynolds Allison 4
7 Johnson Katie 3

Retrieving Selected Rows from a Table

XYZ's President, Sue Scampi, would like a report providing detailed information on all of the employees that report directly to her.  To produce this report, we need to restrict the rows that appear in the query results through the use of a WHERE clause.  Let's look at the SQL code used to accomplish this result.

SELECT *
FROM employees
WHERE ReportsTo = 2

Notice that the wildcard has reappeared in the select_list in order to provide Ms. Scampi with the detailed report she requested.  We've added on a WHERE clause that limits the results to those rows where the ReportsTo field contains a value of 2 (Sue's Employee ID).  Here are the results of executing the above query:

 

Employee LastName FirstName Salary ReportsTo
-------- -------- --------- ------ ---------
1 Smith John 32000 2
3 Kendall Tom 29500 2
4 Jones Abraham 35000 2

After reviewing this report, Sue decides that she would like to further limit the results to those employees that earn a salary in excess of $30,000.  We can use a compound condition in the WHERE clause to achieve these results.  Here's the revised SQL query:

SELECT *
FROM employees
WHERE ReportsTo = 2 AND Salary > 30000

And the results of this query:

 

Employee LastName FirstName Salary ReportsTo
-------- -------- --------- ------ ---------
1 Smith John 32000 2
4 Jones Abraham 35000 2

Notice that Tom Kendall's record dropped out of the results because his salary did not meet the minimum requirement of $30,000.

In the final section of this article we'll look at two techniques used to enhance query results.  Read on!

Next page > Finishing Touches > 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.