Assignment 4: Multiple Table SQL Queries

  • DUE DATE: Thursday Apr. 28, 2015
  • The purpose of this assignment is to learn how to read and write the SQL code needed to process multiple tables using joins and subqueries.
  • Read the questions carefully. (There are three Parts)
  • Please use the same numbering scheme
  • Submit in D2L under Assignment 4 dropbox in .pdf or word format
  • Include: Full name, clas number and assignment number in your submission

Part 1: Guided discovery for subqueries (8 pts)

This portion of the assignment is meant to guide you to better understand simplesubqueries. Please follow instructions carefully. Each question is worth 2pts in Parts 1 and 2.

DIRECTOR
dirnum / firstname / lastname / birthyear
1 / Woody / Allen / 1935
2 / Alfred / Hitchcock / 1899
3 / Cecil B. / De Mille / 1881
4 / Stanley / Kramer / 1913
5 / Stanley / Kubrick / 1928
6 / Otto / Preminger / 1906
7 / John / Ford / 1895
8 / Clint / Eastwood / 1930
9 / Spike / Lee / 1956
10 / Arthur / Penn / 1922

The following tables are smaller subsets of existing relations in the Movies database posted on D2L:

MOVDIR
mvnum / dirnum
1 / 1
2 / 5
3 / 5
4 / 2
5 / 2
6 / 2
7 / 1
8 / 2
9 / 3
10 / 4
MOVIE
mvnum / mvtitle / mvyear
1 / Annie Hall / 1977
2 / Dr. Strangelove / 1964
3 / A Clockwork Orange / 1971
4 / North by Northwest / 1959
5 / Rope / 1948
6 / Psycho / 1960
7 / Interiors / 1978
8 / The Birds / 1963
9 / Samson and Delilah / 1949
10 / Guess Who's Coming to Dinner? / 1967
  1. Write the schema for the database consisting of the tables above. Underline primary keys and use arrows or “Foreign Key ______references ______” to denote foreign keys.
  1. Draw the results table (with appropriate column headers and tuples of data) for the following query:

SELECT AVG( mvyear ) AS AvgYear

FROM movie;

The following is an example of a subquery. Subqueries are nested queries. The inner query (in blue below) gets executed first, then the result is fed to the outer query (ie: replace what is blue with the result of the inner query and execute what is written in black.)

SELECT mvtitle

FROM movie

WHERE mvyear > (

SELECT AVG( mvyear ) AS AvgYear

FROM movie );

  1. Draw the results table(with appropriate column headers and tuples of data) for this subquery. HINT: get a result for the inner query first, then pass it to the outer query.
  1. What does your results table from 3. represent? (In your own words)

The following is another use for subqueries:

SELECT firstname, lastname

FROM director

WHERE dirnum IN (

SELECT dirnum

FROM movdir )

  1. What part of the SQL code above is the inner query? (Please write out the whole inner query)
  1. Draw the results table (with appropriate column headers and tuples of data) for the inner query
  1. Draw the results table (with appropriate column headers and tuples of data) for this subquery.
  1. Write the SQL code of an equivalent equi-join (INNER JOIN)

Part 2: Guided discovery for LEFT Join (2 pts)

The following code executes a LEFT JOIN:

SELECT DISTINCT movdir.dirnum, director.lastname, director.firstname

FROM director LEFT JOIN movdir ON director.dirnum = movdir.dirnum

  1. What tables are being joined?

Given the following result tablefor the LEFT JOIN code above (using the same data as Part 1):

dirnum / lastname / firstname
Eastwood / Clint
Ford / John
Lee / Spike
Penn / Arthur
Preminger / Otto
1 / Allen / Woody
2 / Hitchcock / Alfred
3 / De Mille / Cecil B.
4 / Kramer / Stanley
5 / Kubrick / Stanley
  1. How would you modify the code given for the left join to answer the following question: “Which directors have not directed a movie in our database?” (Write out the full SQL code including the starter code given above)

Part 3: Multiple Table SQL Queries (40 pts)

  • For the following exercises, write the SQL code to retrieve the information requested.
  • Your code should execute
  • Test your code on the Movies database (posted with this assignment)
  • Your query should list requested attributes only
  1. (5 points) For each movie category, show the descriptive name of that category, the number of movies in the database of that category, the total number (sum) of nominations (NOMS) and the average length of movie of that type. Include only those categories for which there are more than 5 movies in the database (i.e. use having). List the output with the greatest number of movies shown first (i.e. the highest count).
  1. (4 points) List the last and first names of all directors who have directed only one movie.
  1. (3 points) List the movies which have more than one director. Include the year the movie was released and the movie title. Sort the output by title in alphabetical order.
  1. (3 points) List the movies which have more than one director. Include the year the movie was released and the movie title, as well as thelast and first name of directors. Sort the output by last name within title. (Use subquery)
  1. (5 points) List the countries (name) where no star was born. (the country name should be listed only once).
  1. (10points) List the names (first and last) of stars, who are also directors. Include the star’s director number in the results. Please note: the director may not have starred in the movie he/she directed, but has been a star in at least one movie and directed as least one movie.
  1. (10 points) List the movie titles that won both best actor and best actress awards.