Exercices corrigés Base de données : Positionnement technique SGBD SQL Server


Reviewed by:
Rating:
5
On January 20, 2012
Last modified:August 30, 2013

Summary:

Exercices corrigés de Base de données sous SGBD SQL Server

Exercice N° 1

Soit la table Pilote suivante dans la base VolAvion :

table Pilote suivante dans la base VolAvion sql server

Vous voulez réaliser une augmentation des salaires des pilotes selon les règles suivantes :

  • Si le pilote a un salaire < 10 000 alors il sera augmenté de 10%
  • Si le pilote a un salaire compris entre 10 000 et 20 000 il sera augmenté de 8 %
  • Si le pilote a un salaire supérieur à 20 000, son salaire reste inchangé

Choisissez la bonne requête à exécuter

UPDATE pilote
SET salaireBrut =
CASE
WHEN salaireBrut < 10000 THEN salaireBrut * 1.10
WHEN salaireBrut between 10000 and 20000 THEN salaireBrut * 1.08
ELSE salaireBrut
END
UPDATE pilote SET salaireBrut = salaireBrut * 1.10 WHERE salaireBrut < 10000
UPDATE pilote SET salaireBrut = salaireBrut * 1.08 WHERE salaireBrut between 10000 and 20000
UPDATE pilote
SET salaireBrut =
CASE
WHEN salaireBrut < 10000 THEN salaireBrut * 1.10
WHEN salaireBrut between 10000 and 20000 THEN salaireBrut * 1.08
END

Exercice N° 2

Soit le diagramme relationnel suivant :

diagramme relationnel

Et la procédure stockée suivante :

create procedure dbo.psCandidatSessionExamen_insert
( @IDCandidat                        int
, @IDSessionExamen      int
, @Presence                bit = NULL )
as
if exists (select 1 from dbo.CandidatSessionExamen
where IDCandidat = @IDCandidat
and IDSessionExamen = @IDSessionExamen )
return 101
insert dbo.CandidatSessionExamen
( IDCandidat
, IDSessionExamen
, Presence
) values (
@IDCandidat
, @IDSessionExamen
, @Presence
)
return 0

Parmi les propositions suivantes, quelles sont celles qui sont exactes ?

  1. La définition du paramètre @Presence indique que celui-ci est optionnel.
  2. Cette procédure permet d’éviter la levée d’une exception si l’application tente d’inscrire un candidat deux fois à une même session d’examen
  3. Cette procédure ne peut pas être exécutée : la déclaration @Presence bit = NULL n’est pas correcte
  4. Cette procédure ne peut pas être exécutée : on ne peut écrire select 1 from dbo.CandidatSessionExamen.

Exercice N° 3

L’utilisateur Hamdaoui a créé une table nommée Parametres dans la base de données NorthWind. Il  est donc le propriétaire de cette table. Hamdaoui n’est pas pour autant dbo. (DataBase Owner). Cela implique :

  1. Que seul l’utilisateur Hamdaoui pourra utiliser cette table dans une requête.
  2. Que pour utiliser cette table dans une requête, il sera nécessaire d’y faire référence sous cette forme : NorthWind.Hamdaoui.Parametres ou Hamdaoui.Parametres tel que :

         SELECT * FROM NorthWind.Hamdaoui.Parametres

  1. Que pour utiliser cette table dans une requête il sera nécessaire d’y faire référence sous cette forme : NorthWind..Parametres SELECT * FROM NorthWind..Parametres
  2. Que l’on pourra se contenter de nommer la table pour l’utiliser dans une requête tel que :

SELECT * FROM NorthWind.Parametres

Exercice N° 4

Parmi les requêtes suivantes, quelles sont celles qui, selon vous,  réalisent la liste des clients n’ayant jamais commandé.

  1. select * from clients where Codeclient not in (select codeclient from commandes)
  2. select clients.* from clients ,commandes where commandes.codeclient <> clients.codeclient
  3. select clients.* from clients left outer join commandes on clients.codeclient = commandes.codeclient where commandes.codeclient is null
  4. select clients.* from clients right outer join commandes on clients.codeclient = commandes.codeclient where commandes.codeclient is null

Exercice N° 5

Parmi les affirmations suivantes, une affirmation est fausse. Quelle est celle-ci ?.

  1. Un curseur permet de traiter la ligne courante indépendamment des autres lignes de la sélection.
  2. Un curseur permet de faire des mises à jour
  3. Un curseur est toujours en lecture seule
  4. Un curseur peut être en déplacement vers l’avant ou vers l’arrière.

Exercice N° 6

Le Trigger LigCde_Insert se déclenche sur l’insertion d’un nouvelle ligne de commande dans la table LigneCommande (Trigger LigCde_Insert ON LigneCommande FOR INSERT).

Trouvez les deux affirmations justes.

  1. Si l’exécution du trigger échoue, l’insertion de la nouvelle ligne de commande dans la table LigneCommande ne sera pas réalisée.
  2. La table Inserted manipulée par le trigger contient les valeurs à insérer dans la table LigneCommande.
  3. Si l’exécution du trigger échoue, l’insertion de la nouvelle ligne de commande dans la table LigneCommande sera quand même réalisée.
  4. Ce trigger ne se déclenche pas sur une mise à jour (UPDATE) réalisée sur la table LigneCommande.

Exercice N° 7

Soit la table [Order Details] suivante :

table sql sever Order Details

Quelles sont les règles de fonctionnement et de définition de la colonne TS de type TimeStamp ?

Deux réponses

  1. Le système y stocke une valeur de type Horodatage (Date Heure), date et heure du système, lors de l’insertion d’une nouvelle ligne dans la table.
  2. Le système stocke dans TS une valeur de type compteur, unique au niveau de la base de données, lors de toute insertion ou mise à jour d’une ligne de cette table.
  3. Il ne peut y avoir plus d’une colonne de type TimeStamp par table.
  4. Une colonne de type TimeStamp permet de stocker des valeurs de type Date Heure mais doit être entièrement gérée par le programmeur.

Exercice N°8

La commande SQL suivante :

RESTORE DATABASE TestCCMX FROM DISK="C:\MSSQL7\Sauvegarde\CCMX.BAK"
WITH MOVE 'CCMX' TO "C:\MSSQL7\TestCCMX.MDF",
MOVE 'CCMX_log' TO "C:\MSSQL7\TestCCMX.LDF"
  1. Permet de restaurer la base TestCCMX depuis un fichier de sauvegarde stocké sur disque à un autre emplacement physique que celui défini au niveau de la sauvegarde
  2. Renvoie une erreur car il n’est pas possible de restaurer une base de données à un autre emplacement physique que celui de la base lors de sa sauvegarde.
  3. Renvoie une erreur car il n’est pas possible de restaurer dans le même temps la base et le journal des transactions : il est nécessaire de faire une commande RESTORE DABASE et une commande RESTORE LOG.
  4. Permet de restaurer la base TestCCMX mais cette dernière ne sera pas opérationnelle car il manque la clause WITH RECOVERY

Exercice N° 9

Soit le diagramme relationnel suivant :

www.megaupload.com

Et le Trigger Suivant :

CREATE TRIGGER [SupStagiaire] ON [dbo].[Stagiaire]
FOR  DELETE
AS
delete suivreFormation where SuivreFormation.CodeStagiaire = (select codeStagiaire from deleted)

Sur la demande de suppression d’un stagiaire de la table stagiaire dont le code existe dans la table SuivreFormation :

  1. Ni la ligne dans la table stagiaire, ni les lignes le concernant dans la table SuivreFormation ne seront supprimées.
  2. La ligne dans la table stagiaire et les lignes le concernant dans la table SuivreFormation seront supprimées
  3. La syntaxe du Trigger n’est pas correcte : Il n’a donc pas pu être enregistré ainsi.
  4. La ligne dans la table stagiaire est supprimée mais celles le concernant dans la table SuivreFormation demeurent.

Exercice N° 10

La base de données de la gestion commerciale comporte 200 Go de données stockées dans un seul  fichier physique. Les tables des commandes et des lignes commandes sont particulièrement volumineuses. Cela pose des soucis pour les sauvegardes complètes de la base qui ne peuvent plus tenir sur une seule bande et des problèmes de temps d’exécution des requêtes, alors que les données d’historique ne sont manipulées qu’au travers d’interfaces spécifiquement conçues pour visualiser ces dernières.

Un collègue vous propose de stocker l’historique de ces données dans des tables historiques qui seront créées dans un fichier de données secondaire placé dans un nouveau groupe de fichiers. Qu’en pensez-vous ?

  1. Cela n’est pas possible car on ne peut avoir qu’un seul fichier de données par base.
  2. Cette solution ne permet pas de résoudre le problème des sauvegardes car on ne peut pas sauvegarder une base sans sauvegarder dans un même temps l’ensemble des fichiers physiques de celle-ci.
  3. Seule la création d’une nouvelle base de données dans laquelle seraient stockées l’historique permet de répondre à ce besoin.
  4. C’est une solution très intéressante que vous tenterez de mettre en œuvre.

Exercice N° 11

Vous souhaitez tester la procédure stockée suivante dans une fenêtre de l’analyseur de requête avant de l’intégrer dans votre application. Celle-ci permet de permuter le statut du compte d’un utilisateur pour lui donner accès ou non à l’application.

Create procedure psUtilisateur_ChangerStatut
@IdUtilisateur varchar(20),
@StatutProfilUtilisateur bit,
@Message varchar(255) output
AS
if Not Exists (Select IdUtilisateur from Utilisateur Where IdUtilisateur = @IdUtilisateur)
begin
set @Message = 'Changement de statut du profil utilisateur
impossible car l''utilisateur n''existe pas.'
return 1
end
else
begin
update Utilisateurs set StatutProfilUtilisateur = @StatutProfilUtilisateur
where IdUtilisateur = @IdUtilisateur
set @Message = 'Changement de statut du profil utilisateur
effectué avec succés.'
return 0
end

Choisissez le lot d’instructions à exécuter correct. Une seule réponse
A:

Declare @Message varchar(255)
Declare @RetValue int
Execute @RetValue = dbo.psUtilisateur_ChangerStatut 'Bost',1,@Message OUTPUT
PRINT @Message + ' Valeur Retour ' + CAST(@RetValue as Varchar(1))

B:

Declare @Message varchar(255)
Declare @RetValue int
Execute dbo.psUtilisateur_ChangerStatut 'Bost',1,@Message OUTPUT, @RetValue
PRINT @Message + ' Valeur Retour ' + CAST(@RetValue as Varchar(1))

C:

Declare @Message varchar(255)
Execute dbo.psUtilisateur_ChangerStatut 'Bost',1,@Message OUTPUT, RETURN()
PRINT @Message + ' Valeur Retour ' + RETURN()

D:

Declare @Message varchar(255)
Declare @RetValue int
Execute @RetValue = dbo.psUtilisateur_ChangerStatut 'Bost',1,@Message OUTPUT
PRINT @Message + ' Valeur Retour ' + @RetValue

Exercice N° 12

Vous souhaitez mettre en place une contrainte d’intégrité de type contrôle sur domaine (CONSTRAINT CHECK) qui prenne en compte la valeur de 2 colonnes.

Quelle est la limite de ce type de contrainte, dite déclarative :

  1. Les 2 colonnes doivent appartenir à la même table.
  2. Il n’y a pas de limites : les colonnes peuvent appartenir à des tables différentes.
  3. Les colonnes peuvent appartenir à des tables différentes mais ces dernières doivent être issues d’une même base de données.
  4. Ce type de contrainte ne peut porter que sur une seule colonne.

Exercice N° 13

Il a été créé un utilisateur guest dans la base de données NorthWind.

Quelle est l’incidence au niveau de la sécurité :

  1. Les utilisateurs autorisés à se connecter sur le serveur SQL bénéficieront systématiquement des droits accordés au rôle public même s’ils ne disposent pas de droits d’accès spécifiques à la base NorthWind.
  2. Les utilisateurs autorisés à se connecter sur le serveur SQL ne bénéficieront d’aucun droit s’ils ne disposent pas de droits d’accès spécifiques à la base NorthWind.
  3. Les utilisateurs autorisés à se connecter sur le serveur SQL bénéficieront systématiquement des droits de lecture et de modification sur l’ensemble des tables de NorthWind même s’ils ne disposent pas de droits d’accès spécifiques à la base NorthWind.
  4. Il n’est pas possible de créer d’utilisateur guest dans une base de données.

Exercice N° 14

Vous avez inscrit l’utilisateur Hamdaoui dans le rôle fixe de base de données db_datareader de la base GestionOfppt. Vous souhaitez qu’il ne puisse pas accéder à la table Salaries en lecture.

Vous exécutez la commande :

  1. revoke Salaries on Select to Hamdaoui
  2. revoke select on Salaries to Hamdaoui
  3. deny select on Salaries to Hamdaoui
  4. grant not select on Salaries to Hamdaoui

Exercice N° 15

Votre serveur SQL est configuré pour permettre des connexions en mode de sécurité intégrée Windows NT. Lorsque le mot de passe d’un utilisateur NT est modifié, quelles sont les modifications à apporter aux connexions SQL associées à ce compte NT ?

  1. Aucune
  2. Il faut recréer la connexion dans SQL Server
  3. Il faut exécuter la procédure sp_updatelogin
  4. Il faut exécuter la procédure sp_changepassword

Exercice N° 16

Le trigger suivant sur la table Pilote devra permettre de comparer les valeurs avant et après modification des lignes de la table des pilotes lors d’une mise à jour.

CREATE    trigger TR_Pilote on pilote for update
as
declare @pil# int,
@nom varchar(10),
@SalaireBrut datetime
declare CurseurPilotesAvant  cursor local
for select pil#,nom,SalaireBrut from deleted
declare CurseurPilotesAPres  cursor local
for select pil#,nom,SalaireBrut from inserted
A CurseurPilotesAvant
A CurseurPilotesApres
/* Parcours des curseurs */
B from CurseurPilotesAvant into @pil#,@nom,@SalaireBrut
while @@FETCH_Status = 0
BEGIN
print 'Image avant MAJ ' + convert(varchar(10),@pil#) + ' ' + @nom + ' ' + Convert(Varchar(20),@SalaireBrut)
B from CurseurPilotesApres into @pil#,@nom,@SalaireBrut
print 'Image après MAJ' +convert(varchar(10),@pil#) + ' ' + @nom + ‘’ + Convert(Varchar(20),@SalaireBrut)
B from CurseurPilotesAvant into @pil#,@nom,@SalaireBrut
END
C CurseurPilotesAvant
C CurseurPilotesApres
deallocate CurseurPilotesAvant
deallocate CurseurPiloteApres

Complétez les instructions en fournissant les valeurs de :

A =

B =

C =

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>