# -*- coding: utf-8 -*-
# Created on Sat Sep 31 08:15:00 2024
# @author: mhebding
# TP4 - Bases de donnees 1



## 1 - Introduction

# 1. Combien comporte-t-elle de tables?
# 3 tables

# 2. Est-ce que nom est une clé primaire pour la table client ?
# nom est une cle primaire pour la table client car elle permet 
#d'identifier chaque enregistrement de maniere unique.

# 3. Identifier les clés primaires de chaque table.
# client : numero, nom
# produit : reference, marque, prix HT
# vente : numero, date

# 4. Identifier les clés étrangères.
# vente : reference_produit (cle primaire de produit)
# vente : numero_client (cle primaire de client)

# 5. Quels sont les attributs de la table produit ?
# la table produit comporte 3 attributs : reference, marque, prix HT

# 6. Combien d’enregistrements comporte la table vente ?
# la table vente comporte 4 enregistrements

# 7. Qui a acheté une Peugeot en 2019?
# Fabre a achete une Peugeot le 20/01/2019



## 2 - Une table

# 8. Ouvrir la base de données disquaire.sqlite dans le SGBDR. Afficher l’ensemble de la table clients.
SELECT * FROM clients

# 9. Afficher le nom et le prénom des clients.
SELECT nom, prenom FROM clients

# 10. Dans la table artistes, n’afficher que les artistes encore en activité.
SELECT nom FROM artistes WHERE fin = 0

# 11. Afficher le nom et l’année de sortie de l’album le plus récent disponible en magasin.
SELECT nom, sortie FROM albums WHERE sortie = (SELECT MAX(sortie) FROM albums)

# 12. Afficher le prix moyen d’un disque.
SELECT AVG(prix) FROM albums

# 13. Afficher le prix moyen d’un disque pour chaque idartiste.
SELECT idartiste, AVG(prix) FROM albums GROUP BY idartiste
# version JOIN pour le nom
SELECT nom, AVG(prix) FROM artistes JOIN albums ON artistes.idartiste = albums.idartiste GROUP BY albums.idartiste

# 14. Afficher le nombre de clients du magasin.
SELECT COUNT(idclient) FROM clients

# 15. Afficher le nombre de clients qui habitent en Saône-et-Loire (71).
SELECT COUNT(idclient) FROM clients WHERE département = 71

# 16. Afficher le nom de l’album et le prix du disque le moins cher.
SELECT nom, prix FROM albums WHERE prix = (SELECT MIN(prix) FROM albums)

# 17. Afficher la quantité de disques totale que le magasin a en stock.
SELECT SUM(stock) FROM albums

# 18. Afficher les idartiste ayant plus de 6 disques en stock ainsi que les nombres de disques.
SELECT idartiste, SUM(stock) FROM albums GROUP BY idartiste HAVING SUM(stock) > 6

# 19. Afficher la table ventes classée par date d’achat du plus récent au plus ancien.
SELECT * FROM ventes ORDER BY date_achat DESC

# 20. Afficher les idalbum vendus en 2013.
SELECT idalbum FROM ventes WHERE date_achat BETWEEN '2013-01-01' AND '2013-12-31'

# 21. Afficher le nombre d’exemplaires de chaque idalbum vendu dans la table ventes.
SELECT idalbum, COUNT(idalbum) FROM ventes GROUP BY idalbum



## 3 - Plusieurs tables

# 22. Afficher le nom des artistes avec le nom des albums qu’ils ont fait.
# version intuitive (faux)
SELECT artistes.nom, albums.nom
FROM artistes, albums
WHERE artistes.idartiste = albums.idartiste
# option plus propre (a retenir) avec JOIN
SELECT artistes.nom, albums.nom
FROM artistes
JOIN albums
ON artistes.idartiste = albums.idartiste

# 23. Afficher le nom et le prénom des clients avec le nom des albums qu’ils ont acheté.
SELECT clients.nom, clients.prénom, albums.album FROM clients, albums, ventes
WHERE clients.idclient = ventes.idclient AND albums.idalbum = ventes.idalbum
# version JOIN
SELECT nom, prénom, album
FROM clients
JOIN ventes ON clients.idclient = ventes.idclient
JOIN albums ON ventes.idalbum = albums.idalbum

# 24. Afficher le nom et le prénom des clients qui ont acheté l’album « Blow ».
SELECT nom, prénom
FROM clients, ventes, albums
WHERE clients.idclient = ventes.idclient AND ventes.idalbum = albums.idalbum AND album = 'Blow'

# 25. Afficher le numéro de téléphone des personnes ayant acheté de la musique française.
SELECT téléphone
FROM clients, ventes, albums, artistes
WHERE clients.idclient = ventes.idclient AND ventes.idalbum = albums.idalbum AND albums.idartiste = artistes.idartiste AND pays = 'France'

# 26. Afficher le style de musique écouté par les clients parisiens.
SELECT artistes.style FROM artistes, clients, albums, ventes 
WHERE clients.idclient = ventes.idclient AND albums.idalbum = ventes.idalbum
AND artistes.idartiste = albums.idartiste AND clients.'département' = '75'



## 4 - BONUS

# 27.
SELECT idalbum, nombre
FROM (SELECT idalbum,COUNT(*) AS nombre FROM ventes GROUP BY idalbum) AS derived_table1
WHERE nombre = (SELECT MAX(nombre) FROM ((SELECT COUNT(*) AS nombre FROM ventes GROUP BY idalbum) AS derived_table2))

# 28.
SELECT idartiste, stock_artiste
FROM (SELECT idartiste, SUM(stock) AS stock_artiste FROM albums GROUP BY idartiste) AS derived_table1
WHERE stock_artiste = (SELECT MIN(stock_artiste) FROM ((SELECT SUM(stock) AS stock_artiste FROM albums GROUP BY idartiste) AS derived_table2))

# 29.
SELECT nom, prix_moyen
FROM (SELECT idartiste, AVG(prix) AS prix_moyen FROM albums GROUP BY idartiste)
AS derived_table1, artistes
WHERE prix_moyen = (SELECT MAX(prix_moyen) FROM ((SELECT AVG(prix) AS prix_moyen FROM albums GROUP BY idartiste) AS derived_table2))
AND derived_table1.idartiste = artistes.idartiste

# 30.
SELECT prenom, nom, nombre
FROM (SELECT idclient, COUNT(*) AS nombre FROM ventes GROUP BY idclient) AS derived_table1, clients
WHERE nombre = (SELECT MAX(nombre) FROM ((SELECT COUNT(*) AS nombre FROM ventes GROUP BY idclient) AS derived_table2))
AND derived_table1.idclient = clients.idclient

# 31.
# FESTIVAL ELECTRO

# 32.
SELECT prénom, clients.nom, département
FROM clients
JOIN ventes ON clients.idclient = ventes.idclient 
JOIN albums ON ventes.idalbum = albums.idalbum
JOIN artistes ON artistes.idartiste = albums.idartiste 
WHERE artistes.nom = 'Bruce Springsteen'

# 33.
SELECT nom, style FROM artistes WHERE nom = 'Jul' OR nom = 'JuL' OR nom = 'JUL'

# 34.
# BLEU LUMIERE

# 35.
# SCH 0
SELECT idalbum, nombre
FROM (SELECT idalbum,COUNT(*) AS nombre FROM ventes GROUP BY idalbum) AS derived_table1
WHERE nombre = (SELECT MAX(nombre) FROM ((SELECT COUNT(*) AS nombre FROM ventes GROUP BY idalbum) AS derived_table2))
#
SELECT nom, prénom, album
FROM clients
JOIN ventes ON clients.idclient = ventes.idclient
JOIN albums ON ventes.idalbum = albums.idalbum

# 36.
SELECT prénom, clients.nom, département
FROM clients
JOIN ventes ON clients.idclient = ventes.idclient 
JOIN albums ON ventes.idalbum = albums.idalbum
JOIN artistes ON artistes.idartiste = albums.idartiste 
WHERE artistes.nom = 'Johnny Hallyday'