Specify the following additional queries on the database of Figure 3.5 in SQL. Show the query results if applied to the database of Figure 3.6.

(a) For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

(b) Suppose we want the number of male employees in each department rather than all employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?


(a) SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
HAVING AVG (SALARY) > 30000

Result:
DNAME DNUMBER COUNT(*)
Research 5 4
Administration 4 3
Headquarters 1 1

(b) 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

Result:
DNAME DNUMBER COUNT(*)
Research 5 3
Administration 4 1
Headquarters 1 1

Computer Science & Information Technology

You might also like to view...

The smallest data item in a computer is the _________.

a) bit b) byte c) kilobyte d) None of the above.

Computer Science & Information Technology

In which gesture do you move two fingers together?

A. stretch B. pinch C. swipe D. slide

Computer Science & Information Technology

A ____ is a database stored on the network that contains information about users and network devices.

A. PKI B. biometric service C. Kerberos service D. directory service

Computer Science & Information Technology

Explain why and how you might print document properties.

What will be an ideal response?

Computer Science & Information Technology