Cours SQL

Cours Language SQL Server avec exemples de A a Z


Télécharger Cours Language SQL Server avec exemples de A a Z

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


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);



435