Now You Try It
Enter a SELECT statement to list employee IDs, salary, bonus percentage, and bonus paid, taken from the POSITION table. Sort the result by the bonus paid. Use numbers to identify the columns in the ORDER BY clause and rename the columns appropriately.
The result looks like this:
EMPLOYEE BASE SALARY BONUS PERCENTAGE BONUS PAID -------- ----------- ---------------- ---------- 4001 36921.00 0.230 8491.83000 4660 36400.00 0.250 9100.00000 3991 42016.00 0.235 9873.76000 5090 48568.48 0.205 9956.53840 3767 50440.50 0.230 11601.31500 3764 54184.00 0.260 14087.84000 2448 70720.00 0.255 18033.60000 2010 76440.00 0.275 21021.00000 3082 68016.00 <null> <null> 2424 <null> <null> <null> 2246 29536.00 <null> <null> 2246 59488.00 <null> <null> 1034 <null> <null> <null> . . . 61 rows processed
If your results do not match what you see above, check Review Answers for Chapter 3 for the correct SQL syntax. Remember that result tables may be shortened in this guide.
Another Way to Name the Column
You've seen that you can use ORDER BY with column names and column numbers. You can also use ORDER BY with a column heading you named with AS.
How It's Done
The Human Resources department needs to see a list of all consultants ordered by the hourly compensation taken from the lowest rate to the highest. You now have three ways you can retrieve this information. Try all three:
select con_id as "Consultant",
rate as "Hourly Rate"
from consultant
order by rate desc;
select con_id as "Consultant",
rate as "Hourly Rate"
from consultant
order by 2 desc;
select con_id as "Consultant",
rate as "Hourly Rate"
from consultant
order by "Hourly Rate" desc;
For each of these,
The result looks like this:
CONSULTANT HOURLY RATE ---------- ----------- 9000 148.00 9388 76.00 9443 50.00 9439 47.00 4 rows processed
You can use any of these methods or a combination of them in an ORDER BY clause.
|
Copyright © 2014 CA.
All rights reserved.
|
|