Getting the employee history of Sales department from HR database in Oracle

oracleoracle-10g

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.

Best Answer

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;
Related Question