Ceci est une ancienne révision du document !
SQLite practice
SQLite is a widespread embedded SQL engine.
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
Movies directed by Steven Spielberg
Find all years (in increasing order) in which there is a movie rated 4 or 5
Find the name of people who rated the movie Gone with the Wind
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.
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.
Give the title of the movies that where not rated by Chris Jackson
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.
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>
Find movies that where not rated
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
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
List movie titles with their average rating
Give the name of reviewers who did at least 3 ratings
Find the movie with the best average rating. Give the movie title and the average rating.
Same question for the worst average rating.
Find the difference between the average rating for movies from before 1980 and the one for the movies from after that year.
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
Find the name of reviewers that didn't put a date on one of their rating.
For each director, give their name and the title of their best rated movie(s), together with the correspinding rating. Ignore movies without director.