Solution for Test Your Knowledge 2

Test your knowledge by answering following questions.

Note:

1. Kindly use the below mentioned tables only from HR Schema.
2. Don’t create your own tables.
3. Sample out will be shown on for 10 records.
4. There are various ways to arrive at solution of the questions listed below. The solutions shared below is just to help you in arriving at a solution.

• COUNTRIES	• DEPARTMENTS	• EMPLOYEES	• JOBS
• JOB_HISTORY	• LOCATIONS	• REGIONS

1. Write a query in SQL to display the first name, last name, department number, and department name for each employee.

Solution:

SELECT E.first_name, E.last_name, E.department_id, D.department_name
  FROM hr.employees E
  JOIN hr.departments D
    ON E.department_id = D.department_id;

106 rows selected.

2. Write a query in SQL to display the first and last name, department, city, and state province for each employee.

Solution:

SELECT E.first_name, E.last_name, 
       D.department_name, L.city, L.state_province
  FROM hr.employees E 
  JOIN hr.departments D  
    ON E.department_id = D.department_id  
  JOIN hr.locations L
    ON D.location_id = L.location_id;

106 rows selected.

3. Write a query to display the first name, last name, salary, and job title for all employees.

Solution:

SELECT E.first_name, E.last_name, E.salary, d.job_title
  FROM hr.Employees E
  JOIN hr.jobs D 
    on e.job_id = d.job_id;

107 rows selected.

4. Write a query to display the first name, last name, department number and department name, for all employees for departments 80 or 40.

Solution:

SELECT E.first_name, E.last_name, E.department_id, D.department_name
  FROM hr.employees E
  JOIN hr.departments D
    ON E.department_id = D.department_id
   AND E.department_id IN (40, 80);

35 rows selected.

5. Write a query to display those employees who contain a letter z to their first name and also display their last name, department, city, and state province.

Solution:

SELECT E.last_name, D.department_name, L.city, L.state_province 
  FROM hr.employees E
  JOIN hr.departments D  
    ON E.department_id = D.department_id  
  JOIN hr.locations L
    ON D.location_id = L.location_id
   AND upper(E.first_name) LIKE '%Z%';

3 rows selected.

6. Write a query to display all departments including those where does not have any employee.

Solution:

SELECT distinct d.DEPARTMENT_NAME
  FROM HR.DEPARTMENTS d
  LEFT JOIN HR.EMPLOYEES e
    ON d.DEPARTMENT_ID = e.DEPARTMENT_ID;  
    
--or

    
SELECT DEPARTMENT_NAME
  FROM HR.DEPARTMENTS;

27 rows selected.

7. Write a query to display the first and last name and salary for those employees who earn less than the employee earn whose number is 182.

Solution:

SELECT e.FIRST_NAME, e.LAST_NAME, e.SALARY
  From HR.EMPLOYEES e
 WHERE e.SALARY < (SELECT e.SALARY
                     FROM hr.EMPLOYEES e
                    WHERE e.EMPLOYEE_ID = 182) ;

5 rows selected.

8. Write a query to display the first name of all employees including the first name of their manager.

Solution:

SELECT E.first_name AS E_fName, M.first_name AS M_name
  FROM hr.employees E 
  JOIN hr.employees M
    ON E.manager_id = M.employee_id;

106 rows selected.

9. Write a query to display the department name, city, and state province for each department.

Solution:

SELECT D.department_name, L.city, L.state_province
  FROM hr.departments D
  JOIN hr.locations L
    ON D.location_id = L.location_id;

27 rows selected.

10. Write a query to display the first name, last name, department number and name, for all employees who have or have not any department.

Solution:

SELECT E.first_name, E.last_name, E.department_id
  FROM hr.employees E
  LEFT OUTER JOIN hr.departments D
    ON E.department_id = D.department_id
 order by E.department_id desc; 
/*Added order by clause to get result set with department id as null in the snapshot below*/

107 rows selected.

11. Write a query to display the first name of all employees and the first name of their manager including those who does not work under any manager.

Solution:

select a.first_name, B.First_Name manger_name
  from hr.employees a, hr.employees b
 where a.manager_id = b.employee_id(+);
 
--or
 
select a.first_name, B.First_Name manger_name
  from hr.employees a
  left join hr.employees b
    on a.manager_id = b.employee_id;

107 rows selected.

12. Write a query to display the first name, last name, and department number for those employees who works in the same department as the employee who holds the last name as Taylor.

Solution:

SELECT e.first_name, e.last_name, d.department_id
  From hr.EMPLOYEES e, hr.DEPARTMENTs d
 WHERE e.department_id = d.department_id
   AND e.department_id IN 
       (SELECT e.department_id
          From hr.EMPLOYEES e 
         WHERE upper(e.last_name) LIKE upper('%Taylor%'));

79 rows selected.

13. Write a query to display the job title, department name, full name (first and last name) of employee, and starting date for all the jobs which started on or after 1st January 1993 and ending with on or before 31 August 1997.

Solution:

SELECT jh.JOB_ID, d.DEPARTMENT_NAME, 
       e.FIRST_NAME || ' ' || e.LAST_NAME Full_Name
  From HR.EMPLOYEES e, HR.JOB_HISTORY jh,
       hr.departments d
 WHERE e.EMPLOYEE_ID = Jh.EMPLOYEE_ID 
   and e.department_id = d.department_id
   and jh.START_DATE BETWEEN 
       to_date('01-01-1993','dd-mm-yyyy') and 
       to_date('31-08-1997','dd-mm-yyyy');

1 row selected.

14. Write a query to display job title, full name (first and last name) of employee, and the difference between maximum salary for the job and salary of the employee.

Solution:

select j.JOB_TITLE, 
       e.FIRST_NAME ||' '|| e.LAST_NAME Full_name, 
       (j.max_salary - e.SALARY) as Diff
  from hr.jobs j, hr.employees e 
 where e.JOB_ID = j.JOB_ID;

107 rows selected.

15. Write a query to display the name of the department, average salary and number of employees working in that department who got commission.

Solution:

SELECT D.department_name, AVG(salary) Avg_Salary, 
       COUNT(commission_pct) NoOfEmployees
  FROM hr.departments D
  JOIN hr.employees E
    ON D.department_id = E.department_id
 GROUP BY department_name;

11 rows selected.

16. Write a query to display the full name (first and last name) of employees, job title and the salary differences to their own job for those employees who is working in the department ID 80.

Solution:

 SELECT E.first_name || ' ' || E.last_name full_name,
        J.job_title, max_salary - salary AS sal_diff
   FROM hr.employees E
natural JOIN hr.jobs j
  WHERE e.department_id = 80;

34 rows selected.

17. Write a query to display the name of the country, city, and the departments which are running there.

Solution:

SELECT C.country_name, L.city, D.department_name
  FROM hr.countries C
  JOIN hr.locations L
    ON C.country_id = L.country_id
  JOIN hr.departments D
    ON L.location_id = D.location_id;

27 rows selected.

18. Write a query to display department name and the full name (first and last name) of the manager.

Solution:

Select D.department_name, 
       e.First_name || ' ' || e.last_name AS FULL_NAME
  FROM hr.departments D
  JOIN hr.employees E
    ON D.manager_id = E.employee_id;

11 rows selected.

19. Write a query to display job title and average salary of employees.

Solution:

SELECT J.job_title, avg_sal
  FROM (select job_id, avg(salary) avg_sal
          from hr.employees
         group by job_id) E
  JOIN hr.jobs J
    ON E.job_id = J.job_id;

19 rows selected.

20. Write a query to display the details of jobs which was done by any of the employees who is presently earning a salary on and above 12000.

Solution:

select jh.* 
  from HR.EMPLOYEES e, HR.JOB_HISTORY jh
 where e.JOB_ID = jh.JOB_ID 
   and e.salary >= 12000;

4 rows selected.

21. Write a query to display the country name, city, and number of those departments where at least 2 employees are working.

Solution:

select c.COUNTRY_NAME, l.CITY, d.department_id
  from hr.countries c, hr.locations l, 
       hr.departments d 
 where c.COUNTRY_ID = l.COUNTRY_ID
   and d.location_id = l.location_id
   and d.department_id IN
       (SELECT department_id 
          FROM hr.employees 
         GROUP BY department_id 
        HAVING COUNT(department_id) > 1);

8 rows selected.

22. Write a query to display the department name, full name (first and last name) of manager, and their city.

Solution:

SELECT D.department_name, 
       e.first_name || ' ' || E.last_name AS MANAGER,
       L.city
  FROM hr.departments D 
  JOIN hr.employees E
    ON D.manager_id = E.employee_id
  JOIN hr.locations L 
    ON D.location_id = L.location_id;

11 rows selected.

23. Write a query to display the employee ID, job name, number of days worked in for all those jobs in department 80.

Solution:

SELECT jh.employee_id, J.job_title, 
       JH.end_date - JH.start_date AS DAYS
  FROM hr.job_history JH
  JOIN hr.jobs J 
    on Jh.JOB_ID = J.JOB_ID
 WHERE jh.department_id = 80;

2 rows selected.

24. Write a query to display the full name (first and last name), and salary of those employees who working in any department located in London.

Solution:

SELECT E.first_name || ' ' ||E.last_name AS FULL_NAME,
       E.salary
  FROM hr.employees E
  JOIN hr.departments D
    ON E.department_id = D.department_id
  JOIN hr.locations L
    ON D.location_id = L.location_id
 WHERE city = 'London';

1 row selected.

25. Write a query to display full name (first and last name), job title, starting and ending date of last jobs for those employees with worked without a commission percentage.

Solution:

SELECT E.first_name || ' ' || E.last_name AS Full_name,
       J.job_title,
 Jh.start_date,JH.end_date
  FROM hr.employees E
  JOIN hr.jobs J
    ON E.job_id = J.job_id
  join hr.job_history jh
    on e.job_id =jh.job_id
   and e.employee_id = jh.employee_id
 where nvl(e.commission_pct, 0) = 0;

1 row selected.

26. Write a query to display the department name and number of employees in each of the department.

Solution:

select d.department_name, e.kount
  from hr.departments d
  join (select department_id, count(1) kount
          from hr.employees 
         group by department_id) e 
    on e.department_id = d.department_id;

11 rows selected.

27. Write a query to display the full name (first and last name) of employee with ID and name of the country presently where (s)he is working.

Solution:

SELECT E.first_name || ' ' || E.last_name AS Full_name,
       E.employee_id, c.country_name
  FROM hr.employees E
  JOIN hr.departments D
    ON E.department_id = D.department_id
  JOIN hr.locations L
    ON D.location_id = L.location_id
  JOIN hr.countries C
    ON L.country_id = C.country_id;	

106 rows selected.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.