Upload
others
View
33
Download
3
Embed Size (px)
Citation preview
BEN ROMDHAN Mourad
ISET Nabeul
OBJECTIFS :
Tester quelques requêtes de SELECT sur plusieurs tables.
Partie I: Présentation
La jointure est l’opération permettant tables.
Une condition de jointure spécifie une relation existante entre les données d’une colonne dans une table avec les données d’une autre colonne d’une autre table
Cette relation est souvent établie entre des colonnes dclé étrangère.
Syntaxe:
SELECT table1.colonne, table2.colonne
FROM table1
WHERE table1.colonne = table2.colonne
Exemple:
Afficher les employés du département
SELECT Employee_id, FROM Employees E , DepWHERE E. department_idAND D. department_name
A
Tester quelques requêtes de SELECT sur plusieurs tables.
Présentation
La jointure est l’opération permettant d’extraire des données issues
Une condition de jointure spécifie une relation existante entre les données d’une colonne dans une table avec les données d’une autre colonne d’une autre table
Cette relation est souvent établie entre des colonnes définies comme clé primaire et
table1.colonne, table2.colonne table1, table2
table1.colonne = table2.colonne ;
employés du département 'Executive' :
Last_name, E.department_id, depart
, Dep artments D ment_id = D. department_id
ment_name = 'Executive' ;
TP 6: LES JOINTURES
ATELIER BASES DE DONNEES
des données issues de plusieurs
Une condition de jointure spécifie une relation existante entre les données d’une colonne dans une table avec les données d’une autre colonne d’une autre table.
éfinies comme clé primaire et
department_name
1
ATELIER BASES DE DONNEES
BEN ROMDHAN Mourad
ISET Nabeul
Application: (base de données HR)
1. Rechercher le prénom, le salaire et le travail des employés.
................................................... ......
................................................... ......
2. Liste des départements avec leurs payes situés à l'Europe.
................................................... ......
................................................... ......
3. Adresse des départements dans lesquels des employés touchent une commission.
................................................... ......
................................................... ......
4. Rechercher le prénom des employés et le numéro de la région de leur département.
................................................... ......
................................................... ......
5. Rechercher le numéro du département, le nom du département, le nom des employés ordonnés par numéro de département et situés en France.
................................................... ......
................................................... ......
6. Afficher le salaire min et max des emplois affectés au département 'Administration'
................................................... ......
................................................... ......
7. liste des employés (nom et prénom) âgés entre 20 et 50 ans, qui ont occupé le poste de ' Finance Manager' dans un département situé a ' Washington'
................................................... ......
................................................... ......
................................................... ......
2
BEN ROMDHAN Mourad
ISET Nabeul
Partie II: Types de jointures
Types de jointures:
‒ Equijointure. ‒ Auto-jointure ‒ Non-équijointure ‒ Jointure externe
II.1 Équijointure: (equi join)
Une équijointure utilise l’opérateur d’égalité dans généralement des clés primaires avec des clés étrangères.
Jointure relationnelleSelect emp.ename, dept.Locfrom Emp, Dept where Emp.noDept = Dept.noDeptAND Dept.Loc = 'Nabeul'
II.2 Auto-jointure: (self join)
Cas particulier de l’équijointure, l’autoalias de tables permettront de distinguer les enregistrements
Exemple:
Afficher la liste des employés avec le nom de leurs manager.
MANAGER_ID dans la table WORKER est
SELECT worker. FROM Employees WHERE worker. MANAGER_ID
A
Types de jointures
(equi join)
Une équijointure utilise l’opérateur d’égalité dans la clause de jointure et compare des clés primaires avec des clés étrangères.
Jointure relationnelle Jointure SQL2, dept.Loc
where Emp.noDept = Dept.noDept = 'Nabeul' ;
Select emp.ename, dept.Locfrom Emp JOIN DeptON Emp.noDept = Dept.noDeptwhere Dept.Loc = 'Nabeul'
(self join)
Cas particulier de l’équijointure, l’auto-jointure relie une table à ellealias de tables permettront de distinguer les enregistrements entre eux).
Afficher la liste des employés avec le nom de leurs manager.
MANAGER_ID dans la table WORKER est égale à EMPLOYEE_ID dans la table MANAGER.
worker. last_name AS "Employé" , manager.last_name AS "
Employees worker, Employees manager MANAGER_ID = manager. EMPLOYEE_ID
ATELIER BASES DE DONNEES
la clause de jointure et compare
Jointure SQL2 , dept.Loc
Dept Emp.noDept = Dept.noDept
= 'Nabeul' ;
ure relie une table à elle-même (des entre eux).
à EMPLOYEE_ID dans la
" Manager"
EMPLOYEE_ID;
3
ATELIER BASES DE DONNEES
BEN ROMDHAN Mourad
ISET Nabeul
Application: (base de données HR)
1. Rechercher le nom et le salaire des employés qui gagnent plus que leur patron, et le nom et le salaire de leur patron.
................................................... ......
................................................... ......
2. Rechercher le nom et le titre des employés qui ont le même titre que 'Luis Popp'.
................................................... ......
................................................... ......
3. Rechercher le nom et le titre des employés qui ont un titre que l'on trouve dans le département 100.
................................................... ......
................................................... ......
4. Rechercher le nom, le titre et le salaire des employés qui ont le même titre et le même salaire que 'Steven'
................................................... ......
................................................... ......
II.3 Non-équijointure:
À l’inverse des équijointures, la clause d’une non-équijointure n’est pas basée sur l’égalité de clés primaires et de clés étrangères.
Exemple:
La liste des employés qui ont un salaire compris entre salaire min et max.
EMPLOYEES JOBS
SELECT Employee_id, LAST_NAME, Salary FROM Employees E, Jobs J WHERE E.salary BETWEEN J.min_salary AND J.max_salary ;
Le salaire SALARY doit être compris entre MIN_SALARY et MAX_SALARY
de la table JOBS
4
BEN ROMDHAN Mourad
ISET Nabeul
II.4 Jointures externes:
Les jointures externes permettent d’extraire des enregistrements qui ne répondent pas aux critères de jointure. Lorsque deux tables sont en jointure externe, une table est « dominante » par rapport à l’autre. dominante qui sont retournés
E
Exemple:
Liste des employés avec leurs départements, même
SELECT LAST_NAMEFROM Employees EON (E. Department_id
SELECT A.*,B.* FROM A LEFT JOIN B ON (A.KEY = B.KEY);
SELECT A.*,B.* FROM A LEFT JOIN B ON (A.KEY = B.KEY) WHERE B.KEY IS NULL;
A
:
Les jointures externes permettent d’extraire des enregistrements qui ne répondent critères de jointure. Lorsque deux tables sont en jointure externe, une table
minante » par rapport à l’autre. Ce sont les enregistrements de la tabledominante qui sont retournés même s’ils ne satisfont pas aux conditions de jointure.
Liste des employés avec leurs départements, même les département
LAST_NAME, D.Department_id, D. department_nameEmployees E RIGHT OUTER JOIN Departments
Department_id = D. Department_id)
SELECT A.*,B.* FROM A RIGHT JOIN B ON (A.KEY = B.KEY);
SELECT A.*,B.*FROM A
ON (A.KEY = B.KEY);
SELECT A.*,B.* FROM A RIGHT JOIN B ON (A.KEY = B.KEY) WHERE A.KEY IS NULL;
SELECT A.*,B.*FROM A
ON (A.KEY = B.KEY)WHERE A.KEY IS NULLB.KEY IS NULL;
ATELIER BASES DE DONNEES
Les jointures externes permettent d’extraire des enregistrements qui ne répondent critères de jointure. Lorsque deux tables sont en jointure externe, une table
Ce sont les enregistrements de la table pas aux conditions de jointure.
départements sans employés
department_name Departments D
SELECT A.*,B.* FULL OUTER JOIN B
ON (A.KEY = B.KEY);
SELECT A.*,B.* FULL OUTER JOIN B
(A.KEY = B.KEY) A.KEY IS NULL OR
B.KEY IS NULL;
5
ATELIER BASES DE DONNEES
BEN ROMDHAN Mourad
ISET Nabeul
Partie III: Exercice (base vente)
1. Le numéro, adresse du Client et noCommande pour les Commandes faites le 4/06/2000.
..................................................
..................................................
2. Les codes des produits commandés au moins une fois par le Client numéro 1350 après le 01/06/2000.
..................................................
..................................................
3. Les noms des produits commandés par le Client numéro 1350.
..................................................
..................................................
4. Les numéros des Commandes qui ont été placées à la même date que la Commande numéro 2.
..................................................
..................................................
5. Les numéros de factures faites à la même date qu'une des Commandes du Client numéro 1350.
..................................................
..................................................
6. Le numéro des commandes effectuées le 4/06/2000 qui contiennent au moins deux lignes de commandes. Le résultat doit être trié par le numéro de commande.
..................................................
..................................................
..................................................
7. Les codes des produits avec la quantité totale commandée depuis le 05/07/2000 dans le cas où cette quantité dépasse 10.
..................................................
..................................................
..................................................
8. Quel est le nombre de commande par client,
..................................................
..................................................
9. Calculer le montant total d'une commande,
..................................................
..................................................
..................................................
10. Afficher les produits les plus chères (trie suivant le prix unitaire) utilisés dans les commandes du client 1350,
..................................................
..................................................
..................................................
6