Join Exercises - Exercise

Contact Us or call 1-877-932-8228
Join Exercises - Exercise

Join Exercises

Duration: 10 to 15 minutes.
  1. Create a query that returns all employee rows. Assign an alias e to the table.
  2. Modify the query so that it accesses both the employees and departments table (using the Theta syntax, but "accidentally" forget the join predicate.). Make sure to add the alias e to the asterisk in the SELECT statement.
  3. Modify the query so that it uses an inner join between the two tables using the department_id columns (thus utilizing the department assignment relationship). Note that this is a natural join (but does not use the ambiguous NATURAL JOIN syntax).
  4. Rewrite the query so that it returns the same rows but uses a semijoin.

Solution:

Joining-Tables/Solutions/join_solutions.sql
--
-- All employee records
--
SELECT * 
FROM employees e;

--
-- This is a cross-join which the cartesian product of the two tables.
--
SELECT e.*, d.department_id 
FROM employees e, departments d;

--
-- An inner join
--
SELECT e.* 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id;

--
-- The same results from a semijoin
--
SELECT e.* 
FROM employees e 
WHERE e.department_id IN ( 
	SELECT d.department_id
	FROM departments d
);
Next