TP N°3 - comprendre le fonctionnement des clauses SQL

Table "Products"

Correspond à la requête : SELECT * FROM Products

productId productLabel productPrice productCategory
1Ordinateur Asus7501
2Ordinateur HP8001
3Téléphone HTC2502
4Ordinateur Lenovo6751
5Téléphone iPhone50002
6Ordinateur MacOS100001
7Ordinateur Acer21

Table "Categories"

Correspond à la requête : SELECT * FROM Categories

categoryId categoryLabel
1Ordinateur
2Téléphone

Clause SELECT

Correspond à la requête : SELECT productLabel, productPrice FROM Products

productLabel productPrice
Ordinateur Asus750
Ordinateur HP800
Téléphone HTC250
Ordinateur Lenovo675
Téléphone iPhone5000
Ordinateur MacOS10000
Ordinateur Acer2

Clause WHERE

Correspond à la requête : SELECT * FROM Products WHERE productPrice > 900

productId productLabel productPrice productCategory
5Téléphone iPhone50002
6Ordinateur MacOS100001

Clauses SELECT + WHERE

Correspond à la requête : SELECT productLabel, productPrice FROM Products WHERE productPrice > 900

productLabel productPrice
Téléphone iPhone5000
Ordinateur MacOS10000

Clause FROM et produit cartésien

Correspond à la requête : SELECT * FROM Products, Categories

productId productLabel productPrice productCategory categoryId categoryLabel
1Ordinateur Asus75011Ordinateur
1Ordinateur Asus75012Téléphone
2Ordinateur HP80011Ordinateur
2Ordinateur HP80012Téléphone
3Téléphone HTC25021Ordinateur
3Téléphone HTC25022Téléphone
4Ordinateur Lenovo67511Ordinateur
4Ordinateur Lenovo67512Téléphone
5Téléphone iPhone500021Ordinateur
5Téléphone iPhone500022Téléphone
6Ordinateur MacOS1000011Ordinateur
6Ordinateur MacOS1000012Téléphone
7Ordinateur Acer211Ordinateur
7Ordinateur Acer212Téléphone

Jointure classique INNER JOIN

Correspond à la requête : SELECT * FROM Products INNER JOIN Cateogies ON productCategory = categoryId

productId productLabel productPrice productCategory categoryId categoryLabel
1Ordinateur Asus75011Ordinateur
2Ordinateur HP80011Ordinateur
3Téléphone HTC25022Téléphone
4Ordinateur Lenovo67511Ordinateur
5Téléphone iPhone500022Téléphone
6Ordinateur MacOS1000011Ordinateur
7Ordinateur Acer211Ordinateur

Clause ORDER BY (Plus compliqué)

Correspond à la requête : SELECT * FROM Products ORDER BY productPrice DESC

productId productLabel productPrice productCategory
6Ordinateur MacOS100001
5Téléphone iPhone50002
2Ordinateur HP8001
1Ordinateur Asus7501
4Ordinateur Lenovo6751
3Téléphone HTC2502
7Ordinateur Acer21

Clause GROUP BY (Plus compliqué)

Correspond à la requête (fictive): SELECT * FROM Products INNER JOIN Cateogies ON productCategory = categoryId GROUP BY categoryId

N.B.: on comprend bien qu'il ne s'agisse pas d'une requête "exécutable" dans la mesure où le résultat produit est un tableau à 3 dimensions ! En effet, en utilisant la clause GROUP BY, on a constitué un tableau de "groupes", à savoir un tableau de tableaux à 2 dimensions. Et maintenant que les groupes sont constitués, il faut "agréger" tout cela de sorte qu'à la fin il ne nous reste plus qu'une ligne par groupe.

productId productLabel productPrice productCategory categoryId categoryLabel
6
7
1
4
2
Ordinateur MacOS
Ordinateur Acer
Ordinateur Asus
Ordinateur Lenovo
Ordinateur HP
10000
2
750
675
800
1
1
1
1
1
1
Ordinateur
Ordinateur
Ordinateur
Ordinateur
Ordinateur
3
5
Téléphone HTC
Téléphone iPhone
250
5000
2
2
2
Téléphone
Téléphone

Correspond à la requête (fictive): SELECT categoryLabel, COUNT(productId) FROM Products INNER JOIN Cateogies ON productCategory = categoryId GROUP BY categoryId

N.B.: on comprend bien que, cette fois encore, la requêtes n'est pas "exécutable" puisqu'elle retourne encore une fois un tableau à 3 dimensions !

COUNT(productId) categoryLabel
5
Ordinateur
Ordinateur
Ordinateur
Ordinateur
Ordinateur
2
Téléphone
Téléphone

Correspond à la requête: SELECT categoryLabel, COUNT(productId) FROM Products INNER JOIN Cateogies ON productCategory = categoryId GROUP BY categoryId, categoryLabel

N.B.: on comprend bien que, cette fois-ci, le résultat produit étant bien à 2 dimensions, correspond bien à une requete "exécutable" !

COUNT(productId) categoryLabel
5Ordinateur
2Téléphone

Conséquence sur l'ordre d'exécution des clauses

Une fois ces algorithmes codés et/ou ces résultats compris, on comprendra sans doute mieux l'ordre d'exécution des clauses SQL. Quoiqu'il en soit, on retiendra :

Clause INSERT INTO

Correspond à la requête : INSERT INTO Products VALUES (8, "Tablette Microsoft", 500.00, null), équivalente à la requête INSERT INTO Products(productId, productLabel, productPrice, productCategory) VALUES (8, "Tablette Microsoft", 500.00, null)

N.B.: on comprend bien que, si l'on ne précise pas les champs, l'ordre des valeurs est capital ! En effet, le SGBD ne saurait jouer aux devinettes et ne fera pas d'hypothèse. Les champs de la table ont été créés dans un certain ordre. Et c'est dans cet ordre que le SGBD s'attend à recevoir les valeurs.

productId productLabel productPrice productCategory
1Ordinateur Asus7501
2Ordinateur HP8001
3Téléphone HTC2502
4Ordinateur Lenovo6751
5Téléphone iPhone50002
6Ordinateur MacOS100001
7Ordinateur Acer21
8Tablette Microsoft500

Clauses DELETE FROM ... WHERE ...

Correspond à la requête : DELETE FROM Products WHERE productCategory = 2

N.B.:
  • bien entendu, lors d'un DELETE FROM, le WHERE consiste en une restriction et, en cela, est absolumment similaire au WHERE. Cependant, et vous le savez, il ne s'agit plus de conserver mais de supprimer les lignes pour lesquelles des conditions sont vérifiées ;
  • surtout, on constate qu'il s'agit d'une WHERE habituel, et plus généralement d'une clause comme une autre. Aussi, rien ne nous interdit d'y placer une sous-requête.


productId productLabel productPrice productCategory
1Ordinateur Asus7501
2Ordinateur HP8001
4Ordinateur Lenovo6751
6Ordinateur MacOS100001
7Ordinateur Acer21
8Tablette Microsoft500

Clauses LEFT et RIGHT JOIN

Correspond à la requête : SELECT * FROM Products P LEFT JOIN Categories C ON P.productCategory = C.categoryId

N.B.: on retiendra bien que l'intérêt d'un LEFT JOIN, c'est de pouvoir conserver toutes les lignes de la table de gauche ! Et pas forcément en un seul exemplaire d'ailleurs...

productId productLabel productPrice productCategory categoryId categoryLabel
1Ordinateur Asus75011Ordinateur
2Ordinateur HP80011Ordinateur
4Ordinateur Lenovo67511Ordinateur
6Ordinateur MacOS1000011Ordinateur
7Ordinateur Acer211Ordinateur
8Tablette Microsoft500

Strictement équivalent à la requête précédente : SELECT * FROM Categories C RIGHT JOIN Products P ON C.categoryId = P.productCategory

N.B.: RIGHT JOIN, LEFT JOIN... Finalement, c'est la même histoire. Tout LEFT JOIN peut être transformé en RIGHT JOIN et inversement. Le LEFT JOIN conserve les lignes de la table de gauche, le RIGHT JOIN celles de droites...

productId productLabel productPrice productCategory categoryId categoryLabel
1Ordinateur Asus75011Ordinateur
2Ordinateur HP80011Ordinateur
4Ordinateur Lenovo67511Ordinateur
6Ordinateur MacOS1000011Ordinateur
7Ordinateur Acer211Ordinateur
8Tablette Microsoft500

Autre remarques

On retiendra également :

Conclusion

Tout d'abord, j'espère que ce travail vous a intéressé et a été instructif. J'espère encore que votre travail et l'étude de la correction vous permettra et/ou vous a permis :



Ensuite, je vous suggère de relire ces résultats à tête reposée de sorte que les clauses SQL n'aient plus de secret pour vous !

Bien entendu, on comprendra qu'il ne s'agit pas d'une version optimisée (performances) et que l'intérêt de ce TP/projet était bel et bien d'en apprendre plus sur le fonctionnement du SQL et sur le fonctionnement interne d'un SGBD.
Merci à tous !