Chapter Seven

(image placeholder)
Chapter Seven
=================================
SET ECHO OFF
SET VERIFY OFF
DEFINE low_date = 01/01/1998
DEFINE high_date = 01/01/1999
SELECT  last_name ||', '|| job_id EMPLOYEES, hire_date
FROM    employees
WHERE   hire_date BETWEEN TO_DATE('&low_date', 'MM/DD/YYYY')
  AND   TO_DATE('&high_date', 'MM/DD/YYYY')
UNDEFINE low_date
UNDEFINE high_date
SET VERIFY ON
SET ECHO ON
(image placeholder)
=================================================
SET ECHO OFF
SET VERIFY OFF
COLUMN last_name HEADING "EMPLOYEE NAME"
COLUMN department_name HEADING "DEPARTMENT NAME"
SELECT  e.last_name, e.job_id, d.department_name
FROM    employees e, departments d, locations l
WHERE   e.department_id = d.department_id
AND     l.location_id = d.location_id
AND     l.city = INITCAP('&p_location')
/
COLUMN last_name CLEAR
COLUMN department_name CLEAR
SET VERIFY ON
SET ECHO ON

(image placeholder)
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
BREAK ON department_name
COLUMN department_name HEADING "DEPARTMENT|NAME"
COLUMN last_name HEADING "EMPLOYEE|NAME"
COLUMN hire_date HEADING "START|DATE"
COLUMN salary HEADING "SALARY" FORMAT $999,990.00
COLUMN asal HEADING "ANNUAL|SALARY" FORMAT $999,990.00

SELECT d.department_name, e.last_name, e.hire_date,
       e.salary, e.salary*12 asal
FROM   departments d, employees e, locations l
WHERE  e.department_id = d.department_id
AND    d.location_id   = l.location_id
AND    l.city          = '&p_location'
ORDER BY d.department_name
/
COLUMN department_name CLEAR
COLUMN last_name CLEAR
COLUMN hire_date CLEAR
COLUMN salary CLEAR
COLUMN asal CLEAR
CLEAR BREAK
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON
Chapter Six
2.
SELECT employee_id, last_name, salary
FROM   employees
WHERE  salary >
(SELECT AVG(salary)
                 FROM   employees)
ORDER BY salary

(image placeholder)

3.
SELECT employee_id, last_name
FROM   employees
WHERE  department_id IN (SELECT department_id
                         FROM   employees
                         WHERE  last_name like '%u%')

5.
SELECT last_name, salary
FROM   employees
WHERE  manager_id = (SELECT employee_id
                     FROM   employees
                     WHERE  last_name = 'King')

7. SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id IN (SELECT department_id
                         FROM   employees
                         WHERE  last_name like '%u%')
AND    salary > (SELECT AVG(salary)
                 FROM   employees)

(image placeholder)