SQL: Requ

1
SQL Requêtes, Programmation et Triggers

• Chapitre 5, Sections 15.5

2
Survol des Composantes de SQL

• Langage de manipulation des données (DML)
  utilisé pour poser des requêtes, insérer, effacer
  ou modifier des lignes.
• Langage de définition des données (DDL) utilisé
  pour créer, détruire ou modifier les tables et
  vues.
• Triggers et contraintes dintégrité avancées
  utilisés pour spécifier des actions que le SGBD
  exécutera automatiquement.
• SQL incorporé permet à SQL dêtre appelé dun
  langage hôte.
• SQL dynamique permet de créer et dexécuter des
  requêtes pendant lexécution dun programme
  dapplication.

3
Survol des Composantes de SQL (Suite)

• Exécution client serveur et accès à distance aux
  BDs commandes sur laccès à un serveur distant.
• Gestion des transactions contrôle lexécution
  des transactions.
• Sécurité contrôle laccès des utilisateurs au
  système.
• Divers composantes orientation objet,
  récursivité, aide à la décision, XML, données
  spatiales, exploration des données (data mining),
  etc.

DML, DDL, triggers et ICs seront vu dans ce
module.
4
Exemples dinstances
R1
S1
S2
5
Requête SQL de Base
SELECT DISTINCT target-list FROM
relation-list WHERE qualification

• relation-list Une liste des noms de relation
  (possiblement avec une variable détendue
  (range-variable) après chaque nom).
• target-list Une liste dattributs des relations
  dans relation-list
• qualification Comparaisons (Attr op const ou
  Attr1 op Attr2, où op est une des opérations
  ) combinées en
  utilisant les particules logiques AND, OR et NOT.
• DISTINCT est un mot-clé optionnel indiquant que
  la réponse ne devrait pas contenir des duplicata.
  Par défaut les duplicata ne sont pas éliminés.

6
Stratégie dÉvaluation Conceptuelle

• La sémantiques dune requête SQL est définie en
  termes de la stratégie dévaluation suivante
• Calculer le produit Cartésien de relation-list.
• Effacer du résultat tous les tuples ne
  remplissant pas les qualifications.
• Effacer les attributs qui ne sont pas dans
  target-list.
• Si DISTINCT est spécifié, éliminer les ligne
  redondantes (duplicata).
• Cette stratégie est probablement la moins
  efficiente manière de calculer la réponse à une
  requête! Un optimisateur trouvera sûrement une
  stratégie plus efficiente de calculer les mêmes
  réponses.

7
Exemple dÉvaluation Conceptuelle
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid AND R.bid103
8
Une Note sur les Variables dÉtendue

• Leur utilisation nest strictement nécessaire que
  si la même relation apparaît deux fois dans la
  clause FROM. Ainsi la requête précédente a deux
  formulations alternatives

SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid AND bid103
Lutilisation des variables détendue est
cependant considérée comme un bon style!
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND
bid103
OU
9
Trouver les navigateurs qui ont réservé au moins
un bateau
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid

• Lajout de DISTINCT ferait-il une différence dans
  cette requête?
• Quel serait leffet du remplacement de S.sid par
  S.sname dans la clause SELECT?
• Lajout de DISTINCT à cette dernière variante de
  la requête ferait-il une différence?

10
Expressions et Chaînes (Strings)
SELECT S.age, age1S.age-5, 2S.age AS age2 FROM
Sailors S WHERE S.sname LIKE B_B

• Illustre lutilisation dexpressions
  arithmétiques et des filtrages des chaînes
  (string pattern matching) Trouver des
  triplets (formés des âges des navigateurs et de
  deux autres attributs définis par des
  expressions) pour des navigateurs dont les noms
  commencent et se terminent par B et contiennent
  au moins 3 caractères.
• AS et sont deux manières de nommer des
  attributs dans le résultat.
• LIKE est utilisé pour le filtrage des chaînes de
  caractères. _ est utilisé pour un caractère
  (manquant) et tient lieu de 0 ou plus dun
  caractère arbitraire.

11
Trouver les sids des navigateurs qui ont réservé
un bateau rouge ou vert

• UNION peut être utilisée pour calculer lunion
  de deux ensembles de tuples qui sont compatibles
  vis-à-vis de lunion (Ces derniers étant
  eux-mêmes le résultat des requêtes SQL).
• Si nous remplaçons OR par AND dans la première
  version, quel serait le résultat?
• Si nous remplaçons UNION par EXCEPT nous
  calculons la différence.

SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid AND
(B.colorred OR B.colorgreen)
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred UNION SELECT S.sid FROM
Sailors S, Boats B, Reserves R WHERE S.sidR.sid
AND R.bidB.bid AND
B.colorgreen
12
Trouver les sids des navigateurs qui ont réservé
un bateau rouge et un bateau vert
SELECT S.sid FROM Sailors S, Boats B1, Reserves
R1, Boats B2, Reserves R2 WHERE
S.sidR1.sid AND R1.bidB1.bid AND
S.sidR2.sid AND R2.bidB2.bid AND
(B1.colorred AND B2.colorgreen)

• INTERSECT Peut être utilisé pour calculer
  lintersection de deux ensembles de tuples qui
  compatibles vis-à-vis de lunion.
• Inclus dans le standard SQL/92, mais certains
  systèmes ne le supportent pas.
• Contrastez la symétrie des requêtes utilisant
  UNION et INTERSECT avec lasymétrie des autres
  requêtes exprimant la même chose.

SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred INTERSECT SELECT S.sid FROM
Sailors S, Boats B, Reserves R WHERE
S.sidR.sid AND R.bidB.bid AND
B.colorgreen
13
Requêtes Imbriquées (Nested Queries)
Trouver les noms des navigateurs qui ont réservé
le bateau 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid103)

• Ceci est un mécanisme très puissant de SQL une
  clause WHERE peut contenir une requête SQL!
• Pour trouver les navigateurs qui nont pas
  réservé le bateau 103, on utilise NOT IN.
• La sémantique des requêtes imbriquées est
  comparable à lévaluation des boucles
  imbriquées Pour chaque tuple de Sailors,
  vérifier la qualification en calculant la sous
  requête.

14
Requêtes Imbriquées avec Corrélation
Trouver les noms des navigateurs qui ont réservé
le bateau 103
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM
Reserves R WHERE
R.bid103 AND S.sidR.sid)

• EXISTS est un autre opérateur de comparaison
  densemble semblable à IN.
• Si UNIQUE est utilisé et est remplacé par
  R.bid, la requête cherche les navigateurs avec au
  plus une réservation pour le bateau 103.
• Cette exemple montre pourquoi, en général, la
  sous requête doit être recalculée pour chaque
  tuple de Sailors.

15
Opérateurs de Comparaison dEnsemble

• On déjà vu IN, EXISTS et UNIQUE. Leur négation
  sont aussi utilisées NOT IN, NOT EXISTS et NOT
  UNIQUE.
• Il y a aussi op ANY, op ALL, op IN
• Trouver les navigateurs dont le niveau est plus
  grand que celui dun navigateur appelé Horatio

SELECT FROM Sailors S WHERE S.rating gt ANY
(SELECT S2.rating
FROM Sailors S2
WHERE S2.snameHoratio)
16
Expression des Requêtes INTERSECT en Utilisant IN
Trouver les sids des navigateurs qui ont réservé
un bateau rouge et un bateau vert
SELECT S.sid FROM Sailors S, Boats B, Reserves
R WHERE S.sidR.sid AND R.bidB.bid AND
B.colorred AND S.sid IN (SELECT
S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sidR2.sid
AND R2.bidB2.bid
AND B2.colorgreen)

• De même, les requêtes EXCEPT peuvent être
  re-écrites en utilisant NOT IN.
• Pour trouver les noms (names) (pas les sids) des
  navigateurs qui ont réservé des bateaux rouges et
  des bateaux vert, il suffit de remplacer S.sid
  par S.sname dans la clause SELECT.

17
Division en SQL
Trouver les navigateurs qui ont réservé tous les
bateaux.
SELECT S.sname FROM Sailors S WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B) EXCEPT
(SELECT R.bid FROM
Reserves R WHERE R.sidS.sid))

• Cette requête est difficile à exprimer sans
  utiliser EXCEPT