Now You Try It
The Corporate Marketing department is considering revamping the bonus system. They want a report showing employee IDs, how much salary they earned and, if any, the bonus percentage and how much bonus each employee earned. This information is maintained in the POSITION table as SALARY_AMOUNT and BONUS_PERCENT.
Enter a SELECT statement to display this information. Rename the columns appropriately.
The result looks like this:
EMPLOYEE SALARY BONUS PERCENTAGE BONUS PAID -------- ------ ---------------- ---------- 3411 53665.00 <null> <null> 3411 44001.40 <null> <null> 4773 45240.00 <null> <null> 2010 76440.00 0.275 21021.00000 3338 22048.84 <null> <null> 2246 59488.00 <null> <null> 2246 29536.00 <null> <null> 1034 <null> <null> <null> 2424 <null> <null> <null> 3767 50440.50 0.230 11601.31500 3767 2200.00 <null> <null> 3449 74776.00 <null> <null> 3082 68016.00 <null> <null> 3341 48465.80 <null> <null> 4660 36400.00 0.250 9100.00000 4660 24000.00 <null> <null> 2209 66144.00 <null> <null> 2894 111593.00 <null> <null> 4001 36921.00 0.230 8491.83000 5090 48568.48 0.205 9956.53840 1765 47009.34 <null> <null> 4456 <null> <null> <null> 1765 18001.00 <null> <null> 3991 42016.00 0.235 9873.76000 3991 27976.00 <null> <null> 3778 <null> <null> <null> 4358 57824.50 <null> <null> 4962 30680.00 <null> <null> 2180 76961.00 <null> <null> 2180 19000.10 <null> <null> 2106 23920.00 <null> <null> 3222 110448.00 <null> <null> 4002 28601.80 <null> <null> 2437 <null> <null> <null> 2096 85280.00 <null> <null> 2096 <null> <null> <null> 2004 59280.00 <null> <null> 2004 <null> <null> <null> 5103 <null> <null> <null> 5008 47944.00 <null> <null> 4321 56977.80 <null> <null> 2598 <null> <null> <null> 3764 54184.00 0.260 14087.84000 3764 28912.00 <null> <null> 2448 70720.00 0.255 18033.60000 2461 43784.00 <null> <null> 1234 117832.68 <null> <null> 1003 146432.00 <null> <null> 4027 28081.40 <null> <null> 2466 94953.52 <null> <null> 2174 49921.76 <null> <null> 2781 43888.00 <null> <null> 3704 22880.00 <null> <null> 4008 24441.00 <null> <null>
3433 <null> <null> <null> 3288 <null> <null> <null> 3841 33800.00 <null> <null> 4703 24857.00 <null> <null> 3294 53665.56 <null> <null> 3769 41600.00 <null> <null> 3118 45241.94 <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.
Why the '<null>' entries?
Most positions are not sales positions and do not have bonuses attached.
Using Parentheses
Use parentheses to specify the order in which you want the arithmetic evaluation to take place.
How It's Done
You have been asked to produce a report that shows what weekly salaries would look like before and after a raise of $1,000 per year. Show yearly salary as well.
Try this without using parentheses first:
select salary_amount,
salary_amount / 52 as "Current Wkly Sal",
salary_amount + 1000 / 52 as "Adjusted Wkly Sal"
from position;
The result looks like this:
SALARY_AMOUNT CURRENT WKLY SAL ADJUSTED WKLY SAL ------------- ---------------- ----------------- 53665.00 1032.019 53684.00 44001.40 846.180 44020.40 45240.00 870.000 45259.00 76440.00 1470.000 76459.00 22048.84 424.016 22067.84 59488.00 1144.000 59507.00 29536.00 568.000 29555.00 <null> <null> <null> <null> <null> <null> 50440.50 970.009 50459.50 2200.00 42.307 2219.00 74776.00 1438.000 74795.00 68016.00 1308.000 68035.00 48465.80 932.034 48484.80 36400.00 700.000 36419.00 24000.00 461.538 24019.00 66144.00 1272.000 66163.00 111593.00 2146.019 111612.00 36921.00 710.019 36940.00 48568.48 934.009 48587.48 47009.34 904.025 47028.34 <null> <null> <null> 18001.00 346.173 18020.00 42016.00 808.000 42035.00 27976.00 538.000 27995.00 <null> <null> <null> 57824.50 1112.009 57843.50 30680.00 590.000 30699.00 76961.00 1480.019 76980.00 19000.10 365.386 19019.10 23920.00 460.000 23939.00 110448.00 2124.000 110467.00 28601.80 550.034 28620.80 <null> <null> <null> 85280.00 1640.000 85299.00 <null> <null> <null> 59280.00 1140.000 59299.00 <null> <null> <null> <null> <null> <null> 47944.00 922.000 47963.00 56977.80 1095.726 56996.80 <null> <null> <null> 54184.00 1042.000 54203.00 28912.00 556.000 28931.00 70720.00 1360.000 70739.00 43784.00 842.000 43803.00 117832.68 2266.013 117851.68 146432.00 2816.000 146451.00 28081.40 540.026 28100.40 94953.52 1826.029 94972.52 49921.76 960.033 49940.76 43888.00 844.000 43907.00 22880.00 440.000 22899.00 24441.00 470.019 24460.00 <null> <null> <null> <null> <null> <null>
33800.00 650.000 33819.00 24857.00 478.019 24876.00 53665.56 1032.030 53684.56 41600.00 800.000 41619.00 45241.94 870.037 45260.94 61 rows processed
Is the result correct?
Take one salary and do your own pencil and paper calculation to check your answers:
24,000.00 + 1,000 = 25,000.00 / 52 = 480.76923
The result is wrong: In the calculation involving the increase, the division occurred before the addition instead of after. Remember that the default order of evaluation is multiplication and division, performed left to right, and then addition and subtraction, performed left to right.
Use parentheses to specify that you want the addition to take place before the division. Enter:
select salary_amount,
salary_amount / 52 as "Current Wkly Sal",
(salary_amount + 1000) / 52 as "Adjusted Wkly Sal"
from position;
The result looks like this:
SALARY_AMOUNT CURRENT WKLY SAL ADJUSTED WKLY SAL ------------- ---------------- ----------------- 53665.00 1032.019 1051.250 44001.40 846.180 865.411 45240.00 870.000 889.230 76440.00 1470.000 1489.230 22048.84 424.016 443.246 59488.00 1144.000 1163.230 29536.00 568.000 587.230 <null> <null> <null> <null> <null> <null> 50440.50 970.009 989.240 2200.00 42.307 61.538 74776.00 1438.000 1457.230 68016.00 1308.000 1327.230 48465.80 932.034 951.265 36400.00 700.000 719.230 24000.00 461.538 480.769 66144.00 1272.000 1291.230 111593.00 2146.019 2165.250 36921.00 710.019 729.250 48568.48 934.009 953.240 47009.34 904.025 923.256 <null> <null> <null>
18001.00 346.173 365.403 42016.00 808.000 827.230 27976.00 538.000 557.230 <null> <null> <null> 57824.50 1112.009 1131.240 30680.00 590.000 609.230 76961.00 1480.019 1499.250 19000.10 365.386 384.617 23920.00 460.000 479.230 110448.00 2124.000 2143.230 28601.80 550.034 569.265 <null> <null> <null> 85280.00 1640.000 1659.230 <null> <null> <null> 59280.00 1140.000 1159.230 <null> <null> <null> <null> <null> <null> 47944.00 922.000 941.230 56977.80 1095.726 1114.957 <null> <null> <null> 54184.00 1042.000 1061.230 28912.00 556.000 575.230 70720.00 1360.000 1379.230 43784.00 842.000 861.230 117832.68 2266.013 2285.243 146432.00 2816.000 2835.230 28081.40 540.026 559.257 94953.52 1826.029 1845.260 49921.76 960.033 979.264 43888.00 844.000 863.230 22880.00 440.000 459.230 24441.00 470.019 489.250 <null> <null> <null> <null> <null> <null> 33800.00 650.000 669.230 24857.00 478.019 497.250 53665.56 1032.030 1051.260 41600.00 800.000 819.230 45241.94 870.037 889.268 61 rows processed
How does this result match your written calculation?
If you did your written calculation correctly, it should match this result.
|
Copyright © 2014 CA.
All rights reserved.
|
|