Saturday, October 15, 2016

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)

1 comment: