Assessment P

Taking Queries to the Next Level

(This is a “project based” assessment; it is not a quiz)

Background : You will place all your answers within the remainder of this document. It is to your advantage to copy your SQL commands and paste them into this document. Simply capturing your commands as an image will not allow you to run them over again if it becomes necessary. SQL Results, however, should be captured as an image using, for example, the Snipping Tool. As you prepare to respond to questions from the Amy Miles, the CEO of Regal Theaters, review the MOVIE ERD (Entity Relationship Diagram) that you generated in fulfillment of Assessment 1. The MOVIE ERD is shown below.

Task 1. (5pts) For each movie list its title, genre, MPAA rating and the first and last name of the director. Hint: This is a multiple table query involving both the MOVIE table and the DIRECTOR table. Mod1218

Your SQL command will match the following. Just replace the ‘????’s with the missing info.

Put your SQL command and your results here:

Task 2. (5 pts) Create the STAR table using the following SQL command.

After you have successfully created the STAR table display the structure of the STAR table by right-clicking the STAR table and selecting “Design View”.

Put your Results of selecting “Design View” here:

Task 3. (5 pts) Create the MOVIE_STAR “join” table using the SQL command shown below:

After you have successfully created the MOVIE_STAR table display the structure of the MOVIE_STAR table by right-clicking the MOVIE_STAR table and selecting “Design View”.

Put your Results of selecting “Design View” here:

Task 4. (5 pts) Amy Miles was wondering why you called the MOVIE_STAR table a “join” table.

Enter your response to her below:

NOTE: You MUST use the information contained in the “Movie Fact Data Sheet.docx” when you INSERT records into the tables of the MOVIE database.

Task 5. (10 pts) To demonstrate the capability of the Movie database to handle movie stars, Regal Theaters CEO, Amy Miles, has asked DB Solutions to add the following seven stars to the STAR table: Tom Hanks, Kevin Bacon, Roy Scheider, Richard Dreyfuss, Drew Barrymore, Henry Thomas and Teri Garr. Hint: Check out section “Adding Rows to a Table” in your eBook, starting on page 72. Shown below is an example of the INSERT commands that you need to run in order to insert Tom Hanks and Kevin Bacon into the STAR table:

After running the above SQL commands, create and run the other five other INSERT commands to add, Roy Scheider, Richard Dreyfuss, Drew Barrymore, Henry Thomas and Teri Garr to the STAR table. After all 7 rows have been inserted,

Put the Results of running the command “SELECT * FROM STAR;” here:

Task 6. (2 pts) In preparation for doing the next task,

Put the Results of running the command “SELECT * FROM MOVIE;” here:

Task 7. (8 pts) Now that you have displayed the 7 records from the STAR table and the 4 records from the MOVIE table, you are able to INSERT the 8 records that “join” each of the stars to the movie(s) they are in. To know what movie(s) each star appeared in you will need to refer to the “Movie Fact Data Sheet.docx” document that Regal Theaters provided you.

As an example, given that Tom Hanks and Kevin Bacon both star in Apollo 13 and assuming that Apollo 13 has a MOVIE_ID value of 1 and Tom Hanks has a STAR_ID value of 1 and Kevin Bacon has a STAR_ID value of 2, the following INSERT commands should be used:

Put your remaining 6 SQL INSERT commands here:

Put the Results of running the command “SELECT * FROM MOVIE_STAR;” here:

Task 8. (10 pts) Now that your database is fully populated with data you can fulfill the second request by Regal Theaters. Specifically they have asked you to list the title, release date and the first and last name of all of the stars of each movie. Order the results by release date. Hint: This multiple query involves three tables (MOVIE, MOVIE_STAR and STAR).

Your SQL command will match the following. Just replace the ‘????’s with the missing info.

Put your SQL command and results here: