CS 4604 Homework #7

(Optional, Extra Credit)

1. (16 points) Question 4 from Fall 2000 final (both parts).
   
   
2. (25 points) Question 5 from Spring 2000 final.
   
   
3. (9 points) Consider the relation GraduatingStudents(id,name,age,starting_salary). Write an SQL query to find the median starting salary of graduating students. If you have forgotten what "median" means, it is one of the measures of central tendency. To find the median salary, think of the algorithm as first (i) arranging the salaries for all students in ascending order (including any repeats), (ii) picking the "middle" salary if the total number of students is odd or (ii) picking the average of the two "middle" salaries if the total number of students is even.
   
   The median is a better measure than the simple average because it is not so sensitive to outliers. For instance, universities and departments often proudly flaunt that the "average salary" of outgoing students is some ridiculous figure; but if you look more closely, you will find that the obscene amount is caused by a few players drafted for basketball or football. In such cases, the median salary gives a more realistic estimate of the salary.
   
   Hint: Use the fact that if you duplicate every row in the GraduatingStudents table, the median stays the same.
   
   If your database system provides a magic function called MEDIAN(*), obviously you are not expected to use it.