Introduction à SQL

1. Introduction à SQL Select-From-Where Le sens des requêtes Requêtes imbriquées et complexes Modifier des relations (d’après les transparents de Ullman)

2. SQL? • SQL est un langage de haut niveau avec lequel le programmeur évite la manipulation explicite des données comme avec un langage de programmation classique • SQL est une solution viable car ses requêtes sont optimisées.

3. Select-From-Where • La syntaxe d’une requête est la suivante : SELECT attributs désirés FROM une ou plusieurs tables WHERE condition (sur les tuples de la table)

4. Un Exemple • Un schéma de base de données • Les attributs soulignés désignent la clé. Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)

5. Exemple • Les bières fabriquées par Kro SELECT name FROM Beers WHERE manf = ‘Kronenbourg’;

6. Résultat de la requèete name ‘1664’ ‘Kronenbourg’ ‘Tourtel’ La réponse est une relation composée d’un seul attribut ‘name’.

7. Sens d’une requête sur une seule relation • Commencer avec la relation référencée dans le ‘from’ • Appliquer la sélection avec la condition du ‘where’ • Appliquer la projection avec les attributs de la partie ‘select’

8. Sémantique opérationnelle • Pour implémenter cet algorithme, pensez à une variable tuple qui stocke tous les n-uplets de la relation mentionnée dans le ‘from’ • Vérifiez si le tuple courant satisfait la clause WHERE • Si oui, calculez les attributs ou expressions de la clause ‘select’ à partir des composants de tuple

9. * Dans une clause ‘select’ • Quand il y a une relation mentionnée dans la partir ‘from’, le * signifie “tous les attributs de cette relation.” • Exemple using Beers(name, manf): SELECT * FROM Beers WHERE manf = ‘Kronenbourg’;

10. Résultat de la requête: name manf ‘1664’ ‘Kronenbourg’ ‘Kronenbourg’ ‘Kronenbourg’ ‘Tourtel’ ‘Kronenbourg’ La relation résultat apparaît avec tous les attributs de la relation ‘beer’

11. Renommer les attributs • Si on souhaite que la relation résultat ait un nom d’attribut différent utiliser “AS <new name>” pour renommer l’attribut. • Exemple sur la relation Beers(name, manf): SELECT name AS beer, manf FROM Beers WHERE manf = ‘Kronenbourg’

12. Result of Query: beer manf ‘1664’ ‘Kronenbourg’ ‘Kronenbourg’ ‘Kronenbourg’ ‘Tourtel’ ‘Kronenbourg’

13. Les expressions dans la clause ‘select’ • Une expression peut apparaître dans la partie ‘select’. • Exemple: pour Sells (bar, beer, price): SELECT bar, beer, price * 120 AS priceInYen FROM Sells;

14. Résultat de la requête bar beer priceInYen Joe’s Bud 300 Sue’s Miller 360 … … …

15. Un autre exemple : avec des expressions constantes • Sur la relation Likes(drinker, beer): SELECT drinker, ‘likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ‘Bud’;

16. Résultat de la requête drinker whoLikesBud ‘Sally’ ‘likes Bud’ ‘Fred’ ‘likes Bud’ … …

17. Expression complexe dans la partie ‘where’ • À partir de la relation Sells(bar, beer, price), donner le prix de la Bud dans le bar ‘Joe’s’ SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’;

18. Points importants • Attention au caractère spécial ‘’’ (quote) • Les conditions de la clause WHERE peuvent utiliser les opérateurs AND, OR, NOT, et les parenthèses. • SQL n’est pas sensible à la casse (pas de différences entre minuscules et majuscules).

19. Patterns • Dans la clause WHERE, on peut vouloir comparer une chaîne de caractères avec un pattern. • Forme générale: • <Attribute> LIKE <pattern> • <Attribute> NOT LIKE <pattern> • Pattern est une chaîne entre quotes et peut référencer les caractères spéciaux • % = “any string”; • _ = “any character.”

20. Exemple • A partir de la relation Drinkers(name, addr, phone) trouver les buveurs avec 555 comme indicatif : SELECT name FROM Drinkers WHERE phone LIKE ‘%555-_ _ _ _’;

21. Les valeurs Nulles • Les tuples peuvent avoir ‘NULL’ comme valeurs de un ou de plusieurs attributs. • 2 cas sont possibles : • Valeurs manquantes : e.g., on ne connaît pas l’adresse d’un bar mais on sait qu’elle existe • Inapplicable : e.g., le champ conjoint pour un célibataire.

22. Comparer avec NULL • La logique utilisée par SQL repose sur TRUE, FALSE, UNKNOWN. • Lorsqu’une valeur est comparée à NULL, on renvoie UNKNOWN. • Une requête sélectionne un tuple si la condition de la partie ‘WHERE’ est évaluée à TRUE (pas FALSE ou UNKNOWN).

23. Requêtes avec plusieurs relations • Les requêtes peuvent faire intervenir plusieurs relations en les mentionnant dans la clause ‘FROM’. • On peut distinguer les attributs (enlever une ambiguïté) en les préfixant avec le nom de la relation “<relation>.<attribut>”

24. Exemple • À partir des relations Likes(drinker, beer) et Frequents(drinker, bar), donnez les bières appréciées par au moins une personne qui fréquente le bar Joe’s. SELECT beer FROM Likes, Frequents WHERE bar = ‘Joe’’s Bar’ AND Frequents.drinker = Likes.drinker;

25. Sémantique • Presque identique au cas d’une seule relation: • Commencez avec le produit cartésien de toutes les relations intervenant dans la clause FROM • Appliquez la condition de sélection mentionnée dans la clause ‘WHERE’. • Projetez sur la liste des attributs et expressions de la clause SELECT.

26. Sémantique opérationnelle • Imaginez une variable tuple pour chaque relation de la clause ‘FROM’. • Ces variables itèrent sur les éléments de chacune des relations. • Si ces variables tuples satisfont la condition de la clause ‘where’, envoyez les tuples vers la clause ‘select’.

27. vers ‘select’ Vérifie Joe’s Vérifie si égalité Exemple drinker bar drinker beer tv1 tv2 Sally Bud Sally Joe’s Likes Frequents

28. Variables tuples explicites • Une requête peut nécessiter 2 copies de la même relation. • On peut distinguer les copies à l’aide de variables tuples explicites. • On peut aussi renommer une relation par commodité (nom trop long par exemple).

29. Exemple • A partir de la relation Beers(name, manf), trouvez toutes les paires de bières fabriquées par le même brasseur • Ne pas faire apparaître des couples identiques (Bud, Bud). • Faire apparaître les couples dans l’ordre alphabétique. (Bud, Miller), pas (Miller, Bud). SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name;

30. Les sous requêtes • Une instruction parenthèsée SELECT-FROM-WHERE (subquery) peut apparaître comme valeur en différents endroits (y compris dans une autre requête). • Ceci est en particulier utilise si la requête ne renvoie qu’un seul n-uplet

31. Exemple • À partir de la relation Sells(bar, beer, price), trouvez les bars qui servent Miller le même prix que Joe demande pour Bud. • Approche : • Trouver le prix que Joe demande pour une Bud. • Trouver les bars qui servent Miller pour ce prix.

32. Le prix fixé Par Joe pour Une Bud Requête + sous requête SELECT bar FROM Sells WHERE beer = ‘Miller’ AND price = (SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’);

33. L’opérateur IN • <tuple> IN <relation> rend VRAI ssi le tuple fait partie de la relation. • <tuple> NOT IN <relation> est le contraire. • IN-expressions peuvent apparaître dans les clauses WHERE. • la <relation> est souvent le résultat d’une sous requête.

34. Les bières aimées par Fred Exemple • À partir des relations Beers(name, manf) et Likes(drinker, beer), donnez les noms et brasseurs pour chaque bière aimée par Fred SELECT * FROM Beers WHERE name IN (SELECT beer FROM Likes WHERE drinker = ‘Fred’);

35. L’opérateur Exists • EXISTS( <relation> ) est VRAI ssi la <relation> n’est pas vide. • Peut apparaître dans une clause WHERE.

36. L’opérateur ANY • x = ANY( <relation> ) est une condition booléene qui indique si x est égal à au moins un tuple de la relation. • Exemple: x >= ANY( <relation> ) signifie que x ne peut pas être plus petit que les tuples de la relation • À noter : les tuples ne peuvent avoir qu’un seul composant.

37. Le prix pour la requête externe ne doit pas être inférieur Exemple avec l’opérateur ALL • À partir de la relation Sells(bar, beer, price), trouvez la ou les bières facturées au prix le plus élevé SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells);

38. Opérations ensemblistes • Union, intersection, et différence sur des relations sont exprimées de la manière suivante : • ( subquery ) UNION ( subquery ) • ( subquery ) INTERSECT ( subquery ) • ( subquery ) EXCEPT ( subquery )

39. Différentes jointures comme en algèbre relationnelle • Jointure naturelle: R NATURAL JOIN S; • Produit cartésien: R CROSS JOIN S; • Exemple: Likes NATURAL JOIN Serves;

40. Aggrégats • SUM, AVG, COUNT, MIN, et MAX peuvent être appliqués à une colonne dans la clause SELECT • COUNT(*) compte le nombre de tuples • NULL n’apparaît jamais dans l’évaluation d’un agrégat

41. Exemple • À partir de la relation Sells(bar, beer, price), trouvez le prix moyen de la Bud: SELECT AVG(price) FROM Sells WHERE beer = ‘Bud’;

42. Grouper • On peut faire suivre une expression SELECT-FROM-WHERE par GROUP BY et une liste d’attributs. • La relation qui résulte de l’évaluation de SELECT-FROM-WHERE est divisée en groupes en fonction de la valeurs des attributs et toute agrégation est évaluée uniquement sur chacun des groupes

43. exemple • À partir de la relation Sells(bar, beer, price), trouvez le prix moyen de chaque bière : SELECT beer, AVG(price) FROM Sells GROUP BY beer;

44. Un autre exemple • À partir de Sells(bar, beer, price) et de Frequents(drinker, bar), donnez pour chaque buveur le prix moyen de la Bud dans les bars qu’ils fréquentent : SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = ‘Bud’ AND Frequents.bar = Sells.bar GROUP BY drinker;

45. Restriction sur la partie SELECT • Si une agrégation est utilisée, chaque élément de la clause SELECT doit être soit : • Agrégée, soit • un attribut de la liste mentionnée par GROUP BY.

46. Clauses HAVING • HAVING <condition> peut suivre une clause GROUP BY • Si oui, la condition s’applique à chaque groupe et les groupes qui ne la satisfont pas sont éliminés.

47. Les conditions HAVING • Ces conditions peuvent référencer n’importe quelle relation de la liste FROM. • Elles peuvent référencer les attributs de ces relations, tant que ces attributs ont un sens dans le groupe : • Un attribut de groupage (grouping attribute), ou • aggrégé

48. Exemple: HAVING • À partir des relations Sells(bar, beer, price) et Beers(name, manf), donnez le prix moyen des bières servies dans au moins 3 bars ou brassées par Pete’s.

49. Groupes de bières composées de au moins 3 bars non NULL Et groupes de bières brassées par Pete’s. Bières brassées par Pete’s. Solution SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = ‘Pete’’s’);

50. Modification de la base • Une commande de modification s’exprime presque comme une requête • Une modification ne retourne pas un résultat comme une requête mais modifie la base de données • 3 sortes de modifications sont possibles : • Insérer un ou plusieurs tuples • Détruire un ou plusieurs tuples. • Mettre à jour un ou plusieurs tuples