16 Join Clauses

• ·         Allows a natural join based on an arbitrary condition or two columns with different names.-ON Clause
• ·         Performs an equijoin based on one specified column name-USING clause

Use the Oracle database for problems 1-6.

1.  Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.

SELECT department_id,department_name, location_id, city

FROM departments JOIN locations USING (location_id)

WHERE location_id = 1400;

2.  Join DJs on Demand d_play_list_items, d_track_listings, and d_cds tables with the JOIN USING syntax. Include the song ID, CD number, title, and comments in the output.

SELECT song_id, cd_number, title, comments

FROM d_cds JOIN d_track_listings USING (cd_number)  JOIN d_play_list_items USING (song_id);

3.  Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle.

SELECT city, department_name, location_id, department_id

FROM departments JOIN locations USING (location_id)

WHERE department_id in (10, 20 , 30) AND city = 'Seattle';

OR

SELECT loc.city, dp.department_name, dp.location_id, dp.department_id

FROM departments dp INNER JOIN locations  loc ON dp.location_id = loc.location_id

WHERE dp.department_id in (10, 20 , 30) AND loc.city = 'Seattle';

4.  Display country name, region ID, and region name for Americas.

SELECT country_name, region_id, region_name

FROM countries  JOIN regions USING(region_id)

WHERE region_name = 'Americas';

OR

SELECT ct.country_name, rg.region_id, rg.region_name

FROM countries ct INNER JOIN regions rg ON  ct.region_id = rg.region_id

WHERE rg.region_name = 'Americas';

5.  Write a statement joining the employees and jobs tables. Display the first and last names, hire date, job id, job title, and maximum salary. Limit the query to those employees who are in jobs that can earn more than $12,000.

SELECT  first_name, last_name, hire_date, job_id, job_title, max_salary

FROM employees  JOIN jobs USING (job_id)

WHERE max_salary > 12000;

OR

SELECT  emp.first_name, emp.last_name, emp.hire_date, emp.job_id, jb.job_title, jb.max_salary

FROM employees emp INNER JOIN jobs jb  ON  emp.job_id = jb.job_id

WHERE jb.max_salary > 12000;

6.  Display job title, employee first name, last name, and email for all employees who are stock clerks.

SELECT  job_title,  first_name,  last_name, LOWER(email) || 'somecomname.sometld' as email

FROM employees JOIN jobs USING(job_id)

WHERE  job_title = 'Stock Clerk' ;

OR

SELECT jb.job_title, emp.first_name, emp.last_name, LOWER(emp.email) || 'somecomname.sometld' as email

FROM employees emp INNER JOIN jobs jb  ON  emp.job_id = jb.job_id

WHERE jb.job_title = 'Stock Clerk' ;

The following questions use the JOIN…ON syntax:

7.  Write a statement that displays the employee ID, first name, last name, manager ID, manager first name, and manager last name for every employee in the employees table. Hint: this is a self-join.

SELECT emp.employee_id AS "employee ID", emp.first_name AS "first name", emp.last_name "last name", emp.manager_id "manager ID", mgr.first_name "manager first name", mgr.last_name "manager last name"

FROM  employees emp LEFT JOIN employees mgr ON  emp.manager_id = mgr.employee_id;

8.  Use JOIN ON syntax to query and display the location ID, city, and department name for all Canadian locations.

SELECT dp.location_id, loc.city, dp.department_name

FROM departments dp INNER JOIN locations  loc ON dp.location_id = loc.location_id  INNER JOIN  countries ct ON loc.country_id = ct.country_id

WHERE ct.country_name = 'Canada';

9.  Query and display manager ID, department ID, department name, first name, and last name for all employees in departments 80, 90, 110, and 190.

SELECT emp.manager_id "Employee's Manager ID", emp.department_id "Department ID", dpt.department_name  "Department Name", emp.first_name "First Name", emp.last_name "Last Name"

FROM  employees emp INNER JOIN departments dpt ON emp.department_id = dpt.department_id

WHERE emp.department_id in (80, 90, 110, 190);

10. Display employee ID, last name, department ID, department name, and hire date for those employees whose hire date was June 7, 1994.

SELECT emp.Employee_id "Employee ID", emp.last_name "Last Name" , emp.department_id "Department ID", dpt.department_name  "Department Name", emp.hire_date "Hire Date"

FROM  employees emp LEFT JOIN departments dpt ON emp.department_id = dpt.department_id

WHERE emp.hire_date = TO_DATE('June 7, 1994', 'fmMonth DD, YYYY');