Embed Size (px)
Citation preview
TD sur les requêtes SQL - exos-corrigés SQL SGBDR TP avec
correctionSQL 0
Examen Corrigé SGBDR - Langage SQL Prérequis : Modèle conceptuel de données (entité-association), modèle relationnel, bases du langage SQL.
TD 1 - Requêtes SQL
Description du système d’informations
La direction des études des Mines de Nancy a décidé d’informatiser la gestion des emplois du temps. Chaque étudiant est caractérisé par son numéro d’étudiant, son nom, son prénom et son âge. Chaque cours est identifié de façon unique par un sigle (SI033, MD021, . . . ) et possède un intitulé (bases de données, mathématiques discrètes, . . . ) ainsi qu’un enseignant responsable. On connaît également le nombre de séances de chaque cours. Les enseignants sont caractérisés par un identifiant alphanumérique, leur nom et leur prénom. Enfin, chaque séance est identifiée par le cours ainsi que le numéro de la séance (séance 3 du cours SI033, séance 1 du cours de MD021, . . . ), le type d’intervention (CM, TD, TP), la date, l’heure de début et l’heure de fin auxquelles la séance a lieu ainsi que la salle et l’enseignant qui dispense la séance. Les étudiants s’inscrivent aux cours auxquels ils souhaitent assister.
Schéma relationnel retenu
Les clés primaires sont soulignées et les clés étrangères sont en italique.--------------------------------------------------------------------------------------etudiant ( numero , nom , prenom , age )enseignant ( id , nom , prenom )cours ( sigle , intitule , responsable, nombreSeances )seance ( cours , numero , type , date , salle , heureDebut , heureFin , enseignant )inscription ( etudiant , cours )--------------------------------------------------------------------------------------
Requêtes simples
i) Écrire les requêtes de création des tables « Etudiant » et « Séance ».ii) Inscrivez l’étudiant (’l0372’,’Léponge’,’Bob’,20) au cours (’LOG015’,’Logique’,’jh1908’).iii) Cherchez le nom et le prénom de tous les étudiants de moins de 20 ans.iv) Cherchez le nom et le prénom de l’enseignant responsable du cours de Statistiques.v) Cherchez le nom et le prénom de tous les étudiants inscrits au cours de Probabilités.vi) Déterminez le nombre d’enseignants intervenant dans le cours de Modélisation Stochatique.vii) Où et quand a lieu le premier cours d’Algèbre linéaire ?viii) Affichez un « emploi du temps » du cours de Logique.ix) Pour chaque enseignant, indiquez le nombre de cours dans lesquels il intervient (restreignez les réponses à l’ensemble des enseignants qui interviennent dans au moins deux cours).
Requêtes imbriquées
i) Ajoutez un cours magistral de Logique le 14 décembre avec Jacques Herbrand en salle S250 de 14h à 18h.
ii) Listez les étudiants inscrits à aucun cours.iii) Combien d’étudiants (différents) ont assistés à au moins une séance animée par Leonhard Euler ?
Syntaxe SQLSélection
Exemple :
SELECT SUM(p.gain)FROM Participe p, Jockey jWHERE p.Numero_jockey = j.Numero_jockeyAND j.nom like ’Jean-Claude Dusse’;
Création de tables
CREATE TABLE nom_de_la_table (nom_de_l’attribut type [ liste_de_contraintes_d’attribut ]nom_de_l’attribut type [ liste_de_contraintes_d’attribut ]...liste_de_contraintes_de_table);
Exemple :
CREATE TABLE cours (sigle VARCHAR(20) NOT NULL,intitule VARCHAR(128) NOT NULL,responsable VARCHAR(50) NOT NULL,nombreSeances INT NOT NULL DEFAULT ’0’,PRIMARY KEY (sigle),FOREIGN KEY (responsable) REFERENCES enseignant(id));
Suppression de tableDROP TABLE nom_de_la_table ;
InsertionINSERT INTO nom_de_la_table ( attribut_1, attribut_2, : : : )VALUES( valeur_1, valeur_2, : : : ) ;
Requêtes imbriquées / sous-requêtes
Une sous-requête est une commande SELECT dans une autre commande. Par exemple :
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
On dit que la sous-requête est imbriquée dans la requête externe. Il est possible d’imbriquer des requêtes dans des sous-requêtes. Une sous-requête doit toujours être entre parenthèses.
Voici un exemple de commande qui montre les principaux avantages des sous-requêtes et de leur syntaxe :
SELECT r1FROM t1WHERE s11 = (SELECT COUNT(*) FROM t2WHERE NOT EXISTS (SELECT * FROM t3WHERE r3 =(SELECT 50,11*s1 FROM t4 WHERE r5 in (SELECT * FROM t5) AS t5)));
EXISTS teste simplement si la requête interne retourne une ligne. NOT EXISTS teste si la requête interne ne retourne aucun résultat.
--------------------------------------------------------------------------------------------
CORRECTION--------------------------------------------------------------------------------------------
Requêtes simplesi) Écrire les requêtes de création des tables « Etudiant » et « Séance ».
Réponse :
CREATE TABLE Etudiant (
numero VARCHAR(20) PRIMARY KEY,nom VARCHAR(50) NOT NULL,prenom VARCHAR(50) NOT NULL,age INT NOT NULL CHECK(age > 0));CREATE TABLE Séance (cours VARCHAR(20) NOT NULL,numero VARCHAR(50) NOT NULL,type VARCHAR(2) NOT NULL CHECK(type in "CM", "TD", "TP"),date DATE NOT NULL,salle VARCHAR(10) NOT NULL,
heureDebut TIME NOT NULL,heureFin TIME NOT NULL CHECK(heureFin > heureDebut),enseignant VARCHAR(20) NOT NULL,FOREIGN KEY (cours) REFERENCES Cours(sigle),FOREIGN KEY (enseignant) REFERENCES Enseignant(id),PRIMARY KEY (cours,numero));
ii) Inscrivez l’étudiant (’l0372’,’Léponge’,’Bob’,20) au cours (’LOG015’,’Logique’,’jh1908’).
Réponse :
INSERT INTO Inscription VALUES ("l0372","LOG015");
iii) Cherchez le nom et le prénom de tous les étudiants de moins de 20 ans.
Réponse :
SELECT nom, prenom FROM Etudiant WHERE age < 20;
iv) Cherchez le nom et le prénom de l’enseignant responsable du cours de Statistiques.
Réponse :
SELECT nom, prenomFROM Enseignant, CoursWHERE responsable = idAND intitule LIKE "Statistiques";
v) Cherchez le nom et le prénom de tous les étudiants inscrits au cours de Probabilités.
Réponse :
SELECT e.nom, e.prenomFROM etudiant e, inscription i, cours cWHERE e.numero = i.etudiantAND i.cours = c.sigleAND c.intitule like "Probabilites";
vi) Déterminez le nombre d’enseignants intervenant dans le cours de Modélisation Stochatique.
Réponse :
SELECT count(DISTINCT enseignant)FROM Seance, CoursWHERE sigle = coursAND intitule LIKE "%Modelisation%";
vii) Où et quand a lieu le premier cours d’Algèbre linéaire ?
Réponse :
SELECT date, salle, heureDebut, heureFinFROM Seance, CoursWHERE sigle = coursAND numero = 1AND intitule LIKE "Algebre lineaire";
viii) Affichez un « emploi du temps » du cours de Logique.
Réponse :
SELECT numero, date, salle, heureDebut, heureFin, e.nom, e.prenomFROM Seance, Cours, Enseignant eWHERE sigle = coursAND enseignant = idAND intitule LIKE "Logique"ORDER BY date,heureDebut;
ix) Pour chaque enseignant, indiquez le nombre de cours dans lesquels il intervient (restreignez les réponses à l’ensemble des enseignants qui interviennent dans au moins deux cours).
Réponse :
SELECT e.nom, e.prenom, count(distinct cours)FROM Seance, Cours, Enseignant eWHERE sigle = coursAND enseignant = e.idGROUP BY e.idHAVING count(distinct cours)>1
Requêtes imbriquées
i) Ajoutez un cours magistral de Logique le 14 décembre avec Jacques Herbrand en salle S250 de 14h à 18h.
Réponse :
INSERT INTO ‘Seance‘ VALUES ((SELECT sigle FROM ‘Cours‘WHERE intitule LIKE ’Logique’),(SELECT nombreSeances+1 FROM ‘Cours‘WHERE intitule LIKE "Logique"),’CM’, ’2008-12-14’, ’S250’, ’14:00’, ’18:00’,(SELECT id FROM ‘Enseignant‘
WHERE nom like "Herbrand"AND prenom = "Jacques"));UPDATE ‘cours‘ SET nombreSeances = nombreSeances+1 WHERE intitule LIKE ’Logique’;
ii) Listez les étudiants inscrits à aucun cours.
Réponse :
SELECT e.nom, e.prenomFROM Etudiant eWHERE NOT EXISTS(SELECT * FROM Inscription iWHERE i.etudiant = e.numero);
iii) Combien d’étudiants (différents) ont assistés à au moins une séance animée par Leonhard Euler ?
Réponse :
SELECT COUNT(DISTINCT e.numero)FROM Etudiant e, Inscription iWHERE i.etudiant = e.numeroAND EXISTS(SELECT s.coursFROM Enseignant e, Seance sWHERE e.id = s.enseignantAND s.cours = i.coursAND e.nom LIKE "Euler"AND e.prenom LIKE "Leonhard");
Les procédures stockées SQL-Server - Oracle - exercices-
corrigésBases de Données, Oracle, SQL 0
1. Définition et avantages des procédures stockées
Une procédure stockée est une collection précompilée d'instructions Transact-SQL stockée sous un nom et traitée comme une unité. Les procédures stockées de SQL Server permettent de gérer celui-ci et d'afficher les informations sur les bases de données et les utilisateurs. Les procédures stockées fournies avec SQL Server sont appelées procédures stockées du système.
Elles renvoient les données de quatre façons différentes : des paramètres de sortie, qui renvoient soit des données (entiers ou caractères) ou une variable de curseur, les curseurs étant des ensembles de résultats pouvant être extraits ligne par ligne ;
des codes de retour, qui sont toujours un entier ;
un ensemble de résultats pour chaque instruction SELECT contenue dans la procédure stockée ou toute autre procédure stockée appelée par cette dernière ;
un curseur global qui peut être référencé en dehors de la procédure stockée.
Les curseurs :
Les opérations réalisées dans une base de données relationnelle s'exécutent sur un ensemble complet de lignes. L'ensemble de lignes renvoyé par une instruction SELECT contient toutes les lignes satisfaisant aux conditions de la clause WHERE de l'instruction. Cet ensemble complet de lignes renvoyées par l'instruction est appelé jeu de résultats. Les applications, en particulier les applications interactives en ligne, peuvent ne pas toujours fonctionner efficacement si le jeu de résultats est traité comme une unité. Ces applications ont besoin d'un mécanisme leur permettant de travailler avec une seule ligne ou un petit bloc de lignes à la fois. Les curseurs sont une extension des jeux de résultats et contiennent ce mécanisme.
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
Avantages des procédures stockées:
Les procédures stockées contribuent à mettre en œuvre une logique cohérente dans les applications. Les instructions SQL et la logique nécessaires à l'exécution d'une tâche fréquente peuvent être créées, codées et testées une seule fois dans une procédure stockée. Il suffit ensuite à chaque application devant effectuer la tâche d'exécuter la procédure stockée. Le codage de la logique de gestion en une seule procédure offre aussi un point de contrôle unique permettant de vérifier que les règles d'entreprise sont bien respectées.
Les procédures stockées peuvent également améliorer les performances. De nombreuses tâches sont mises en œuvre sous forme de séquences d'instructions SQL. La logique conditionnelle appliquée aux résultats des premières instructions SQL détermine les instructions suivantes à exécuter. Si ces instructions SQL et la logique conditionnelle sont écrites dans une procédure stockée, elles deviennent partie intégrante d'un plan d'exécution unique sur le serveur. Les résultats n'ont pas besoin d'être renvoyés au client pour que la logique conditionnelle soit appliquée, car tout le travail est réalisé sur le serveur.
Les procédures stockées évitent aussi aux utilisateurs d'avoir à connaître les détails des tables de la base de données. Si un ensemble de procédures stockées prend en charge toutes les fonctions de gestion nécessaires aux utilisateurs, ceux-ci n'ont pas besoin d'accéder directement aux tables ; il leur suffit d'exécuter les procédures stockées qui modélisent les processus avec lesquels ils ont l'habitude de travailler.
Les procédures stockées du système SQL Server évitant aux utilisateurs d'accéder aux tables système en sont un exemple. SQL Server comprend un ensemble de procédures stockées système dont les noms commencent en général par sp_. Ces procédures prennent en charge toutes les tâches administratives nécessaires à l'exécution d'un système SQL Server. Vous pouvez administrer un système SQL Server à l'aide des instructions Transact-SQL associées à l'administration (telles que CREATE TABLE) ou des procédures stockées du système, sans jamais avoir à mettre à jour directement les tables système.
1. Les différents types de procédures stockées
1.1. Procédure stockée système
Ensemble de procédures stockées fournies par SQL Server pour la réalisation d'opérations telles que l'extraction d'informations du catalogue système ou l'exécution de tâches d'administration.
Nombre d'activités administratives dans Microsoft® SQL Server™ 2000 s'exécutent à l'aide d'un type spécial de procédure connu sous le nom de procédure stockée système. Les procédures stockées système sont créées et enregistrées dans la base de données master et ont le préfixe sp_. Les procédures stockées du système
peuvent s'exécuter depuis n'importe quelle base de données, sans avoir à qualifier complètement le nom de la procédure stockée, en utilisant le nom de base de données master.
Il est fortement recommandé de ne pas créer de procédures stockées avec le préfixe sp_. SQL Server recherche toujours une procédure stockée en commençant par sp_ dans l'ordre suivant :
1. elle existe dans la base de données master ;2. ensuite, en fonction des éventuels identificateurs fournis (nom de base de données ou propriétaire) ;
3. enfin, avec dbo comme propriétaire si aucun propriétaire n'est spécifié.Par conséquent, bien qu'il puisse exister dans la base de données en cours une procédure stockée créée par l'utilisateur ayant le préfixe sp_, la base de données master est toujours analysée la première, même si la procédure stockée est qualifiée avec le nom de la base de données.
Informations sur les procédures stockéesPour afficher le texte utilisé pour créer la procédure, exécutez sp_helptext dans la base de données dans laquelle la procédure se trouve en passant le nom de la procédure en paramètre.
Pour obtenir une liste des objets référencés par une procédure, utilisez sp_depends. Pour renommer une procédure, utilisez sp_rename
Important Si une procédure stockée créée par un utilisateur porte le même nom qu'une procédure stockée système, celle de l'utilisateur ne s'exécutera jamais.
1.1. Procédures stockées temporaires
Les procédures stockées temporaires privées et globales, comme les tables temporaires, peuvent être créées en ajoutant les préfixes # et # # à leur nom. # désigne une procédure stockée temporaire locale, et # #, une procédure stockée temporaire globale. Ces procédures n'existent plus après l'arrêt de SQL Server.
Les procédures stockées temporaires locales sont disponibles au sein d’une seule session d’utilisateur. Tandis que les procédures stockées temporaires globales sur l’ensemble des sessions d’utilisateur.
Les procédures stockées temporaires sont utiles lorsque vous vous connectez à des versions antérieures de SQL Server qui ne prennent pas en charge la réutilisation des plans d'exécution des instructions ou lots d'instructions Transact-SQL.
Pour créer et exécuter les procédures stockées temporaires :
Create procedure # #procedure_nameAssql_statementExec sp_executesql # #procedure_name
1.2. Procédures stockées distantes
Les procédures stockées distantes sont une ancienne fonctionnalité de Microsoft® SQL Server™ 2000. Leur fonctionnalité dans Transact-SQL est limitée à l'exécution d'une procédure stockée sur une installation SQL Server distante. Les requêtes distribuées introduites dans la version 7.0 de SQL Server prennent en charge cette possibilité ainsi que l'accès à des tables dans des sources de données OLE DB hétérogènes directement à partir d'instructions Transact-SQL locales. Au lieu d'utiliser un appel de procédure stockée distante sur SQL Server 2000, utilisez des requêtes distribuées et une instruction EXECUTE pour exécuter une procédure stockée sur un serveur distant.
Une instance SQL Server 2000 peut envoyer et recevoir des appels de procédures stockées distantes à d'autres instances de SQL Server 2000 et SQL Server version 7.0. Une instance SQL Server 2000 peut également
envoyer des appels de procédures stockées distantes vers des instances SQL Server 6.0 ou 6.5 et en recevoir. Un serveur exécutant SQL Server 2000 peut recevoir des appels de procédures stockées distantes d'une instance SQL Server 4.21a, mais l'instance SQL Server 2000 ne peut pas faire des appels de procédures stockées distantes à l'instance SQL Server 4.21a. L'instance SQL Server 4.21a ne peut pas reconnaître la version du flux de données tabulaires (TDS, Tabular Data Stream) utilisée par SQL Server 2000.
1.1. Procédures stockées étendues
Les procédures stockées étendues vous permettent de créer vos propres routines externes dans un langage de programmation comme le langage C. Les procédures stockées étendues apparaissent aux utilisateurs comme des procédures stockées normales et s'exécutent de la même façon. Des paramètres peuvent être passés à une procédure stockée étendue pour renvoyer des résultats et un état. Les procédures stockées étendues permettent d'étendre les fonctionnalités de Microsoft® SQL Server™ 2000.
Les procédures stockées étendues sont des bibliothèques de liaison dynamique (DLL, dynamic-link library) que SQL Server peut charger et exécuter dynamiquement. Elles s'exécutent directement dans l'espace d'adresse de SQL Server et sont programmées au moyen de l'API Open Data Services de SQL Server.
Une fois que la procédure stockée étendue est écrite, les membres du rôle de serveur fixe sysadminpeuvent l'inscrire dans SQL Server, puis donner l'autorisation de l'exécuter à d'autres utilisateurs. Les procédures stockées étendues ne peuvent être ajoutées qu'à la base de données master.
Les procédures stockées étendues sont généralement identifiées par le préfixe xp_
2. Codification des procédures stockée
3.1. Création des procédures stockées
Vous pouvez créer une procédure stockée en utilisant l'instruction Transact-SQL CREATE PROCEDURE. Lisez les informations ci-dessous avant de créer une procédure stockée. L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions SQL dans un même lot d'instructions.
L'autorisation de créer des procédures stockées revient par défaut au propriétaire de la base de données, qui peut la transmettre à d'autres utilisateurs.
Les procédures stockées sont des objets de base de données et leur nom doit respecter les règles gouvernant les identificateurs.
Vous ne pouvez créer une procédure stockée que dans la base de données en cours.
Pour créer une procédure stockée, vous devez préciser :
es paramètres d'entrée et de sortie de la procédure ou du lot appelant ;
les instructions de programmation qui exécutent les opérations dans la base de données, y compris l'appel à d'autres procédures ;
la valeur d'état renvoyée à la procédure ou au lot appelant pour indiquer la réussite ou l'échec et, dans ce cas, la raison de l'échec.
SyntaxeCREATE PROC [ EDURE ] procedure_name [ ; number ] { @parameter data_type } AS sql_statement [ ...n ]
Argumentsprocedure_name
Nom de la nouvelle procédure stockée. Les noms des procédures doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son propriétaire.
;number
Nombre entier facultatif utilisé pour regrouper les procédures de même nom afin qu'elles puissent être supprimées ensemble à l'aide d'une seule instruction DROP PROCEDURE. Par exemple, les procédures utilisées avec une application appelée order peuvent être nommées orderproc;1,orderproc;2, etc. L'instruction DROP PROCEDURE orderproc abandonne le groupe tout entier.
@parameter
Un paramètre de la procédure. Vous pouvez déclarer un ou plusieurs paramètres dans une instruction CREATE PROCEDURE. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'exécution de la procédure (sauf si vous définissez une valeur par défaut pour le paramètre). Une procédure stockée peut comprendre au maximum 2100 paramètres.
Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Ce nom doit respecter les règles gouvernant les identificateurs. Un paramètre est local à une procédure, vous pouvez donc utiliser le même nom dans d'autres procédures.
data_type
Type de données du paramètre. Tous les types de données y compris les types text, ntext et image, peuvent être utilisés comme paramètre dans une procédure stockée.
AS
Spécifie les actions que peut entreprendre la procédure.
sql_statement
Tout numéro et type d'instructions Transact-SQL à inclure dans la procédure. Certaines limitations s'appliquent.
n
Espace réservé qui indique que plusieurs instructions Transact-SQL peuvent être incluses dans cette procédure.
3.2. Modification des procédures stockées
Pour modifier une procédure stockée :
ALTER PROC [ EDURE ] procedure_name [ ; number ] { @parameter data_type } AS sql_statement [ ...n ]
3.3. Suppression des procédures stockées
Pour supprimer une procédure stockée :
Drop proc[edure] procedure_name
3.4. Appel d’une procédure stockée
Utilisez l'instruction EXECUTE de Transact-SQL pour exécuter une procédure stockée. L'utilisation du mot clé EXECUTE n'est pas nécessaire à cette exécution si la procédure est la première instruction du lot.
Exec [ute] procedure_name @parameter 1= value1,@parameter2 = value2,@parameter3 = value3…
Des valeurs de paramètres peuvent être fournies si une procédure stockée a été écrite pour les accepter.
Remarque Si vous entrez des paramètres sous la forme @Parameter = value, leur ordre n'a pas d'importance. Vous pouvez aussi omettre les paramètres pour lesquels une valeur par défaut a été définie. Si vous spécifiez un paramètre donné sous la forme @Parameter = value, vous devez tous les spécifier de cette façon. Sinon, ils doivent apparaître dans l'ordre indiqué par l'instruction CREATE PROCEDURE.
Lorsque le serveur exécute une procédure stockée, il refuse tous les paramètres qui n'étaient pas insérés dans la liste des paramètres au moment de la création de la procédure. Tout paramètre qui est passé par référence (en fournissant explicitement son nom) ne sera pas accepté si son
nom ne concorde pas.Bien que vous puissiez omettre des paramètres ayant des valeurs par défaut, seule la liste des paramètres peut être tronquée. Par exemple, si une procédure stockée a cinq paramètres, vous pouvez omettre les deux derniers paramètres, mais pas omettre le quatrième et inclure le cinquième, à moins d'utiliser le format @parameter = value.La valeur par défaut d'un paramètre, si elle a été définie dans la procédure stockée, est utilisée dans les cas suivants : si aucune valeur n'est spécifiée pour le paramètre au moment de l'exécution de la procédure ;
si le mot clé DEFAULT est spécifié comme valeur du paramètre.
Pour exécuter une procédure stockée qui est groupée avec d'autres procédures du même nom, indiquez le numéro d'identification de la procédure à l'intérieur du groupe. Par exemple, pour exécuter la seconde procédure stockée du groupe my_proc, spécifiez :EXECUTE my_proc;2
Pour faire appel à une procédure stockée on utilise le mot call
{call procedure_name(@parameter….)}{call "DataBase"."Owner"."Procedure_name" }
3.5. Définition des paramètres, traitement des erreurs les procédures ne seraient pas intéressantes si on ne pouvait pas spécifier de paramètres. Heureusement, il est très facile d’écrire une procédure stockée paramétrable.Pour déclarer un paramètre, il suffit donc de le spécifier dans l’entête de la procédure en lui indiquant :
son nom : @Parameter (n’oubliez pas le @), qui sera utilisable comme une variable dans la procédure stockée. Un type de donnée, choisi parmi les types SQL ou les types utilisateurs. Une valeur par défaut optionnelle. Une direction, en mettant OUTPUT derrière le nom d’un paramètre.
3.4.1 Paramètres optionnels
Vous pouvez spécifier pour chaque paramètre une valeur par défaut.
3.4.2 Direction des paramètres
Vous pouvez également définir des paramètres de sortie. Ces paramètres sont modifiés dans la procédure stockée puis retournés à l’appelant.
3.6. Utilisation de NOCOUNT, EXISTS
NOCOUNT : Empêche le message indiquant le nombre de lignes affectées par une instruction Transact-SQL d'être renvoyé en tant que résultat.
SyntaxeSET NOCOUNT {ON | OFF}
NotesSi SET NOCOUNT est activée (ON), le chiffre indiquant le nombre de lignes affectées par une instruction Transact-SQL n'est pas renvoyé. Si la valeur de SET NOCOUNT est définie sur OFF, ce chiffre est renvoyé.EXISTS :
Précise une sous-requête pour déterminer l'existence ou non de lignes. **************
Dans les exemples et les exercices qui suivent, on travaille sur la base de données «GestionCommande »:
1. Exemples
----Création d'une procédure stockée simpleCreate procedure PS;1asselect * from Clientwhere IdClient>1650Exec PS;1---------------------------------
----Création d'une procédure avec un seul paramètrecreate proc myprocedure@d datetimeasselect * from Facture where DateFacture=@dexec myprocedure '08/09/2002'
create proc PS;2(@Id int)asselect IdClient,Nom,Prénom from Client where IdClient=@IdExec PS;2 @Id=1668---------------------------------
----Création d'une procédure avec deux ou plusieurs paramètrescreate proc PS;3(@n varchar(20),@p varchar(20))asselect * from Client where Nom=@n and Prénom=@p
Exec PS;3 'Alaoui','Ahmed'---------------------------------
----Procédure qui a un paramètre optionnelCreate proc PS;4(@Id int =null)AsIf @Id is null Begin Select * from Client EndElse Begin Select * from Client where IdClient=@Id EndGoexec PS;4 @Id=1925exec PS;4 1668exec PS;4exec PS;4 @Id=default---------------------------------
----On crée une procédure stockée porte le même nom d'une procédure stockée système "sp_depends"create procedure sp_depends@n intasselect * from Commande where NumCommande=@nexec sp_depends 'Client'---------------------------------
----Procédure Stockée temporaire globalecreate procedure ##ProcGlobaleasselect * from Client where IdClient=1668exec sp_executesql ##ProcGlobale----------------------------------
----Procédure Stockée temporaire localecreate procedure #ProcLocaleasselect distinct * from Commandeexec sp_executesql #ProcLocale---------------------------------
----Appel d'une procédure stockée{call "GestionCommande"."dbo"."PS;1"}{call PS;1}{call PS;3 ('alaoui','ahmed')}{call myprocedure ('08/09/2002')}---------------------------------
-------Procédure avec plusieurs commandes SQLcreate proc procédureMasbeginselect * from Clientendbegininsert into Client values(3334,'Tourabi','Amina','Casa')
end-----appel de la procédure{call procédureM}--------------------------------------
------Modification d'une Procédure stockéealter proc procédureMasselect * from Commande--------------------------------------
-------Suppression d'une Procédure stockéedrop proc procédureM--------------------------------------
--------Gestion des erreurs-----------Create procedure pp@aa intasif @aa>0select * from client where IdClient=@aaelseprint'Attention le IdClient que vous avez entrer n''est pas correct!'Exec pp -55--------------------------------------
1. Exercices
Exercice 1 :
Créer une procédure stockée qui affiche les clients dont la quantité commande est supérieur à 75 et les factures sont réalisées entre 2003 et 2004
Corrigé :
create proc E;1asselect * from Client where exists(select * from Commandewhere IdClient=Client.IdClient and Quntité>75 and exists(select * from Facturewhere datefacture between '01/01/2003'and '31/12/2004'and NumCommande=Commande.NumCommande ))exec E;1
Exercice 2 :
Créer une procédure stockée qui retourne la somme des prix à payer par tous les clients en utilisant un paramètre de sortie.
Corrigé :
create proc E;2@somme money outputas
select @somme = sum(PrixTotal) from Commandeif @somme < 1000 print 'La société va fermer ses portes.'else ---SELECT 'La société a réalisé ' + convert (varchar(20), @somme)+' F' as PrixTotalselect @somme as SommeRéaliségodeclare @P moneyexec E;2 @P output
Exercice3 :
Créer une procédure qui affiche les noms et les prénoms des clients dont le nom commence par ‘Al’ en utilisant un cursor qui permet d’extraire les lignes ligne par ligne
Corrigé :
create proc ProcCursorasDECLARE Cur CURSOR FORSELECT Nom, Prénom FROM Client where Nom like 'Al%'OPEN CurFETCH NEXT FROM CurWHILE @@FETCH_STATUS = 0BEGINinsert into client values(2056,'toto','titi','Mars') FETCH NEXT FROM CurENDCLOSE Curdrop proc ProcCursorexec ProcCursor
Exercice 4 :
Créer une procédure qui exécute la procédure précédente
Corrigé :
create proc ProcAppelante@P1 varchar(10)asexec @P1---------exec ProcAppelante 'ProcCursor'
Exercice 5 :
Créer la procédure stockée qui compte le nombre de commandes d’un client et affiche le résultat
Corrigé :
create proc prc(@id int,@var int output)as select @var = count(NumCommande)
from Commande where IdClient=@idgroup by IdClient
drop proc prcdeclare @P intExec prc 1578,@P outputSelect @p as NbreCommandes
TD SQL -exo SQL TP SGBD SQLSQL 0
Introduction aux bases de données Le langage SQL
Exercice 1 – Clinique de médecine
Soit la base de données suivante :
------------------------------------------------------------------------------------------------------
PATIENT (NoPatient, NoAssSociale, Nom, Prenom)
MEDECIN (NoMedecin, Nom, Prenom)
DIAGNOSTIC (NoDiagnostic, description)
TRAITEMENT (NoTraitement, description)
ENTREE_DOSSIER (NoDossier, DateVisite, #NoPatient, #NoMedecin, #NoTraitement, #NoDiagnostic)------------------------------------------------------------------------------------------------------
1) Donnez le code SQL pour créer la base de données
2) Vous réalisez que la taille de l'attribut "description" de la table DIAGNOSTIC n'est pas adéquate. Donnez le code SQL pour la modifier pour une chaîne de longueur variable de 255 caractères maximum.
3) Donnez le code SQL pour ajouter les attributs "NoTelephone" et "DateNaissance" dans la table PATIENT.
4) Donnez le code SQL pour entrer les données suivantes dans la base de données
Table PATIENT
Table MEDECIN
Table DIAGNOSTIC
Table TRAITEMENT
Table ENTREE_DOSSIER
5) Vous avez entré le mauvais traitement dans l'entrée de dossier no. 3. Modifiez l'enregistrement pour donner le traitement no. 2 au lieu du no. 1.
6) Effectuez les requêtes SQL simples suivantes :
● Afficher toutes les informations de tous les patients;
● Afficher le nom et le prénom de tous les patients;
● Afficher le nom et le prénom des patients dont le nom de famille est 'Delisle';
● Afficher le nom et le prénom des patients nés après 1976;
● Afficher les noms de famille différents des patients;
● Afficher les patients en ordre croissant de date de naissance;
● Afficher les entrées de dossier où la patient traité est de no. 111111 et le médecin traitant est de no. 67899
7) Effectuez les jointures suivantes :
● Afficher toutes les entrées de dossier et les informations de leurs patients respectifs;
● Afficher les entrées de dossier de Pierre Delisle;
● Afficher la description des traitements dont a bénéficié Pierre Delisle;
● Afficher, du plus jeune au plus vieux, le nom et le prénom des patients traités par René Lajoie le 26 avril 2008.
Exercice 2 – Bibliothèque
Soit la base de données suivante :
--------------------------------------------------------------------------------------------
SPECIALITE (NoSpecialite, Description)SECTION (NoSection, Emplacement, Description)LIVRE (CodeISBN, Titre, #NoSpecialité, #NoSection)FOURNISSEUR (NoFournisseur, Nom)EXEMPLAIRE (NoExemplaire, #CodeISBN, #NoFournisseur)ABONNE (NoAbonne, Nom, Prenom)CARTE (NoCarte, DateDebut, DateFin, #NoAbonne)EMPRUNT (NoExemplaire, NoCarte, DateLocation, DateRetour)--------------------------------------------------------------------------------------------
1) Effectuez les requêtes SQL suivantes :
● Afficher la liste des livres classés dans les sections 1 et 4;● Ajouter un attribut adresse à la table abonné;
● Ajouter le fournisseur 'Livres du Québec inc.' à la base de données';
● Afficher le nom et le prénom des abonnés qui se sont abonnés ou ont renouvelé leur carte en 2012;
● Afficher le code et le titre des livres qui ont été empruntés le 28 avril 2012, triés par ordre alphabétique de titre;
● Afficher le nom et le prénom des abonnés qui ont déjà emprunté le livre intitulé 'Nos amis les français';
● Prolonger tous les abonnements échus le 25 avril 2012 au 25 mai 2012;
● Afficher le titre des livres de science-fiction empruntés durant le mois d'avril 2012.
Exercices en SQL - TP SQLSQL 0
Soit la base de données constituée des relations (autoexplicatives) suivantes:
aime (personne, boisson)frequente (personne, cafe)sert (cafe, boisson)
aime+----------+----------+| personne | boisson |+----------+----------+
| ali | coca || ali | 7up || ali | oulmes || said | coca || said | orangina || aziz | oulmes || aziz | 7up || amine | coca || amine | oulmes |+----------+----------+
frequente+----------+----------+| personne | cafe |+----------+----------+| ali | rif || ali | atlas || amine | commerce || aziz | rif || aziz | commerce || aziz | atlas || said | atlas |+----------+----------+
sert+----------+----------+| cafe | boisson |+----------+----------+| commerce | coca || commerce | 7up || atlas | 7up || rif | coca || commerce | oulmes || commerce | orangina || atlas | oulmes |
+----------+----------+
Ecrire en SQL les requêtes suivantes
1. Quelles sont les personnes, les cafés qu'ils frequentent, et les
boissons servies par ces cafés.
2. Quelles sont les personnes qui fréquentent des cafés qui servent
des boissons qu'ils aiment.
3. Quels sont les café servant toutes les boissons.
4. Quelles sont les personnes qui ne fréquentent que les cafés qui
servent des boissons qu'ils aiment.
5. Quelles sont les personnes qui ne fréquentent que les cafés qui
servent des boissons qu'ils n'aiment pas.
Travaux Pratiques SQL - exercices SQL - TP SQLSQL 0
Exercice SQL SQL SERVER ORACLE
Soit la base de donnée SPDB, contenant les relations Fournisseurs (S), Produits (P) et Fournit (SP).
S+------+-------+--------+--------+
| snum | sname | status | city |
+------+-------+--------+--------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
+------+-------+--------+--------+
5 rows in set (0.00 sec)
P
+------+-------+-------+--------+--------+
| pnum | pname | color | weigth | city |
+------+-------+-------+--------+--------+
| P1 | Nut | Red | 12.00 | London |
| P2 | Bolt | Green | 17.00 | Paris |
| P3 | Screw | Blue | 17.00 | Rome |
| P4 | Screw | Red | 14.00 | London |
| P5 | Cam | Blue | 12.00 | Paris |
| P6 | Cog | Red | 19.00 | London |
+------+-------+-------+--------+--------+
6 rows in set (0.00 sec)
SP+------+------+------+
| snum | pnum | qty |
+------+------+------+
| S1 | P1 | 300 |
| S1 | P2 | 200 |
| S1 | P3 | 400 |
| S1 | P4 | 200 |
| S1 | P5 | 100 |
| S1 | P6 | 100 |
| S2 | P1 | 300 |
| S2 | P2 | 400 |
| S3 | P2 | 200 |
| S4 | P2 | 200 |
| S4 | P4 | 300 |
| S4 | P5 | 400 |
+------+------+------+
12 rows in set (0.00 sec)
I) Créer cette base de données et choisir les types de données appropriés.
II) Ecrire en SQL les requêtes suivantes:
1. Tous les détails sur tous les fournisseurs
2. Le nom et le status des fournisseurs de 'Paris'
3. Idem par ordre decroissant de status
4. les paires numéro de fournisseur et de produit situés dans la même ville
5. le numéro des fournisseurs et le nom des produits qu'ils fournissent
6. le nom des fournisseurs et le nom des produits qu'ils fournissent
7. les numéros de fournisseurs et numéros de produits fournis et situé dans la même ville.
8. Les paires numéros de fournisseurs ayant même status
9. Il y a combiens d'expéditions du produit de numéro 'P2'?
10. Ecrire de deux façon différentes la requête: "Nom des fournisseurs du produit de numéro 'P2'
11."Nom des fournisseurs qui fournissent au moins un produit de couleur rouge ('Red')
12. Nom des fournisseurs qui ne fournissent pas le produit 'P2'.
Travaux Dirigés 7Introduction aux bases de données
Le langage SQLExercice 1 – Clinique de médecine:Soit la base de données suivante :
PATIENT (NoPatient, NoAssSociale, Nom, Prenom)
MEDECIN (NoMedecin, Nom, Prenom)
DIAGNOSTIC (NoDiagnostic, description)
TRAITEMENT (NoTraitement, description)
ENTREE_DOSSIER (NoDossier, DateVisite, #NoPatient, #NoMedecin, #NoTraitement, #NoDiagnostic)
1) Donnez le code SQL pour créer la base de données
2) Vous réalisez que la taille de l'attribut "description" de la table DIAGNOSTIC n'est pas adéquate. Donnez le code SQL pour la modifier pour une chaîne de longueur variable de 255 caractères maximum.
3) Donnez le code SQL pour ajouter les attributs "NoTelephone" et "DateNaissance" dans la table PATIENT.
4) Donnez le code SQL pour entrer les données suivantes dans la base de données
5) Vous avez entré le mauvais traitement dans l'entrée de dossier no. 3. Modifiez l'enregistrement pour donner le traitement no. 2 au lieu du no. 1.
6) Effectuez les requêtes SQL simples suivantes :
● Afficher toutes les informations de tous les patients;
● Afficher le nom et le prénom de tous les patients;
● Afficher le nom et le prénom des patients dont le nom de famille est 'Delisle';
● Afficher le nom et le prénom des patients nés après 1976;
● Afficher les noms de famille différents des patients;
● Afficher les patients en ordre croissant de date de naissance;
● Afficher les entrées de dossier où la patient traité est de no. 111111 et le médecin traitant est de no. 67899
7) Effectuez les jointures suivantes :
● Afficher toutes les entrées de dossier et les informations de leurs patients respectifs;
● Afficher les entrées de dossier de Pierre Delisle;
● Afficher la description des traitements dont a bénéficié Pierre Delisle;
● Afficher, du plus jeune au plus vieux, le nom et le prénom des patients traités par René Lajoie le 26 avril 2008.
Exercice 2 – BibliothèqueSoit la base de données suivante :
SPECIALITE (NoSpecialite, Description)
SECTION (NoSection, Emplacement, Description)
LIVRE (CodeISBN, Titre, #NoSpecialité, #NoSection)
FOURNISSEUR (NoFournisseur, Nom)
EXEMPLAIRE (NoExemplaire, #CodeISBN, #NoFournisseur)
ABONNE (NoAbonne, Nom, Prenom)
CARTE (NoCarte, DateDebut, DateFin, #NoAbonne)
EMPRUNT (NoExemplaire, NoCarte, DateLocation, DateRetour)
1) Effectuez les requêtes SQL suivantes :
● Afficher la liste des livres classés dans les sections 1 et 4;
● Ajouter un attribut adresse à la table abonné;
● Ajouter le fournisseur 'Livres du Québec inc.' à la base de données';
● Afficher le nom et le prénom des abonnés qui se sont abonnés ou ont renouvelé leur carte en 2008;
● Afficher le code et le titre des livres qui ont été empruntés le 28 avril 2008, triés par ordre alphabétique de titre;
● Afficher le nom et le prénom des abonnés qui ont déjà emprunté le livre intitulé 'Nos amis les français';
● Prolonger tous les abonnements échus le 25 avril 2010 au 25 mai 2010;
● Afficher le titre des livres de science-fiction empruntés durant le mois d'avril 2008.
Examen mcd-sql-mld- Modèle relationnel et sqlSQL 0
MODÈLE RELATIONNEL ET REQUÊTES SQL
Dans la perspective de la refonte du SI, l’étude des autres composantes du système d’information actuel en vue de leur unification est une étape indispensable.
Après le domaine de la gestion collective, on s’intéresse à l’activité « Gestion privée » de STAR FINANCE. On vous présente en annexe 1 un extrait du schéma conceptuel des données propre à ce pôle.
Travail à faire1. Traduire le modèle conceptuel de données en modèle relationnel.
2. Rédiger les requêtes suivantes en langage SQL :
2.1. Nom et prénom des clients possédant un compte titre de la catégorie «risque modéré».
2.2. Nombre d’opérations par compte.
2.3. Numéro et nom des clients ne possédant pas de compte de la catégorie « risque élevé ».
2.4. Total des montants des opérations par compte.
3. Dans le but d’archiver les opérations anciennes, l’administrateur de la base de données a créé une table ARCH-OPÉ ayant la même structure que la table OPÉRATION.
Rédiger les requêtes suivantes en langage SQL :
3.1. Insérer dans la table ARCH-OPÉ les opérations antérieures à une date saisie au clavier lors de l’exécution de la requête.
3.2. Supprimer de la table ARCH-OPÉ les enregistrements archivés dont l’année est inférieure ou égale à 2000. On peut utiliser la fonction YEAR.
------------------------------------------------------------------------------------------------
CORRECTION------------------------------------------------------------------------------------------------
1- Traduction du MCD en modèle relationnel
CLIENT (N°Client, Nom, Prénom, Ad_Rue, Ad_CodeP, Ad_Ville)
COMPTE (N°Compte, Date_Création, Type_Extrait, Fiscalité, #N°Client, #N°Catégorie)
CATEGORIE (N°Catégorie, Lib_Catégorie)
VALEUR (Code-Valeur, Libellé_Valeur, Nominal_Valeur, Date_Emission, Date_Echéance)
OPERATION (N°Opération, Date_opération, Sens_opération, Quantité_Négociée, Cours_Négocié, #Code_Valeur)
AFFECTER (N°Opération, N°Compte, Quantité_Aff)
2- Requêtes simples SQL
a) Nom et prénom des clients possédant un compte titre de catégorie «risque modéré»
SELECT Nom, Prénom
FROM COMPTE, CLIENT, CATÉGORIE
WHERE COMPTE.N°Client=CLIENT.N°Client
AND COMPTE.N°Catégorie=CATÉGORIE.N°Catégorie
AND Lib_Catégorie = "Risque Modéré" ;
b) Le nombre d’opérations par compte
SELECT N°Compte, COUNT (N°Opération) AS [Nombre d’opérations]
FROM AFFECTER
GROUP BY N°Compte ;
c) Numéro et nom des clients ne possédant pas de compte de catégorie « risque élevé »
SELECT N°Client, Nom
FROM CLIENT
WHERE N°Client NOT IN
(SELECT N°Client
FROM COMPTE, CATÉGORIE
WHERE COMPTE.N°Catégorie=CATÉGORIE.N°Catégorie
AND Lib_Catégorie ="risque élevé" ;) ;
d) Total des montants des opérations par compte .
SELECT N°Compte, SUM (Quantité_Aff*Cours_Négocié) AS [Montant Total]
FROM OPERATION, AFFECTER
WHERE OPERATION.N°opération = AFFECTER.N°Opération
GROUP BY N°Compte ;
3- Requêtes d’archivage SQL)
a) Insérer dans la table ARCH_OPE les opérations antérieures à une date saisie au clavier lors de l’exécution de la requête.
INSERT INTO ARCH_OPE
SELECT *
FROM OPÉRATION
WHERE Date_opération < [Entrez une date d’opération : ] ;
b) Supprimer de la table ARCH_OPE les enregistrements archivés dont l’année est inférieure ou égale à 2000. On peut utiliser la fonction YEAR.
DELETE
FROM ARCH_OPE
WHERE YEAR( Date_opération) <= 2000 ;
OU
WHERE Date_opération <= #01/01/2000#
SQL et phpMyadmin – TP avec solutions – Exercices et
corrigés SQLSQL 0
SQL et phpMyadmin – TP avec solutions – Exercices et corrigés SQL
Exercice 1:Créer une base nommée voitures. Créer ensuite les tables de la base voitures selon
le modèle logique défini
dans les exercices du chapitre 13. Omettre volontairement certaines colonnes et faire volontairement quelques erreurs de type de colonne. Une fois les tables créées, ajouter les colonnes manquantes et corriger les erreurs. Vérifier la structure de chaque table.
SolutionNous utilisons bien sûr phpMyAdmin pour créer la base puis les tables.
• Création de la table personne (en omettant volontairement le champcodepostal)
CREATE TABLE `proprietaire` (
`id_pers` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nom` VARCHAR( 30 ) NOT NULL ,
`prenom` VARCHAR( 30 ) NOT NULL ,
`adresse` VARCHAR( 50 ) NOT NULL ,
`ville` VARCHAR( 40 ) NOT NULL ,
PRIMARY KEY ( `id_pers` )
) TYPE = MYISAM ;
Nous ajoutons le champ codepostal « oublié » lors de la création de la table.
ALTER TABLE `proprietaire` ADD `codepostal` MEDIUMINT( 5 ) UNSIGNED
NOT NULL ;
Nous modifions le type du champ id_pers pour avoir un éventail de valeurs plus grand.
ALTER TABLE `proprietaire` CHANGE `id_pers` `id_pers` MEDIUMINT
UNSIGNED NOT NULL AUTO_INCREMENT
• Création de la table carte grise
CREATE TABLE `cartegrise` (
`id_pers` MEDIUMINT UNSIGNED NOT NULL ,
`immat` VARCHAR( 6 ) NOT NULL ,
`datecarte` DATE NOT NULL ,
PRIMARY KEY ( `id_pers` , `immat` )
);
• Nous créons la table voiture.
CREATE TABLE `voitures` (
`immat` VARCHAR( 6 ) NOT NULL ,
`id_modele` VARCHAR( 10 ) NOT NULL ,
`couleur` ENUM( 'claire', 'moyenne', 'foncée' ) NOT NULL ,
`datevoiture` DATE NOT NULL ,
PRIMARY KEY ( `immat` )
);
• Nous créons la table modele
CREATE TABLE `modele` (
`id_modele` VARCHAR( 10 ) NOT NULL ,
`modele` VARCHAR( 30 ) NOT NULL ,
`carburant` ENUM( 'essence', 'diesel', 'gpl', 'électrique' ) NOT
NULL ,
PRIMARY KEY ( `id_modele` )
);
Exercice 2:
Exporter les tables de la base voitures dans des fichiers SQL.
SolutionNous obtenons les fichiers suivants :
• Le fichier proprietaire.sql :
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:21
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de données: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `proprietaire`
--
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
• Le fichier cartegrise.sql :
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:26
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de données: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;
• Le fichier voiture.sql
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:27
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de données: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `voitures`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','foncée') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;
• Le fichier modele.sql
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:29
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de données: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','électrique') NOT NULL default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;
Exercice 3:
Supprimer toutes les tables de la base voitures.
Solution
Le code SQL est le suivant :
DROP TABLE `proprietaire`
DROP TABLE `cartegrise`
DROP TABLE `voiture`
DROP TABLE `modele`
Exercice 4:
Recréer les tables de la base voitures en utilisant les fichiers SQL précédents.
Pour recréer avec phpMyAdmin, les tables détruites, choisir successivement la base, puis l’onglet « SQL », « Emplacement du fichier texte », « Parcourir » pour désigner l’emplacement du fichier .sql, et enfin « Exécuter ». Les tables sont alors recréées l’une après l’autre.
Dans l’exercice 2, nous avions également la possibilité d’exporter l’ensemble de la base voitures en un seul fichier .sql. Nous aurions obtenu alors le fichier voitures.sql suivant :
Solution-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:41
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- Base de données: `voitures`
--
-- --------------------------------------------------------
--
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','électrique') NOT NULL
default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `proprietaire`
--
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Structure de la table `voiture`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','foncée') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;
Exercice 5:
Insérer des données dans la table proprietaire de la base voitures puis en vérifier la bonne insertion.
Solution
Exemple de code d’insertion :
INSERT INTO `proprietaire` ( `id_pers` , `nom` , `prenom` ,
`adresse` , `ville` , `codepostal` )
VALUES (
'', 'Zouk', 'Julia', '56 Boulevard Nez', 'Paris', '75011'
);
Exercice 6:
Créer un fichier texte contenant une liste de modèles de voitures avec autant de données par ligne que de colonnes dans la table modèle de la base voitures. Insérer ces données dans la base.
Solution
Exemple de fichier texte contenant des modèles : le fichier
modele.txt
"17C92853AZ";"Citroën C5";"diesel"
"178524ER45";"Citroën Picasso";"essence"
"7499RF5679";"Renault Mégane Scénic";"diesel"
"33356677PO";"Peugeot 206";"électrique"
"563339GH56";"Citroën C3";"essence"
"83321TY455";"Renault Espace";"diesel"
Pour revoir la méthode d’insertion à partir d’un fichier texte avec phpMyAdmin, voir la page 382 et suivantes.
Exercice 7:
Créer un fichier Excel ou OpenOffice contenant une liste de modèles de voitures avec autant de données par ligne que de colonnes dans la table modele. L’enregistrer au format CSV et insérer les données dans la base.
Solution
La feuille du tableur à l’aspect type suivant :
L’insertion des données se fait selon la même procédure que celle utilisée pour un fichier texte. Après l’insertion la table modèle a le contenu suivant :
Exercice 8:
Insérer des données dans les autres tables de la base voitures. Effectuer des mises à jour en modifiant certaines valeurs.
Solution
Trivial avec phpMyAdmin.
Exercice 9:
Dans la base magasin, sélectionner les articles dont le prix est inférieur à 1 500 €.
Solution
Requête SQL :
SELECT id_article, designation, prix
FROM article
WHERE prix <1500
Exercice 10:
Dans la base magasin, sélectionner les articles dont le prix est compris entre 100 et 500 €.
Solution
Requête SQL :
SELECT id_article, designation, prix
FROM article
WHERE prix
BETWEEN 100
AND 500
Exercice 11:
Dans la base magasin, sélectionner tous les articles de marque Nikon (dont la désignation contient ce mot).
Solution
Requête SQL :
SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%Nikon%'
Exercice 12:
Dans la base magasin, sélectionner tous les caméscopes, leur prix et leur référence.
Solution
Requête SQL :
SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%caméscope%'
On peut également écrire :
SELECT id_article, designation, prix
FROM article
WHERE categorie = 'vidéo'
Exercice 13:
Dans la base magasin, sélectionner tous les produits de la catégorie informatique et
afficher leur code, leur désignation et leur prix par ordre décroissant de prix.
Solution
Requête SQL :
SELECT id_article, designation, prix
FROM article
WHERE categorie = 'informatique '
ORDER BY prix DESC
Exercice 14:
Dans la base magasin, sélectionner tous les clients de moins de 40 ans et ordonner les résultats par ville en ordre alphabétique.
Solution
Requête SQL :
SELECT nom, prenom, age, ville
FROM CLIENT WHERE age <40
ORDER BY ville ASC
Exercice 15:
Dans la base magasin, calculer le prix moyen de tous les articles.
Solution
Requête SQL :
SELECT avg( prix )
FROM article
Exercice 16:
Dans la base magasin, calculer le nombre d’e-mails non NULL et distincts l’un de l’autre.
Solution
Requête SQL :
SELECT count( DISTINCT mail) FROM client
Exercice 17:
Dans la base magasin, afficher les coordonnées des clients ayant la même adresse (même adresse et même ville).
Solution
Requête SQL :
SELECT nom,prenom,adresse,ville,mail FROM client WHERE adresse='75
Bd Hochimin' AND ville='Lille'
Avec PHP, si l’adresse et la ville étaient contenues respectivement dans les variables $adresse et $ville on aurait le code suivant :
SELECT nom,prenom,adresse,ville,mail FROM client WHERE
adresse='$adresse' AND ville='$ville'
Exercice 18:
Dans la base magasin, sélectionner tous les articles commandés par chaque client.
Solution
Requête SQL :
SELECT nom,prenom,article.id_article,designation
FROM `client` ,commande,article,ligne
WHERE client.id_client=commande.id_client
AND ligne.id_comm=commande.id_comm
AND ligne.id_article=article.id_article
ORDER BY nom
Exercice 19:
Dans la base magasin, sélectionner tous les clients dont le montant d’une commande dépasse 1 500 €.
Solution
Requête SQL :
SELECT nom,prenom, ligne.id_comm, sum(prixunit*quantite) AS 'total'
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY ligne.id_comm
HAVING sum(prixunit*quantite)>1500
Exercice 20:
Dans la base magasin, sélectionner tous les clients dont le montant total de toutes les commandes dépasse 5 000 €.
Solution
Requête SQL :
SELECT client.id_client, ligne.id_comm, sum(prixunit*quantite)
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY client.id_client
HAVING sum(prixunit*quantite)>5000
Exercice 21:
Dans la base voitures, sélectionner tous les véhicules d’une personne donnée.
Solution
Requête SQL : Nous cherchons par exemple tous les véhicules de M. Algout.
SELECT cartegrise.immat,modele,proprietaire.id_pers
FROM voiture,modele,proprietaire,cartegrise
WHERE proprietaire.nom='Algout'
AND proprietaire.id_pers=cartegrise.id_pers
AND cartegrise.immat=voiture.immat
AND voiture.id_modele=modele.id_modele
Exercice 22:
Dans la base voitures, sélectionner toutes les personnes ayant le même modèle de voiture.
Solution
Requête SQL : Nous cherchons par exemple tous les propriétaires de véhicules de type « Picasso ».
SELECT
proprietaire.nom, proprietaire.prenom,modele.modele,modele.carburant
FROM voiture,modele,proprietaire,cartegrise
WHERE modele LIKE '%Picasso'
AND voiture.id_modele=modele.id_modele
AND cartegrise.immat=voiture.immat
AND proprietaire.id_pers=cartegrise.id_pers
Exercice 23:
Dans la base voitures, sélectionner tous les véhicules ayant plusieurs copropriétaires.
SolutionRequête SQL :
SELECT cartegrise.immat FROM cartegrise
GROUP BY immat
HAVING count(*) >1
Examen SGBDR MYSQL – Exercices SQLSQL 0
Examen SGBDR MYSQL – Exercices SQL
Soit une base de données qui gère les formations du personnel :
Travail à Faire :
1/ Faites la création des 3 tables à l’aide de SQL (1.5pts)
2/ Faites le remplissage à l’aide des requêtes paramétrés des 3 tables (0.75Pts)
3/ Affichez le nombre de Formation effectués entre le 01/03/2008 et le 01/12/2008
(1 Pts)
4/Affichez les formations dont le coût est supérieur au cout moyen des formations (1.5pts)
5/Affichez les Personnes qui ont faits des formations en Fiscalité et dont le nombre d’heures des formations est supérieur au minimum du nombre d’heures (1.5pts)
6/ Afficher les modules effectués par Mr Benamar (0.5pts)
7/ afficher la recette globale des formations effectués par du personnel habitant à Fès (1.25pts)
Examen SGBD SQL Devoir Bases de Données ExercicesBases de Données, SQL 0
Partie I :
Quelles sont les étapes d’un cycle de vie d’un logiciel ?
Définir l’architecture 3 tiers
Définir et expliquer à quoi servent les concepts suivants :
a. Un Triggerb. Une contrainte d’intégrité
Partie II : Soit les tables suivantes :
« Candidats » composé des champs suivants :
Matricule : Numéro d’immatriculation du candidat (clé primaire)
Nom : nom du candidat
DateNaissance : date de naissance du candidat
DateDiplome : date d’obtention du diplôme
Code_ecole : code de l’école qui a délivrée le diplôme (clé etrangère)
« Ecole » composé des champs suivants :
Code_ecole : (clé primaire)
Lib_ecole : intitulé de l’école
Ecrire en langage SQL les requêtes permettant de :
Insérer dans la table « candidats » un nouveau candidat ayant le matricule 3200, nommé « Albert », né
le 12/05/1980, et qui a obtenu son diplôme le 15/08/2000 délivré par l’école ayant le code 03.
Avoir la liste des candidats triés par ordre croissant des Ecoles.
Avoir la liste des candidats lauréats de l’école « XXXXX ».
Calculer l’age moyen des candidats.
Partie III : La société IMPTON est spécialisée dans le recyclage des toners d’imprimantes. Pour gérer une partie de leur métier le directeur accède à la base de données dont le schéma est le suiavant : CARTOUCHE (RefCartouche, DesCartouche, PrixCartouche)IMPRIMANTE (RefImp, DesignationImp, #CodeType)TYPE (CodeType, LibelleType)COMPATIBILITE ( #RefImp, #RefCartouche)
Un attribut (ou un ensemble d’attributs) souligné est une clé primaire. Les clés étrangères sont
précédées par #.
La table CARTOUCHE renseigne sur tous les modèles de cartouches vendues par la société IMPTON.
La table IMPRIMANTE renseigne sur tous les modèles d’imprimantes existantes.
La table TYPE contient trois lignes (Imprimantes Matricielle, Imprimante Laser, Imprimante Jet d’encre).
La table COMPATIBILITE permet de connaître toute imprimante acceptant un modèle de cartouche, et
réciproquement, toutes les cartouches compatibles avec un modèle d’imprimante.
Questions :
Présenter le schéma du modèle conceptuel des données (MCD) du modèle logique indiqué ci-dessus.
Qu’est ce qu’une clé étrangère ?
Exprimer la requête pour créer la table CARTOUCHE.
Exprimer en langage SQL la requête permettant d’obtenir le Nombre et prix de vente moyen des
cartouches compatibles avec les imprimantes à jet d’encre.
Exercices SQL TP langage SQL TD base de données SGBD
Requêtes SQLSQL 0
Présentation de la base de données
Légende :
Les clés primaires sont soulignées
Les clés externes sont indiqués par un symbole " + ".
Les flèches indiquent les liens entre les tables où une tête de flèche simple pointe vers la clé primaire et la tête de flèche double pointe vers la clé externe.
Les colonnes "quantite" et "cout" sont d'un type numérique. Les colonnes dont le nom comporte le mot "date" sont de type date. La colonne "taxable" est de type "tinyint", n'ayant que les valeurs 0 pour FAUX, et 1 pour VRAI.
Toutes les autres colonnes sont d'un type caractère.
Note :
Pour utiliser les dates dans un format uniforme, lancer la commande suivante au début de votre session dans
l'Analyseur de requêtes :
SET DATEFORMAT YMD
Par la suite, les valeurs "date" sont présentées et saisies dans le format international année-mois-jour (ex: 2004-
10-23).
1. Produisez la liste complète des classes de produit.
2. Produisez la liste complète des chefs de projet (matricule, nom).
3. Produisez la liste contenant le numéro, la description et la quantité, des produits en inventaire qui valent au
moins $50.00 l'unité.
4. Produisez la liste des sorties d'inventaire où la quantité sortie est supérieure à un (1) mais inférieure à dix (10).
5. Modifiez le coût du produit 6794 à $17.95.
6. Présentez la liste des sorties d'inventaire qui ont été effectuées entre le 10 mai 1989 et le 25 février 1990,
exclusivement.
7. La liste (code_produit, description et quantité) des produits qui ont au moins 15 unités en inventaire.
8. La liste des sorties d'inventaire du projet 'P1206' oú la quantité sortie est supérieure à un (1).
9. Effacer de la base de données la produit dont le code est '78-U'.
10. Ajoutez un nouveau projet, dont le code est P1384. Il est dirigé par le chef de projet dont le matricule est 106,
et il doit débuter le 20 octobre 1994.
11. La liste des produits, code et description seulement, que l'on retrouve dans une étagère débutant par '21' ou
'11'.
12. La liste des produits (classe, code_produit, description et étagère) que l'on retrouve dans la section d'étagère
'S' (ex: '99S99') et qui n'ont pas de quantité en inventaire.
13. La liste des sorties d'inventaire (no_sortie, code_produit, code_projet et quantité) qui ont été effectuées avec
le produit 'BXM100', ou qui ont été effectuées pour le projet 'P1259'.
14 Modifiez l'unité de mesure des produits appartenant à la classe 'C10' et entreposées à la rangée 'L' pour la
valeur 'UN'.
15. Fournissez la liste des numéros des projets ayant débuté entre le 24 mai 1987 et le 4 mars 1990
inclusivement.
16. Le produit dont le code est 128R a été mal saisi ; sa classe doit être C10, son étagère doit être 03T33 et son
coût unitaire est 4 588.23$. Faites les modifications nécessaires.
17. Présentez les sorties d'inventaire affectées au projet P1208, en autant qu'elles n'ont pas eu lieu entre le 5
avril 1991 et le 26 mars 1992, inclusivement.
18. Quel est le code des projets dont la date de fin est inconnue ?
Cours de bases de données - TP PLSQL SGBD relationnels
exercices avec des exemples Requêtes PL/SQLOracle, SQL 1
1 - Interrogation avec SQL
Au départ, vous avez accès au schéma et à la base de données « Films », vue et revue en cours, qui est
partagée (en lecture) par tout le monde. Voici le script de création de ce schéma (disponible sur le site). Ces
commandes doivent maintenant vous être familières (sinon relisez les chapitres correspondant).
Exemple 1 SchemaFilms.sql : Le script de création du schéma
/*
Commandes de création de la base Films, testé avec MySQL et PostgreSQL.
Pour Oracle, il suffit de remplacer le type TEXT par le type LONG dans la table Film.
*/
/* Destruction eventuelle des tables existantes */
DROP TABLE Notation;
DROP TABLE Role;
DROP TABLE Film;
DROP TABLE Artiste;
DROP TABLE Internaute;
DROP TABLE Pays;
DROP TABLE Genre;
/* Creation des tables */
CREATE TABLE Internaute (email VARCHAR (40) NOT NULL,
nom VARCHAR (30) NOT NULL ,
prenom VARCHAR (30) NOT NULL,
region VARCHAR (30),
CONSTRAINT PKInternaute PRIMARY KEY (email));
CREATE TABLE Pays (code VARCHAR(4) NOT NULL,
nom VARCHAR (30) DEFAULT ’Inconnu’ NOT NULL,
langue VARCHAR (30) NOT NULL,
CONSTRAINT PKPays PRIMARY KEY (code));
CREATE TABLE Artiste (idArtiste INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
CONSTRAINT PKArtiste PRIMARY KEY (idArtiste),
CONSTRAINT UniqueNomArtiste UNIQUE (nom, prenom));
CREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR (20) NOT NULL,
/* Remplacer TEXT par LONG pour ORACLE */
resume TEXT,
codePays VARCHAR (4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (40) NOT NULL,
note INTEGER NOT NULL,
CONSTRAINT PKNotation PRIMARY KEY (idFilm, email));
CREATE TABLE Role (idFilm INTEGER NOT NULL,
idActeur INTEGER NOT NULL,
nomRole VARCHAR(30),
CONSTRAINT PKRole PRIMARY KEY (idActeur,idFilm),
FOREIGN KEY (idFilm) REFERENCES Film,
FOREIGN KEY (idActeur) REFERENCES Artiste);
CREATE TABLE Genre (code VARCHAR (20) NOT NULL,
CONSTRAINT PKGenre PRIMARY KEY (code));
Vous pouvez remarquer que l’ordre de création des tables respecte le référencement entre PRIMARY KEY et
FOREIGN KEY. Les tables qui sont référencées par cette dernière clause doivent être créées avant celles qui les
référencent. Par exemple la table Artiste est créée avant la table Film à cause de la clé étrangère idMES. C’est
en revanche l’ordre inverse qui est suivi pour les commandes DROP : on ne peut pas détruire une table qui est
référencée par une commande FOREIGN KEY. Notez qu’en principe on ne place pas les commandes DROP
dans un script de création puisqu’on ne souhaite pas prendre le risque de détruire des données existantes.
Comme il s’agit ici d’une base de test, la situation est différente.
La base est disponible sur le site et contient un échantillon de films avec leur metteur en scène, leurs acteurs et
les notations de quelques internautes. À vous de jouer : il faut concevoir, saisir et exécuter les ordres SQL
correspondant aux requêtes qui suivent.
1.1 Sélections simples
1. Tous les titres de films.
2. Nom et prénom des internautes auvergnats.
3. Titre et année de tous les drames, triés par année ascendante. Donnez ensuite le tri par année descendante.
4. Nom et année de naissance des artistes nés avant 1950.
5. Titre et année de tous les films parus entre 1960 et 1980
6. Tous les genres de films (éliminez les doublons).
7. Titre, genre et résumé des films qui sont soit des drames, soit des westerns (utilisez la construction IN), et dont
le résumé contient la chaîne de caractères « vie ».
8. Les artistes dont le nom commence par ’H’ (commande LIKE).
9. Quels sont les acteurs dont on ignore l’année de naissance ? (Attention : cela signifie que la valeur est
absente).
10. Prénom, nom et âge de chaque artiste (NB : l’âge est la différence entre l’année courante et l’année de
naissance). Nommez âge la colonne obtenue (commande AS).
1.2 Jointures
1. Qui joué le rôle de Morpheus (nom et prénom) ?
2. Qui est le réalisateur de Alien ?
3. Prénom et nom des internautes qui ont donné une note de 4 à un film (donner aussi le titre du film).
4. Quels acteurs ont joué quel rôle dans le film Vertigo ?
5. Films dont le réalisateur est Tim Burton, et un des acteurs est Johnny Depp.
6. Titre des films dans lesquels a joué Bruce Willis. Donner aussi le nom du rôle.
7. Quel metteur en scène a tourné dans ses propres films ? Donner le nom, le rôle et le titre des films.
8. Quel metteur en scène a tourné en tant qu’acteur (mais pas dans son propre film) ? Donner le nom, le rôle et le
titre des films où le metteur en scène a joué.
9. Dans quels films le metteur en scène a-t-il le même prénom que l’un des interprètes ? (titre, nom du metteur en
scène, nom de l’interprète). Le metteur en scène et l’interprète ne doivent pas être la même personne.
1.3 Requêtes imbriquées
Les requêtes suivantes peuvent s’exprimer avec une imbrication des clauses SELECT, mais on peut
également utiliser des jointures « à plat ». Si le cœur vous en dit, essayez les deux versions.
1. Donnez les nom et prénom des artistes qui on mis en scène un film.
2. Donnez le titre et année des films qui ont le même genre que Matrix.
3. Donnez le nom des internautes qui ont noté le film Alien. Donnez également la note.
1.4 Négation
Là encore, il existe souvent plusieurs manières d’exprimer la même requête.
1. Les films sans rôle.
2. Nom et prénom des acteurs qui n’ont jamais mis en scène de film.
3. Les internautes qui n’ont pas noté de film paru en 1999.
1.5 Fonctions de groupe
1. Quelle est le nombre de films notés par l’internaute [email protected], quelle est la moyenne des notes données,
la note minimale et la note maximale ?
2. Combien de fois Bruce Willis a-t-il joué le role de McClane ?
3. Année du film le plus ancien et du film le plus récent.
4. id, Nom et prénom des réalisateurs, et nombre de films qu’ils ont tournés.
2 - Création d’un schéma relationnel
Il s’agit de définir un schéma de base de données, d’y intégrer des contraintes, des vues et d’y insérer quelques
informations. Vérifiez le comportement des contraintes et des vues en essayant de les mettre en défaut.
2.1 Création des tables
Créez les tables du schéma ’Agence de voyages’, vues en cours, et rappelées ci-dessous.
– Station (nomStation, capacité, lieu, région, tarif)
– Activite (nomStation, libellé, prix)
– Client (id, nom, prénom, ville, région, solde)
– Sejour (id, station, début1, nbPlaces)
Attention à bien définir les clés primaires et étrangères. Voici les autres contraintes portant sur ces
tables.
1. Les données capacité, lieu, nom, ville, solde et nbPlaces doivent toujours être connues.
2. Les montants (prix, tarif et solde) ont une valeur par défaut à 0.
3. Il ne peut pas y avoir deux stations dans le même lieu et la même région.
4. Les régions autorisées sont : ’Ocean Indien’, ’Antilles’, ’Europe’, ’Ameriques’ et ’Extreme Orient’.
5. La destruction d’une station doit entraîner la destruction de ses activités et de ses séjours.
6. Le prix d’une activité doit être inférieur au tarif de la station et supérieur à 0.
7. Pour une date de début donnée, le nombre total de places réservées dans une station doit être inférieur à la
capacité de la station.
Conseil : donnez des noms à vos contraintes PRIMARY KEY, FOREIGN KEY et CHECK avec la clause
CONSTRAINT.
FIG. 1 – La base ’Agence’
2.2 Insertion de données
Insérez dans la base les données de la figure 1 avec des ordres INSERT. Attention, l’ordre des INSERT est
important (pourquoi ?).
Vous pouvez ensuite tester les contraintes avec quelques ordres SQL. Par exemple : détruisez la station et
vérifiez que les activités ont disparu ; insérez une autre station en (Guadeloupe, Antilles) ; insérez une station
dans une région ’Nullepart’, etc.
2.3 Vues
Maintenant il faut créer des vues et tester l’interrogation et la mise à jour à travers ces vues.
1. Créez les vues suivantes sur le schéma précédent.
(a) Une vue ActivitesModiques (Station, Activite) donnant le nom des stations et des activités dont le prix est
inférieur à 140 FF. Toute ligne insérée dans cette vue doit apparaître dans la vue ensuite.
(b) Une vue ActivitesCheres, de même schéma, avec prix supérieur à 140 FF, et la même contrainte d’insertion.
(c) Une vue StationDollars (Nom, Capacite, Lieu, TarifDollar) donnant le nom d’une station, sa capacité, le lieu et
le tarif en dollars (metez le taux de conversion « en dur », ou bien – mieux – créez une table stockant le taux de
conversion).
(d) Une vue Tarifs (Station, Tarif, OptionMin, OptionMax) donnant, pour chaque station, le tarif et les prix min et
max des activités.
(e) Une vue Reservation (nomStation, PlacesReservees) donnant, par station et date de début de séjour, le
nombre de places réservées.
2. Consultez ensuite le contenu de ces vues. Vous pouvez insérez quelques lignes supplémentaires dans
les tables et constater qu’elles sont prises en compte dans les vues.
3. Dans quelles vues peut-on insérer, détruire et mettre à jour ? Essayez les opérations suivantes :
(a) Insérez une activité ’Kayac’ pour la station ’Venusa’ dans ActivitesCheres et ActivitesModiques. Le contrôle
sur l’insertion est-il utile dans ce cas ?
(b) Peut-on insérer dans StationEuro ? Sous quelle condition ? Faites l’essai.
(c) Détruisez une ligne de StationEuro.
3 - Programmation
Les exercices qui suivent permettent de se familiariser avec les langages de programmation interne (PL/SQL) et
externe (Java/JDBC, PHP) permettant de manipuler une base de données de manière procédurale. Des
exemples sont fournis sur le site, qui doivent vous permettre de créer vos propres procédures, fonctions et
programmes.
3.1 Procédures stockées
Le langage utilisé est le PL/SQL version PostgreSQL. Quelques modifications mineures suffisent à transcrire pour
le PL/SQL d’Oracle.
1. Créer une fonction NomClient qui prend en entrée l’id d’un client et qui renvoie une chaîne contenant le
prénom et le nom du client (voir l’exemple réalisateur .plsql).
2. Créer une fonction Activités qui prend en entrée le nom du station et produit une chaîne de caractères
contenant l’énumération des activités de la station (par exemple, “Ski, Yoga, Massage”).
3. Créer ensuite une vue qui affiche les stations, avec un attribut supplémentaire donnant la liste des activités
(par appel à la fonction bien sûr).
4. Créer une fonction Actualiser qui prend en entrée un pourcentage et le nom d’une station, et augmente le tarif
de la station et le prix de chacune de ses activités du pourcentage indiqué.
3.2 Triggers
1. Implantez par un trigger la règle suivante : si le prix d’une activité baisse, alors le tarif de la station doit
augmenter de la différence.
Indication : le trigger doit se déclencher sur une modification, et tester pour chaque ligne que la nouvelle valeur
est plus grande que l’ancienne. Si ce n’est pas le cas, faire un UPDATE de la station pour ajouter la différence
entre l’ancienne et la nouvelle valeur.
2. Faites l’expérience : diminuez le prix d’une activité, et regardez ce qui se passe pour la station.
3. On veut disposer de l’information nbActivites dans la table Station. Pour cela :
(a) Ajoutez la colonne nbActivites avec pour valeur par défaut 0.
(b) Créez un trigger qui maintient cette information.
4. Interdisez par un trigger l’insertion d’une ligne dans la table Séjour si le solde du client est inférieur au
nombre de places multiplié par le tarif de la station.
3.3 Applications externes : PHP
Il n’y a aucune limite (enfin, presque), à ce que l’on peut faire avec une programmationHTML/PHP/PostgreSQL
(ou n’importe quelle autre SGBD). Pour commencer vous devez installer les trois scripts vus en cours qui
montrent les principales techniques (connexion, exécution d’une requête, interrogation par formulaire).
Récupérez les fichiers sur le site (ils sont dans une archive pgphp.tar disponible dans la page des exemples).
Vous devez ensuite effectuer les opérations suivantes :
1. Créer les tables FilmComplet et FilmSimple avec les deux scripts FilmSimple.sql et FilmCOmplet.sql
2. Insérer quelques lignes dans la table FilmSimple avec le script InsFilmSimple.sql
3. Modifier les paramètres de connexion dans Connect.php
4. ExPGSQL1.php interroge et affiche le contenu de FilmSimple.
5. ExForm3.html affiche un formulaire pour rechercher des données en lançant le script ExPGSQL2.php
6. ExForm4.html affiche un formulaire pour insérer, modifier oui détruire des données en lançant le script
ExPGSQL3.php
Voici quelques suggestions pour des scripts PHP sur la base Station.
1. Récupérez les scripts PHP donnés en exemple, et travaillant sur la table FilmSimple, et adaptez-les
pour saisir et modifier les informations de la table Client.
2. Faites un formulaire de saisie d’un séjour, en proposant la liste des stations et la liste des clients dans
un menu déroulant.
4 - Concurrence d’accès
On va maintenant étudier le comportement concret d’un SGBD en cas d’accès concurrents à la même ressource.
Pour celà on va simuler l’exécution concurrente de programmes à l’aide du petit ensemble de lectures/écritures
sur la base “Agence de voyage” créé dans le premier exercice : modification du solde de certains clients et
sélection des clients. Créez 4 fichiers, nommés SEL.sql, MAJpas.sql, MAJfog.sql et MAJker.sql et entrez-y les
commandes suivantes :
1. SEL.sql
PROMPT ’Affichage des clients =>’;
SELECT * FROM client;
2. MAJpas.sql
PROMPT ’Augmentation du client Pascal =>’;
UPDATE client SET solde = solde + 1000
WHERE client = ’Pascal’;
3. MAJfog.sql
PROMPT ’Augmentation du client Fogg =>’;
UPDATE client SET solde = solde + 1000
WHERE client = ’Fogg’;
4. MAJker.sql
PROMPT ’Augmentation du client Kerouac =>’;
UPDATE client SET solde = solde + 1000
WHERE client = ’Kerouac’;
Ensuite, ouvrez deux fenêtres et lancez SQLPLUS dans chacune : chaque session est considérée par ORACLE
comme un utilisateur, et on a donc 2 utilisateurs, nommés 1 et 2, en situation de concurrence.
Dans tout ce qui suit, on note INSTRi l’exécution de l’instruction INSTR par l’utilisateur i. Par exemple MAJker1
corespond à l’exécution du fichier MAJker dans la première fenêtre par la commande @MAJker. On note de
même ROLi et COMi l’exécution des commandes rollback; et commit ;dans la fenêtre i.
Questions Executez les séquences d’instruction décrites ci-dessous. Dans chacun des cas, expliquez ce qui se
passe.
1. Première expérience : l’utilisateur 1 effectue des mises-à-jour, tandis que l’utilisateur 2 ne fait que des
sélections. Que constate-t-on ?
SEL1; SEL2;MAJker1; SEL2;MAJpas; SEL2;ROL1; SEL2.
2. idem, mais avec des commit.
SEL1; SEL2;MAJker1; SEL2;COM1; SEL2;MAJker1; SEl2;COM1; SEL2.
3. Maintenant les deux utilisateurs effectuent des MAJ simultanées.
SEL1; SEL2;MAJker1;MAJpas2; SEl1; SEL2;MAJfog1;MAJfog2; SEL1;COM1;COM2.
Un blocage est apparu. Pourquoi ?
4. Idem, avec un ordre des opérations qui diffère d’un utilisateur à l’autre.
SEL1; SEL2;MAJker1;MAJpas2; SEl1; SEL2;MAJpas1;MAJker2ROL1ROL2.
Que constate-t-on ?
5.En fait, ORACLE pratique une verrouilage à deux phases assez libéral, qui ne garantit pas la
sérialisabilité : aucun verrrou n’est placé sur une ligne lors d’une lecture. L’utilisateur peut verrouiller
explicitement en ajoutant la clause FOR UPDATE. Exemple :
SELECT * FROM client FOR UPDATE;
Chaque ligne sélectionnée est alors verrouillée. Refaites les expériences précédentes avec un verrouillage
explicite des lignes que vous voulez modifier.
6. Expérimentez les exécutions précédentes en spécifiant le mode suivant :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Exercices SQL niveau avancé TP SQLSQL 0
Exercices : SQL Avancé
Objectif de l'exercice:
Utiliser les notions de valeur nulle, tris, expressions statistiques en SQL
Les champs des tables de données sont les suivants:
ENO: Numéro d'employé (clé)ENOM: Nom d'employéPROF: ProfessionDATEEMB: Date d'embaucheSAL: SalaireCOMM: Commission (un employé peut ne pas avoir de commission)DNO: numéro de département
DNOM: Nom de départementDIR: Directeur du departementVILLE: Localisation du département
Considérez les Tables de Données suivantes:
Employés:
Departements:
Exprimez les requêtes suivantes :
– en Algèbre relationnelle– en SQL
Requête 1:
Donnez la liste des employés ayant une commission
Requête 2:
Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi, par salaire décroissant
Requête 3:
Donnez le salaire moyen des employés
Requête 4: Donnez le salaire moyen du département Production
Requête 5:
Donnes les numéros de département et leur salaire maximum
Requête 6: Donnez les noms des employés ayant le salaire maximum dans chaque département
Requête 7: Donnez les différentes professions et leur salaire moyen
Requête 8: Donnez le salaire moyen par profession le plus bas
Requête 9: Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen
B. Exercice B
Création de tables
Donnez les requêtes SQL qui permettent de créer les tables suivantes. Indiquer les contraintes sur les champs, ainsi que les clés primaires et étrangères.
Table Magasin, qui contient les champs suivants:
– nom du magasin– nom du gérant– prénom du gérant– date de création– nombre de clients– chiffre d'affaire.
Table Personne, qui contient les champs suivants:
– prénom– nom– fonction (gérant, employé, client)– telephone– date de naissance.
