[MySQL] Requêtes Imbriquées et corrélées

Les tables SQL sans clé étrangère entre elles, on peut jouer avec les requêtes imbriquées et corrélées sans jointure : IN, EXIST, ALL, ANY, etc.

Je crée deux tables EMPLOYE, SERVICE pour pratiquer en jouant avec ces syntaxes-là. Les structures des tables sont comme suivantes :

CREATE TABLE EMPLOYE(
mat INT(11),
nomEmploye varchar(15),
fonction varchar(15),
dateEmbauche date,
salaire INT(11),
commission decimal(7,2),
numServEmploye INT(11),
matSuperieur INT(11));

CREAT TABLE SERVICE(
numService INT(11),
nomService varchar(15),
lieu varchar(15));

Affichez par ordre alphabétique de nom, les noms d’employés qui travaillent dans le nom Service correspondant à Recherche. Faites la requête de 2 manière et avec imbrication sans jointure.

SELECT nomEmploye 
FROM EMPLOYE 
WHERE numServEmploye IN( 
	SELECT numService 
	FROM SERVICE 
	WHERE nomService="Recherche") 
ORDER BY nomEmploye;

OU, on peut aussi utiliser la syntaxe EXISTS

SELECT nomEmploye
FROM EMPLOYE
WHERE EXISTS(
	SELECT numService
	FROM SERVICE
	WHERE nomService="Recherche")
ORDER BY nomEmploye;

Quelle est la différence entre EXISTS et IN ?

EXISTS est beaucoup plus rapide que IN lorsque le champ de résultats est large. À l'inverse, pour le champ de résultat est petit, IN est plus adaptée EXISTS que .

Affichez les noms d’employés et le salaire trié par ordre décroissant, des employés qui ont un salaire strictement inférieur à tous dont la fonction correspondant à ingénieur. Utilisez le mot clé ALL (tous).

SELECT nomEmploye, salaire 
FROM EMPLOYE 
WHERE salaire < ALL(
	SELECT salaire 
	FROM EMPLOYE 
	WHERE fonction="ingénieur") 
ORDER BY salaire DESC;

À l’aide de la clause EXISTS, affichez les nom d’employés qui ont au moins un employé sous leur responsabilité.

SELECT nomEmploye 
FROM EMPLOYE s 
WHERE EXISTS(
	SELECT matSuperieur 
	FROM EMPLOYE e 
	WHERE s.mat=e.matSuperieur);

J'ai aussi manipuler cette requête avec la syntaxe IN.

SELECT nomEmploye 
FROM EMPLOYE e 
WHERE e.mat IN(
	SELECT s.matSuperieur 
	FROM EMPLOYE s)

Affichez par ordre alphabétique de nom, les nom d’employés qui n’ont aucun employé sous leur responsabilité.

SELECT nomEmploye
FROM EMPLOYE s
WHERE NOT EXISTS(
	SELECT matSuperieur
	FROM EMPLOYE e
	WHERE s.mat=e.matSuperieur)
ORDER BY nomEmploye;

Affichez les noms des employés dont le lieu correspondant à Rennes et le nom de leur supérieur. Vous devez utiliser une auto-jointure sur la table des employés (pour afficher les 2 noms) et une sous-requête pour un lieu.

SELECT e.nomEmploye,s.nomEmploye
FROM EMPLOYE e
JOIN EMPLOYE s
	ON e.matSuperieur=s.mat. //auto jointure sur la table des employés
WHERE numServEmploye IN( 
	SELECT numService
	FROM SERVICE 
	WHERE lieu="Rennes"); //sous-requête pour le lieu = "Rennes"

Affichez les nom d’employés qui ont un salaire strictement supérieur à la moyenne des employés qui ont la même fonction qu’eux.

SELECT nomEmploye
FROM EMPLOYE c1 
WHERE salaire > (
	SELECT avg(c2.salaire) 
	FROM EMPLOYE c2 
	WHERE c1.fonction=c2.fonction);

Affichez par service le nombre d’employés et le pourcentage de ce nombre par rapport au nombre total d’employés.

Ici, la sous requête est utilisée pour obtenir un élément de groupe count() sur la table EMPLOYE.

SELECT 
    s.nomService, 
    COUNT(e.mat) AS Nombre, 
    CAST(ROUND(COUNT(e.mat) * 100.0 / 
	(SELECT COUNT(*) FROM EMPLOYE), 2) AS DECIMAL(10, 2)) AS Pourcentage
FROM 
    SERVICE s
JOIN 
    EMPLOYE e ON s.numService = e.numServEmploye
GROUP BY 
    s.nomService