0

I am currently using the 'hr' database with Oracle 10g. You can view the schema diagram over here: http://docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63

What I would like is to retrieve the history of all employees who have worked in the 'Sales' department. I have selected only first name, last name, and job title for the working example but I'm getting the same job title where I should be getting two different ones.

This is the query I have so far:

SELECT a.first_name, a.last_name, b.job_title
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND a.job_id = b.job_id
AND c.department_id = d.department_id;

Any help would be appreciated. JazakAllah khayr.

  • Why do you use WHERE d.department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales') instead of a simple WHERE department_name = 'Sales'? – dezso Aug 27 '13 at 16:24
  • I've been doing so many of these that I didn't think twice about that. I'm helping a friend make sample questions for an introductory class. Thanks. – Khalid Hussain Aug 27 '13 at 16:30
1

An introduction to SQL queries but better you look for an introductory SQL text

I assume that every employee is in the job_history table and every employee and therefore job is assigned to a department. Otherwise one will loose some employees.

Let's display the columns of the tables involved:

select employee_id,first_name,last_name,department_id
from employees;

select department_id, department_name
from departments;

select job_id,employeed_id,department_id
from job_history;

select job_id,job_title
from jobs;

First a list of all employees and their departement information

select *
from employee e, departments d
where e.department_id=d.department_id;

now select only the rows from the employees that are in the department 'Sales'

select *
from employee e, departments d
where e.department_id=d.department_id
and d.department_name='Sales';

for these employees now find the jobs have already head fron the job_history

select *
from employee e, departments d, jobhistory h
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id;

Now for each job find the job title

select *
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

Now we display only the columns we need

select e.first_name,e.last_name,j.job_title
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

If we want to know in which department they worked in their job wee join another department table to get these information

select e.first_name,e.last_name,j.job_title,d2.department_name
from employee e, departments d, jobhistory h, jobs j,departments d2
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id
and h.depatment_id=d2.department_id;
  • Great Explanation..!!! – Dba Aug 28 '13 at 9:24
0

I've figured out the original problem. I should have constricted job_id from job_history and jobs instead of the original employees and jobs tables.

This would be the final answer:

SELECT a.employee_id, a.first_name, a.last_name, b.job_title, d.department_name
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND c.job_id = b.job_id
AND c.department_id = d.department_id;
0

A better answer, using ANSI Join syntax, which is significantly clearer:

SELECT e.employee_id, e.first_name, e.last_name, j.job_title, d.department_name
  FROM departments d
       inner join job_history jh
          on d.department_id = jh.department_id
       inner join employees e
          on jh.employee_id = e.employee_id
       inner join jobs j
          on jh.job_id = j.job_id
WHERE d.department_name = 'Sales';

The ANSI syntax helps prevent accidental cartesian products and clearly differentiates join criteria from filter criteria.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.