Wednesday, August 5, 2015

Solution
1.
Write a query to display the last name, department number, and salary of any employee whose department number and salary match the department number and salary of any employee who earns a commission.
SELECT last_name, department_id, salary
FROM employees
WHERE (salary, department_id) IN
(SELECT salary, department_id
FROM employees
WHERE commission_pct IS NOT NULL);

2.
Display the last name, department name, and salary of any employee whose salary and
job_ID match the salary and job_ID of any employee located in location ID 1700.

SELECT e.last_name, d.department_name, e.salary
FROM
employees e JOIN departments d
ON e.department_id = d.department_id
AND (salary, job_id) IN
(SELECT e.salary, e.job_id FROM employees e JOIN
departments d ON e.department_id = d.department_id
AND d.location_id = 1700);

3.
Create a query to display the last name, hire date, and salary for all employees who have the same salary and manager_ID as Kochhar.
Note: Do not display Kochhar in the result set.
SELECT last_name, hire_date, salary
FROM
employees
WHERE (salary, manager_id) IN
(SELECT salary, manager_id
FROM
employees
WHERE last_name = 'Kochhar')
AND last_name != 'Kochhar';


Solution 6-1: Retrieving Data by Using Subqueries
Create a query to display the employees who earn a salary that is higher than the salary of all the sales managers (JOB_ID = 'SA_MAN'). Sort the results on salary from the highest to the lowest.
SELECT last_name, job_id, salary
FROM
employees
WHERE salary > ALL
(SELECT salary
FROM
employees
WHERE job_id = 'SA_MAN')
ORDER BY salary DESC;

5.
Display details such as the employee ID, last name, and department ID of those
employees who live in cities the names of which begin with T.

SELECT employee_id, last_name, department_id
FROM
employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id IN
(SELECT location_id
FROM locations
WHERE city LIKE 'T%'));

6.
Write a query to find all employees who earn more than the average salary in their
departments. Display last name, salary, department ID, and the average salary for the
department. Sort by average salary and round to two decimals. Use aliases for the columns
retrieved by the query as shown in the sample output.
SELECT e.last_name ename, e.salary salary,
e.department_id deptno, ROUND(AVG(a.salary),2)
dept_avg
FROM
employees e, employees a
WHERE e.department_id = a.department_id
AND
e.salary > (SELECT AVG(salary)
FROM
employees
WHERE department_id = e.department_id )
GROUP BY e.last_name, e.salary, e.department_id
ORDER BY AVG(a.salary);


4.
Find all employees who are not supervisors.
a. First, do this by using the NOT EXISTS operator.
SELECT outer.last_name
FROM
employees outer
WHERE NOT EXISTS (SELECT 'X'
FROM employees inner
WHERE inner.manager_id =
outer.employee_id);
b.
Can this be done by using the NOT IN operator? How, or why not?
SELECT
FROM
WHERE
NOT IN
outer.last_name
employees outer
outer.employee_id
(SELECT inner.manager_id
FROM
employees inner);
This alternative solution is not a good one. The subquery picks up a NULL value, so the entire query returns no rows. The reason is that all conditions that compare a NULL value result in NULL. Whenever NULL values are likely to be part of the value set, do not use NOT IN as a substitute for NOT EXISTS. A much better solution would be a subquery like the following:
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees WHERE manager_id IS NOT
NULL);
8.
Write a query to display the last names of the employees who earn less than the average
salary in their departments.
SELECT last_name
FROM
employees outer
WHERE outer.salary < (SELECT AVG(inner.salary)
FROM employees inner
WHERE inner.department_id
= outer.department_id);
7.
Write a query to display the last names of employees who have one or more coworkers in
their departments with later hire dates but higher salaries.
SELECT last_name
FROM
employees outer
WHERE EXISTS (SELECT 'X'
FROM employees inner
WHERE inner.department_id =
outer.department_id
AND inner.hire_date > outer.hire_date
AND inner.salary > outer.salary);

10. Write a query to display the employee ID, last names, and department names of all employees. Note: Use a scalar subquery to retrieve the department name in the SELECT statement.
SELECT employee_id, last_name,
(SELECT department_name
FROM departments d
WHERE
e.department_id =
d.department_id ) department
FROM employees e
ORDER BY department;

11. Write a query to display the department names of those departments whose total salary cost is above one-eighth (1/8) of the total salary cost of the whole company. Use the WITH clause to write this query. Name the query SUMMARY.

WITH summary AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name)
SELECT department_name, dept_total
FROM summary
WHERE dept_total > ( SELECT SUM(dept_total) * 1/8
FROM summary )
ORDER BY dept_total DESC;