Assignment 1: SQL Queries
Objectives:
To understand and be able to write simple SQL queries.
Number of points:
100
Due date:
Tuesday, January 16th, 6pm
Tools for the assignment
For this assignment, you will use
your SQL Server account on IPROJSRV. Your account will be your UW email, and
you know your password from the first lecture.
As an alternative to SQL Server, you are allowed to use a
different database, e.g. MySQL, Postgres, or SQL Server installed in
your own machine. In that case you fist need to import the movie
database in your database system: you can obtain the data from here. There is no support for
database systems
other than IPROJSRV.
What to turn in:
You need to email me a single file containing all your queries, before 6pm on the due date. Your file
should look like this. The name of your
file will be your SQL
server login id and the extension of the file will be
.sql. For example, if Professor Suciu were to turn in his file, the
file will be named suciu.sql
because his login id on SQL server is suciu. Note that if
for any reason, you have to write some notes or comments for me, please
add them as comments to the SQL in the file that you submit. The file
that you email me should strictly follow the format as indicated to
simplify the grade process, otherwise it will impact your performance. Please include [CSEP 544] in the title of your submission emails.
Note :
(1) You DO NOT have to turn in the results you get from executing your
queries.
(2) If you use alternatives, e.g. MySQL, please ensure the queries you
email me are execuable on MS SQL Server (e.g. on IPROJSRV) .
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
IPROJSRV.
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
- (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.