Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
enseignement:dbdm:tp1 [2016/02/05 06:14] ecoquery créée |
enseignement:dbdm:tp1 [2018/02/01 09:42] (Version actuelle) rthion [SQLite practice] |
||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
- | ====== | + | ====== SQLite practice ====== |
- | [[https:// | + | [[https:// |
This lab will give you some practice on SQL querying. | This lab will give you some practice on SQL querying. | ||
Ligne 20: | Ligne 20: | ||
As SQLite is not a full featured DBMS server, starting the SQLite shell on a database file is sufficient to get started. | 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 {{: | + | In a working directory of your choice, download {{: |
<code sh> | <code sh> | ||
sqlite3 movies.db | sqlite3 movies.db | ||
</ | </ | ||
Execute the sql script: | Execute the sql script: | ||
- | < | + | < |
- | .read TP1-DBDM.sql | + | .read tp1-dbdm.sql |
</ | </ | ||
- | And turn on headers for getting attributes on results | + | And turn on headers |
< | < | ||
.headers on | .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' | ||
+ | - 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.</ | ||
+ | - 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' | ||
+ | |||
+ | |||
+ | ===== 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' | ||
+ | |||
+ | ===== 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. | ||
+ | |||
+ | |||