cours 4 - efreidoc.frefreidoc.fr/l3/bdd/cours/cours complet 1/20xx-xx.cours.04... · 2018. 6....

SQL query -4 / 33Djamel Berrabah

Requêtes avec opérateurs ensemblistes

Villes où il y a soit un employé soit un projet?

( SELECT City FROM Emp) UNION( SELECT City FROM Project)

Villes où il y a des employés mais pas de projets?

( SELECT City FROM Emp) EXCEPT( SELECT City FROM Project)

Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City)Pay(Title, Salary) Works(Eno, Pno, Resp, Dur)


Requête imbriquée dans la clause WHERE d'une requête externe:SELECT …FROM …WHERE [Opérande] Opérateur ( SELECT …

FROM … WHERE …)

Opérateurs ensemblistes : • (A 1,…An) IN : appartenance ensembliste

• EXISTS : test d’existence (réponse non-vide)

• (A 1,…An) [ ALL| ANY] : comparaison avec quantificateur (ANY par défaut)

SQL : Requêtes imbriquées


SELECT …FROM …WHERE (A 1,…, An) [ NOT] IN ( SELECT B1,…,B n

FROM … WHERE …)

Sémantique : la condition est vraie si le n-uplet désigné par (A1,…, An) de la requête externe appartient (n’appartient pas) au résultat de la requête imbriquée.

Expression « IN »


Exemple avec “IN”

Noms des employés qui travaillent dans des villes où il y a [n’y a pas] des projets de budget inférieur à 50 k€ ?

SELECT EnameFROMEmpWHERE City [ NOT] IN ( SELECT City

FROM Project WHERE Budget < 50)



Exemple avec “EXISTS”

Noms des employés qui travaillent dans une ville où il y a un projet?

SELECT Ename FROM EmpWHERE EXISTS ( SELECT *

FROM Project WHERE Emp.City=Project.City)

Noms des employés qui travaillent dans une ville sans projet?

SELECT Ename FROM EmpWHERE NOT EXISTS ( SELECT *

FROM Project WHERE Emp.City=Project.City)



Calcul relationnel et SQL

Requête SQL de base:

SELECT r i . Ao, …, r j . Ap FROM R1 r 1, Ri r i , …, Rj r j , …, Rk r k WHERE P( r 1, r 2, …, r k)

Calcul relationnel :{ (r i.Ao ,…, rj.Ap) | ∃r l,..., ∃rk (R1(r1 ) ∧ Ri (r i ) ... ∧ Rk(rk ) ∧

P(r1,..., rk ) ) }

Rappel: SQL (sans fonctions d’agrégation et de groupement) est équivalent au calcul relationnel sain (et à l’algèbre relationnel).


Calcul relationnel et SQL

Employés qui travaillent dans tous les projets? • Algèbre relationnelle : πENO, PNO (Works) ÷ πPNO (Project)

• Calcul relationnel (division) :{ e.Eno | Emp(e) ∧∀p ( Project(p) ⇒ ∃ w( Works(w) ∧

p.Pno = w.Pno ∧ w.Eno= e.Eno ))}{ e.Eno | Emp(e) ∧∀p (¬Project(p) ∨ ∃ w( Works(w) ∧

p.Pno = w.Pno ∧ w.Eno= e.Eno ))}

Eliminer ∀: ∀x F(x) ≡ ¬∃x(¬ F(x)){ e | Emp(e) ∧¬∃ p (Project(p) ∧ ¬∃ w( Works(w) ∧

p.Pno = w.Pno ∧ w.Eno = e.Eno ))}



SQL : interrogation de BD

Requêtes d'interrogation simplesRequêtes complexesSQL et le calcul relationnel Agrégats et groupementCouplage SQL et langage de programmation


Exemples d'agrégation

Budgets totaux des projets de Paris?SELECT SUM(Budget)FROM ProjectWHERECity = ‘Paris’

Nombre de villes où il y a un projet avec l'employé E4?SELECT COUNT( DISTINCT City)FROM Project, WorksWHEREProject.Pno = Works.PnoAND Works.Eno = ‘E4’



Fonctions d'agrégation

Noms des projets dont le budget est supérieur au budget moyen?

SELECT PnameFROM ProjectWHEREBudget > ANY

( SELECT AVG(Budget) FROM Project)



GROUP BY

SELECT A1, B1, sum(A2)FROM R1, R2

WHERE A1 < 3GROUP BY A1, B1

R1 A1 A2

R2 B1

A1 A2 B1

A1 A2 B1

group by

from

where

A1 B1 sum(A2) select

A1 B1 A2*

143

12

68

6182

2121

22

11

2

2

13

1

3

6

8

23

1

3

a

a

aa

a

a

a

aaa

a

aaa

a

2

2

13

1

3

6

8

23

1

3


Exemples de groupement

Numéros des projets avec le nombre d’employés impliqués par projet ?SELECTPno, Count(Eno)FROM WorksGROUP BY Pno

Noms des projets avec la durée moyenne et la durée maximale de participation d’un employé par projet ?SELECT Pname, AVG(Dur), MAX(Dur)FROM Works, ProjectWHERE Works.Pno=Project.PnoGROUP BY Pno, Pname



Predicats sur des groupesPour garder les groupes (partitions) qui satisfont une certaine

condition :SELECT Ai , …, AnFROM R1, …, Rm WHERE PGROUP BY Aj …, AkHAVING Q

Règle : La condition Q porte généralement sur des valeurs atomiques retournées par un opérateur d'agrégation sur les attributs qui n’apparaissent pas dans le GROUP BY


Exemples de groupement

Villes dans lesquelles habitent plus de 2 employés?SELECTCityFROM EmpGROUP BY CityHAVING COUNT(ENO) > 2

Projets dans lesquels plus de 2 employés partagent une responsabilité?SELECT DISTINCT PnoFROM WorksGROUP BY Pno, RespHAVING COUNT(DISTINCT ENO) > 2



SQL : interrogation de BD

Requêtes d'interrogation simplesRequêtes complexesSQL et le calcul relationnel Agrégats et groupementCouplage SQL et langage de programmation


Développement d'applicationProgramme source

avec SQL « embedded »

Préprocesseur

Programme source

Compilateur

Fichiers objets

Edition de liens

Exécutable

Bibliothèques(fcts. SQL, ...)

Autres fichiersobjets et bibs


CurseursSi la requête retourne un ensemble de n-uplets, il faut utiliser un

curseur qui permet d’accéder successivement aux n-uplets du résultat de la requête SQL:• déclarer (declare) le curseur :

� associe un identificateur de curseur avec une requête SQL

• ouvrir (open) le curseur :� exécution de la requête et génération du résultat (vue logique)� positionner le curseur avant le premier n-uplet

• récupérer (fetch) un n-uplet :� avancer le curseur (boucle)� affecter des variables avec les valeurs du n-uplet pointé par le curseur

• fermer (close) le curseur.


Exemple de curseurPour chaque projet employant plus de 2 programmeurs, donner le numéro de

projet et la durée moyenne d'affectation des programmeurs…EXEC SQL BEGIN DECLARE SECTION;

char pno[3]; /* project number */real avg-dur; /* average duration */

EXEC SQL END DECLARE SECTION;…EXEC SQL DECLARE duration CURSOR FOR

SELECT Pno, AVG(Dur)FROM WorksWHERE Resp = ‘Programmer’GROUP BY PnoHAVING COUNT(*) > 2;…EXEC SQL OPEN duration ;…while(1) {

EXEC SQL FETCH FROM duration INTO :pno , :avg-durif(strcmp(SQLSTATE, “02000”) then breakelse < print the info >

}EXEC SQL CLOSE duration…


PL/SQL d’OraclePL/SQL : langage de programmation procédural intégrant SQL.

Bloc PL/SQL : regroupement logique de déclarations et d’ordres.declare

sal number;begin

SELECTSALARY INTO salFROM PAYWHERE TITLE = ‘Programmer’;if sal>10 then

UPDATE PAYSET SALARY = SALARY * 1.1WHERE TITLE = ‘Programmer’;

end if;COMMIT;

exceptionwhen no_data_found then

INSERT INTO ERROR values (‘TITLE inexistant’);


Traitement de PL/SQL

BlocPL/SQL Exécution

Analyse

Moteur PL/SQL

Serveur Oracle

cours 4 - efreidoc.frefreidoc.fr/l3/bdd/cours/cours complet 1/20xx-xx.cours.04... · 2018. 6....

Documents