INSFP Ain Oulmene

Module: SQL Server

SQL Server
I. Présentation du SGBDR SQL Server
1. Définition : SQL Server est un système de gestion de base de données client/serveur
qui utilise transact-SQL pour transmettre des requêtes entre un client et SQL Server.
SQL Server et beaucoup plus robuste et évolutif d’un SGBD bureau tel que Access.
SQL Server peut être exécuté comme un SGBD bureau, mais il est plus utilisé comme
un SGBD Serveur.

2. SQL Server Management Studio (SSMS) : SQL Server est la console

d’administration principale pour SQL Server. Il permet de :
- Créer des objets de base de données (BDs, tables, procédures stockées, vues, etc.)
- Afficher les données
- Configurer des comptes utilisateurs
- Effectuer des sauvegardes, la réplication des données, etc.

II. Installation et configuration du SQL Server

1. Editions du SQL Server: SQL Server 2008 est disponible en plusieurs éditions :

- Enterprise Edition : L'édition Entreprise est la plus complète, elle est disponible
pour les versions 32 et 64 bits.
- Standard Edition : Cette édition correspond à la demande de la plupart des
entreprises. Elle offre une facilité d'utilisation et de gestion pour l'exécution des
applications.
- Workgroup Edition
- Developer Edition
- WebEdition
- Compact Edition
- Express Edition : C’est une version gratuite, supporte un petit volume de données,
peut être installée sur toutes les éditions de Windows. C’est la version que nous
allons utiliser pendant le semestre.
- Edition d’évaluation pour 180 jours

1
[Link]
INSFP Ain Oulmene
Module: SQL Server

2. Configuration requise:

Nous allons utiliser SQL Server 2008 Express with Advanced Services.

- Système d’exploitation : Windows Server 2003 SP 2, Windows Server 2008,

Windows Vista SP1, XP SP2, XP3, Win 7.
- Systèmes 32 bits : ordinateur avec processeur Intel ou compatible à 1 GHz ou plus
(2 GHz ou plus recommandé).
- Systèmes 64 bits : processeur à 1,4 GHz ou plus (2 GHz ou plus recommandé).
- Minimum de 256 Mo de RAM (1 Go ou plus recommandé).
- 1 Go d'espace disponible sur le disque dur.

3. Installation:

- Microsoft .Net Framework 3.5 SP1 doit être installé.

- Windows Installer 4.5 doit être installé.
- Installer SQL Server 2008 express with Advanced Services.

III. Outils d’administration du SQL Server

Depuis SQL Server 2005, plusieurs outils d’administration de SQL Server 2000 ont été
intégrés dans SQL Server Management Studio, d’autres ont été intégrés dans le gestionnaire
de configuration SQL Server.

1. SQL Server Management Studio : C’est un environnement intégré qui permet

d’avoir accès, de configurer, de gérer, d’administrer et de développer tous les
composants de SQL Server. SSMS associe dans un seul environnement les
fonctionnalités d’Entreprise Manager, de l’analyseur de requêtes et d’Analysis Manager,
comprises dans les versions précédentes de SQL Server. À l'aide de SQL Server
Management Studio, les développeurs et les administrateurs de base de données peuvent
développer ou administrer n'importe quel composant du moteur de base de données.

2
[Link]
INSFP Ain Oulmene
Module: SQL Server

Figure1 : Microsoft SQL Server Management Studio

2. Gestionnaire de configuration SQL Server : C’est un outil qui permet de gérer

les services associés à SQL Server, de configurer les protocoles réseau utilisés par SQL
Server et de gérer la configuration de la connectivité réseau à partir des ordinateur
clients SQL Server.

Figure2 : SQL Server Configuration Manager

3
[Link]
INSFP Ain Oulmene
Module: SQL Server

IV. Décrire les bases de données SQL Server

1. Types des bases de données SQL Server : SQL Server héberge deux types de
base de données : base de données système et base de données utilisateurs.
1.1. Bases de données
système : Elles contiennent des informations nécessaires au bon fonctionnement de
SQL Server. Lors de l’installation de SQL Server, le programme d’installation crée
plusieurs bases de données système :
- master : contrôle les bases de données utilisateurs et le fonctionnement global de
SQL Server.
- model : utilisée pour la création de nouvelle base de données utilisateur.
- tempdb : offre une zone de stockage pour les tables temporaires et les autres
besoins de stockage temporaire.
- msdb : offre une zone de stockage pour les informations de programmation et
l’historique des travaux
1.2. Bases de données
utilisateur : Selon les options choisies, le programme d’installation peut créer les
bases de données utilisateurs suivantes :
- Pubs : propose un exemple de base de données comme outil d’apprentissage.
- Northwind : propose un exemple de base de données comme outil d’apprentissage
avec un grand nombre de données.

2. Objets de base de données : Les objets BD aident l’utilisateur à structurer les

données et à définir les mécanismes de sécurité, d’améliorer des performances et de
contrôle d’intégrité des données. Parmi ces objets BD :
- Table : définit un ensemble de lignes ayant des colonnes associées.
- Contrainte : définit les règles relatives aux valeurs autorisées dans les colonnes et
constitue le mécanisme standard pour garantir l’intégrité des données.
- Valeur par défaut : définit une valeur qui est stockée dans une colonne si aucune
autre valeur n’est fournit au moment de l’ajout.
- Index : constitue une structure de stockage offrant un accès rapide pour l’extraction
de données.

4
[Link]
INSFP Ain Oulmene
Module: SQL Server

- Vue : permet de visualiser des données provenant d’une ou de plusieurs tables ou

vues d’une même base de données.
- Fonction définit par l’utilisateur : renvoie soit une valeur scalaire, soit une table.
- Procédure stockée : constitue un ensemble nommé d’instructions Transact-SQL
pré compilées devant être exécutées ensemble.
- Déclencheur : constitue une forme spéciale de procédure stockée exécutée
automatiquement lorsqu’un utilisateur modifie des données d’une table ou d’une
vue.
3. Références aux objets SQL Server

On peut faire référence aux objets SQL Server de plusieurs manières : on peut spécifier le
nom complet de l’objet ou ne spécifier qu’une partie du nom de l’objet et laisser SQL
Server déterminer le reste du nom à partir de votre connexion de travail.

3.1. Noms complets : Le

nom complet d’un objet SQL Server comprend quatre identificateurs : le nom du
serveur, le nom de la base de données, le nom du propriétaire et le nom de l’objet au
format suivant :

[Link]é[Link]é[Link]

3.2. Noms incomplets : Lorsqu’on fait référence à un objet, on n’est pas toujours obligé
de spécifier le serveur, la base de données et le propriétaire. Lorsqu’on crée un
objet, si différentes parties du nom ne sont pas spécifiées, SQL Server utilise les
valeurs par défaut décrites ci-dessous.
- Le serveur est défini par défaut sur le serveur local.
- La base de données en cours est utilisée par défaut.
- Le nom d’utilisateur est employé comme propriétaire par défaut dans la base de
données, associée à l’ID de connexion de la connexion en cours.
4. Tables systèmes

Les tables système stockent des informations (métadonnées) relatives au système et aux
objets dans les bases de données. Les métadonnées sont des informations relatives aux
données. Chaque base de données (y compris master) contient un ensemble de tables
système qui stockent les métadonnées. Cet ensemble de tables système constitue le
5
[Link]
INSFP Ain Oulmene
Module: SQL Server

catalogue de la base de données. Le catalogue du système stocke les métadonnées relatives

au système entier et à toutes les autres bases de données.

5. Extraction des métas données

Lorsque vous rédigez des applications conçues pour extraire des informations à partir des
tables système, vous devez utiliser les procédures stockées système, les fonctions système
ou les vue de schéma d’informations fournies par le système.

Vous pouvez interroger une table système comme toute autre table de base de données pour
extraire des informations relatives au système. Toutefois, il est déconseillé de modifier
directement les tables système. Cela risque d’empêcher SQL Server de procéder à une
récupération correcte en cas de panne du système.

5.1. Procédures stockées

système : Afin de faciliter le regroupement d’informations relatives à l’état du
serveur et des objets de base de données, SQL Server comporte un ensemble de
requêtes pré-rédigées appelées procédure stockées système. Le nom de la plupart
des procédures stockées système commence par le préfixe sp_
Exemple : l’exemple suivant exécute une procédure stockée système afin d’obtenir
des informations sur la table « apprenti »

Exec sp_help apprenti

5.2. Fonction de système et

de méta données : Les fonctions système permettent d’interroger les tables système
à partir d’instructions Transact-SQL. Par exemple la fonction USER_NAME(ID)
renvoie le nom de l’utilisateur.
5.3. Vue de schémas
d’information : Les vues de schéma d’informations proposent une vue interne,
indépendante des tables système, des métadonnées SQL Server. Chaque vue de
schéma d’informations contient des métadonnées pour tous les objets de données
stockés dans cette base de données.
Exemple : l’exemple suivant interroge une vue de schéma d’informations afin
d’obtenir la liste des tables d’une base de données.

SELECT * FROM INFORMATION_SCHEMA.TABLES

6
[Link]
INSFP Ain Oulmene
Module: SQL Server

V. Sécurité de SQL Server

Authentification des comptes de connexion : L’authentification identifie l’utilisateur

qui emploie un compte de connexion et vérifie uniquement sa capacité à se connecter à SQL
Server. Si l’authentification réussit, l’utilisateur se connecte à SQL Server.

L’utilisateur est alors autorisé à accéder aux bases de données sur le serveur. Pour cela, des
autorisations spécifiques aux bases de données sont affectées aux comptes d’utilisateur et aux
rôles. Les autorisations contrôlent les activités que l’utilisateur est autorisé à effectuer dans une
base de données SQL Server.

7
[Link]
INSFP Ain Oulmene
Module: SQL Server

VI. Langage T-SQL (Transact Structured Query Language)

1. Langage T-SQL et outil de programmation SQL Server

1.1. Définition du langage
Transact SQL : C’est un langage procédural, permet de modéliser le développement
des applications liées aux BDs.
1.2. Ecrire/ exécuter un
script SQL: Un script est un ensemble des instructions TSQL. On peut utiliser les
scripts SQL pour créer des objets de base de données, insérer, sélectionner, modifier ou
supprimer des données. Pour cela :
- Cliquez sur « New Query »
- Saisissez votre requête dans l’espace de travail.
- Cliquez sur « Exécuter » ou bien F5 pour exécuter la requête
- Vous pouvez réutiliser les scripts SQL déjà enregistrés.
2. Types d’instruction transact: Le langage SQL possède trois types
d’instructions :
2.1. Langage de définition
de données (DDL) : utilisé pour la création, la modification et la suppression des objets
de base de données. Il utilise principalement les instructions Create, Alter et Drop.
2.2. Langage de
Manipulation des données (DML) : qui permet de sélectionner d’ajouter, de modifier
et de supprimer des données dans les objets de base de données. Il utilise
principalement les instructions Select, Insert, Update et Delete.
2.3. Langage de Contrôle de
données (DCL) : utilisé pour la gestion des protections d’accès aux données. Il utilise
principalement les instructions Grant, Deny et Revoke.
3. Eléments de la syntaxe T-SQL
3.1. Les directives : Les directives indiquent comment traiter les
instructions TSQL. Les principales directives sont :
- Use « base de données » : permet de préciser sur quelle base de données vont
porter les instructions qui suivent. Exemple : Use Master

8
[Link]
INSFP Ain Oulmene
Module: SQL Server

- Go : cette commande termine un lot d’instructions TSQL. Certaines instructions

TSQL doivent s’exécuter comme des lots, dans ce cas, l’utilisation de la commande
« GO » est obligatoire. Exemple : Use GestionDeStock
Select * from produit
Go

- Print : est une instruction permettant de générer une ligne en sortie de procédure.
- Exec[ute] : est utilisée pour exécuter une fonction ou une procédure stockée.
3.2. Commentaires
--Commentaire :
en ligne
/* Commentaire en
Bloc*/

3.3. Identificateurs : les identificateurs sont les noms des objets

tels que les bases de données, les tables, les colonnes, etc. Les identificateurs standards
peuvent contenir de 1 à 128 caractères notamment des lettres, des symboles et des
nombres. La première lettre doit être alphabétique et aucun espace n’est autorisé dans le
nom. Ils ne doivent correspondre à aucun mot-clé du langage TSQL. Si un
identificateur ne respect pas une ou plusieurs de ces règles doit être délimité par [] ou
« ». Exemple : Select * from « liste des étudiants »
3.4. Types de données : ils déterminent la nature du contenu des
objets tels que les colonnes, les variables, les paramètres, etc.
- Numérique: int, smallint, bigint, decimal, numeric, money,float, real, bit, etc.
- Date: dateTime, smallDateTime.
- Caractères: char, varchar, nvarchar, text, ntext, etc.
- Binaire : binary, varBinary, image.
3.5. Variables : sont des éléments permettant de stocker une valeur
en vye de son utilisation future. Une variable locale est définie par l’utilisateur par
l’instruction Declare , puis on affecte une valeur initiale dans une instruction Set.
3.6. Mots clés réservés : Microsoft SQL Server utilise des mots
clés réservés pour définir, manipuler des bases de données et y accéder. Exemple : add,
all, alter, exit, primary, etc.

9
[Link]
INSFP Ain Oulmene
Module: SQL Server

VII. Gestion d’une base de données

1. Mode de stockage de données

Une base de données SQL Server est constituée d’au moins deux fichiers :
- Un fichier de données qui contient les objets de base de données avec une extension
.mdf
- Un ou plusieurs fichiers de journal des transactions avec une extension .ldf. Les
fichiers journaux des transactions contiennent toutes les informations nécessaires à
la récupération de la base de données en cas de défaillance du système. Par défaut la
taille du journal des transactions est égale au quatre de celle de fichiers de données.

2. Mode de fonctionnement du journal des transactions

Une transaction est un ensemble d’instructions TSQL qui sont traitées comme une unité
unique de travail et de récupération. Chaque transaction est enregistrée dans un journal
des transactions pour maintenir la cohérence de la base de données et permettre sa
récupération en cas de défaillance du système. Le journal des transactions enregistre les
modifications apportées aux données à mesure qu’elles interviennent. Le processus
d’enregistrement est décrit comme ce suit :
- Une modification des données est envoyée par l’application
- En cas de modification, les pages de données concernées sont chargées à partir du
disque dans la mémoire
- Chaque instruction de modification des données est enregistrée dans le journal lors
de son exécution, avant d’être réellement effectuée dans la base de données.
- Le point de contrôle écrit régulièrement toutes les transactions achevées dans la base
de données sur le disque.

En cas de défaillance du système, le processus de récupération automatique utilise

le journal des transactions pour transmettre toutes les transactions validées et
annuler celles inachevées.

3. Création des bases de données : La gestion d’une base de données ou d’un objet
d’une base de données SQL Server se fait soit en utilisant l’inspecteur d’objet de
Microsoft SQL Server Management Studio, soit du code Transact SQL.

10
[Link]
INSFP Ain Oulmene
Module: SQL Server

- Pour créer une BD il suffit de faite un clic droit sur « databases »  new database.
- Ou bien en utilisant un code SQL : Pour créer une base de données, on doit définir
les éléments suivant : son nom, sa taille et les fichiers utilisés pour la stocker
Exemple :
create database exemple
on
primary (name = donneesExemple,
filename = 'D:\tp1\[Link]',
size = 10MB,
maxsize = 15MB,
filegrowth = 20%)
Log On
(name = nournExemple,
filename = 'D:\tp1\[Link]',
size = 3MB,
maxsize = 5MB,
filegrowth = 1MB)

4. Configuration des options de base de données

On peut configurer des options de base de données.

Option de base de données Description

dbo use only Limiter l’utilisation de la BD à son propriétaire
Read only Lecture seule de la base de données
Select into/bulkcopy Autoriser une BD à accepter les opérations non
journalisées.
Single user Limiter l’accès à la BD à un utilisateur à la fois.
[Link] Permet de vider le journal des transactions
Autoshrink Déterminer si la taille de la BD doit être réduite
automatiquement.

5. Modification des bases de données

L’instruction Alter database permet de modifier une base de données
Exemple : alter database exemple
modify name = ex
go

6. Suppression d’une base de données

L’instruction Drop database permet de supprimer une base de données
Exemple : drop database ex

11
[Link]
INSFP Ain Oulmene
Module: SQL Server

VIII. Gestion d’une table

1. Gestion d’une table à l’aide de l’explorateur d’objet

1.1. Création d’une table :

- cliquer sur la BD
- Faire un clic droit sur tables  new table  saisir les attributs de la table ainsi que
leurs types
- Faire un clic droit sur l’attribut clé et mettre « set primary key »
- Enfin, enregistrer la table.

1.2. Modification d’une table:

- Faire un clic droit sur la table  Design

1.3. Suppression d’une table :

- Faire un clic droit sur la table  Delete

1.4. Editer les données d’une table : pour ajouter, supprimer ou modifier une
donnée :

- Faire un clic droit sur la table  Edit top 200 rows

1.5. Consultation d’une table :

- Faire un clic droit sur la table  Select top 1000 rows

2. Gestion d’une table en utilisant des scripts TSQL

2.1. Création d’une table :

Syntaxe : Create table nomTable( att1 type1 [contrainte1],

att2 type2 [contrainte2],
………)

Le mot-clé AS permet de créer des colonnes calculées à partir des valeurs d’autres colonnes.

Create table note( note1 real,

note2 real,
moyenne as (note1+note2)/2 12
[Link] Go
INSFP Ain Oulmene
Module: SQL Server

Exemple :

2.2. Modification d’une table: l’instruction Alter table permet de modifier la

structure d’une table

Syntaxe : Pour ajouter une colonne : Alter table nomTable add colonne type

Pour supprimer une colonne : Alter table nomTable drop att1, att2...

Exemple : alter table note add note3 int

alter table note drop column m

2.3. Suppression d’une table :

drop table nomtable

Syntaxe :

Exemple : drop table note

TP : Soit la BDR suivante :

Patient (Numero, NomP, PrenomP, Sexe, Age)

Médecin (Code, NomM, prenomM, Specialite)
Chambre (NC, type, NPavillon, nbrLit)
Hospitaliser (Numero, Code, NC, Date_Entree, Date_Sortie)

1- Créer une base de données « Hopital »

2- Créer les quartes tables
3- Etablir le diagramme de la base de données
4- Insérer quelques données
5- Afficher les données de la table « Patient »

13
[Link]
INSFP Ain Oulmene
Module: SQL Server

IX. Implémentation des contraintes d’intégrités

1. Introduction : Les contraintes d’intégrité permettent d’assurer des contrôles sur les
données saisies dans la base de données. Il existe différents catégories : contraintes
d’entités, contraintes de domaine et contrainte d’intégrité référentielle.
2. Contraintes d’entités : permet de définir une ligne comme une entité unique.

2.1. Primary key : permet de définir la clé primaire d’une tale

create table T ( att1 type1 primary key,
Syntaxe : att2 type2)
Go
create table T2 (att1 type1,
att2 type2,
att3 type3,
constraint pkDEF primary key (att1, att2)
)
Go

create table T ( num int primary key,

Exemple : nom char(20))
Go
create table T2 ( numA int,
numM int,
note int,
constraint pkDEF primary key (numA, numM)
)
Go

2.2. Unique : permet de garantir qu’aucune valeur en double n’est entrée dans une
colonne spécifique.

create table employe ( numE int primary key,

Exemple : NSS int unique,
nom char(20))
Go

2.3. Identity : permet d’incrémenter automatiquement les valeurs numériques d’une

colonne

identity [(valeurInitial, pasIncrement)]

Syntaxe :

14
[Link]
INSFP Ain Oulmene
Module: SQL Server

Lorsqu’aucun paramètre n’est précisé, la propriété identity prend comme valeur

initial 1 et comme pas d’incrémentation 1

Exemple : create table employe ( numE int identity (10,1) primary key,
NSS int
)
Go

3. Contrainte de domaine : permet de définir un intervalle ou une liste des valeurs

possible pour une colonne

3.1. Check : permet de définir une condition que la valeur doit respecter avant d’être
enregistrée.

Exemple : create table visit ( age

num int identity primary key,
int check (age<120),
jour char (20) check (jour in ('Lundi',
'Mercredi', 'Vendredi')),
poid real check (poid between 5 and 500))
Go

3.2. Default : permet de définir une valeur par défaut pour une colonne.

Exemple : create table commande ( NC int primary key,

dateC datetime default (getdate())
)
Go

3.3. [not]null : permet de définir la possibilité pour une colonne d’accepter une valeur
null.
create table apprenti ( num int primary key,
nom char (30) not null
Exemple :
)
Go

4. Contrainte d’intégrité référentielle : elle est fondée sur les relations entre les
clés étrangères et les clés primaires. Elle préserve les relations définies entre les tables
lorsque des valeurs, de clé étrangère, sont entrés et aussi lorsque des valeurs, de clé
primaire, sont modifiés ou supprimées.

15
[Link]
INSFP Ain Oulmene
Module: SQL Server

La contrainte Foreign key appliqué à une colonne, permet de vérifier la valeur doit
exister dans la colonne référencée.

Syntaxe : [foreign key nom_colonne]

references table_ref (colonne_ref)]
[on delete { cascade | no action}]
Exemple : [on update { cascade | no action}]
Create table pere ( np int primary key,
nomP char (20))
go
Create table fils ( nf int primary key,
Prenom char (20),
Np int references pere(np)on delete cascade)
go

TP : Cas : gestion de réservation d’un hôtel

Pour enregistrer l’activité d’un hôtel, on propose le schéma de relation suivant :

Chambre (numch, prix, typech)

Reservation (numr, dateDebut, dateFin, #numch, #numcl)
Client (numcl, nom, villecl, pays)

Note :

a) Un client peut effectuer une ou plusieurs réservations

b) Une chambre peut faire l’objet de plusieurs réservations à des périodes différentes. On
ne peut pas trouver deux réservations pour la même chambre et la même période
c) L’hôtel possède des chambres simple ou double

Questions :

1) Créer la base de données « Gesthotel »

2) Créer les tables en appliquant les contraintes d’intégrités

16
[Link]
INSFP Ain Oulmene
Module: SQL Server

X. Manipulation de données

1. Ajout de données : L’instruction Insert permet d’ajouter une ou plusieurs lignes

dans une table

Syntaxe : Insert [into] nomTable [(liste des colonnes)] Values (expression)

Exemple : Insert into apprenti (nom, prenom)Values ('serai', 'sara')

1. Modification de données : L’instruction update permet de changer les valeurs

d’une ou plusieurs lignes dans une table.

Update apprenti set {(nom_colonne = { expression }

Syntaxe : [from table_source] [where condition]

Exemple :
Update employer set salaire = salaire+1000

2. Suppression de données : L’instruction Delete permet de supprimer une ou

plusieurs lignes dans une table.

Syntaxe : Delete nomTable [from table_source][where condition]

Exemple : Delete apprenti where nom = 'serai'

TP :

- Après la création des trois tables du TP précédent « gestion de réservation d’un

hôtel », saisir quelques données dans les trois tables.

17
[Link]
INSFP Ain Oulmene
Module: SQL Server

- L’hôtel offre une remise de 1000DA pour les chambres doubles, programmer cette
modification.
- Supprimer les chambres non réservées.

XI. Extraction de données

1. Introduction: L’extraction de données se base sur une requête. Une requête est une
demande de données stockées dans SQL Server

2. Expression d’une projection :

Syntaxe : select <attribut1>, <attribut2>,....., <attributn>

From <nomTable>

Exemple1 : select * -- *permet de selectionner tout les colonnes de la table

From apprenti

Exemple2 : select Nom as "nom de l'aprentie" /* le mot clé as permet de

définir un alias pour une colonne*/
From apprenti

3. Expression d’une selection : La clause where permet de spécifier un critère de

recherche que les lignes devront respecter pour être sélectionnées.

Exemple : select *
From apprenti
where dateN > '01/01/1990' /* afficher la liste des apprenté nés
aprés le 01/01/1990*/

La condition de recherche est une combinaison d’une ou plusieurs opérateurs logiques. En plus
des opérateurs de comparaison standard, on peut aussi utiliser les suivants :
Expression [not] between valeur1 and valeur2
Expression is [not] null
Expression [not] like chaine /* l’opérateur like permet
d’effectuer des comparaisons sur des chaînes de caractères*/

18
[Link]
INSFP Ain Oulmene
Module: SQL Server

4. La clause Order By : La clause order by spécifie l’ordre de tri affecté aux colonnes
renvoyées dans une instruction Select

Exemple : select *
From apprenti
order by dateN desc /* Afficher la liste des apprentis par ordre
décroissant par date de naissance. On peut aussi utiliser
l’argument Asc pour un ordre de tri croissant*/

5. Techniques avancées d’accès aux données :

Le mot-clé Distinct permet d’éliminer les doublant dans le jeu de résultat d’une instruction
Select.
select distinct typeP
Exemple : From produit /* Afficher la liste des différents types des
produits*/

Le mot-clé Top permet de limiter le nombre de lignes renvoyées dans le jeu de résultat d’une
instruction Select.
select top 5 *
Exemple : From apprenti /* Afficher la liste des cinq premiers apprentis*/

TP : Soit la BDR suivante :

Auteur (numA, nom, prenom, adresse)

Livre (numL, titre, editeur, anneeEdition)
Ecrit (numA, numL)

Questions :

1) Créer la base de données « ouvrage ».

2) Créer les trois tables à l’aide de l’explorateur d’objet en appliquant les contraintes
d’intégrité.
3) Etablir le diagramme de base de données.
4) Saisir quelques données dans les trois tables.

19
[Link]
INSFP Ain Oulmene
Module: SQL Server

5) Exprimer les requêtes suivantes en SQL

- La liste des noms et prénoms des auteurs
- La liste des livres écrits en 1990
- La liste des numéros et des titres des livres édités en 2006 par d’éditeur « Eyrolles »
- Afficher la liste des noms et des prénoms des auteurs habitent en France par ordre
alphabétique ascendant sur le nom

XII. Jointure des tables

1. Introduction: Pour retourner des données provenant de deux ou plusieurs tables, On

doit utiliser une jointure entre ces tables. Il existe deux types : jointures internes et
jointures externes.

2. Jointure interne : Elle retourne les enregistrements uniquement quand les deux
tables respectent les conditions de la jointure.

Syntaxe : select <attribut1>, <attribut2>,....., <attributn>

From TableA [INNER]join TableB
On condition

Exemple1 : select [Link], [Link]

from pere p join fils f
on [Link] = [Link] /* Aficher les listes des fils qui ont
un père en précisant le nom du père */

Ou bien : select [Link], [Link]

from pere, fils
where [Link] = [Link]

3. Jointure externe : Elle retourne tous les enregistrements d’au moins une des deux
tables mentionnées dans la clause From et renvoient les lignes de l’autre table qui
répondent à la condition mentionnée par la clause On.

select <attribut1>, <attribut2>,....., <attributn>

Syntaxe : From TableA {left| right|full}[OUTER]join TableB

20
[Link]
INSFP Ain Oulmene
Module: SQL Server

On distingue trois types de jointure externe :

3.1. Jointure externe gauche : introduite par la clause left outer join. Elle retourne
tous les enregistrements qui respectent la condition ainsi que tous les enregistrements de
table à gauche du form qui ne respectent pas la condition ;

Exemple1 : select [Link], [Link]

from pere P left join fils f
on [Link] = [Link]
/* afficher la liste de tous les pères et préciser le prénom des
fils lorsqu'ils existent*/

3.2. Jointure externe droite : introduite par la clause right outer join. Elle retourne
tous les enregistrements qui respectent la condition ainsi que tous les enregistrements de
table à droite du form qui ne respectent pas la condition.
3.3. Jointure externe complète : introduite par la clause full outer join. Elle retourne
tous les enregistrements qui respectent la condition, et dans les cas où la condition ne
peut être satisfaite de ces lignes met la valeur NULL dans les champs inhabités.
4. Jointure croisée: est un cas spécial de jointure qui permet d’obtenir le produit
cartésien des tables impliquées dans la jointure. Elle est introduite par la clause Cross
Join.

TP : Soit la BDR suivante :

Patient (numP, nomP, prenomP, sexe, age)

Medecin (CodeM, nomM, prenomM, specialite)
Chambre (numC, type, numPavillon, nbrLit)
Hospitaliser (numP, codeM, NC, dateEntree, dateSortie)

Questions :

1) Créer la base de données « Hopital ».

2) Créer les tables à l’aide de l’explorateur d’objet en appliquant les contraintes
d’intégrité.
3) Etablir le diagramme de base de données.
4) Remplir les tables.
5) Exprimer les requêtes suivantes en utilisant des scripts SQL:
- Afficher la liste des patients

21
[Link]
INSFP Ain Oulmene
Module: SQL Server

- La liste des Médecins chirurgiens

- La liste des noms et prénoms des patients hospitalisés par le médecin Amrane
- La liste des noms et prénoms des patients hospitalisés pour une durée qui dépasse 25
jours.
- La liste des patients homme
- La liste des patients hommes qui occupent la chambre n° 25 du pavillon A
- Noms et âges des patients hospitalisé le 01/03/2015
- La liste des patients femmes avec les chambres qui les occupent

XIII. Regroupement et synthèse de données

1. Fonctions d’agrégation:
Min() : renvoie la valeur minimale d’une colonne ou d’une expression.
Max() : renvoie la valeur maximale d’une colonne ou d’une expression.
Sum() : renvoie la somme d’une colonne ou d’une expression de type numérique.
Avg() : renvoie la moyenne d’une colonne ou d’une expression de type numérique.
Count() : retourne le nombre d’éléments d’une colonne ou d’une expression.

fctAgrt ([ALL/Distinct] Expression)

Syntaxe :

Exemple1 : select AVG(note)

from etudiant
go /* afficher la moyenne des notes */

2. Regroupement avec Group By:

La clause Group By est utilisée pour produire des valeurs d’agrégation regroupées selon une
expression.

Group by [ALL] Expression [,]

Syntaxe :

Exemple: Use pubs

select type, min(price), max(price), avg(price)
from titles
group by type
order by type /*La clause Order by permet d’ordonner e jeu de
résultat après le regroupement des données */
go /* cette exemple affiche la liste des types de livre avec pour
chaque type, le prix maximu, le prix minimum et la moyenne des prix
*/ 22
[Link]
INSFP Ain Oulmene
Module: SQL Server

La clause Having définit les critères de sélections pour la clause Group

Use pubs
Exemple: select type, min(price), max(price), avg(price)
from titles
group by type
having max(price)>1000
go

3. Synthèse avec Compute:

La clause Compute permet de générer une valeur de synthèse apparaissant après le jeu de
résultat de l’instruction Select.

Compute { {AVG| Sum| Min| Max| Count} | Expression [,]} [By

Syntaxe :
Expression]

Exemple: Use pubs

select type, price
from titles
order by type
compute min(price), max (price) by type

NB : La clause Order By deviant obligatoire dès qu’on utilise Compute By

Exercice :

Soit la BDR suivante : Soit la BDR suivante :

Employe (numE, nom, fonction, chef, #numS)

Service (numS, nomS, location)
Enfant (numEn, nomEn, age, numE)

Exprimez les requêtes suivantes en SQL :

1) Lister tous les employés avec leurs numéros

2) Donner les noms des vendeurs ainsi que leurs salaires
3) Combien y a-t-il d'analyste dans l'entreprise ?

23
[Link]
INSFP Ain Oulmene
Module: SQL Server

4) Donner les noms, fonctions et salaires des employés classés par salaire en ordre
décroissant
5) Donner la somme des salaires par fonction
6) Parmi les agents, quel est le nom et le salaire de l'employé le moins payé
7) Donner le nom du chef de l’employé ‘Boutayeb’
8) Donner les noms, fonctions et salaires des employés exerçant la même fonction que
'Nasri' (triés par ordre croissant des salaires)
9) Donner les noms et fonctions des employés ainsi que le nom du service où ils
travaillent
10) Pour les employés qui ont des enfants, donner leurs noms et celui de leurs enfants
et leurs âges
11) Donner les noms de tous les employés et pour ceux qui ont des enfants donner les
noms de ces derniers
12) Donner les noms des 5 employés les plus payés ainsi que leurs fonctions et salaires

IVX. Utilisation des sous-requêtes

1. Introduction : Une sous-requête est une requête Select qui est imbriquée dans une
autre requête.
select nom ‘ ‘ + prenom as [Nom Complet]
Exemple: from sectionInf
where moyenne > (select AVG (moyenne)
from sectionInf)
Order by moyenne desc
/* Aficher par ordre décroissant de moyenne, les noms des étudiants
qui ont eu une note superieure à la moyenne de la section */

2. Les règles des sous-requêtes : L’utilisation d’une sous-requête obéit à plusieurs

règles, dont les principales sont les suivantes :
- Une sous-requête précédée d’un opérateur de comparaison ne doit retourner qu’une
seule valeur sauf si elle est introduite par le mot-clé any, all ou exists.
- Les clauses compute, into et distinct ne peuvent pas être utilisées dans une sous-
requête.
- Order By ne peut être utilisé qu’avec le mot-clé top dans une sous-requête.
- Lest types de données ntext, text et image ne sont pas autorisés dans la liste de
sélection d’une sous-requête.
3. Les types des sous-requêtes : En plus des opérateurs de comparaisons, les sous-
requêtes peuvent être introduites par divers mots-clés dans la clause where.

24
[Link]
INSFP Ain Oulmene
Module: SQL Server

3.1. Le mot clé in : Le mot-clé in pemet de déterminer si une valeur donnée

appartient à une liste de valeur.

Syntaxe : where expression not in (sous-requête)

Exemple: Select nom

from etudiant
where numE in (select numE
from bourssier)
/* Afficher la liste des étudiants boursiers */

3.2. Les mots clés any et all : Le mot-clé all spécifie tous les éléments retournés
tandis que le mot-clé any spécifie l’un d’entre eux.

Syntaxe : where expression opDeComparaison {any| all} (sous-requête)

Exemple: Select nom

from sectionInf
where moyenne > all (select moyenne
from sectionGRH)
/* Afficher la liste des étudiants d’informatique qui ont une
moyenne superieure à toutes celles des étudiant de GRH */

3.3. Les mots clés existes : Le mot-clé existes retourne une valeur booléenne qui est
vraie lorsque la sous-requête renvoie des lignes et fausse dans le cas contraire.

Syntaxe : where expression [not] exists (sous-requête)

Exemple: Select nom exists (select *

from stage S
where [Link] = [Link] )
from etudiant E
/* Afficher la liste des étudiants avec une colonne suppmémentaire
indiquant si l’étudiant a déjà effectué un stage */

4. Combinaisons de résultats de requêtes : La combinaison de résultats de

requêtes consiste à transformer deux ou plusieurs jeux de résultats en un seul jeu de
résultat. Les jeux de résultats combinés doivent tous avoir la même structure (même
nombre de colonnes et même type de données). La combinaison se réalise à l’aide du
mot-clé Union.

25
[Link]
INSFP Ain Oulmene
Module: SQL Server

Exemple:
select nom, fonction
from employe
union all
select nom, 'stagiaire'
from stagiaire
/* Afficher la liste employé ainsi que leurs fonctions et la liste
des stagiaires */

26
[Link]