Using HTML_Table with Subqueries

(Page 1 of 2 )

In this fourth part of a five-part series on using HTML_Table with advanced queries, you'll learn how to perform comparisons and database maintenance with subqueries. This article is excerpted from chapter 34 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Performing Comparisons with Subqueries

Subqueries are also very useful for performing comparisons. For example, suppose that the HR department wants to know who makes more money than the average salary in the company. The following query will give the HR department what it needs:

select employee_id, first_name, last_name, salary from employees
   where salary > (select avg(salary) from employees);

You’re free to use any of the comparison operators and aggregation functions when creating subqueries; if the query returns a single row with a single column, a query enclosed in parentheses is the equivalent of a constant or another column in a comparison operation.

Determining Existence with Subqueries

Building on the HR department theme, the database supports employment history in the JOB_HISTORY table. An installalation of Oracle Database XE includes this table with data in the HR schema. The structure of the table is as follows:

SQL> describe job_history;

--------------------------------------------
Name                   Null?    Type
------------------     -------  ----------
EMPLOYEE_ID            NOT NULL NUMBER(6)
START_DATE             NOT NULL DATE
END_DATE               NOT NULL DATE
JOB_ID                 NOT NULL VARCHAR2(10)
DEPARTMENT_ID                   NUMBER(4)
--------------------------------------------

A given employee will be in the table only if they have changed jobs since they started with the company. Their current job and department is recorded in theEMPLOYEEStable. When the employee changes jobs or departments, a new row is added to this table. Now suppose that you want to determine which employees have changed jobs more than once. To find out, you can use theEXISTSclause in conjunction with a subquery to easily retrieve this information:

select employee_id, first_name, last_name, salary from employees
   where exists (select count(*) from job_history
                 where job_history.employee_id = employees.employee_id
                 having count(*) > 1);

This query produces the results in Figure 34-5.

Figure 34-5.  Employees with more than one job change

The subquery only retrieves counts from theJOB_HISTORYtable that have more than one row for a given employee linked to the main query in theWHERE condition. Similarly, you can determine which employees have not changed jobs at the company by using theNOT EXISTSclause in conjunction with a subquery:

select employee_id, first_name, last_name, salary from employees
   where not exists (select * from job_history
                     where job_history.employee_id =
                     employees.employee_id);

This produces the output in Figure 34-6; more than ten employees at the company have never changed jobs.

Even more simply, you can find out which employees have not changed jobs by using theNOT INclause:

select employee_id, first_name, last_name, salary from employees
   where employee_id not in (select employee_id from job_history);

Figure 34-6.  Employees with no job changes

Next: Database Maintenance with Subqueries >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus