19 Oracle Equijoin and Cartesian Product

• ·         Results from an invalid or omitted join condition; all combinations of rows are displayed- Cartesian Product
• ·         Values in a column in one table are equal to a value in another table; also called an inner join or simple join-equijoin
• ·         Connection command exclusive to a specific company-proprietary join
• ·         Gives a table another name to simplify queries and improve performance-alias
• ·         Display data from two or more related tables-join conditions

1.  Create a Cartesian product that displays the columns in the d_play_list_items and the d_track_listings in the DJs on Demand database.

SELECT d_play_list_items.event_id "event id in playlist", d_play_list_items.song_id "song id in playlist", d_play_list_items.comments "comments in playlist", d_track_listings.song_id "song id in tracklist", d_track_listings.cd_number "cd number in tracklist",  d_track_listings.track "track in tracklist"

FROM  d_play_list_items, d_track_listings;

2.  Correct the Cartesian product produced in question 1 by creating an equijoin using a common column.

SELECT d_play_list_items.event_id "event id in playlist", d_play_list_items.song_id "song id in playlist", d_play_list_items.comments "comments in playlist", d_track_listings.song_id "song id in tracklist", d_track_listings.cd_number "cd number in tracklist",  d_track_listings.track "track in tracklist"

FROM  d_play_list_items, d_track_listings

WHERE d_play_list_items.song_id = d_track_listings.song_id;

3.  Write a query to display the title, type, description, and artist from the DJs on Demand database.

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

FROM d_songs, d_types

WHERE d_songs.type_code = d_types.code;

4.  Rewrite the query in question 3 to select only those titles with an ID of 47 or 48.

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

FROM d_songs, d_types

WHERE d_songs.type_code = d_types.code AND d_songs.id in (47, 48);

5.  Write a query that extracts information from three tables in the DJs on Demand database, the d_clients table, the d_events table, and the d_job_assignments table.

SELECT d_clients.email AS "d_clients - email" , d_clients.phone AS "d_clients - phone" , d_clients.last_name AS "d_clients - last_name" , d_clients.first_name AS "d_clients - first_name"  , d_clients.client_number AS "d_clients - client_number"  , d_events.id AS "d_events - id", d_events.name AS "d_events - name", d_events.event_date AS "d_events - event_date", d_events.description AS "d_events - description", d_events.cost AS "d_events - cost", d_events.venue_id AS "d_events - venue_id", d_events.package_code AS "d_events - package_code", d_events.theme_code AS "d_events - theme_code", d_events.client_number AS "d_events - client_number", d_job_assignments.partner_id AS "d_job_assignments - partner_id", d_job_assignments.event_id AS "d_job_assignments - event_id", d_job_assignments.job_date AS "d_job_assignments - job_date", d_job_assignments.status AS "d_job_assignments - status"

FROM d_clients, d_events, d_job_assignments

WHERE d_clients.client_number = d_events.client_number AND d_events.id =  d_job_assignments.event_id;

Why not Cartesian product here (by skipping where): assignments are made for events And events are for client. All the permutation rows, if returned give virtual data, which actually can never happen.

  But what if I do equijoin as above mentioned [ equivalent to INNER JOIN], I loose information that there are few events for which assignment didn’t happen yet and there is a client ,  for which there is no event yet, And I want to list it: for that in oracle propriety conventions (+) will be used in future clusters of practices. E.g.  WHERE d_clients.client_number = d_events.client_number(+) AND d_events.id =  d_job_assignments.event_id(+);

6.  Create and execute an equijoin between DJs on Demand tables d_track_listings and d_cds. Return the song_id and the title only.

SELECT d_track_listings.song_id "song id in tracklist", d_cds.title "title in cds"

FROM d_track_listings, d_cds

WHERE d_track_listings.cd_number = d_cds.cd_number;

7.  Mark T for the statements that are true and F for the statements that is false.

____ a.  A join is a type of query that gets data from more than one table based on columns with the same name. False.  that is only a natural join, statement doesn’t cover everything

____ b.  To join tables using an equijoin, there must be a common column in both tables and that column is usually a primary key in one of the tables. True

____ c.  A Cartesian product occurs because the query does not specify a WHERE clause. TRUE

____ d.  Table aliases are required to create a join condition. FALSE

____ e.  If a table alias is used for a table name in the FROM clause, it must be substituted for the table name throughout the SELECT statement. TRUE

____ f.  Table alias must be only one character in length.-FALSE

____ g.  A simple join or inner join is the same as an equijoin.-TRUE. Study material says: Equijoin- Sometimes called a "simple" or "inner" join; an equijoin is a table join that combines rows that have the same values for specifies columns

8.  What advantage does being able to combine data from multiple tables have for a business?

Business doesn’t care where I store data or how I retrieve it. In Relational DB, we store the data in different tables related to each other. And since we can combine data from multiple tables following these relations, we get useful information as output which is the purpose of DB’s existence.