Saturday, March 25, 2006

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 *
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.song_id,d_track_listings.song_id
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.artist,d_types.description
from d_songs,d_types;

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

----select id,d_songs.title,d_songs.artist,d_types.description
from d_songs,d_types
where 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 *
from d_clients table,d_events table,the d_job_assignments;

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,d_cds.title
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 are False.

__f__a. A join is a type of query that gets data from more than one table based on columns with the same name.
__t__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.
__t__c. A Cartesian product occurs because the query does not specify a WHERE clause.
__f__d. Table aliases are required to create a join condition.
__t__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.
__f__f. Table alias must be only one character in length.
__f__g. A simple join or inner join is the same as an equijoin.

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

----That is convenient to get information from different tables.