Human Resource Schema Of Oracle10g

The following is the structure of the tables provided by Oracle in Human Resource Schema (HR).

Queries

1. Display employees who joined in 2000 and doing job that has maximum salary more than 10000.

   select  first_name,job_id,salary, hire_date
   from employees 
   where to_char(hire_date,'yyyy') = 2000
    and job_id in ( select job_id from jobs where max_salary > 10000)
   

2. Display departments where the name of the manager is MICHAEL.

   select * from departments
   where manager_id in 
     (select employee_id 
      from employees where  upper(first_name) like '%MICHAEL%')
   

3. Display jobs where the minimum salary is less than salary of employee 105.

   select * from jobs where min_salary <
    (select salary from employees
     where employee_id = 105)
   

4. Display employees who have underscore in their email address

   select * from employees where email like '%\_%' ESCAPE '\' 

5. Display employee name and manager name of the employee.

   select e1.first_name Employee, e2.first_name Manager from employees e1, employees e2
   where  e1.manager_id  = e2.employee_id
   

6. Display number of employees joined in each year into department 30.

   select to_char(hire_date,'yyyy') , count(*) from employees
   where department_id = 30 
   group by to_char(hire_date,'yyyy');
   

7. Display job id, department id and sum of salary by including al possible dimensions.

   select department_id department, job_id job, sum(salary) TotalSalary
   from employees
   group by cube( department_id, job_id)
   order by department_id, job_id;
   

8. Display employee name and job title of jobs where salary of employee is between minimum and maximum salary for job.

   select first_name, job_title
   from employees e, jobs j
   where salary between min_salary and max_salary
   order by first_name;
   

9. Display how many employees have commission percentage and how many do not have.

   select    count(commission_pct)  NoEmployeesWithCommission,
             count(*) - count(commission_pct)  NoEmployeesWithoutCommssion
   from employees
   

10. Display first name, job title, department name of employees who joined on 28th Feb.

    select first_name, job_title, department_name
    from employees e, jobs j, departments d
    where e.job_id = j.job_id and e.department_id = d.department_id
    and  to_char(hire_date,'ddmm') = '2802';