15 Cross Joins and Natural Joins

• ·         Returns the Cartesian product from two tables. –CROSS JOIN
• ·         Joins two tables based on the same column name.-NATURAL JOIN

Use the Oracle database for problems 1-4.

1.  Create a cross-join that displays the last name and department name from the employees and departments tables.

SELECT last_name, first_name, department_name

FROM employees CROSS JOIN departments;

2.  What is the result of the query that you have used for question 1?

160 rows, with all possible combinations of employee being part of all departments.

https://drive.google.com/open?id=0B6dqipWMkn9XRlB1bE1BdEZ6OG8

3.  Create a query that uses a natural join to join the departments table and the locations table by the location_id column. Display the department id, department name, location id, and city.

SELECT department_id,department_name, location_id, city

FROM departments NATURAL JOIN locations;

4.  Rewrite problem 2 using equijoin syntax.

SELECT department_id,department_name, location_id, city

FROM departments, locations

WHERE departments.location_id = locations.location_id;

or

SELECT department_id,department_name, location_id, city

FROM departments JOIN locations  ON departments.location_id = locations.location_id;

or

SELECT department_id,department_name, location_id, city

FROM departments INNER JOIN locations  ON departments.location_id = locations.location_id;

5.  Create a query that uses a natural join to join the departments table by the location_id column. Restrict the output to only department IDs of 20 and 50. Display the department id, department name, location id, and city.

SELECT department_id,department_name, location_id, city

FROM departments NATURAL JOIN locations

WHERE department_id in (20, 50);

6.  Use an equijoin between the two DJs on Demand database tables: d_songs and d_types. Display the type code, description, and title. Limit the rows returned to those type codes between 70 and 80.

SELECT d_songs.type_code, d_songs.title, d_types.description

FROM d_songs  INNER JOIN  d_types ON  d_songs.type_code = d_types.code

WHERE d_songs.type_code BETWEEN 70 AND 80;

7.  When creating a join, the join condition is always placed in the _______________ clause of the SELECT statement.

ON clause

WHERE clause

See Topic 4 above for example of on and where

8.  A/an _______________ can be used to preface the column name in order to clarify which table and column are participating in the join. -  Table name itself or the alias

SELECT sng.type_code, sng.title, typ.description

FROM d_songs  sng INNER JOIN  d_types  typ ON  sng.type_code = typ.code

WHERE sng.type_code BETWEEN 70 AND 80;

Or

SELECT d_songs.type_code, d_songs.title, d_types.description

FROM d_songs  INNER JOIN  d_types ON  d_songs.type_code = d_types.code

WHERE d_songs.type_code BETWEEN 70 AND 80;

9.  Table aliases are created in the ________________ clause of the SELECT statement.

 – AS (it is optional though)