sql initiation

ECOLE NATIONALE DES INGENIEURS DES TRAVAUX AGRICOLESDE BORDEAUX

DEPARTEMENT ENTREPRISE ET SYSTEMES

UNITE DE FORMATION INFORMATIQUE ET GENIE DES EQUIPEMENTS

~o~O~o~

INITIATION AU LANGAGE SQL

Notes de cours

Mai 2003

Monique BORIES, Jrme STEFFE, Franois THIBERVILLE

Initiation au langage SQL - 2 / 21

ENITA Bordeaux - Dpartement Entreprise et Systme - UF Informatique et Gnie des Equipements -cours_SQL-003.doc

SOMMAIRE

A - GENERALITES 3

B - REQUETES DE SELECTION SUR UNE TABLE 4

1 - LINSTRUCTION SELECT 42 - CLAUSE DISTINCT 43 - CLAUSE WHERE 44 - UTILISATION DES ATTRIBUTS BETWEEN, IN, LIKE 55 - UTILISATION DEXPRESSIONS 66 - UTILISATION DE FONCTIONS 6A) FONCTIONS MATHMATIQUES ET STATISTIQUES 6B) FONCTIONS DE DATE 7C) FONCTIONS RELATIVES AUX CARACTRES 77 - CLASSEMENT DES DONNES 88 - REGROUPEMENT D'ENREGISTREMENTS 8A) - CLAUSE GROUP BY 8B) - CLAUSE HAVING 8

C - REQUETE DE SELECTION MULTITABLES - LES JOINTURES 10

1 - JOINTURE DE 2 TABLES 10- JOINTURE DE PLUS DE 2 TABLES 123 - JOINTURE D'UNE TABLE AVEC ELLE-MME (AUTO-JOINTURE) 124 - JOINTURE EXTERNE 13

D - REQUETES IMBRIQUEES 14

1 - REQUTES IMBRIQUES RENVOYANT UNE VALEUR UNIQUE 142 - REQUTES IMBRIQUES RENVOYANT DES VALEURS MULTIPLES 153 - REQUTES IMBRIQUES MULTIPLES 15

E - REQUETES DINSERTION 16

F - LES REQUETES DE MODIFICATION 17

G LES REQUETES DE SUPPRESSION 18

H) METHODE POUR ECRIRE UNE REQUETE 19

ANNEXE 1 : LES TABLES DE LEXEMPLE 21



A - GENERALITES

Qu'est-ce que SQL ?

L'utilisation du langage SQL (Structured Query Language) permet de normaliser le dveloppementdes applications de bases de donnes relationnelles dans les entreprises.

SQL est un langage volu de manipulation de bases de donnes relationnelles, il opre sur desensembles logiques de donnes appels relations.

SQL comprend un ensemble de commandes permettant de dfinir, de mettre jour et d'afficher lesinformations des tables.

SQL a t dvelopp chez IBM dans le milieu des annes 70. Adopt ds sa parution par denombreuses socits, il est considr aujourd'hui comme une norme dans les environnements degrandes et moyennes entreprises.

Note pour les exercices et la connexion la base dexemples :

Dmarrer Programme SQL anywhere 5.0 Sybase Central Tools connectUSER ID : dbaPASSWORD sqlDatabase file C:\bdd\exo_sql.db

Cliquez sur exo-SQLCliquez sur exo-SQL(dba)FILES OPEN ISQL

Saisir les requtes SQL dans la fentre commande.



B - REQUETES DE SELECTION SUR UNE TABLE

1 - Linstruction SELECTSELECT nom champ1, nom champ2, FROM table ;

Le nom de champ sert den tte de colonne, pour utiliser un autre titre, appel alias, utiliserlinstruction AS

Ex : Liste des employs et date de naissanceSELECT nom, date_naissance as date de naissance FROM employes

Remarque : SELECT * FROM table;Cette instruction permet dafficher toutes les colonnes.

Exercices :

1 - Liste de toutes les informations des employs

2 - Liste des employs (nom, prnom)

3 - Liste des clients (socit, contact))

2 - Clause DISTINCT

N'affiche qu'un exemplaire de chaque jeu de lignes identiques.SELECT DISTINCT nom champ1, nom champ2, FROM table ;

Ex : Liste des produits achetsSELECT DISTINCT ref_produit FROM Details_commandes

3 - Clause WHERE

Linstruction WHERE permet d'introduire une condition pour spcifier les lignes afficher.SELECT nom champ1, nom champ2, FROM tableWHERE condition

Ex : Liste des clients dont la fonction est chef des ventesSELECT code_client, societeFROM clientsWHERE fonction = 'chef des ventes'

Remarque :Pour construire une condition on dispose de plusieurs oprateurs : = , >= , , < , et ! quiest la ngation soit : !> , !< , !=Pour combiner plusieurs conditions, utiliser les oprateurs logiques AND, OR et NOT.Pour comparer un champ du texte, on utilisera le caractre pour encadrer le texte. Pourcomparer un champ une valeur numrique, ce caractre ne doit pas tre employ.



Exercices :

4 - Liste des clients habitant London

4bis- Liste des clients habitant Marseille et Nantes

5 - dates des commandes ralises par lemploy 8

6 - Date dembauche de lemploy nomm King

7 - Quantit en stock et libell du produit rf 39

7bis- Employs embauchs aprs le 25 janvier 93

4 - Utilisation des attributs Between, In, Like

Ces instructions permettent de spcifier les conditions de recherche.

- Between peut-tre utilis pour vrifier si une valeur de colonne (caractre, date, numrique) se situeentre 2 valeurs.

Ex : Liste des produits dont la quantit en stock est comprise entre 100 et 200SELECT ref_produit, unites_en_stockFROM produitsWHERE unites_en_stock BETWEEN 100 and 200

- In vrifie si une valeur de colonne correspond l'une de celle spcifi dans la liste

- Not In vrifie si une valeur de colonne ne correspond pas l'une de celle spcifi dans la liste

Ex : Liste des clients nhabitant pas Paris, Montal ou LondresSELECT societe, villeFROM clientsWHERE ville NOT IN ('Paris', 'Montreal','London')

- Like slectionne les lignes en comparant la valeur d'une colonne de type caractre une chane decaractres spcifie.

Remarque : Le caractre % reprsente nimporte quel nombre de caractres. Le caractre _ reprsente un seul caractre quelconque.

Ex : Liste des clients du dpartement 75SELECT societe, code_postalFROM clientsWHERE code_postal like '75%'

Ex : Liste des employs dont la fonction contient le mot " assistant "SELECT *FROM employsWHERE fonction like '%assistant%'



Exercice :

8 Nom de socit, contact du client habitant une adresse dont le nom contient le mot place

5 - Utilisation dexpressions

Dans la clause SELECT on peut :- insrer des instructions descriptives qui seraient rptes chaque ligne,- dfinir une nouvelle colonne (colonne calcule)

SELECT nom champ1, nom champ2, , expression calcule, "expression rpte"FROM table ;

Ex dexpression numrique :Liste des lignes commandes avec le total factur par ligne (hors remise)

SELECT ref_produit, quantite, prix_unitaire, quantite * prix_unitaire as totalFROM details_commandes

Liste des lignes commandes dont le montant est infrieur 100 fSELECT n_commande, prix_unitaire * quantite as montantFROM details_commandesWHERE prix_unitaire * quantite



Ex : Nombre demploysSELECT COUNT (*) FROM employes ;

Ex : Prix unitaire maximum, minimu et moyenSELECT MAX(prix_unitaire), MIN(prix_unitaire), AVG(prix_unitaire)FROM produits

Exercices :

10 - Prix unitaire du produit le plus cher

10 bis Dlai moyen, maximum et minimum entre commande et livraison

11 - Nombre de commandes effectues par le client cod LILAS

11 bis Date de naissance de lemploy le plus jeune

b) Fonctions de date

Current Date : renvoie la date du jour

Month() : renvoie le mois dune colonne de type date

Year() : renvoie lanne dune colonne de type date

Day() : renvoie le jour dune colonne de type date

Ex : Liste des employs ns au mois de janvierSELECT *FROM employesWHERE month(date_naissance)=1

Exercices :

12 - Liste des employs embauchs en 1992

13 - Liste des employs embauchs en 1993 et 1994

c) Fonctions relatives aux caractres

LEFT(colonne, i) : renvoie les i caractres situs gauche de la colonne

RIGHT(colonne, i) : renvoie les i caractres situs droite de la colonne

LENGTH(colonne) : renvoie le nombre de caractres dune colonne

SUBSTR(colonne, i, j) : renvoie les j caractres de la colonne partir de la ime position

Ex : Liste des produits dont la rfrence se termine par 5SELECT ref_produitFROM produitsWHERE right(ref_produit,1)='5'



Ex : Nombre de clients dans le dpartement 67SELECT COUNT (*)FROM clientsWHERE LEFT(Code_postal,2)=67

7 - Classement des donnes

La clause ORDER BY spcifie la liste des colonnes sur lesquelles l'affichage doit tre tri (ASC ouDESC).

Ex : Liste des employs classs selon la date dembaucheSELECT nom, date_embaucheFROM employesORDER BY date_embauche

Exercice :

14 - Classer par ordre dcroissant de prix, les produits vendus dans lentreprise

8 - Regroupement d'enregistrements

a) - Clause GROUP BY

Cette clause regroupe les lignes ayant des valeurs identiques dans une ou plusieurs colonnesissues de l'instruction SELECT, en une ligne.

Remarque : Chaque colonne figurant dans la clause select doit figurer dans la clause GroupBy.

Ex : Nombre darticles diffrents par fournisseurSELECT n_fournisseur, count(*)FROM produitsGROUP BY n_fournisseur

Ex : Nombre demploys par fonctionSELECT fonction, count (*)FROM employesGROUP BY fonction

Exercices :

15 - Quantits commandes par produit

16 - Nombre de produits commands par commande

17 - Nombre de fois o chaque produit est command

b) - Clause Having

Cette clause permet de restreindre la slection des groupes figurant dans le rsultat. Elle prcise lacondition laquelle chaque groupe doit rpondre.



Ex :Nombre darticles par commande, pour les n de commandes aprs 10030SELECT n_commande,count(*) as Nombre darticlesFROM details_commandesGROUP BY n_commandeHAVING n_commande>10030

Exercices :

18 - Nombre de produits commands dans la commande 10250

19 - Liste des codes produits commands au moins 50 fois



C - REQUETE DE SELECTION MULTITABLES - LES JOINTURES

Une jointure ralise une liaison entre deux tables en se basant sur lgalit entre lun des attributs dechaque table.Deux solutions sont possibles pour crire une jointure interne.Citer les attributs recherchs dans la clause SELECT et prciser dans la clause WHERE la conditiondgalit entre les attributs qui ralisent la jointure entre les tables. La clause FROM doit contenir lenom de toutes les tables. La clause WHERE permet toujours de dfinir les critres de slectioncombin avec la condition de jointure par un oprateur AND.On peut galement dfinir la jointure en prcisant les attributs de liaison entre les tables par la clauseJOIN.ON. Cette clause permet de sparer la condition de jointure et les conditions additionnellesde filtres.

Remarque : le produit cartsien de deux tables est quivalent une jointure sans la condition dejointure.

Explication de la jointure

Exemple : Liste des ventes par employ

Commandes Employes

N_commande Code_client N_employe N_employe Nom Prenom

01 A1 100 100 Dupont Pierre

02 A12 100 101 Martin Paul

03 A7 100 102 Dubois Jack

04 A10 101

Aprs jointure le rsultat donnera

N_commande Code_client N_employe Nom Prenom

01 A1 100 Dupont Pierre



04 A10 101 Martin Paul

1 - Jointure de 2 tables

Ex : Liste des ventes des employsSELECT employes.nom, commandes.n_commandeFROM employes,commandesWHERE employes.n_employe=commandes.n_employe

Autre criture :SELECT employes.nom, commandes.n_commandeFROM employes JOIN commandes ONemployes.n_employe=commandes.n_employe;



Ex : Vente du vendeur FullerSELECT commandes.n_commande, employes.NomFROM employes,commandesWHERE employes.n_employe = commandes.n_employeAND nom = Fuller;

Autre criture :SELECT commandes.n_commande, employes.NomFROM employes JOIN commandes ONemployes.n_employe = commandes.n_employeWHERE nom = 'Fuller

Ex : Liste des commandes avec dates et vendeursSELECT n_commande, date_commande, NomFROM commandes, employesWHERE employes.n_employe = commandes.n_employe;

Autre criture :SELECT n_commande, date_commande, NomFROM commandes JOIN employes ONemployes.n_employe = commandes.n_employe

Ex : Nombre de ventes de chaque vendeurSELECT employes.n_employe, nom, COUNT(*)FROM employes, commandesWHERE employes.n_employe = commandes.n_employeGROUP BY employes.n_employe,nom

Autre criture :SELECT employes.n_employe, nom, COUNT(*)FROM employes JOIN commandes ONemployes.n_employe = commandes.n_employeGROUP BY employes.n_employe,nom

Exercices :

19 bis - Numro et nom des employs ayant pris plus de 10 commandes en France

20 - Libell des socits franaises ayant command avant le 31/07/1993

21 - Liste des commandes et date des commandes du client cod BONAP

22 - Calendrier des ventes de lemploy Fuller

22 bis - Nombre de commandes par pays selon la nationalit du client

22 ter - Liste et nombre de commandes des clients franais ayant ralis plus de 10commandes en 1993 ou plus tard



- Jointure de plus de 2 tables

La jointure fonctionne selon le mme principe quavec 2 tables, il faut juste procder plusieursjointures

Ex : Liste des commandes avec le numro de commande, le nom de lemploy et le nom du clientSELECT employes.Nom, n_commande, SocieteFROM employes, commandes, clientsWHERE employes.n_employe = commandes.n_employeAND commandes.code_client=clients.code_clientORDER BY employes.nom

Autre criture :SELECT employes.Nom, n_commande, SocieteFROM commandesJOIN employes ON employes.n_employe = commandes.n_employeJOIN clients ON commandes.code_client=clients.code_clientORDER BY employes.nom

Exercices :

23 - Quantits du produit rfrenc 38, pour chaque commande de ce produit, vendu aprs le31/07/93

23 bis - Liste des produits de rfrence 20 38 vendus plus de 100 exemplairesaprs le 19-07-1993.On affichera le nom du produit, la rfrence et la quantit.

24 - Dtails de la commande 10250 (libell produit, quantit, prix unitaire)

25 - Libell des produits vendus par Fuller

26 - Villes dans lesquelles habitent les clients de Fuller

27 - Chiffre daffaire de Fuller

27 bis - Liste des employs ayant un chiffre daffaire > 1000 avec des clients habitant enFrance.(on affichera :nom de lemploy et Chiffre daffaire)

3 - Jointure d'une table avec elle-mme (auto-jointure)

On peut joindre une table avec une copie de celle-ci. Attention, Il faut dfinir diffrents noms ou aliaspour la table et sa copie.

Ex : Liste des employs et de leur chef de service classs par ordre dcroissant des noms de chefs deservice

SELECT Super.nom, 'est le chef de', Emp.nomFROM employes Emp, employes SuperWHERE Super.n_employe= Emp.rend_compte_aORDER BY Super.nom DESC

Emp et Super sont les alias de la table employs.



Autre criture :SELECT Super.nom, 'est le chef de', Emp.nomFROM employes Emp JOIN employes Super ONSuper.n_employe= Emp.rend_compte_aORDER BY Super.nom DESC

Exercices :

28 - Liste des vendeurs embauchs avant leur suprieur

4 - Jointure externe

Les jointures prcdentes sont qualifies de jointures internes, si la condition de jointure nest pasvrifie, lenregistrement napparat pas dans le rsultat. Pour visualiser tous les enregistrements, ycompris ceux ne rpondant pas au critre, il faut raliser une jointure externe

Ex : Liste des clients qui nont rien achetSELECT DISTINCT Societe, N_commandeFROM ClientsLEFT OUTER JOIN Commandes ONClients.Code_client = Commandes.Code_clientWHERE N_commande is null



D - REQUETES IMBRIQUEES

On peut employer des requtes imbriques pour joindre deux requtes.

La construction de la commande SELECT suprieure dpend du nombre de valeurs renvoyes par larequte imbriques.

1 - Requtes imbriques renvoyant une valeur unique

Ex : Ensemble des commandes du client nomm BON APP SELECT n_commande,date_commandeFROM commandesWHERE code_client =(SELECT code_clientFROM clientsWHERE societe = 'Bon app')ORDER BY date_commande

Ex : Ensemble des commandes du client nomm "BON APP" ou par le vendeur " FULLER ".SELECT n_commande,date_commandeFROM commandesWHERE code_client =(SELECT code_clientFROM clientsWHERE societe = 'Bon app')OR n_employe =(SELECT n_employe FROM employes WHERE nom=Fuller) ;

Exercice :

29 - Liste des vendeurs embauchs avant King



2 - Requtes imbriques renvoyant des valeurs multiples

Ex : Liste des commandes et clients du dpartement 44SELECT n_commande, code_client,date_commandeFROM commandesWHERE code_client IN(SELECT code_client FROM clients WHERE code_postal like '44%')ORDER BY n_commande

3 - Requtes imbriques multiples

Une sous requte peut contenir d'autres sous requtes imbriquesEx : Ensemble des clients ayant command le produit "50"

SELECT code_client, societe, contact, villeFROM clientsWHERE code_Client IN(SELECT code_client FROM commandes WHERE n_Commande IN(SELECT n_Commande FROM details_commandes WHERE ref_produit=50)) ;



E - REQUETES DINSERTION

SQL Permet galement dinsrer des donnes dans un table.

Ex : Ajouter le client : socit la nouvelle de code LANOUV INSERT INTO (code_client, societe)VALUES (lanouv, la nouvelle)

Attention, ici tous les champs nont pas t renseigns.Cela nempche pas la requte de fonctonner mais certaines donnes peuvent tre indispensable aubon fonctionnement de la table et ne doivent pas tre oublies.Cest en particulier le cas des identifiants.



F - LES REQUETES DE MODIFICATION

Syntaxe : UPDATE liste_table SET colonne1 = {expression/instruction_select},colonne2 = {expression/instruction_select}FROM liste_tablesWHERE conditionORDER BY expression [ASC | DESC]

Si on ne spcifie aucune condition dans la clause WHERE, lupdate sera ralis sur toutes les lignesde la table.

Normalement, lordre de mise jour des lignes importe peu. Cependant, il existe certains cas o onpeut avoir intrt spcifier cet ordre (ex : si on veut augmenter un champ cl de 1, il faut commencerpar la fin des enregistrements). On utilisera dans ces cas la clause ORDER BY.

Ex : UPDATE produits SET prix_unitaire = prix_ unitaire * 1.12 WHERE code_categorie = 1

Ex : UPDATE produits p SET prix_unitaire = prix_unitaire * 2 from produits p, categories cWHERE c.code_categorie = p.code_categorie

Exercice :

Augmenter le prix des produits de la catgorie desserts de 10%



G LES REQUETES DE SUPPRESSION

Syntaxe : DELETE [FROM] nom_table... [FROM table-list]... [WHERE search-condition]

Le 1er FROM est optionnel avec Sybase ASE. Il est toutefois conseill de lutiliser pour conserver lacompatibilit avec SQL2.Le 2me FROM est optionnel et indique les tables impliques dans la jointure par la conditionWHERE.Attention : si aucune condition WHERE nest applique, toutes les lignes de la table serontsupprimes.

Ex : suppression des clients habitant ParisDELETE from client WHERE ville = Paris

Exercice :

Supprimer les commandes passes par lemploy dodsworth



H) METHODE POUR ECRIRE UNE REQUETE

Pralable : bien connatre le modle et comprendre prcisment la question.

1) Etablir la liste des informations traiter :On distinguera :

- Les informations afficher

o Champs affichero Calculs simples sur x champs (ex : qt * prix_unitaire)o Agrgation dun champ => fonctions dagrgation comme COUNT, SUM, MIN, MAX

Dans le cas dune agration, soit on veut obtenir un seul rsultat (dans ce cas, on ne

peut pas faire apparatre dautres champs dans le select.) soit on veut obtenir x lignes

de rsultat (ex : le nombre de produits commands par commande) : dans ce cas, il

faut obligatoirement crer un niveau de regroupement : GROUP BY.

- Les informations de restriction de slection. On en a de 2 types :

o Des informations de restriction qui portent sur des lignes dune table => WHEREo Des informations de restrictions daffichage des groupes (celles-ci portent en gnral

sur des fonctions dagrgation) => HAVING

2) Ecrire le SELECT (cf. partie informations afficher)

3) Ecrire le FROMLa liste des tables se dfinit partir de :

- des champs afficher (cf. SELECT)

- des champs de restriction sur les lignes (WHERE)

- des champs de restriction sur les groupes (HAVING)

- des jointures ncessaires pour relier les tables (jointure classique ou jointure gauche ou

droite)

4) Ecrire le WHEREOn distinguera ici :

- les conditions de restriction sur les lignes

- les conditions de jointure

Ds quon ralise une jointure : on sinterrogera sur le type de jointure (normale, gauche ou droite) et

on sassurera de la pertinence du rsultat obtenu (on obtient toujours le nombre de lignes qui

correspond au plus petit niveau de dtails).



5) Ecrire le GROUP BYQuand on cre un groupe, il faut sassurer de la cohrence du groupe cr avec le niveau daffichage

souhait.

Quand jai un GROUP BY, je ne peux faire apparatre dans le SELECT que :

- des champs qui sont repris dans le GROUP BY

- des fonctions dagrgation (SUM, MIN, MAX )

6) Restreindre laffichage des groupes => HAVINGOn crira ici la condition de restriction portant sur les groupes. En gnral, celle-ci porte sur la

fonction dagrgation. (les restrictions concernant les champs scrivent dans la clause WHERE).

7) Trier les enregistrements (ORDER BY)

Cette mthode sapplique pour les requtes de slection (type SELECT). Pour les autres types derequtes, le principe reste le mme.



ANNEXE 1 : Les tables de lexemple

Tables :

ClientsCommandesEmployesMessagersDetails_commandesProduitsFournisseursCategories

sql initiation

Documents