This page provides exercises and solutions to help you practice SQL JOIN methods. These exercises are based on the HR database, and may be performed online or by running the sample schema scripts on your local database server. For additional exercises in other subjects, use this link.
 

Inner JOIN Practice

  1. Employees and departments  (Employees & Departments tables)
    1. For each employee, display the first name, last name, department number and department name.
    2. Display the first name, last name, department number and department name, for all employees in departments 50 or 90.
  2. Departments and locations (Departments, Employees & Locations tables)
    1. For each department, display the department name, city, and state province.
    2. For each employee, display the full name, department name, city, and state province.
    3. Display the full name, department name, city, and state province, for all employees whose last name contains the letter a.

 

None Equi JOIN Practice

  1. For each employee, display the first name, salary, and job grade (Employees & Job_Grades tables)

 

Outer JOIN Practice

  1. Employees & departments
    1. Display the first name, last name, department number and department name, for all employees including those without any department.
    2. Modify your query to display all departments including departments without any employees.

 

Self JOIN Practice

  1. Employees and managers (Employees table)
    1. For each employee, display the last name, and the manager’s last name.
    2. Modify your query to display all employees including those without any manager.
  2. Display the first name, last name, and department number for all employees who work in the same department as employee whose last name is “King”.
  3. Display the last name and salary for all employees who earn less than employee number 103.

 

Solutions – Oracle

These solutions apply to Oracle, for solutions that apply to SQL Server click here.

-- 1. 
-- a 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,    dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id 
-- b 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id 
AND 
      emp.department_id IN (50 , 90)
ORDER BY emp.last_name 
-- 2.
-- a 
SELECT dep.department_name , loc.city , loc.state_province
FROM   departments dep , locations loc 
WHERE  dep.location_id = loc.location_id
 -- b 
SELECT emp.last_name || ' '|| emp.first_name AS "FULL_NAME", 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
              dep.location_id = loc.location_id 
-- c 
SELECT emp.last_name || ' '|| emp.first_name AS "FULL_NAME", 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp , departments dep , locations loc 
WHERE         emp.department_id = dep.department_id  
AND
             dep.location_id = loc.location_id 
AND          emp.last_name LIKE  '%a%'
-- 3. 
SELECT emp.last_name , emp.salary , job_g.grade_level
FROM   employees emp , job_grades job_g
WHERE  emp.salary BETWEEN job_g.lowest_sal AND job_g.highest_sal
-- 4.
-- a 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id = dep.department_id (+)
-- b 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp , departments dep 
WHERE emp.department_id (+) = dep.department_id 
-- 5.
-- a 
SELECT emp.last_name AS "EMPLOYEE_NAME", 
       mng.last_name AS "MANAGER_NAME"
FROM   employees emp , employees mng
WHERE  emp.manager_id = mng.employee_id
-- b 
SELECT emp.last_name AS "EMPLOYEE_NAME", mng.last_name AS "MANAGER_NAME"
FROM   employees emp , employees mng
WHERE  emp.manager_id = mng.employee_id (+)
-- 6.
SELECT emp.last_name , emp.first_name , emp.department_id 
FROM   employees emp , employees specific_employee
WHERE  emp.department_id = specific_employee.department_id
AND    specific_employee.last_name = 'King'
-- AND emp.last_name <> ‘King’
-- 7.
SELECT emp.last_name , emp.salary 
FROM   employees emp , employees specific_emp
WHERE  emp.salary < specific_emp.salary 
AND    specific_emp.employee_id = 103

Solutions – SQL Server

-- 1. 
-- a 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,    dep.department_name 
FROM employees emp JOIN departments dep 
ON emp.department_id = dep.department_id 
-- b 
SELECT emp.first_name , emp.last_name , 
       emp.department_id ,  dep.department_name 
FROM employees emp JOIN departments dep 
ON emp.department_id = dep.department_id 
AND 
      emp.department_id IN (50 , 90)
ORDER BY emp.last_name 
-- 2.
-- a 
SELECT dep.department_name , loc.city , loc.state_province
FROM   departments dep JOIN locations loc 
ON  dep.location_id = loc.location_id
 -- b 
SELECT emp.last_name + ' '+ emp.first_name AS 'FULL_NAME', 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp JOIN departments dep  
ON         emp.department_id = dep.department_id  
       JOIN locations loc
ON     dep.location_id = loc.location_id 
-- c 
SELECT emp.last_name + ' '+ emp.first_name AS 'FULL_NAME', 
       dep.department_name , loc.city , loc.state_province
FROM   employees emp JOIN departments dep  
ON         emp.department_id = dep.department_id 
        JOIN locations loc 
ON      dep.location_id = loc.location_id 
WHERE   emp.last_name LIKE  '%a%'
-- 3. 
SELECT emp.last_name , emp.salary , job_g.grade_level
FROM   employees emp JOIN job_grades job_g
ON  emp.salary BETWEEN job_g.lowest_sal AND job_g.highest_sal
-- 4.
-- a 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp LEFT OUTER JOIN departments dep 
ON emp.department_id = dep.department_id
-- b 
SELECT emp.first_name , emp.last_name , emp.department_id ,  dep.department_name 
FROM employees emp RIGHT OUTER JOIN departments dep 
ON emp.department_id = dep.department_id 
-- 5.
-- a 
SELECT emp.last_name AS 'EMPLOYEE_NAME', 
       mng.last_name AS 'MANAGER_NAME'
FROM   employees emp JOIN employees mng
ON  emp.manager_id = mng.employee_id
-- b 
SELECT emp.last_name AS 'EMPLOYEE_NAME', mng.last_name AS 'MANAGER_NAME'
FROM   employees emp LEFT OUTER JOIN employees mng
ON  emp.manager_id = mng.employee_id
-- 6.
SELECT emp.last_name , emp.first_name , emp.department_id 
FROM   employees emp JOIN employees specific_employee
ON  emp.department_id = specific_employee.department_id
AND    specific_employee.last_name = 'King'
-- AND emp.last_name <> ‘King’
-- 7.
SELECT emp.last_name , emp.salary 
FROM   employees emp JOIN employees specific_emp
ON  emp.salary < specific_emp.salary 
AND  specific_emp.employee_id = 103