Dbs301 seneca

DBS301 Assignment 2 and 3

Due Date: Monday August 10, 2015

Submission: By email - rob.stewart@senecacollege.ca

Subject line: DBS301 YourSection - Assignment 2 and 3 YourNameHere/Student#

Email Body: Put all SQL script answers in the body of the email – NO attachments

Instructions:

1. Use the data file you are supplied and the schema attached to this document to guide you.

2. You MUST only use the command and keywords you were taught in this class.

3. You may use the following aggregate functions if needed:

a. SUM

b. AVG

c. COUNT

d. MIN

e. MAX

4. All queries must use the ANSI SQL92 format (ie: using JOIN keywords)

5. A function based (lower) index was created in the KeyWordDesc column of the table

tblKeyWord. See the last line of the IMDB_SCHEMA.SQL file for details.

Prep Work:

1. Load the IMDB_SCHEMA.SQL file 1st

2. Load the IMDB_DATA.SQL file 2nd

3. Answer the questions

mailto:rob.stewart@senecacollege.ca
Assignment 2

1. Create a query that will list any ‘Movie Titles’ that have not yet been assigned a director.

2. Create a query that will list all “Movie Titles” and the corresponding keyword/keywords that

have the words france, waitress, or pizza anywhere in their keywords/keyword phrases.

3. Create a query that will list all ‘Movie Titles’ along with their average ‘Star Review Rating’

for each type of critic (ie: critics and users). Order by the Movie tiltle.

4. Same as above but use a cross tab query. In ORACLE you may use the PIVOT keyword.

5. List all actor names that do not have any movie credits (ie: have not been assigned to a

movie). Use the concatenate symbol to join first and last name together in the same

column.

6. Create a query that will count how many male actors we have and how many female actors

we have.

7. Create a query that list all ‘Movie Titles’ and the number of awards they either won or were

nominated for. Use the PIVOT keyword. Order by the Movie title.

8. Create a query that lists the ‘Movie Title’ of the Movie(s) that have WON the most awards.

If there is a tie all movie titles should be shown. This can be done with subqueries and the

MAX function since Oracle does not support the TOP predicate.

Assignment 3

1. Show the query to modify the existing table tblReview so that the default value of the

ReviewStar column is 0 for any records inserted.

2. Show the query that would create a unique index on the ActorFName and ActorLName

in the existing table tblActor. Give the cindex a name of ndx_Actor_Name.

3. Show the query that would modify the existing tblActor table to only allow either a “M”

or “F” or Null in the ActorGender column and set the default to NULL for new records.