IT 240 Assignment Four

Due Date: Tuesday, Apr. 26th by 11:58 PM

This assignment will be graded on a 50-point basis. The purpose of this assignment is to

• Utilize the relational algebra concepts intrinsic in SQL for processing single or multiple tables using

joins • Analyze the request to identify the tables, attributes, selection and join criteria • Apply the SQL clauses to perform grouping operations, calculations and aggregate functions and

sorting techniques SQL SELECT

Write the SQL statement to retrieve the information requested in the following exercises. Your query

should list requested attributes only. Each request should be one SELECT statement. Test your statements

in the Movie database in ACCESS. DO NOT SUBMIT THE OUTPUT FROM YOUR QUERIES OR THE

ACCESS DATABASE FILE.

In some requests, you will be retrieving data from multiple tables, it is critical that you understand how the

tables are related. Please view the video Movie Database Analysis for an overview of the tables, attributes,

and relationships.

Each query has a 10-point value even though they vary in difficulty. The values in parenthesis ( ) is the

number of rows that should be retrieved by your query.

1. List the oldest, youngest and average ages of the living male and female stars. (2) 2. List the years in which PG-rated movies were made. If several PG-rated movies are made in the same

year, that year should be listed only once. Include the year and MPAA rating in the output with the

earliest year listed first. (50)

3. List the average length of all movies in each category. Include the category description in the

output. The output should be sorted by the average length with the shortest movie time first in the

list. (23)

4. List the movie titles that won both best actor and best actress awards. Consider only the actors who

have won (W) not those who have been nominated (N). (8)

(hint: do a self-join on MOVSTAR; i.e. use the MOVSTAR table with an alias that represent female

actors and another MOVSTAR table with an alias to represent the male actors. This is necessary

because one actor cannot win an award for best male and best female actor in same movie. You need a

third table to get the movie title. You must join 3 tables with 2 join criteria and 2 selection criteria. )

5. List the director number, last and first names and the number of movies for all directors who

directed more than 10 movies. Display the list in order with the name of the director who directed

the most movies first.(8) Submitting the homework

Type all SQL statements