Cours Language SQL Server avec exemples de A à Z
...
L’interrogation en SQL: la commande SELECT
En SQL, l’interrogation d’une base de données se fait avec la commande SELECT
Forme de base de la commande SELECT
SELECT < liste des colonnes de la table résultat> FROM < liste des tables impliquées dans l’interrogation> WHERE < condition de sélection des tuples>
Exemple (→ A-3 pour la définition de “Chambres”) SELECT NumChambre, Confort
FROM Chambres
WHERE Confort='bain' AND Equipement='TV'
De l’algèbre relationnelle à l’interrogation en SQL La relation R
R ≡ select ∗ from R
La projection ðAu,Av,...Az(R)
π select Au, Av, ..., Az from R
Au,Av,...Az(R) ≡
La sélection óF(R)
σ from R where F
F(R) ≡ select ∗
Le produit cartésien R × S
R × S ≡ select R.∗, S.∗ from R, S
La jointure R S
(Ai1 è1 Bj1) ∧ (Ai2 è2 Bj2) ∧... ∧ (Aim èm Bjm)
R S ≡
(Ai1 è1 Bj1) ∧ (Ai2 è2 Bj2) ∧... ∧ (Aim èm Bjm)
select R.∗, S.∗ from R, S where R.Ai1 è1 S.Bj1 and R.Ai2 è2 S.Bj2
...
and R.Aim èm S.Bjm
où èi est un opérateur de comparaison (=, <, ≤, >, ≥, ≠ ) notation SQL: =, <, <=, >, >=, <> (noté aussi != et ^=)
De l’algèbre relationnelle à SQL (suite)
La jointure naturelle R S
si les attributs de jointure de R et S sont Au, Av,...,Az et les attributs propres de S sont Bp, Bq,..., Bt
R S = select R.*, Bp, Bq,..., Bt from R, S
where R.Au = S.Au and R.Av = S.Av
...
and R.Az = S.Az
L'union R u S
R u S = select * from R union select * from S L'intersection R n S
R n S = select * from R intersect select * from S La différence R − S
R − S = select * from R minus select * from S (~ A-24 pour ces trois opérations ensemblistes)
De l’algèbre relationnelle à SQL: cas général
D’une manière générale, la requête SQL
select A1,A2,...Am from R1 , R2,...,Rn where F est équivalente à
π ))
A1,A2,...Am(óF(R1 x R2 x...x Rn
Cette équivalence nous donne le modèle d’exécution simplifié de la machine SQL:
...
Remarques:
De l’algèbre relationnelle à SQL: exemples
→ A-8
Soit les instances de cru, vins et cepage_region: instance de cru:
...
De l’algèbre relationnelle à SQL: exemples (suite)
Q3: “La liste des noms de crus rouges”
E.a: TC (cru))
NOM_CRU(6COUL.=rouge
SQL> select nom_cru from cru where coul='rouge';
NOM_CRU
Ch. Margaux
Ch. Rausan-Segla
Ch. Latour
Ch. Lynch-Bages
Ch. Lagrange Clos Vougeot Corton
Les Epenots Les Gravieres
9 rows selected.
Q4: “À partir de quel cépage principal est produit le Meursault blanc ?”
E.a: TCCÉPAGE(6COMMUNE=Meursault) ∧ (COUL.=blanc)
(cru (COUL.=COUL.) ∧(RÉGION = R.PROD)
cépage_région))
SQL> select cepage from cepage_region, cru
where region=r_prod and cepage_region.coul=cru.coul and commune='Meursault' and cru.coul=’blanc’;
CEPAGE
Chardonnay Chardonnay Chardonnay
Remarque: nous verrons plus loin comment éliminer les doublons
De l’algèbre relationnelle à SQL: exemples (suite)
Q5: “Quels sont les bons millésimes du Château Latour?” (i.e. bon, très bon ou excellent)
E.a: TCMILL.(GQUALITÉ>_bonnenNOM_CRU=Ch.
Latourcru vins)
SQL> select millesime from vins, cru
where vins.region=cru.region and vins.coul=cru.coul
and nom_cru='Ch. Latour'
and (qualite='bonne' or qualite='tres bonne'
or qualite = 'excellente');
MILLESIME
1990
Q6 : “Quels sonts les crus rouges et leurs millésimes qui sont de bonne qualité?”
E.a: TCNOM-CRU, COMMUNE, RÉGION, COUL., MILL.(
G (cru vins))
(COUL.=rouge)n(QUALITÉ>_ bonne)
SQL> select cru.*, millesime from cru, vins
where cru.region=vins.region and cru.coul=vins.coul and cru.coul='rouge'
and (qualite='bonne' or qualite='tres bonne' or qualite = 'excellente');
De l’algèbre relationnelle à SQL: exemples (suite)
Q7: “Quels sont les millésimes où les Bourgognes blancs sont de qualité supérieure au Bordeaux blancs?”
E.a: Tc (vins)
MILL.(G(REGION=Bourgogne)∧(COUL.=blanc)
(MILL.=MILL.)∧(QUALITÉ>QUALITÉ)
G (vins) )
(REGION=Bordeaux)∧(COUL.=blanc)
SQL> select bourg.millesime from vins bord,vins bourg where bord.region='Bordeaux' and bord.coul='blanc' and bourg.region='Bourgogne' and bourg.coul='blanc' and bourg.millesime=bord.millesime and bourg.qualite > bord.qualite;
MILLESIME
1991
Remarque: pour que cette interrogation SQL donne toujours le résultat escompté, il faudrait que le type de données de la colonne “qualité” soit numérique (voir plus loin).
La forme complète de la commande SELECT
A l’aide d’exemples, nous allons examiner en détail les différentes parties de la commande SELECT, à savoir
- l’utilisation de *
- les noms de colonnes ambigus
- les fonctions d’aggrégation
- alias pour une colonne
- la selection sans condition
- les opérateurs de comparaison
- les interrogations imbriquées
- les quantificateurs en SQL ($ , " )
Les types de données en SQL
- number(p) et number(p,f) et : nombre de p chiffres dont f chiffres après la virgule
- date: une valeur de type date s’écrit sur le modèle ‘21-feb-94’
...
Les colonnes du résultat (dispayed column) Cas de base
les colonnes que l’on désire afficher comme résultat doivent être énumérées après “select “
Q3: “La liste des noms de crus rouges” select nom_cru from cru where coul='rouge'
NOM_CRU
Ch. Margaux
Ch. Rausan-Segla
Ch. Latour
Ch. Lynch-Bages
Ch. Lagrange Clos Vougeot Corton
Les Epenots Les Gravieres
Utilisation de *
“select * from ...” entraîne l’affichage de toutes les colonnes de toutes les tables citée après “from” Q8: “La description des crus des bourgognes 1990”
select * from cru, vins
where cru.region=vins.region and cru.coul=vins.coul
and region=bourgogne and millesime=1990
Les colonnes du résultat (suite)
la requête “select t.* ,... from t,...” a pour effet d’afficher toutes les colonnes de la table t
Q6 : “Quels sonts les crus rouges et leurs millésimes qui sont de bonne qualité?”
select cru.*, millesime from cru, vins
where cru.region=vins.region and cru.coul=vins.coul and cru.coul='rouge'
and (qualite='bonne' or qualite='tres bonne' or qualite = 'excellente');
Noms de colonne ambigus
ce cas se produit lorsque l’interrogation se réfère à des colonnes qui portent le même nom mais qui appartiennent à des tables différentes -> pour lever l’ambiguïté, il faut préfixer le nom de la colonne par le nom de la table à laquelle elle appartient
Q4: “À partir de quel cépage principal est produit le Meursault blanc ?”
select cepage from cepage_region, cru
where region=r_prod and cepage_region.coul=cru.coul and commune='Meursault';
CEPAGE
Chardonnay Chardonnay Chardonnay
Les colonnes du résultat (suite) Les fonctions d’aggrégation opèrent sur une liste de valeurs; on les utilise généralement sur une colonne
Q9: “La capacité théorique d'acceuil de l'hôtel” select sum(nbr_pers) from CHAMBRES
SUM(NBR_PERS)
Les colonnes du résultat (suite) Alias pour une colonne
permet de donner un nom à une colonne du résultat; lors de l’utilisation d’une fonction d’aggrégation:
Q9a:
select sum(nbr_pers) ‘capacité de l’hôtel’ from chambres
capacité de l’hôtel
ou pour expliciter une colonne:
Q10: “les qualités du millésime 1986”
select region,coul,millesime,qualite ‘QUALITE (1 à 7)’ from vins1 where millesime=1986;
REGION COUL MILLESIME QUALITE(1 à 7)
Bordeaux rouge 1986 6
Bordeaux blanc 1986 7
Bourgogne rouge 1986 5
Bourgogne blanc 1986 7
...
Les tables de l’interrogation (selected table) Cas de base
toutes les tables impliquées dans l’interrogation doivent être citées après “from”
Q5:“Quels sont les bons millésimes du Château Latour?”
select millesime from vins, cru
where vins.region=cru.region and vins.coul=cru.coul and nom_cru='Ch. Latour' and (qualite='bonne' or qualite='tres bonne' or qualite = 'excellente');
MILLESIME
1990
Alias pour une table
lorsqu’il faut joindre plusieurs fois la même table;
Q7: “Quels sont les millésimes où les Bourgognes blancs sont de qualité supérieure au Bordeaux blancs?”
select bourg.millesime from vins1 bord,vins1 bourg where bord.region='Bordeaux' and bord.coul='blanc' and bourg.region='Bourgogne' and bourg.coul='blanc' and bourg.millesime=bord.millesime and bourg.qualite > bord.qualite;
MILLESIME
1985 1987 1988 1991
La table comme ensemble
Rappel: une table SQL peut contenir plusieurs tuples identiques
Utilisation de distinct
élimine les doublons du résultat
Q4a: “À partir de quel cépage principal est produit le Meursault blanc ?”
select distinct cepage from cepage_region, cru
where region=r_prod and cepage_region.coul=cru.coul and commune='Meursault' and cru.coul=’blanc’;
CEPAGE
Chardonnay
Remarques:
- l’élimination des doublons est “coûteuse” car elle nécessite le tri préalable des tuples
- les opérateurs ensemblistes union, intersect et minus éliminent automatiquement les doublons du résultat (voir plus loin)
La condition de sélection
Cas de base
la commande SELECT sélectionne tous les tuples de la table (ou des tables) spécifiée après FROM qui satisfont la condition de sélection spécifiée après WHERE
Q2: “Tous les crus rouges”
select * from cru where coul='rouge';
...
Sélection sans condition
l’omission de la partie WHERE indique qu’il n’y a aucune condition de sélection des tuples -> tous les tuples de la table spécifiée après WHERE sont sélectionnés
Q1 : “Tous les crus”
La condition de sélection (suite)
Les opérateurs de comparaison
Q5a: “Quels sont les bons millésimes du Château Latour?”
select millesime from vins, cru
where vins.region=cru.region and vins.coul=cru.coul and nom_cru='Ch. Latour'
and qualite in ('bonne', 'tres bonne', 'excellente');
MILLESIME
1990
Q11: “La liste des articles qui contiennent le mot 'ordinateur' dans leur titre
select * from article where titre like '%ordinateur%';
TITRE NOM AUTEUR
-------------------------------------------------- ---
PRENOM_AUTEUR COD NUM ANNEE
---------------- --- L'ordinateur va-t'il imposer sa maniere de penser? Moeckli
Gustave TS 10 1984
Quand je pense "a", l'ordinateur repete "a" Demenjoz
Luc LNQ 658 1993
(explication de % et _ → A-16, A-32 et A-33)
Les opérateurs de comparaison (suite)
Q12: “Nombre de chambres dont le prix est compris entre 85 et 120 francs”
select count(*) from chambres where prix between 85 and 120
count(*)
Q13: “Le nom des clients qui n’ont pas annoncé leur date de départ”
select nom from clients, reservation
where clients.num_client=reservations.num_client and date_dep is null;
NOM DUPONT
La condition de sélection (suite) Interrogations imbriquées
certaines interrogations nécessite la connaissance préalable de certaines données de la bd pour pouvoir spécifier la condition de sélection
Q14: “Quels sont les millésimes du Bordeaux rouge dont la qualité est supérieure à la moyenne ( qualité moyenne des millésimes du Bordeaux rouge)”
select millesime from vins1
where region='Bordeaux' and coul='rouge' and qualite > (select avg(qualite) from vins1 where
region='Bordeaux' and coul='rouge');
MILLESIME
1985 1986 1989 1990
Q15: “La recette du 25 décembre 1989”
select sum(prix) from chambres
where num_chambre in (select num_chambre
from reservations
where date_arr <= ‘25-dec-89’
and date_dep > ‘25-dec-89’)
SUM(PRIX)
600
Les quantificateurs en SQL ( 3 , V ) Exists (3), not exists(3 )
Soit la relation langue
...
Q16: “Quels sont les cantons suisse où l’on ne parle pas l’allemand?”
select * from canton where not exists
(select * from langue
where canton.nom_canton=langue.nom_canton and langue_parlee='allemand');
...
Les regroupements
permet d’appliquer les fonctions d’aggrégation à des sous-groupes de tuples
Q17: “Le prix minimum et maximum des chambres par type de confort”
select confort, min(prix) 'PRIX MINIMUM', max(prix) 'PRIX MAXIMUM' from chambres group by confort;
CONFORT PRIX MINIMUM PRIX MAXIMUM
BAIN 120 180
DOUCHE 100 100
WC 80 90
Q18: “Combien de crus rouges et de crus blancs sont produits dans chaque commune?”
select commune, coul, count(*) from cru group by commune, coul;
Les opérations ensemblistes opérateurs: union (È), intersect (Ç) et minus (−)
Q19: “Quels sont les termes employés pour décrire l’équipement et le confort d’une chambre?”
select confort 'TERMES' from chambres union
select equipement from chambres;
TERMES
BAIN
DOUCHE
NON
TV WC
Le tri du résultat
Q11: “les cantons suisses dans l’ordre d’entrée dans la confédération et par ordre alphabétique”
select * from canton
order by date_entree asc, nom_canton asc
...
Définition des schémas de relation en SQL (suite)
create table article (titre varchar2(50),
nom_auteur char(16),
prenom_auteur char(16),
code_j char(3),
num char(6), annee number(4));
create table personne (nom char(16), prenom char(16), adresse char(16));
create table numero (code_j char(3), num char(6),
annee number(4), jour number(2), mois char(3));
Interrogation du dictionnaire de SQL
SQL> select table_name, column_name, data_type, data_length, data_precision, data_scale from user_tab_columns where table_name='NUMERO'
...
La définition des contraintes d’intégrité en SQL
“Une contrainte d’intégrité est une règle qui restreint les valeurs possibles pour une ou plusieurs colonnes dans une table”.
Nous ne verrons que la définition des contraintes de clé (primary key) et des contraintes de référence (foreign key) qui correspondent aux trois règles d’intégrité minimum du modèle relationnel (voir pp. 123-125)
Définition d’une clé primaire (primary key)
Exemple: base de données “articles de journaux” (pour le schéma, voir p. 178)
alter table editeur add
(constraint pk_editeur primary key (nom_ed));
alter table journal add
(constraint pk_journal primary key (code_j));
alter table numero add
(constraint pk_numero primary key (code_j, num, annee));
Définition des contraintes d’intégrité en SQL (suite)
alter table personne add
(constraint pk_personne primary key (nom, prenom));
Exemple de violation d’une contrainte de clé:
SQL> insert into personne(nom,prenom) values ('Monnier','Claude');
1 ligne créée.
SQL> insert into personne(nom,prenom) values ('Monnier','Claude');
insert into personne(nom,prenom)values ('Monnier','Claude') *
ERREUR à la ligne 1:
ORA-00001: Présence d'une clé dupliquée dans l'index
La définition des contraintes de référence
“Une contrainte de référence désigne une colonne ou une combinaison de colonnes comme clé externe (foreign key) et établit une association entre cette clé externe et la clé primaire de la table référencée”.
Définition d’une clé externe (foreign key)
Exemples:
alter table journal add
(constraint fk_ed_journal foreign key(nom_ed) references editeur,
constraint fk_red_journal foreign key(nom_redenchef, prenom_redenchef)references personne(nom,prenom))
alter table numero add
(constraint fk_numero foreign key (code_j) references journal);
La définition des contraintes de référence (suite)
alter table article add
(constraint fk_n_article foreign key(code_j, num, annee) references numero,
constraint fk_aut_article foreign key(nom_auteur, prenom_auteur)references personne(nom,prenom));
Exemple de violation d’une contrainte de référence:
SQL> insert into article (titre, nom_auteur, prenom_auteur, code_j,num,annee)
values('La SBS n’aime pas les extraterrestres', 'Genoud', 'Madeleine', 'LAS', '11', 1994);
insert into article (titre,nom_auteur,prenom_auteur,code_j,num,annee) *
ERREUR à la ligne 1:
ORA-02291: violation de contrainte NERIMA.FK_AUT_ARTICLE d'intégrité - touche parent introuvable
il y a violation de la contrainte de référence fk_aut_article car 'Genoud Madeleine' n’existe pas dans dans la table PERSONNE
Contraintes d’intégrité et dictionnaire SQL Interrogation du dictionnaire de SQL:
Q: “Quelles sont les contraintes d’intégrité qui concernent la table JOURNAL?”
SQL> select owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name, status from user_constraints where table_name='JOURNAL';
Définiton de vue en SQL
SQL> create view canton_romand as
select canton.* from canton, langue where canton.nom_canton=langue.nom_canton and langue_parlee='francais';
Vue créée.
Exemples d’interrogation:
Q: “Tous les cantons romands” SQL> select * from canton_romand;
Définiton de vue en SQL (suite)
Q: “Quels sont les cantons romands entrés dans la confédération au 19ème siècle?”
SQL> select * from canton_romand
where date_entree between 1800 and 1899 order by date_entree;
NOM CANTON CHEF LIEU DATE ENTREE
_ _ _
Vaud Lausanne 1803
Geneve Geneve 1815
Neuchatel Neuchatel 1815
Valais Sion 1815
Remarques
SQL: une histoire d’amour...
On se propose de donner toutes les variantes en français de la célèbre phrase tirée du Bourgeois Gentilhomme “Belle marquise, vos beaux yeux me font mourir d’amour.”
Solution:
1° on découpe la phrase en cinq parties indivisibles que l’on insère dans une table (à une seule colonne) appelée “marquise”:
marquise P
belle marquise
vos beaux yeux
me font
mourir d'amour
2° on produit toutes les permutations possibles en joignant 5 fois la table “marquise” avec elle-même et en éliminant les tuples dont deux colonnes ont même valeur:
SQL> select * from marquise m1, marquise m2, marquise m3, marquise m4,marquise m5
where m2.p <> m1.p and m3.p not in (m1.p,m2.p) and m4.p not in (m1.p,m2.p,m3.p) and m5.p not in (m1.p,m2.p,m3.p,m4.p);
