Comments on SQL Assignment

Question 7

7. Retrieve the average salary of all female employees who have dependents.

This question had more errors than any other. You cannot see the difference between the typical incorrect answer and a correct answer with the contents of the current database, but you can if you change the question to Retrieve the average salary of all male employees who have dependents. Try these and you will see:

     SELECT AVG(salary)  /* incorrect */
     FROM EMPLOYEE, DEPENDENT  /* a salary is included once */
     WHERE EMPLOYEE.sex = 'M'  /* for each dependent        */
     AND ssn = essn;

     SELECT AVG(salary)  /* correct */
     FROM EMPLOYEE             /* each salary included once */
     WHERE EMPLOYEE.sex = 'M'
     AND ssn in (select essn from DEPENDENT);

Question 11

For each department, list the department name and the total number of hours assigned to projects controlled by the department and the total number of hours assigned to employees of the department. Results in ascending alpha order.

This and #10 were the next most frequently missed. The question asks you to combine two queries and show the results for each department. The first query is the total number of hours assigned to projects controlled by the department. The second is the total number of hours assigned to employees of the department. For the first query,

select dnum, sum(NVL(hours,0)) phours 
                 /* project.dnum means the dept controls project */
		 /* phours is an alias used below                */
		 /* NVL(hours,0) replaces null hours with 0      */
from works_on, project
where pno=pnumber
group by dnum;

The second is similar

select dno, sum(NVL(hours,0)) ehours
from works_on, employee
where essn=ssn
group by dno;

The whole SQL statement selects from these by putting them on the FROM line:

select dname, phours, ehours
from   department,
       (select dnum, sum(NVL(hours,0)) phours 
        from works_on, project
        where pno=pnumber
        group by dnum),
       (select dno, sum(NVL(hours,0)) ehours
        from works_on, employee
        where essn=ssn
        group by dno)
where department.dnumber=dnum
and   department.dnumber=dno;

Note that the Marketing Department has no employees assigned to any project and it has no projects of its own so it does not show up in the answer. Therefore the above is not really a complete solution. No one got extra credit by finding a more complete answer.

Question 10

was also frequently missed:

Find the employees who are assigned to projects for a total of fewer than 40 hours. For each one, give the first and last name, the department name, and the total number of hours assigned. (When this list is used for the SQL assignment, also include a column showing the number of hours fewer than 40 for which the employee is assigned.) Results in ascending alpha order, first name within last name.

But that was mostly due to people missing required columns. I will just include the first line to show how to get the deficiency hours:

select fname, lname, dname, SUM(hours) total, 40 - SUM(hours) deficiency

By the way, most answers were incomplete as the line above will not get people who are assigned to no projects and therefore do not show up at all in the WORKS_ON table. To get them you need to UNION the above with

select fname, lname, dname, 0, 40

Question 9

was missed mostly because the underlined requirement was missed.

List the first and last name of employees who work on projects controlled by their department and only on projects controlled by their department. Results in ascending alpha order, first name within last name.

When a check for working on any project was not included, employees who were not assigned to any project were included in the answer.

Question 1

was missed, not because it was difficult, but because the question was not parsed by the student.

Retrieve the names of departments which have at least one employee who is employed on some other department's project for more than 10 hours. Results in ascending alpha order.

For this to work, the answer must include something like

employee.dno <> project.dnum

as well as

employee.dno = department.dnumber

and must NOT say

project.dnum = department.dnumber

Queston 4

was missed mostly through inattention. The question is:

Retrieve the first and last names of all supervisors who are not also department managers. Results in ascending alpha order, first name within last name.

One common mistake was to start out like

select fname, lname
from employee
where superssn ....

which gets the names of employees whose supervisors are not .... Several people invoked the impossible condition

 
where e.ssn = e.superssn

The remaining questions were missed so infrequently it is not worth discussing here.