Le but de ce TP est d'utiliser d'autres outils de SQL, qui sont moins importants que ceux vu dans le cours, mais qui font partie de l'écosystème SQL.
Importez ce fichier sql : cinema.sql.
Voici le schéma UML simbolisant ces tables :
BETWEEN est un mot clé utilisé dans les conditions.
WHERE colonne BETWEEN value1 AND value2;- Forumulez une requête pour obtenir les acteurs nés entre 1970 et 1980 en utilisant
BETWEEN.
SELECT *
FROM acteurs
WHERE date_naissance BETWEEN '1970-01-01' AND '1979-12-31'- Formulez une requête pour obtenir les films sortis dans les années 90 en utilisant
BETWEEN.
SELECT *
FROM films
WHERE date_sortie BETWEEN '1990-01-01' AND '1999-12-31'- Formulez une requête pour obtenir les réalisateurs dont les prénoms ont une première lettre comprise entre 'D' et 'N', en utilisant
BETWEEN.
SELECT *
FROM realisateurs
WHERE prenom =! 'O'
AND prenom BETWEEN 'D' AND 'O'Seulement, BETWEEN n'est pas très utile. En effet, il vaudra mieux utiliser des conditions plus classiques, afin de pouvoir mieux gérer la question de l'inclusivité des bornes.
BETWEEN ne peux avoir des bornes qu'inclusives.
Rappel : borne inclusive x>=3, borne exclusive x>3
Reformulez les trois questions précédentes, en utilisant des bornes exclusives.
A contrario de BETWEEN, qui n'est pas très interessant, LIKE est nécessaire à une maitrise importante de SQL.
LIKE permet de jouer sur les champs de texte (charcater varying par exemple).
WHERE colonne LIKE 'expression'L'interet de LIKE réside dans le fait qu'on puisse filtrer sur des champs de texte qui vérifient des patterns.
Par exemle,
SELECT * FROM cinema.acteurs
WHERE nom LIKE `%osling'donne tous les acteurs dont le nom finit par 'osling', tels que 'Gosling', 'Aosling', mais aussi 'ABCDosling', etc...
Il existe deux characters spéciaux à utiliser dans les exprssions de LIKE :
- '%' permet de remplacer tous les charactères, peut importe leur nombre.
- '_' permet de remplacer n'importe quel charactère, mais qu'un seul charactère uniquement.
- Sélectionnez les acteurs avec un prénom commenceant par 'K'.
SELECT *
FROM acteurs
WHERE prenom LIKE 'K%'- Sélectionnez les films avec un titre finissant par 'd'.
SELECT *
FROM films
WHERE titre LIKE '%d'- Sélectionnez les films avec 'The' dans leur titre.
SELECT *
FROM films
WHERE titre LIKE '%The%`- Sélectionnez les réalisateurs des films ayant deux 'l' à la suite dans leur titre.
SELECT r.nom
FROM realisateurs AS r
JOIN films AS f
ON r.realisateur_id = f.realisateur_id
WHERE f.titre LIKE '%ll%'- Sélectionnez les acteurs ayant joué dans des films ayant au moins deux 'l' dans leur titre.
SELECT a.nom
FROM acteurs AS a
JOIN casting AS c
ON a.acteur_id = c.acteur_id
JOIN films AS f
ON f.film_id = c.film_id
WHERE f.titre LIKE '%l%l%'On remarque que 'La La Land' n'est pas dans cette dernière liste. Pourquoi ?
Pour pallier ce problème, on peut utiliser la fonction UPPER(texte), qui renvoie tout le texte en majuscules.
- Donnez le titre (avec la casse correcte) des films ayant au moins deux 'l', peut importe leur casse, dans leur titre.
SELECT titre
FROM films
WHERE UPPER(titre) LIKE UPPER('%l%l%')- Quels sont les acteurs des comédies ?
SELECT a.nom
FROM acteurs AS a
JOIN casting AS c
ON a.acteur_id = c.acteur_id
JOIN films AS f
ON f.film_id = c.film_id
WHERE UPPER(f.genre) LIKE UPPER('comédie')- Comment trouver les rôles avec le charactère '_' dedant, comme 'Bruce Wayne _ Batman' ?
SELECT role
FROM casting
WHERE role LIKE '%!_%' ESCAPE '!'- Quels sont les films dans lequels le réalisateurs, et au moins un des acteurs, possède un 'e' dans leur nom ou prénom ?
SELECT DISTINCT a.nom
FROM acteurs AS a
JOIN casting AS c
ON a.acteur_id = c.acteur_id
JOIN films AS f
ON f.film_id = c.film_id
JOIN realisateurs AS r
ON r.realisateur_id = f.realisateur_id
WHERE (r.nom LIKE '%e%' OR r.prenom LIKE '%e%')
AND (a.nom LIKE '%e%' OR a.prenom LIKE '%e%')