Lab - SQL et bases de données avec SQLite

Ouvrir dans Google Colab

Objectifs

chinook database diagram

Setup

import doctest
import sqlite3

def query(db, sql, n=50):
    """Return n rows from a sql query.

    >>> db = sqlite3.connect(':memory')
    >>> query(db, "SELECT name FROM sqlite_master")
    []
    """
    return db.execute(sql).fetchmany(n)

Dataset

import io
import zipfile
import requests

DATABASE = (
    "https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip"
)


request = requests.get(DATABASE, stream=True)
archive = zipfile.ZipFile(io.BytesIO(request.content))

archive.extract('chinook.db')

db = sqlite3.connect('chinook.db')

Question 1

Lecture du modèle de données

  1. Repérez les PK et FK sur : artists, albums, tracks, genres, customers,
  2. Décrivez la relation Artist → Album → Track.
  3. Le modèle de données est-il normalisé ? Si, oui jusqu’à quelle forme normale ?

Question 2

  1. Écrivez une requête qui sélectionne toutes les informations sur les clients vivant au Canada.

  2. Que pourriez-vous faire pour accélérer les requêtes liées au pays des clients ?

sql = """
-- YOUR CODE HERE --
"""
cursor = query(db, sql)
for row in cursor:
    print(row)
(3, 'François', 'Tremblay', None, '1498 rue Bélanger', 'Montréal', 'QC', 'Canada', 'H2G 1A7', '+1 (514) 721-4711', None, 'ftremblay@gmail.com', 3)
(14, 'Mark', 'Philips', 'Telus', '8210 111 ST NW', 'Edmonton', 'AB', 'Canada', 'T6G 2C7', '+1 (780) 434-4554', '+1 (780) 434-5565', 'mphilips12@shaw.ca', 5)
(15, 'Jennifer', 'Peterson', 'Rogers Canada', '700 W Pender Street', 'Vancouver', 'BC', 'Canada', 'V6C 1G8', '+1 (604) 688-2255', '+1 (604) 688-8756', 'jenniferp@rogers.ca', 3)
(29, 'Robert', 'Brown', None, '796 Dundas Street West', 'Toronto', 'ON', 'Canada', 'M6J 1V1', '+1 (416) 363-8888', None, 'robbrown@shaw.ca', 3)
(30, 'Edward', 'Francis', None, '230 Elgin Street', 'Ottawa', 'ON', 'Canada', 'K2P 1L7', '+1 (613) 234-3322', None, 'edfrancis@yachoo.ca', 3)
(31, 'Martha', 'Silk', None, '194A Chain Lake Drive', 'Halifax', 'NS', 'Canada', 'B3S 1C5', '+1 (902) 450-0450', None, 'marthasilk@gmail.com', 5)
(32, 'Aaron', 'Mitchell', None, '696 Osborne Street', 'Winnipeg', 'MB', 'Canada', 'R3L 2B9', '+1 (204) 452-6452', None, 'aaronmitchell@yahoo.ca', 4)
(33, 'Ellie', 'Sullivan', None, '5112 48 Street', 'Yellowknife', 'NT', 'Canada', 'X1A 1N6', '+1 (867) 920-2233', None, 'ellie.sullivan@shaw.ca', 3)

Question 3

  1. Écrivez une requête qui sélectionne le minimum, le maximum et la moyenne du total des factures.

  2. Les bases de données SQL de type OLTP sont-elles conçues pour optimiser ce type de requête ou plutôt celle de la question 2 ?

sql = """
-- YOUR CODE HERE --
"""
cursor = query(db, sql)

for row in cursor:
    print(row)
(0.99, 1.99, 1.0395535714285522)

Question 4

  1. Écrivez une requête qui compte le nombre de morceaux regroupés par genre.
  2. Quelle est la différence entre une jointure LEFT, RIGHT, INNER et OUTER en SQL ?
sql = """
-- YOUR CODE HERE --
"""
cursor = query(db, sql)

for row in cursor:
    print(row)
(1, 'Rock', 1297)
(2, 'Jazz', 130)
(3, 'Metal', 374)
(4, 'Alternative & Punk', 332)
(5, 'Rock And Roll', 12)
(6, 'Blues', 81)
(7, 'Latin', 579)
(8, 'Reggae', 58)
(9, 'Pop', 48)
(10, 'Soundtrack', 43)
(11, 'Bossa Nova', 15)
(12, 'Easy Listening', 24)
(13, 'Heavy Metal', 28)
(14, 'R&B/Soul', 61)
(15, 'Electronica/Dance', 30)
(16, 'World', 28)
(17, 'Hip Hop/Rap', 35)
(18, 'Science Fiction', 13)
(19, 'TV Shows', 93)
(20, 'Sci Fi & Fantasy', 26)
(21, 'Drama', 64)
(22, 'Comedy', 17)
(23, 'Alternative', 40)
(24, 'Classical', 74)
(25, 'Opera', 1)

Question 5

  1. Écrivez une requête qui sélectionne le nom des artistes ayant au moins 10 morceaux.

  2. Quel est l’avantage d’utiliser la clé primaire pour ce type de requête avec un GROUP BY ?

sql = """
-- YOUR CODE HERE --
"""
cursor = query(db, sql)

for row in cursor:
    print(row)
(1, 'AC/DC', 18)
(3, 'Aerosmith', 15)
(4, 'Alanis Morissette', 13)
(5, 'Alice In Chains', 12)
(6, 'Antônio Carlos Jobim', 31)
(8, 'Audioslave', 40)
(9, 'BackBeat', 12)
(11, 'Black Label Society', 18)
(12, 'Black Sabbath', 17)
(13, 'Body Count', 17)
(14, 'Bruce Dickinson', 11)
(15, 'Buddy Guy', 11)
(16, 'Caetano Veloso', 21)
(17, 'Chico Buarque', 34)
(18, 'Chico Science & Nação Zumbi', 36)
(19, 'Cidade Negra', 31)
(20, 'Cláudio Zoli', 10)
(21, 'Various Artists', 56)
(22, 'Led Zeppelin', 114)
(24, 'Marcos Valle', 17)
(27, 'Gilberto Gil', 32)
(36, 'O Rappa', 17)
(37, 'Ed Motta', 14)
(41, 'Elis Regina', 14)
(42, 'Milton Nascimento', 26)
(46, 'Jorge Ben', 14)
(50, 'Metallica', 112)
(51, 'Queen', 45)
(52, 'Kiss', 35)
(53, 'Spyro Gyra', 21)
(54, 'Green Day', 34)
(55, 'David Coverdale', 12)
(56, 'Gonzaguinha', 14)
(57, 'Os Mutantes', 14)
(58, 'Deep Purple', 92)
(59, 'Santana', 27)
(68, 'Miles Davis', 37)
(69, 'Gene Krupa', 22)
(70, 'Toquinho & Vinícius', 15)
(72, 'Vinícius De Moraes', 15)
(76, 'Creedence Clearwater Revival', 40)
(77, 'Cássia Eller', 30)
(78, 'Def Leppard', 16)
(80, 'Djavan', 26)
(81, 'Eric Clapton', 48)
(82, 'Faith No More', 52)
(83, 'Falamansa', 14)
(84, 'Foo Fighters', 44)
(85, 'Frank Sinatra', 24)
(86, 'Funk Como Le Gusta', 16)

Question 6

  1. Écrivez une requête qui compte la taille totale des albums de “Queen”.

  2. Expliquez les avantages et les inconvénients de la dénormalisation, ainsi que les bénéfices dans ce cas précis.

sql = """
-- YOUR CODE HERE --
"""

cursor = query(db, sql)

for row in cursor:
    print(row)
('Queen', 340479458)

Question 7

  1. Écrivez une requête qui crée une vue supprimant les informations sensibles des clients.

  2. Quelle est la différence entre une vue et une table matérialisée en SQL ?

sql = """
-- YOUR CODE HERE --
"""
cursor = query(db, sql)

for row in cursor:
    print(row)