Le langage SQL Langage de manipulation de données

2016-02-05
Séances de cours 6 et 7
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
1
S’initier au langage SQL utilisé pour
interroger les bases de données (BD) à partir
du schéma relationnel.
 Traduire les questions d’affaires en requêtes
SQL permettant d’extraire des données
d’intérêt de la BD.
 Formuler les requêtes en utilisant le langage
de manipulation de données (LMD SQL).

TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

Requêtes SQL

Opérations de mise à jour en SQL
2
 SELECT
 INSERT
 DELETE
 UPDATE

Gestion des transactions en SQL
 COMMIT
 ROLLBACK
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
3
1
2016-02-05

Requêtes SQL

Opérations de mise à jour en SQL
 SELECT
 INSERT
 DELETE
 UPDATE

Gestion des transactions en SQL
 COMMIT
 ROLLBACK
TCH054 Bases de données



© Robert Godin, Lévis Thériault, Hiver 2016.
4
Structured Query Language
Norme établie pour SGBD relationnel
Partie LDD (Langage de définition de données)
 Conceptuel : CREATE SCHEMA, TABLE,...
 Externe : CREATE VIEW, GRANT,…
 Interne : CREATE INDEX, CLUSTER,...

Partie LMD (Langage de manipulation de
données)

Partie LCD (Langage de contrôle de données)
 SELECT, INSERT, DELETE, UPDATE
 GRANT, REVOKE
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
5
Requête
 Ensemble de commande SQL dont l’exécution (par le SGBD)
permet d’extraire des données désirées
 Interroge les données de la base de données
▪ Ne crée pas une copie des données
 Génère un résultat qui se présente sous forme d’un tableau
contenant des lignes et des colonnes
▪ Une ligne correspond à un enregistrement
▪ Une colonne correspond à un attribut
▪ Un champ correspond à l’intersection d’une ligne et d’une colonne (une
valeur)
 Permet de générer une vue partielle de l’ensemble des données
de la BD
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
6
2
2016-02-05

Requête
 Entrée : les conditions que doivent respecter les données
 Sortie : les données dans la BD qui respectent ces
conditions

Langage pour formuler la requête

Résultat
 LMD SQL
 Une table virtuelle
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
7
Syntaxe de requêteSQL
selectSQL |
(requêteSQL) {UNION|INTERSECT|EXCEPT} (requêteSQL)

Syntaxe du selectSQL
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY

...
TCH054 Bases de données

{[ALL|DISTINCT] expression [AS nomColonne]
[,expression [AS nomColonne]]…}|*
table [AS nomTable [(nomColonne[,nomColonne])]]]
[,table [AS nomTable [(nomColonne[,nomColonne])]]]]…
conditionSQL]
nomColonne [,nomColonne]…
conditionSQL]
nomColonne [ASC|DESC] [,nomColonne[ASC|DESC]]…]
© Robert Godin, Lévis Thériault, Hiver 2016.
8
Produire les noClient et dateCommande de toutes les
SELECT
noClient, dateCommande
Commandes
FROM
noClient
10
20
10
10
30
20
40
40
SELECT
FROM
TCH054 Bases de données
Commande
dateCommande
01/06/2000
02/06/2000
02/06/2000
05/07/2000
09/07/2000
09/07/2000
15/07/2000
15/07/2000
Multi-ensemble !
ALL noClient, dateCommande
Commande
© Robert Godin, Lévis Thériault, Hiver 2016.
9
3
2016-02-05

Produire les noClient et dateCommande de toutes les
SELECT
DISTINCT noClient, dateCommande
Commandes
FROM
noClient
10
20
10
10
30
20
40
Commande
dateCommande
01/06/2000
02/06/2000
02/06/2000
05/07/2000
09/07/2000
09/07/2000
15/07/2000
 noClient, dateCommande (Commande)
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
10
Sélectionner les Articles dont le prix est inférieur à
$20.00 et le numéro est supérieur à 30
SELECT
FROM
WHERE
*
Article
prixUnitaire < 20 AND noArticle > 30
noArticle
60
70
95
description
Erable argenté
Herbe à puce
Génévrier
prixUnitaire
15.99
10.99
15.99
 prixUnitaire < 20.00 ET noArticle > 30 (Article)
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
11
{conditionSimple
(conditionSQL)|
NOT(conditionSQL)|
conditionSQL AND conditionSQL |
conditionSQL OR conditionSQL}

Syntaxe (incomplète) de la conditionSimple :
{expression {=|<|>|<=|>=|<>} expression|
expression BETWEEN expression AND expression|
expression {IS NULL |IS NOT NULL}|
expression {IN |NOT IN} listeConstantes|
expression {LIKE |NOT LIKE} patron}
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
12
4
2016-02-05

Sélectionner les Commandes du mois de juin
de l'année 2000
SELECT
FROM
WHERE
SELECT
FROM
WHERE
TCH054 Bases de données

*
Commande
dateCommande BETWEEN '01/06/2000' AND '30/06/2000'
*
Commande
dateCommande >= '01/06/2000' AND
dateCommande <='30/06/2000'
© Robert Godin, Lévis Thériault, Hiver 2016.
Sélectionner les Commandes du Client dont le
noClient est 10 ou 40 ou 80
SELECT
FROM
WHERE
SELECT
FROM
WHERE
TCH054 Bases de données

*
Commande
noClient IN (10, 40, 80)
*
Commande
noClient = 10 OR noClient = 40 OR noClient = 80
© Robert Godin, Lévis Thériault, Hiver 2016.
14
Sélectionner les Clients dont le nomClient
contient le mot Le
SELECT *
FROM
WHERE

13
Client
nomClient LIKE '%Le%'
2ième lettre du nomClient est un o et dernière
lettre est un k
SELECT *
FROM
WHERE
TCH054 Bases de données
Client
nomClient LIKE '_o%k'
© Robert Godin, Lévis Thériault, Hiver 2016.
15
5
2016-02-05

Sélectionner les Articles dont la description
n'est pas une valeur nulle
SELECT
FROM
WHERE
TCH054 Bases de données

*
Article
description IS NOT NULL
© Robert Godin, Lévis Thériault, Hiver 2016.
Produire les noClient et dateCommande des
Commandes dont la date est supérieure au
05/07/2000
SELECT
FROM
WHERE
noClient, dateCommande
Commande
dateCommande > '05/07/2000'
noClient
30
20
40
40
TCH054 Bases de données

16
dateCommande
09/07/2000
09/07/2000
15/07/2000
15/07/2000
© Robert Godin, Lévis Thériault, Hiver 2016.
17
Produire toutes les combinaisons possibles
de lignes de Client et de Commande...
SELECT
FROM
*
Client, Commande
Client  Commande
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
18
6
2016-02-05

Produire les informations au sujet des Clients et de leurs Commandes
SELECT
Client.noClient, nomClient, noTéléphone, noCommande,
dateCommande
Client, Commande
Client.noClient = Commande.noClient
FROM
WHERE
Client.noClient
10
10
10
20
20
30
40
40
nomClient
Luc Sansom
Luc Sansom
Luc Sansom
Dollar Tremblay
Dollar Tremblay
Lin Bô
Jean Leconte
Jean Leconte
noTéléphone
(999)999-9999
(999)999-9999
(999)999-9999
(888)888-8888
(888)888-8888
(777)777-7777
(666)666-6666
(666)666-6666
noCommande
1
3
4
2
6
5
7
8
dateCommande
01/06/2000
02/06/2000
05/07/2000
02/06/2000
09/07/2000
09/07/2000
15/07/2000
15/07/2000
 Client.noClient, nomClient, noTéléphone, noCommande, dateCommande
( Client.noCliente = Commande.noClient (Client  Commande))
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
19
Produire les informations au sujet des Clients et de leurs Commandes
SELECT
FROM
*
Client NATURAL JOIN Commande {SQL2}
Client  Commande
Client NATURAL JOIN Commande {Illégal!}
▪

Il faut absolument enchâsser le JOIN dans un SELECT
Jointure- (si les noms de colonnes de la jointure sont différents)
SELECT
FROM
TCH054 Bases de données

*
Client JOIN Commande ON
Client.noClient = Commande.numéroCLient {SQL2}
© Robert Godin, Lévis Thériault, Hiver 2016.
20
Sélectionner les nomClient des Clients qui ont
commandé au moins un plant d'herbe à puce
SELECT
FROM
WHERE
nomClient
Client, Commande, LigneCommande, Article
description = 'Herbe à puce' AND
Client.noClient = Commande.noClient AND
Commande.noCommande = LigneCommande.noCommande AND
LigneCommande.noArticle = Article.noArticle
 nomClient ( description
= "Herbe à puce"
(Client  Commande  LigneCommande  Article))
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
21
7
2016-02-05

~Algèbre relationnelle
SELECT
FROM
WHERE
nomClient
Client, Commande, LigneCommande, Article
description = 'Herbe à puce' AND
Client.noClient = Commande.noClient AND
Commande.noCommande = LigneCommande.noCommande AND
LigneCommande.noArticle = Article.noArticle

Ordre quelconque des tables du FROM


AND commutatif…
Processus d ’évaluation de requête
 la plupart du temps…
TCH054 Bases de données

22
~ renommer ()
SELECT
FROM
WHERE
SELECT
FROM
WHERE
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
Client.noClient, nomClient, noTéléphone, noCommande,
dateCommande
Client, Commande
Client.noClient = Commande.noClient
Cl.noClient, nomClient, noTéléphone, noCommande,
dateCommande
Client AS Cl, Commande AS Co
Cl.noClient = Co.noClient
© Robert Godin, Lévis Thériault, Hiver 2016.
23
Quels sont les Clients qui ont le même numéro de téléphone?
SELECT
FROM
WHERE
Client.noClient, Client2.noClient
Client, Client AS Client2
Client.noTéléphone = Client2.noTéléphone
 Client.noClient, Client2.noClient, ( Client.noTéléphone = Client2.noTéléphone (Client   Client2 (Client)))
SELECT
FROM
noClient, noClient2
Client NATURAL JOIN {SQL2}
Client AS Client2(noClient2, nomClient2, noTéléphone)
 noClient, noClient2 (Client   Client2(noClient2, nomClient2, noTéléphone) (Client))
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
24
8
2016-02-05

Produire les informations au sujet des Clients et de leurs Commandes
incluant les informations sur les Clients qui n’ont pas placé de Commande
Client =  Commande
SELECT
FROM

Oracle

« + » après colonne pour inclure la valeur NULL
SELECT
FROM
WHERE
TCH054 Bases de données

*
Client NATURAL LEFT OUTER JOIN Commande {SQL2}
*
Client,Commande
Client.noClient = Commande.noClient(+)
© Robert Godin, Lévis Thériault, Hiver 2016.
25
Produire les noms et numéros de téléphone des Employés qui sont aussi des Clients de la pépinière
noClient
10
20
30
40
50
60
70
80
Table Client
nomClient
Luc Sansom
Dollard Tremblay
Lin Bô
Jean Leconte
Hafedh Alaoui
Marie Leconte
Simon Lecoq
Dollard Tremblay
noTéléphone
(999)999-9999
(888)888-8888
(777)777-7777
(666)666-6666
(555)555-5555
(666)666-6666
(444)444-4419
(333)333-3333
codeEmployé
CASD1
PIOY1
LAFH1
HASC1
Table Employé
nomEmployé
Dollard Tremblay
Yan Piochuneshot
Yvan Lafleur
Jean Leconte
noTéléphone
(888)888-8888
911
(111)111-1111
(666)666-6666
(SELECT
nomClient as nomPersonne, noTéléphone
FROM
Client)
INTERSECT
(SELECT
nomEmployé as nomPersonne, noTéléphone
FROM
Employé)
nomPersonne
Dollard Tremblay
Jean Leconte
TCH054 Bases de données

noTéléphone
(888)888-8888
(666)666-6666
© Robert Godin, Lévis Thériault, Hiver 2016.
26
La liste des noArticle avec le prixUnitaire avant
et après inclusion de la taxe de 15%
SELECT noArticle, prixUnitaire, prixUnitaire*1.15 AS prixPlusTaxe
FROM
Article
noArticle
10
20
40
50
60
70
80
81
90
95
TCH054 Bases de données
prixUnitaire
10.99
12.99
25.99
22.99
15.99
10.99
26.99
25.99
25.99
15.99
prixPlusTaxe
12.64
14.94
29.89
26.44
18.39
12.64
31.04
29.89
29.89
18.39
© Robert Godin, Lévis Thériault, Hiver 2016.
27
9
2016-02-05

Produire le détail de chacun des Articles commandés la
Commande #1 incluant le prix total avant et après la taxe de
15% pour chacun des Articles commandés
SELECT L.noArticle, quantité, prixUnitaire, prixUnitaire*quantité AS total,
prixUnitaire*quantité*1.15 AS totalPlusTaxe
FROM
LigneCommande AS L, Article AS A
WHERE
L.noArticle = A.noArticle AND
L.noCommande = 1
noArticle
10
70
90
quantité
10
5
1
TCH054 Bases de données

prixUnitaire
10.99
10.99
25.99
total
109.90
54.95
25.99
totalPlusTaxe
126.38
63.19
29.89
© Robert Godin, Lévis Thériault, Hiver 2016.
28
Les Articles dont le prixUnitaire incluant la
taxe de 15% est inférieur à $16.00
SELECT noArticle, prixUnitaire, prixUnitaire*1.15 AS prixPlusTaxe
FROM
Article
WHERE
prixUnitaire*1.15 < 16
noArticle
10
20
70
TCH054 Bases de données
prixPlusTaxe
12.64
14.94
12.64
© Robert Godin, Lévis Thériault, Hiver 2016.
Symbole
+
*
/
||

prixUnitaire
10.99
12.99
10.99
29
Signification
Somme
Différence
Produit
Division
Concaténation de chaîne (SQL2)
Conversions automatiques entre types
compatibles
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
30
10
2016-02-05

Les Commandes de la journée
SELECT *
FROM
WHERE
Commande
dateCommande = CURRENT_DATE

CURRENT TIME
CURRENT TIMESTAMP


CURRENT_USER (ou USER)
SESSION_USER

 SYSDATE Oracle
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
31
+, - (unaire)
*, /
+, -, ||
=, <>, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN, SIMILAR
NOT
AND
OR
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

POSITION(patron IN chaîne)
CHARACTER_LENGTH(chaîne)
OCTET_LENGTH (chaîne)
BIT_LENGTH(chaîne)
EXTRACT(champ FROM dateOuTime)
SUBSTRING (chaîne FROM indiceDébut FOR nombreCaractères)
UPPER | LOWER (chaîne)
TRIM ([LEADING|TRAILING|BOTH] caractère FROM chaîne)
CAST(expression AS type)
…

Voir documentation du SGBD









TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
32
33
11
2016-02-05
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------02-02-05
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------05-02-2002 09:08:26
SQL> SELECT TO_DATE('05/02/2000', 'DD/MM/YYYY') FROM DUAL;
TO_DATE('05/02/2000
------------------05-02-2000 00:00:00
SQL> SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL;
TO_CHAR(SY
---------22/01/2002
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
34
SQL> SELECT SYSDATE + INTERVAL '1' DAY FROM DUAL;
SYSDATE+INTERVAL'1'
------------------23-01-2002 16:02:18
SQL> SELECT SYSDATE - INTERVAL '1' DAY FROM DUAL;
SYSDATE-INTERVAL'1'
------------------21-01-2002 16:02:18
SQL> SELECT SYSDATE + 1 FROM DUAL;
SYSDATE+1
------------------23-01-2002 16:02:18
SQL> SELECT SYSDATE + 1/24 FROM DUAL;
SYSDATE+1/24
------------------22-01-2002 17:02:18
SQL> SELECT SYSDATE + INTERVAL '30' SECOND FROM DUAL;
SYSDATE+INTERVAL'30
------------------22-01-2002 16:02:48
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

Arithmétique

Comparaison (>, <, …)
35
 opérande NULL => NULL
 opérande NULL => UNKNOWN
x
TRUE
TRUE
TRUE
UNKNOWN
UNKNOWN
UNKNOWN
FALSE
FALSE
FALSE
TCH054 Bases de données
y
TRUE
UNKNOWN
FALSE
TRUE
UNKNOWN
FALSE
TRUE
UNKNOWN
FALSE
x AND y
TRUE
UNKNOWN
FALSE
UNKNOWN
UNKNOWN
FALSE
FALSE
FALSE
FALSE
x OR y
TRUE
TRUE
TRUE
TRUE
UNKNOWN
UNKNOWN
TRUE
UNKNOWN
FALSE
© Robert Godin, Lévis Thériault, Hiver 2016.
NOT x
FALSE
FALSE
FALSE
UNKNOWN
UNKNOWN
UNKNOWN
TRUE
TRUE
TRUE
36
12
2016-02-05

Si x est NULL
SELECT *
FROM T
WHERE x = 0 OR x <> 0
 UNKNOWN OR UNKNOWN = UNKNOWN
▪ pas dans le résultat !
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
37
Le nombre d'Articles différents à vendre ainsi
que le prixUnitaire moyen des Articles
SELECT
FROM
COUNT(*) AS nombreArticles,
AVG(prixUnitaire) AS prixMoyen
Article
nombreArticles
10
TCH054 Bases de données
SELECT
FROM
prixMoyen
19.49
© Robert Godin, Lévis Thériault, Hiver 2016.
38
Count(DISTINCT prixUnitaire) AS nombrePrix
Article
nombrePrix
6
SELECT
FROM
Count(prixUnitaire) AS nombrePrixNonNull
Article
nombrePrixNonNull
10
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
39
13
2016-02-05

Produire le nombre de Commandes passées par chacun des
Clients qui ont passé au moins une Commande
SELECT
FROM
GROUP BY
noClient, COUNT(*) AS nombreCommandes
Commande
noClient
Table Commande
noCommande dateCommande
1
01/06/2000
3
02/06/2000
4
05/07/2000
2
02/06/2000
6
09/07/2000
5
09/07/2000
7
15/07/2000
8
15/07/2000
TCH054 Bases de données
SELECT
FROM
GROUP BY
noLivraison
100
101
100
102
102
100
103
104
105
noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
nombreCommandes
3
2
1
2
© Robert Godin, Lévis Thériault, Hiver 2016.
40
noCommande, noArticle, SUM(quantitéLivrée) AS totalLivré,
COUNT(*)AS nombreLivraisons
DétailLivraison
noCommande, noArticle
Table DétailLivraison
noCommande noArticle
1
10
1
10
1
70
2
40
2
95
3
20
1
90
4
40
5
70
quantitéLivrée
7
3
5
2
1
1
1
1
2
noCommande
1
1
1
2
2
3
4
5
noArticle
10
70
90
40
95
20
40
70
totalLivré
10
5
1
2
1
1
1
2
nombreLivraisons
2
1
1
1
1
1
1
1
Calcul de plusieurs agrégats à la fois avec CUBE et ROLLUP SQL:1999
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
41
Produire le nombre de Commandes passées par chacun des
Clients qui ont passé deux Commandes ou plus
SELECT
FROM
GROUP BY
HAVING
noClient, COUNT(*) AS nombreCommandes
Commande
noClient
COUNT(*) >= 2
Table Commande
noCommande dateCommande
1
01/06/2000
3
02/06/2000
4
05/07/2000
2
02/06/2000
6
09/07/2000
5
09/07/2000
7
15/07/2000
8
15/07/2000
TCH054 Bases de données
noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
© Robert Godin, Lévis Thériault, Hiver 2016.
nombreCommandes
3
2
1
2
42
14
2016-02-05
SELECT
FROM
WHERE
GROUP BY
HAVING
noClient, COUNT(*) AS nombreCommandes
Commande
dateCommande > '02/06/2000'
noClient
COUNT(*) >= 2
Table Commande
noCommande dateCommande
1
01/06/2000
3
02/06/2000
4
05/07/2000
2
02/06/2000
6
09/07/2000
5
09/07/2000
7
15/07/2000
8
15/07/2000
TCH054 Bases de données

noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
nombreCommandes
1
1
1
2
© Robert Godin, Lévis Thériault, Hiver 2016.
43
Produire la quantité qui a été livrée pour l'Article #50 de la Commande #4
noLivraison
100
100
101
102
102
100
103
104
105
Table DétailLivraison
noCommande noArticle
1
10
1
70
1
10
2
40
2
95
3
20
1
90
4
40
5
70
quantitéLivrée
7
5
3
2
1
1
1
1
2
SELECT
CASE
WHEN SUM(quantitéLivrée) IS NULL THEN 0
ELSE SUM(quantitéLivrée)
END AS quantitéTotaleLivrée
FROM
DétailLivraison
WHERE
noArticle = 50 AND noCommande = 4
quantitéTotaleLivrée
0
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
44
Les Clients en ordre alphabétique du nom
SELECT
FROM
ORDER BY
SELECT
FROM
ORDER BY
TCH054 Bases de données
*
Client
nomClient
*
Client
nomClient DESC, noTéléphone ASC
© Robert Godin, Lévis Thériault, Hiver 2016.
45
15
2016-02-05

Les Clients qui ont passé au moins une Commande
SELECT
*
FROM
Client
WHERE
noClient IN
(SELECT noClient
FROM
Commande)
Client  Commande
SELECT
FROM
WHERE
TCH054 Bases de données

DISTINCT Client.noClient, nomClient, noTéléphone
Client, Commande
Client.noClient = Commande.noClient
© Robert Godin, Lévis Thériault, Hiver 2016.
46
Chercher les LigneCommandes pour
lesquelles au moins une Livraison a été
effectuée
SELECT
*
FROM
LigneCommande
WHERE
(noCommande,noArticle) IN
(SELECT
noCommande, noArticle
FROM
DétailLivraison)
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
47
Sélectionner les Commandes du Client Hugh Paycheck
SELECT
*
FROM
Commande
WHERE
noClient =
(SELECT
noClient
FROM
Client
WHERE
nomClient = 'Hugh Paycheck')

Exception si plusieurs lignes retournées par SELECT
imbriqué
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
48
16
2016-02-05

Produire les informations au sujet des Clients qui ont
passé au moins une Commande
SELECT
*
Référence à une colonne
FROM
Client
non locale
WHERE
0 <
(SELECT
COUNT(*)
FROM
Commande
WHERE
noClient = Client.noClient)
POUR chaque ligne de Client
Exécuter le SELECT suivant :
(SELECT
COUNT(*)
FROM
Commande
WHERE
noClient = Client.noClient)
SI le compte retourné > 0
Placer la ligne de Client dans la table du résultat à retourner
FIN SI
FIN POUR
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
49
Produire les informations au sujet des Clients qui ont
passé au moins une Commande
SELECT
*
FROM
Client
WHERE
EXISTS
(SELECT
*
FROM
Commande
WHERE
noClient = Client.noClient)
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
50
Les Clients qui ont passé au moins deux
Commandes
SELECT
*
FROM
Client
WHERE NOT UNIQUE
(SELECT
noClient
FROM
Commande
WHERE
noClient = Client.noClient)
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
51
17
2016-02-05

Commandes passées après la dernière Livraison (date
ultérieure)
SELECT * FROM Commande
WHERE
dateCommande > ALL
(SELECT dateLivraison
FROM
Livraison)

Commandes passées après au moins une des Livraisons
SELECT * FROM Commande
WHERE
dateCommande > ANY
(SELECT dateLivraison
FROM
Livraison)
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
52
T1  T2
NOT EXISTS (T1 EXCEPT T2)
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
noArticle
10
70

SELECT
noCommande
FROM
Commande
WHERE
NOT EXISTS
((SELECT
noArticle
FROM
Article
WHERE
prixUnitaire = 10.99
)
EXCEPT
(SELECT
noArticle
FROM
LigneCommande
WHERE
noCommande = Commande.noCommande
)
)
noCommande
1
5
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
53
noCommande
1
1
1
2
2
3
4
4
5
5
5
6
6
7
7
noArticle
10
70
90
40
95
20
40
50
70
10
20
10
40
50
95
54
18
2016-02-05

Produire les noClient et dateCommande des Commandes dont
la dateCommande est supérieure au 05/07/2000
SELECT noClient, dateCommande
FROM
(SELECT *
{SQL 2}
FROM Commande
WHERE
dateCommande > '05/07/2000'
)
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

Requêtes SQL

Opérations de mise à jour en SQL
55
 SELECT
 INSERT
 DELETE
 UPDATE

Gestion des transactions en SQL
 COMMIT
 ROLLBACK
TCH054 Bases de données

INSERT

DELETE

UPDATE
© Robert Godin, Lévis Thériault, Hiver 2016.
56
 Insertion dans une table
 Suppression de lignes
 Modification de lignes
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
57
19
2016-02-05

Insérer une nouvelle ligne dans la table Client
INSERT INTO Client
VALUES (100, 'G. Lemoyne-Allaire', '911')

Changer l ’ordre de défaut
INSERT INTO Client(nomClient, noClient, noTéléphone)
VALUES ('G. Lemoyne-Allaire', 100, '911')
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
CREATE TABLE Article
(noArticle
INTEGER
description
VARCHAR(20),
prixUnitaire
DECIMAL(10,2)
quantitéEnStock
INTEGER
PRIMARY KEY (noArticle)
)
58
NOT NULL,
NOT NULL,
DEFAULT 0 NOT NULL ,
INSERT INTO Article(noArticle, prixUnitaire)
VALUES (30, 5.99)
INSERT INTO Article(noArticle, description, prixUnitaire, quantitéEnStock)
VALUES (30, NULL, 5.99, 0)
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
59
Produire les lignes de DétailLivraison pour la
Livraison #106 à partir des LigneCommandes
de la Commande #7
INSERT INTO DétailLivraison
SELECT
106, noCommande, noArticle, quantité
FROM
LigneCommande
WHERE
noCommande = 7
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
60
20
2016-02-05

Supprimer toutes les lignes de la table Client
DELETE FROM Client

Supprimer le Client #70 de la table Client
DELETE
WHERE

FROM Client
noClient = 70
Supprimer les Clients qui n'ont pas passé de Commande
DELETE FROM Client
WHERE noClient NOT IN
(SELECT
DISTINCT noClient
FROM
Commande)
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

Supprimer toutes les lignes de la table Client


Plus performant que DELETE
Outrepasse les mécanismes transactionnels
normaux

Meilleure récupération d’espace
61
TRUNCATE TABLE Client
 ROLLBACK peut être interdit
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
Changer le noTéléphone du Client #10 pour (222)222-2222
UPDATE
SET
WHERE

Client
noTéléphone = '(222)222-2222'
noClient = 10
Augmenter tous les prixUnitaires des Articles de 10%
UPDATE
SET

62
Article
prixUnitaire = prixUnitaire * 1.1
Modification de plusieurs colonnes à la fois
UPDATE
SET
WHERE
TCH054 Bases de données
Article
prixUnitaire = 12.99, quantitéEnStock = 5
noArticle = 10
© Robert Godin, Lévis Thériault, Hiver 2016.
63
21
2016-02-05

Requêtes SQL

Opérations de mise à jour en SQL
 SELECT
 INSERT
 DELETE
 UPDATE

Gestion des transactions en SQL
 COMMIT
 ROLLBACK
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.

COMMIT WORK

ROLLBACK WORK

Début de transaction implicite
64
 confirme la transaction en cours
 annule la transaction en cours
 début de session
 fin de la précédente

Commande LDD provoque un COMMIT
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
65
Ouverture d'une
connexion SQL
Fermeture de la
connexion SQL
Début d'une
transaction SQL
COMMIT
Transaction
SQL
Début d'une
transaction SQL
COMMIT
Transaction
SQL
Début d'une
transaction SQL
COMMIT
Transaction
SQL
Session
SQL
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
66
22
2016-02-05

Godin, R. (2012). Systèmes de gestion de
bases de données par l’exemple. 3ième édition,
Montréal, Canada: Loze‐Dion, Chapitre 4.
TCH054 Bases de données

© Robert Godin, Lévis Thériault, Hiver 2016.
67
Interface entre SQL et un programme
TCH054 Bases de données
© Robert Godin, Lévis Thériault, Hiver 2016.
68
23