SQLite practice

SQLite is a widespread embedded SQL engine. On your personal computer, one may appreciate SQLiteBrowser which provides a user friendly GUI on top of SQLite.

This lab will give you some practice on SQL querying.

Application context

You are interested in creating a web site to rate movies. You already collected a few example rates on some movies. Even if you don't have much data, you can still try some queries.

The schema of the database is the following:

  • Movie(mID, title, year, director)
  • Reviewer(rID, name)
  • Rating(rID, mID, stars, ratingDate)

Setting up the database

As SQLite is not a full featured DBMS server, starting the SQLite shell on a database file is sufficient to get started. In a working directory of your choice, download tp1-dbdm.sql, then start SQLite1):

sqlite3 movies.db

Execute the sql script:

.read tp1-dbdm.sql

And turn on headers and column mode for getting attributes on results and human friendly format

.headers on
.mode column

Simple queries

  1. Movies directed by Steven Spielberg
  2. Find all years (in increasing order) in which there is a movie rated 4 or 5
  3. Find the name of people who rated the movie Gone with the Wind
  4. For each rating where the reviewer is the same as the movie director (same name), give the name of the reviewer, the movie and the rating.
  5. Give all ratings, but with rID replaced by the reviewer's name and mID by the movie's title. Sort first by reviewer name, then by movie title, then by stars.
  6. Give the title of the movies that where not rated by Chris Jackson
  7. Give the name of reviewer and the title of movies such that the reviewer reviewed the mvoe twice, and gave a better grade the second time.
  8. Give pairs of reviewer that reviewed the same movie. We don't want duplicates like (a,b) and (b,a).

Advanced queries

<note tip>Try to find queries answering these questions without aggregation.</note>

  1. Find movies that where not rated
  2. Give the name of the reviewer, the title of the movie and the number of stars for all the movies that have the worst rating in the database
  3. For each movie, provide its best rating. Return also the movie's title and the number of stars. The result is to be sorted by title.

Aggregation queries

  1. List movie titles with their average rating
  2. Give the name of reviewers who did at least 3 ratings
  3. Find the movie with the best average rating. Give the movie title and the average rating.
  4. Same question for the worst average rating.
  5. Find the difference between the average rating for movies from before 1980 and the one for the movies from after that year.
  6. For each movie, find the difference between the worst and the best rating. Give this difference together with the movie's title. Sort according to this difference, then to the movie title.

Null values

  1. Find the name of reviewers that didn't put a date on one of their rating.
  2. For each director, give their name and the title of their best rated movie(s), together with the correspinding rating. Ignore movies without director.
1)
this creates the movies.db file if it doesn't exists already