Question

Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees (as in Exer- cise 7.5a). Can we specify this query in SQL? Why or why not?

Answer 1

Answer:

The query may still be specified in SQL by using a nested query as follows (not all

implementations may support this type of query):

SELECT DNAME, COUNT (*)

FROM DEPARTMENT, EMPLOYEE

WHERE DNUMBER=DNO AND SEX='M' AND DNO IN ( SELECT DNO

FROM EMPLOYEE

GROUP BY DNO

HAVING AVG (SALARY) > 30000 )

GROUP BY DNAME;

Explanation: