|Retrieving Data with SQL Queries|
|Part 2: Restricting Query Results|
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,
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:
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.
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:
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:
And the results of this query:
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!