Java Reference
In-Depth Information
FROM Customers
WHERE First_Name = 'Sonny');
One advantage of using a subquery is that you can easily test the subquery by itself
to make sure you are getting the correct data set. Then, once it checks out OK, you
can plug the subquery into the actual update command.
Using a subquery with the DELETE command
Finally, here's an example of the use of a subquery with DELETE. This example uses
a subquery to select the Employee_IDs of all employees so that they can be deleted
from the Customers Table:
DELETE FROM Customers
WHERE Customer_Number IN
(SELECT Employee_ID FROM Employees);
Sorting the Results of a Query
A common requirement when retrieving data from a database is to sort the results of
the query in alphabetic or numeric order on one or more of the columns. Results are
sorted using the ORDER BY clause in a statement like this:
SELECT First_Name, Last_Name, City, State
FROM CUSTOMERS
WHERE Last_Name = 'Corleone'
ORDER BY First_Name;
This gives you a list of all the Corleones sorted in ascending order by first name, as
shown in
Table 3-9
.
Table 3-9:
Records Sorted Using ORDER BY
First_Name
Last_Name
City
State
Francis
Corleone
New York
NY
Fredo
Corleone
New York
NY
Michael
Corleone
New York
NY
Sonny
Corleone
Newark
NJ
Vito
Corleone
Newark
NJ
The default sort order is ascending. This can be changed to descending by adding
the DESC keyword as shown here:
