Assignment 1: SQL Queries
Objectives:
To understand and be able to write simple SQL queries.
You should do this assignment by yourself.
Number of points:
100
Due date:
Saturday, April 12, at 9 pm. Friday, April 11, at the beginning of class.
If you chose to use any of your late days for this assignment, please
notify the TAs that you have done this.
Tools for the assignment
For this assignment, you will use
your SQL Server account on IISQLSRV. Your account is your CSE logon, and
you know your password from the first lecture. If you are interested, the movie
database data can be downloaded here., but it is already
available on IISQLSRV.
What to turn in:
We will use catalyst to handle the turn in this quarter. Go to the
turnin page (linked here when available),
then login using your UWNET id. The rest
should be intuitive enough. The limit of submitted file size is 2MB,
which should be more than enough for SQL script file.
Please help us handle your assignments by doing the following::
Include your name, and your CSE and UWNET
ids in the submitted files, as in this
sample file
Do not use blank space or any special
character in your file names
Name your file by YourUWNETid_HW1
Thank you :-)
Note :
(1) You DO NOT need to turn in the results you get from executing your
queries.
(2) Preferable format: TXT (Plain text)
Homework Description
In this homework you are asked to write 12 SQL queries on a
relational movie database. The data in this
database is from
the IMDB website.
The database
exists already on
IISQLSRV.
The database consists of seven tables, however you will be using only
the following five tables
- ACTOR (id, fname, lname, gender)
- MOVIE (id, name,
year, rank)
- DIRECTORS (id, fname, lname)
- CAST (pid,
mid, role)
- MOVIE_DIRECTORS
(did, mid)
id column in ACTOR, MOVIE & DIRECTOR tables
is a key
for the respective table.
CAST.pid refers to ACTOR.id, CAST.mid refers to MOVIE.id
MOVIE_DIRECTORS.did refers to DIRECTORS.id and MOVIE_DIRECTORS.mid
refers
to MOVIE.id
Note: In every question
below, whenever you are asked to return all actors,
or all directors, or all movies, you should return the id and the name
(i.e. fname, lname, or name respectively).
- (8 points) a. List all the actors who acted in at
least one
film in 2nd
half
of the 19th century and in at least one film in the 1st half of the
20th century.
b. List all the directors who directed a film in a leap year.
- (8 points) List all the movies that have the same year as
the movie
'Shrek
(2001)', but a better rank. (Note: bigger value of rank implies a
better rank)
- (8 points) List first name and last name of all the actors
who played
in the
movie 'Officer 444 (1926)'.
- (8 points) List all directors in descending order of the
number of
films
they directed.
- (8 points) a. Find the film(s) with the largest
cast.
b. Find the film(s)
with the
smallest cast.
In both cases, also return the size of the cast.
- (8 points) Find all the actors who acted in films by at
least 10
distinct
directors (i.e. actors
who
worked with at least 10 distinct directors).
- (8 points) Find all actors who acted only in films before
1960.
- (8 points) Find the films with more women actors than men.
- (8 points) For every pair of male and female actors that
appear
together in
at least two films, find the total number of films in which they appear
together. Sort the answers in decreasing order of the total number of
films.
- (8 points) For every actor, list the films he/she appeared
in their
debut
year. Sort the results by last name of the actor.
- (10 points) The Bacon number of an
actor is the length of the
shortest path between the actor and Kevin Bacon in the "co-acting"
graph. That is, Kevin Bacon has Bacon number 0; all actors who acted in
the same film as KB have Bacon number 1; all actors who acted in the
same film as some actor with Bacon number 1 have Bacon number 2, etc.
Return all actors whose Bacon number is
2.
Bonus: Suppose
you
write a single SELECT-FROM-WHERE SQL query that returns all actors
who have finite
Bacon numbers. How big is the query?
- (10 points) A decade is a sequence of
10 consecutive years. For
example 1965, 1966, ..., 1974 is a decade, and so is 1967, 1968, ...,
1976. Find the decade with the largest number of films.