Assignment 1: SQL Queries
Objectives:
To understand and be able to write simple SQL queries, including
updates.
Reading Assignments:
6.1 - 6.6, 7.1
Number of points:
100
Due date:
Wednesday, January 19th in class.
Tools for the assignment
For this assignment, you will use
your SQL Server account on IISQLSRV. You should have an account, 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 IISQLSRV.
What to turn in:
You need to turn in a printout containing the SQL query for each of the
questions below, at the end of class on the due date. In addition, you also need
to email me a single file containing all your queries. 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
I were to turn in my file, the file will be named gupta.sql. Note that if for
any reason, you have to write some notes or comments for me, please add them
on the print out that you submit. The file that you email me should strictly
follow the format as indicated.
Homework Description
In this homework you are asked to write ten SQL queries on a
relational movie database. This is a stripped down version of
a
dataset from the UCI KDD archive. The database exists already on
IISQLSRV.
The database consists of three tables:
- FILMS(filmid, filmname, year, director, category)
- ACTORS(stagename, lname, fname, gender, dob, dod,
country)
- CASTS(film, actor)
filmid is a key for FILMS and stagename is a
key for ACTORS. CASTS.film refers to FILMS.filmid and
CASTS.actor
refers to FILMS.stagename.
- a. List all the actors who were born in 2nd half of the 19th century and
died in the 1st half of the 20th century
b. List all the directors who directed a film in a leap year
- List all the actors born in 1982 and the movies they cast in
- List all the actors who played in 'dead man walking'
- List all actors in descending order of the number of films they acted in
- Find the film(s) with the largest cast. Find the film(s) with the smallest
cast. In both cases, also return the size of the cast.
- Find all the actors who acted in films in at least 10 distinct categories.
- Find all actors who acted only in films before 1960.
- Find the films with more women actors than men.
- For every pair of male and female actors that appear together in some
film, find the total number of films in which they appear together. Sort the
answers in decreasing order of the total number of films.
- For every actor, list the films he/she appeared in their debut year. Sort
the results by stagename of the actor.
- 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
whose Bacon number is infinity. How big is the query?
- 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.