Cours Language SQL Server avec exemples de A a Z
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:
- ce modèle est un modèle logique destinéàl’utilisateur; il lui fournit la sémantique des requêtes select de SQL;
- l’exécution réelle d’une requête select est différente: propriétés de l’algèbre relationnelle -> optimisations.
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
- les colonnes du résultat
- l’utilisation de *
- les noms de colonnes ambigus
- les fonctions d’aggrégation
- alias pour une colonne
- les tables de l’interrogation - alias pour une table
- la table comme ensemble(élimination des doublons)
- la condition de sélection
- la selection sans condition
- les opérateurs de comparaison
- les interrogations imbriquées
- les quantificateurs en SQL ($ , " )
- la jointure externe
- le regroupement
- les opérations ensemblistes
- le tri du résultat
Les types de données en SQL
- type de données d’une colonne≡ domaine d’un attribut
- il y a essentiellement trois types de données en SQL: - char(n) et varchar2(n): chaîne de n caractères
- 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’
- Ex: create table vins1 (region char(16), coul char(5), millesime number(4), qualite number(1));
- Instance de la table vins1:
...
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
- avg: calcule la moyenne d’une liste
- count: compte le nombre d’éléments d’une liste
- min: donne la valeur minimum d’une liste
- max: donne la valeur maximum d’une liste
- sum: calcule la somme d’une liste
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
- in et not in: appartenance (resp. non appartenance) à une liste
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
- like: appartenance à une chaîne de caractère
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)
- between: appartenance à un intervalle
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(*)
- is null et is not null: comparaison avec la valeur null; utilisé généralement pour rechercher dans une table les tuples dont la valeur d’un attribut est indéfinie;
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 )
- permet de tester si le résultat d’une interrogation imbriquée contient au moins un tuple (respectivement aucun tuple).
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 (−)
- les opérandes doivent avoir le même nombre de colonnes et les colonnes correspondantes doivent être égales en type (pas forcément en taille)
- l’utilisation de ces opérateurs implique implicitement la clause distinct
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
- le résultat est trié selon une ou plusieurs clés de tri;
- s’il y a plusieurs clés de tri, la première de la liste est la clé primaire, la deuxième est la clé secondaire etc.
- asc -> ordre croissant (par défaut)
- desc -> ordre décroissant
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)
- Définition de la clé d’une table -> unicité des valeurs et valeurs non nulles
- Cette clé pourra être référencées dans une contrainte de référence.
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)
- la table qui contient la clé externe est appelée table enfant
- la table qui contient la clé primaire référencée est appelée table parent
- contrainte de référence -> la valeur de la clé externe de chaque tuple de la table enfant doit exister comme valeur de clé d’un tuple de la table parent (c-à-d que chaque tuple de la table enfant doit faire référence à un tuple qui existe dans la table parent)
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
- “Une vue est une table logique basée sur une ou plusieurs tables ou vues”
- On définit une vue en spécifiant une interrogation Exemple: la vue “les cantons romands”
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
- en interrogation, une vue se comporte exactement comme une table
- il n’est par contre pas possible d’insérer, de modifier ou de supprimer un tuple dans une vue
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);
