Chapter 7 Application

Question 1

Code a query with subquery to project employee data for all with salary less than the average salary for all employees.

Answer 1

Select * from employee where salary < (select avg(salary) from employee);

Question 2

Prepare a nested query that projects employee name for all located in DALLAS (i.e. subquery to find, not “hard code”, the department number).

Answer 2

Select ename from employee where deptno = (select deptno from department where location = ‘DALLAS’);

Question 3

Use the IN operator in a query with subquery to project department names for those departments with an employee earning more than $3000.

Answer 3

select dName, deptno from department where deptno IN (select deptno from employee where salary > 3000);

Question 4

Use the ANY keyword in a query with subquery to show those departments with an employee earning less than $2000.*

Answer 4

select distinct(deptno) from employee where salary < ANY (select salary from employee where salary < 2000);

Question 5

Prepare a correlated subquery to project employee name for all employees in that department located in DALLAS.

Answer 5

select ename from employee where deptno = (select deptno from department where location = ‘DALLAS’);

Question 6

Do a correlated subquery with the EXISTS operator to list the names of departments that have employees.

Answer 6

select dName from department where deptno IN (select deptno from employee where ename IN (select ename from employee));

Question 7

Do a correlated subquery with the NOT EXISTS operator to list the names of departments that do not have employees.

Answer 7

select dName from department where NOT EXISTS (select deptno from employee where deptno = department.deptno);

Question 8

Prepare a nested query to project department name for the department having the lowest paid employee.

Answer 8

select dName from department where deptno = (select deptno from employee where salary = (select MIN(salary) from employee));