Assignment 14 - Advanced Query
Objectives
To be able to perform advanced query and reporting of results
Assignment
Using the Spreadsheet supplied 2010-2011JobPlacementDataFlorida.xlsx you are going to determine the best choices of programs to select a profession. Here is how you will rank these. Lower scores are better - scores are determined by the rank order of the profession for that field. For example;
| Program Title |
Number Completed |
Number Continued Education |
Percent Continued Education |
Number Employed |
Percent Employed |
Estimated Average Annual Full-Time Wage |
| Nursing RN |
4,605 |
1,467 |
31% |
4,014 |
87% |
$49,180 |
| Law Enforcement Officer |
2,161 |
575 |
26% |
1,815 |
83% |
$39,652 |
| Emergency Medical Technician (Basic)--ATD |
1,465 |
837 |
57% |
1,067 |
72% |
$30,488 |
| Educator Preparation Inst. |
1,363 |
88 |
6% |
1,143 |
83% |
$39,844 |
| Emergency Medical Technician - Basic Ccc |
1,300 |
738 |
56% |
938 |
72% |
$30,416 |
with the following data Nursing RN would rank 1 for Full-time wage, 1 for Percent Employed, 1 for number employed, 3 for percent continued education. The last 4 fields (underlined) will be the fields that you will use to determine the rank score. This rank score will be the sum of the rank for these fields so for Nursing RN the score would be 6.
You will import the entire spreadsheet and calculate a rank score for every profession - and report the top 10 with total score and rank score for each category. You should have a stored procedure that outputs this as a text report.
Information
Data was obtained from http://smart-college-choices.com/smart-college-choices.aspx
Estimated Completion Time
There are many ways to accomplish this report. The method I used took me just under one hour. Be clever here and you can make this a relatively easy assignment. Hint: I created 4 new fields in the database and wrote a simple SQL statement that populated them, once populated this was very easy.
Supporting Lectures
This uses all the skills that you have obtained through the course of the class.
Questions and Answers
External Resources
Grading Criteria
Comments (0)
You don't have permission to comment on this page.