The following statement returns employees who have more years of work experience than their department managers:
SELECT workers.name, workers.yrs_exp, workers.dept_no, managers.name, managers.yrs_exp FROM employee AS workers, employee AS managers WHERE managers.dept_no = workers.dept_no AND UPPER (managers.jobtitle) IN ('MANAGER' OR 'VICE PRES') AND workers.yrs_exp > managers.yrs_exp;
The FROM clause in the preceding statement enables the employee table to be processed as though it were two identical tables: one named workers and the other named managers.
As in a normal join operation, the WHERE clause defines the conditions of the join, establishing dept_no as the column whose values are common to both tables.
- A workers row must contain a dept_no value that matches the dept_no value in a managers row.
- The matching workers row must also contain a yrs_exp value that is greater than the yrs_exp value in the managers row.
The following result is returned:
name
---- |
yrs_exp
------ |
dept_no
------- |
name
---- |
yrs_exp
------ |
---|---|---|---|---|
Greene W | 15 | 100 | Jones M | 13 |
Carter J | 20 | 200 | Watson L | 8 |
Aguilar J | 11 | 600 | Regan R | 10 |
Leidner P | 13 | 300 | Phan A | 12 |
Ressel S | 25 | 300 | Phan A | 12 |