Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

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:
-====== SQL Lab: SQLite practice ======+====== SQLite practice ======
  
-[[https://www.sqlite.org/index.html|SQLite]] is a widespread embedded SQL engine.+[[https://www.sqlite.org/index.html|SQLite]] is a widespread embedded SQL engine. On your personal computer, one may appreciate [[http://sqlitebrowser.org/|SQLiteBrowser]] which provides a user friendly GUI on top of SQLite.
  
 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 {{:enseignement:dbdm:tp1-dbdm.sql|}}, then start SQLite((this create the ''movies.db'' file if it doesn't exists already)):+In a working directory of your choice, download {{:enseignement:dbdm:tp1-dbdm.sql|}}, then start SQLite((this creates the ''movies.db'' file if it doesn't exists already)):
 <code sh> <code sh>
 sqlite3 movies.db sqlite3 movies.db
 </code> </code>
 Execute the sql script: Execute the sql script:
-<code sql+<code> 
-.read TP1-DBDM.sql+.read tp1-dbdm.sql
 </code> </code>
-And turn on headers for getting attributes on results+And turn on headers and column mode for getting attributes on results and human friendly format
 <code> <code>
 .headers on .headers on
 +.mode column
 </code> </code>
 +
 +===== 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.
 +
 +