Question

Problem 8.2 c from Databases Illuminated Third Edition:Assume you have a statistical database with the following schema. The only legal queries are those involving COUNT, SUM, and AVERAGE. newFaculty(facId, lastName, firstName, department, salary, rank, dateHired) a. Write a legal SQL query to find the salary of the only faculty member who is an instructor in the Art department. b. Assume the system will refuse to answer queries for which only one record satisfies the predicate as in (a). Write a legal set of queries that allows the user to deduce the salary of the Art instructor. c. Assume that there are 10 faculty members in the Art department. The system refuses to answer queries where the number of records satisfying the query is less than six. It will also refuse to answer pairs of queries where the number of records satisfying them simultaneously exceeds three. Would these restrictions make your query for (a) or (b) illegal? If so, is there another legal set of queries that will allow you to deduce the salary of the Art instructor?

Answer 1

a.) SELECT salary, COUNT(*), department

FROM newFaculty

WHERE department = 'Art'

GROUP BY department

b.) CREATE PROCEDURE deductAmount(@amountToDeduct)

AS BEGIN

SELECT (salary - @amountToDeduct) as salaryAfterDeducted, count(*), salary as originalSalary

FROM newFaculty

WHERE department IN ('Art')

GROUP BY department

  HAVING COUNT(*) = 1

END

C.) a) SELECT SUM(salary) as salary, facId, firstName, lastName

FROM newFaculty

WHERE department = 'Art'

GROUP BY facId

b) CREATE PROCEDURE deductAmount(@amountToDeduct, @facIdForDeduction)

AS BEGIN

SELECT (salary - @amountToDeduct) as salaryAfterDeducted, facId, SUM(salary) as originalSalary

FROM newFaculty

WHERE department = 'Art' AND facId = @facIdForDeduction

GROUP BY facId

END