-- Ce fichier utilise le code de caractères UTF-8
-- S'il ne s'affiche pas correctement (problèmes de caractères accentués) sélectionner l'encodage approprié dans les options d'affichage de votre logiciel
-- --------------------
-- Requetes vues en TD3
-- --------------------

-- 1. les artistes français
SELECT nomArtiste FROM Artiste
WHERE nationalite = 'français';

-- 2. les artistes américains de genre pop-rock
SELECT nomArtiste FROM Artiste, Genre
WHERE nationalité = 'américain'
AND Artiste.idGenre = Genre.idGenre
AND nomGenre = 'pop-rock';

-- 3. les spectacles ayant lieu pendant les prochaines vacances de Noël
SELECT * FROM Spectacle
WHERE dateSpectacle >= '2011-12-17'
AND dateSpectacle <= '2012-01-03';

-- 4. les noms des clients ayant déjà commandé des billets
SELECT nomClient FROM Client, Commande
WHERE Client.idClient = Commande.idCommande;

-- 5. le genre des artistes italiens
SELECT nomGenre FROM Genre, Artiste
WHERE Genre.idGenre = Artiste.idGenre
AND nationalite = 'italien';

-- 6. le prix des billets des concerts de Metallica
SELECT idSpectacle, nomLieu, dateSpectacle, prix
FROM Artiste, Spectacle, Billet, Lieu
WHERE nomArtiste = 'Metalica'
AND Artiste.idArtiste = Spectacle.idArtistePrincipal
AND Ticket.idSpectacle = Spectacle.idSpectacle
AND Spectacle.idLieu = Lieu.idLieu;

-- 7. les artistes que David Dupont a vu au Transbordeur
SELECT nomArtiste
FROM Artiste, Lieu, Spectacle, Billet, Commande, Client
WHERE Artiste.idArtiste = Spectacle.idArtistePrincipal
AND Billet.idSpectacle = Spectacle.idSpectacle
AND Spectacle.idLieu = Lieu.idLieu
AND Commande.idCommande = Billet.idCommande
AND Commande.idClient = Client.idClient
AND nomLieu = 'Le Transbordeur'
AND nomClient = 'David Dupont';

-- 8. les spectacles pour lesquels aucune place n'a été encore vendue
-- Voici deux solutions, dont l'une ne fonctionne pas dans MySQL :
--  * solution ne fonctionnant pas dans MySQL, mais correct dans d'autres systèmes
(SELECT idSpectacle, nomLieu, dateSpectacle
 FROM Spectacle, Lieu
 WHERE Spectacle.idLieu = Lieu.idLieu)
MINUS
(SELECT idSpectacle, nomLieu, dateSpectacle
 FROM Spectacle, Lieu, Billet, Commande
 WHERE Spectacle.idLieu = Lieu.idLieu
 AND Billet.idSpectacle = Spectacle.idSpectacle
 AND Commande.idCommande = Billet.idCommande);
--  * solution fonctionnant dans MySQL
SELECT idSpectacle, nomLieu, dateSpectacle
FROM Spectacle, Lieu
WHERE Spectacle.idLieu = Lieu.idLieu
AND idSpectacle NOT IN (
	SELECT Spectacle.idSpectacle
	FROM Spectacle, Lieu, Billet, Commande
	WHERE Spectacle.idLieu = Lieu.idLieu
	AND Billet.idSpectacle = Spectacle.idSpectacle
	AND Commande.idCommande = Billet.idCommande
);

-- 9. pour détecter des conflits entre spectacles, nous voudrions connaître la liste des spectacles ayant lieu simultanément au même endroit
-- Remarque :  si la date comprenait aussi l'heure (type DATETIME), il faudrait extraire le jour, le mois et l'année avec les fonctions SQL DAY(), MONTH() et YEAR() qui prennent un argument de type DATE ou DATETIME
SELECT idSpectacle, dateSpectacle, nomLieu
FROM Spectacle AS s1, Spectacle AS s2, Lieu
WHERE s1.idLieu = s2.idLieu
AND s1.dateSpectacle = s2.dateSpectacle
AND s1.idLieu = Lieu.idLieu
AND s1.idSpectacle <> s2.idSpectacle;

-- 10. quelle(s) salle(s) de spectacle vend des billets plus chers que tous les billets vendus au Zénith ?
-- Il existe une solution qui traduit directement la formule en algèbre relationnelle :
SELECT nomLieu FROM (
	SELECT idBillet FROM Billet
	WHERE idBillet NOT IN (
		SELECT idBillet
		FROM Billet b1, Billet b2, Spectacle, Lieu
		WHERE b1.prix <= b2.prix
		AND b1.idSpectacle = Spectacle.idSpectacle
		AND Spectacle.idLieu = Lieu.idLieu
		AND nomLieu = 'Zénith'
	)
) AS BilletCher, Spectacle, Lieu
WHERE BilletCher.idSpectacle = Spectacle.idSpectacle
AND Spectacle.idLieu = Lieu.idLieu;
-- mais on peut aussi le faire avec un agrégat :
SELECT nomLieu FROM Billet, Spectacle, Lieu
WHERE Billet.idSpectacle = Spectacle.idSpectacle
AND Spectacle.idLieu = Lieu.idLieu
AND prix >= (
	SELECT MAX(prix) 
	FROM Billet, Spectacle, Lieu
	WHERE Billet.idSpectacle = Spectacle.idSpectacle
	AND Spectacle.idLieu = Lieu.idLieu
	AND nomLieu = 'Zénith'
);

-- Requêtes supplémetaires avec agrégats :

-- 11. le nombre de billets disponibles par spectacle
SELECT idSpectacle, COUNT(*)
FROM Billet GROUP BY idSpectacle;

-- 12. le (ou les) spectacle(s) le(s) plus cher(s)
SELECT idSpectacle FROM Spectacle, Billet
WHERE Spectacle.idSpectacle = Billet.idSpectacle
AND prix = (SELECT MAX(prix) FROM Billet);

-- 13. spectacles pour lesquels plus de 5 billets ont été annulés
SELECT idSpectacle
FROM Spectacle, Commande, Billet
WHERE Spectacle.idSpectacle = Billet.idSpectacle
AND Commande.idCommande = Billet.idCommande
AND annule = 'true'
GROUP BY idSpectacle
HAVING COUNT(idBillet) >= 5;


-- --------------------
-- Requetes vues en TD4
-- --------------------

-- Requêtes "simples" :

-- 1. les 3 prochains spectacles
SELECT idSpectacle, dateSpectacle FROM Spectacle
WHERE dateSpectacle >= NOW()
ORDER BY dateSpectacle LIMIT 3;

-- 2. pour chaque client, les salles de spectacles se trouvant dans la même ville que le client (dans la base, on ne note que la ville dans l'adresse)
SELECT nomClient, nomLieu
FROM Client, Lieu
WHERE adresseClient = adresseLieu;

-- 3. le nombre de salles de spectacles par villes
SELECT adresseLieu, COUNT(*) FROM Lieu
GROUP BY adresseLieu;

-- 4. les artistes qui sont artiste principal et artiste supplémentaire dans le même spectacle
SELECT nomArtiste, Spectacle.idSpectacle
FROM Artiste, Spectacle, ArtisteSupplementaire
WHERE Artiste.idArtiste = ArtisteSupplementaire.idArtiste
AND Artiste.idArtiste = Spectacle.idArtistePrincipal
AND Spectacle.idSpectacle = ArtisteSupplementaire.idSpectacle;

-- Requêtes complexes :

-- 5. le total de l'argent dépensé par David Dupont
SELECT SUM(prix) AS depense
FROM Billet, Commande, Client
WHERE Billet.idCommande = Commande.idCommande
AND Commande.idClient = Client.idClient
AND nomClient = 'David Dupont';

-- 6. le palmarès des clients ayant dépensé le plus d'argent dans des billets
SELECT nomClient, SUM(prix) AS depense
FROM Billet, Commande, Client
WHERE Billet.idCommande = Commande.idCommande
AND Commande.idClient = Client.idClient
GROUP BY idClient ORDER BY depenses;

-- 7. le nombre de fois que Jean Perrin a vu Risky Misfit en concert
-- Remarque :  on ne tient pas compte des artistes supplémentaires
SELECT COUNT(Billet.idBillet) AS nbBillets
FROM Spectacle, Client, Artiste, Commande, Billet
WHERE Spectacle.idArtistePrincipal = Artiste.idArtiste
AND Spectacle.idSpectacle = Billet.idSpectacle
AND Commande.idCommande = Billet.idCommande
AND Commande.idClient = Client.idClient
AND Client.nomClient = 'Jean Perrin'
AND Artiste.nomArtiste = 'Risky Misfit';

-- 8. l'artiste préféré de David Dupont
-- Remarque :  on ordonne la liste d'artistes par nombre de billets achetés pour des spectacles dans lesquels ils sont artistes principaux (dans l'ordre décroissant) puis on ne sélectionne que le premier résultat. On suppose que c'est ce qui correspond à l'artiste préféré 
SELECT nomArtiste, COUNT(Billet.idBillet) AS nbBillets
FROM Spectacle, Client, Artiste, Commande, Billet
WHERE Spectacle.idArtistePrincipal = Artiste.idArtiste
AND Spectacle.idSpectacle = Billet.idSpectacle
AND Commande.idCommande = Billet.idCommande
AND Commande.idClient = Client.idClient
AND Client.nomClient = 'David Dupont'
GROUP BY idArtiste
ORDER BY nbBillets DESC
LIMIT 1;

-- 9. les salles de spectacles ne proposant aucun spectacle
SELECT nomLieu FROM Lieu
WHERE nomLieu NOT IN (
    SELECT nomLieu FROM Lieu, Spectacle
	WHERE Lieu.idLieu = Spectacle.idLieu
);

-- 10. le nombre de billets annulés par spectacle
-- Remarque :  en fait, on ne connait que les *commandes* annulées, il faut donc trouver les billets appartenant à des commandes annulées
SELECT Spectacle.idSpectacle, COUNT(Billet.idBillet) AS nbBillets
FROM Spectacle, Billet, Commande
WHERE Spectacle.idSpectacle = Billet.idSpectacle
AND Billet.idCommande = Commande.idCommande
AND annulee = TRUE
GROUP BY Spectacle.idSpectacle;

-- 11. les artistes qui ont déjà joué en tant qu'artiste principal et en tant qu'artiste supplémentaire, dans différents spectacles
SELECT DISTINCT nomArtiste
FROM Spectacle AS s1, Spectacle AS s2, Artiste, ArtisteSupplementaire
WHERE s1.idArtistePrincipal = Artiste.idArtiste
AND s2.idSpectacle = ArtisteSupplemantire.idSpectacle
AND ArtisteSupplemantire.idArtiste = Artiste.idArtiste;


-- --------------------
-- Requêtes vues en TP3
-- --------------------

-- * suppression, modification *
-- 1. supprimer les billets coûtant plus de 50 euros
DELETE FROM Billet WHERE prix > 50;
-- 2. modifier l'heure du spectacle ayant lieu le 21 septembre 2012 dans la salle de spectacle 11 (Le Rocher de Palmer) pour qu'elle prenne la valeur '20:30:00'
UPDATE Spectacle SET heure='20:30:00' WHERE idLieu = 11 AND dateSpectacle = '2012-09-21';

-- * projections, sélections *
-- 3. les nom, adresse et numéro de carte de fidélité de tous les clients ayant une carte
SELECT nomClient, adresseClient, carteDeFidelite FROM Client WHERE carteDeFidelite IS NOT NULL;
-- 4. les spectacles ayant lieu après le 1er avril 2012, par ordre chronologique
SELECT Spectacle.* FROM Spectacle WHERE dateSpectacle > '2012-04-01' ORDER BY dateSpectacle;
-- 5. les adresses des salles de concert ayant plus de 2 000 places (sans doublons dans la réponse)
SELECT DISTINCT adresseLieu FROM Lieu WHERE capacite >= 2000;
-- 6. le nom des clients dont le prénom commence par 'j'
SELECT nomClient FROM Client WHERE nomClient LIKE 'j%';

-- * jointures *
-- 7. les identifiant, date et heure de tous les spectacles ayant lieu à Saint-Étienne
SELECT idSpectacle, dateSpectacle, heure FROM Spectacle, Lieu WHERE Spectacle.idLieu = Lieu.idLieu AND adresseLieu = 'Saint-Étienne';
-- 8. les identifiant, date, heure et lieu de tous les spectacles ayant lieu à Bordeaux dans l'aprè-midi entre 14 heures et 18 heures
SELECT idSpectacle, dateSpectacle, heure, nomLieu FROM Spectacle, Lieu WHERE Spectacle.idLieu = Lieu.idLieu AND adresseLieu = 'Bordeaux' AND heure >= '14:00:00' AND heure <= '18:00:00';
-- 9. le nom des clients ayant achetés des billets pour un concert de Apparatus Jingle
-- Deux solutions possibles :
--  * l'une en ne tenant compte que de l'artiste principal *
SELECT DISTINCT nomClient FROM Client, Spectacle, Commande, Billet, Artiste WHERE Client.idClient = Commande.idClient AND Commande.idCommande = Billet.idCommande AND idArtistePrincipal = idArtiste AND Billet.idSpectacle = Spectacle.idSpectacle AND nomArtiste = 'Apparatus Jingle';
--  * l'une en ne tenant compte que de l'artiste principal *
(SELECT DISTINCT nomClient FROM Client, Spectacle, Commande, Billet, Artiste WHERE Client.idClient = Commande.idClient AND Commande.idCommande = Billet.idCommande AND idArtistePrincipal = idArtiste AND Billet.idSpectacle = Spectacle.idSpectacle AND nomArtiste = 'Apparatus Jingle')
UNION
(SELECT DISTINCT nomClient FROM Client, Spectacle, Commande, Billet, Artiste, ArtisteSupplementaire WHERE Client.idClient = Commande.idClient AND Commande.idCommande = Billet.idCommande AND ArtisteSupplementaire.idArtiste = Artiste.idArtiste AND ArtisteSupplementaire.idSpectacle = Spectacle.idSpectacle AND Billet.idSpectacle = Spectacle.idSpectacle AND nomArtiste = 'Apparatus Jingle');

-- * groupes *
-- 10. le nombre de commandes annulées
SELECT COUNT(*) FROM Billet;
-- 11. le nombre de commandes effectuées par client
SELECT nomClient, COUNT(*) FROM Commande, Client WHERE Commande.idClient = Client.idClient GROUP BY Client.idClient;
-- 12. la capacité moyenne des salles
SELECT adresseLieu FROM Lieu GROUP BY adresseLieu HAVING COUNT(*) > 5;
-- 13. les villes disposant de plus de 5 salles de concerts
SELECT villeAgence, COUNT(*) FROM Agence WHERE dptAgence=13 GROUP BY villeAgence HAVING COUNT(*) > 5;

-- * requêtes complexes *
-- 14. la commande ayant été effectuée la plus en avance par rapport à la date du spectacle
-- Deux solutions possibles
--  * En utilisant LIMIT (mais on ne trouve qu'une seule commande, même si peut-être plusieurs ont été faites avec autant d'avance
SELECT idCommande, dateCommande, dateSpectacle, DATEDIFF(dateSpectacle,dateCommande) AS diff FROM Commande, Spectacle, Billet WHERE Commande.idCommande = Billet.idCommande AND Spectacle.idSpectacle = Billet.idSpectacle ORDER BY diff DESC LIMIT 1;
--  * En utilisant un agrégat et des requêtes imbriquées
SELECT * FROM (SELECT Commande.idCommande, dateSpectacle, dateCommande, DATEDIFF(dateSpectacle, dateCommande) AS diff FROM Spectacle, Billet, Commande WHERE Spectacle.idSpectacle = Billet.idSpectacle AND Billet.idCommande = Commande.idCommande) AS tabaux WHERE diff = (SELECT MAX(diff) FROM (SELECT DATEDIFF(dateSpectacle, dateCommande) AS diff FROM Spectacle, Billet, Commande WHERE Spectacle.idSpectacle = Billet.idSpectacle AND Billet.idCommande = Commande.idCommande) AS tabaux2);
-- Remarque :  lorsqu'on utilise "FROM (SELECT ...)" il est obligatoire de nommer le résultat de la sélection imbriquées avec "FROM (SELECT ...) AS nomResultat"
-- 15. le palmarès des villes ayant les plus grandes salles en moyenne
SELECT adresseLieu, AVG(capacite) AS capaciteMoyenne FROM Lieu GROUP BY adresseLieu ORDER BY capaciteMoyenne DESC;
-- 16. le nombre total d'enregistrements dans toute la base (en comptant les billets, les artistes, les spectacles, les lieux, les clients, les commandes et les artistes supplémentaires)
SELECT SUM(total) FROM (SELECT COUNT(*) AS total FROM Artiste UNION SELECT COUNT(*) AS total FROM ArtisteSupplementaire UNION SELECT COUNT(*) AS total FROM Billet UNION SELECT COUNT(*) AS total FROM Client UNION SELECT COUNT(*) AS total FROM Commande UNION SELECT COUNT(*) AS total FROM Genre UNION SELECT COUNT(*) AS total FROM Lieu UNION SELECT COUNT(*) AS total FROM Spectacle) AS comptes;
-- 17. pour chaque lieu, le spectacle ayant rapporté le plus d'argent
SELECT nomLieu, Spectacle.idSpectacle, topRecette FROM Lieu, Spectacle, (SELECT idLieu, MAX(recette) AS topRecette FROM (SELECT idLieu, SUM(prix) AS recette FROM Spectacle, Billet WHERE Spectacle.idSpectacle = Billet.idSpectacle AND idCommande IS NOT NULL GROUP BY Spectacle.idSpectacle) AS tabaux GROUP BY idLieu) AS tabaux1, (SELECT idSpectacle, SUM(prix) AS recette FROM Billet WHERE idCommande IS NOT NULL GROUP BY idSpectacle) AS tabaux2 WHERE tabaux1.idLieu = Lieu.idLieu AND tabaux2.idSpectacle = Spectacle.idSpectacle AND tabaux1.topRecette = tabaux2.recette AND Spectacle.idLieu = Lieu.idLieu;