ECRIRE/EXECUTER DES REQUETES SQL

 

Le SQL (Structured QueryLanguageou langage structuré de requêtes), est un langage informatique (de type requête) normalisé (standard) destiné à interroger ou à manipuler une base de données relationnelle avec :

• Un langage de définition de données (LDD, ou en anglais DDL, Data définition language) qui permet de modifier la structure de la base de données.

• Un langage de manipulation de données (LMD, ou en anglais DML, Data manipulation language) qui constitue la partie la plus courante et la plus visible de SQL, permettant de consulter et modifier le contenu de la base de données.

• Un langage de contrôle de données (LCD, ou en anglais DCL, Data control language) qui permet de gérer les privilèges, c'est-à-dire les utilisateurs et les actions qu'ils peuvent entreprendre sur la base de données.

 

1. Manipulation de données

Les commandes de manipulation de données sont des commandes qui permettent de manipuler le contenu de la base de données, c'est-à-dire d'insérer, de modifier, de consulter ou de supprimer des lignes dans les tables de la base de données :

La manipulation des données en mode commande est composée de deux types de commandes : commandes de mise à jour de données et commande de consultation (ou recherche) de données.

 

1.1. Mise à jour de données

La mise à jour des tables d'une base de données relationnelle se fait à l'aide de trois commandes : la commande d'insertion, la commande de modification et la commande de suppression de lignes.

 

1.1.1. Insertion de lignes

L'opération d'ajout de nouvelles données consiste à insérer de nouvelles lignes dans une table dont la structure a été déjà créée dans la base. La commande du langage SQL permettant d'insérer une ligne dans une table est la commande INSERT.

 

Syntaxe :

INSERT INTO nom_table[ liste_Nom_colonne]

VALUES (liste_valeur)

 

-Le paramètre liste_Nom_colonnesert à préciser la liste des colonnes dans lesquelles va s'effectuer l'opération d'insertion. Cette liste peut être omise dans le cas où l'opération d'insertion concerne toutes les colonnes de la table. Dans ce cas, l'ordre des valeurs fournies par le paramètre liste_valeurdoit être le même que celui des colonnes données pour la commande CREATE TABLE.

- Pour que l'opération d'insertion puisse être exécutée, les conditions suivantes doivent être respectées :

*Les types des données de liste_valeurdoivent être compatibles avec ceux des colonnes de la table.

*Unicité des lignes (contrainte de clé primaire),

*Caractère obligatoire associé à une colonne (clause NOT NULL),

*Existence de la valeur dans une autre table quand il s'agit d'une clé étrangère(contrainte d'intégrité référentielle) : nécessité de respecter un certain ordre lors de l'insertion des lignes

*Vérification d'une condition de validité (contrainte valeur : clause CHECK).

 

Exemple :

Code SQL ;

INSERT INTO professeur (matricule_prof, Nom_prof,Prenom_prof,Grade_prof,Age_profAdresse_prof,Salaire_prof,Matiere_ens,Nombre_matieres_ens )

VALUES (‘580097-Y’,'Talla', 'André’ ‘PLEG’, 45, 'BP 1030 Yaoundé',‘Maths’, 150 000,2) ;

 

 

 

Remarque : Compte tenu du fait que chaque colonne de la table professeur, reçoit une valeur et que l'ordre de ces valeurs est le même que celui des colonnes, on aurait pu dans ce cas omettre de préciser ces colonnes et écrire tout simplement :

Code SQL :

INSERT INTO professeur

VALUES (‘580097-Y’,'Talla', 'André’,45, ‘PLEG’, 'BP 1030 Yaoundé', 150 000, ‘Maths’,1) ;

 

 

 

Insertion de plusieurs lignes

Il est possible d’ajouter plusieurs lignes à un tableau avec une seule requête.

Code SQL:

INSERT INTO client (prenom, nom, ville, age)

VALUES

('Pascal', 'Amougou', 'Bp 50 yaounde', 24),

('Théophile', 'Kenne', 'Bp 490 Mbouda', 36),

('Alain', 'Kamga', 'Bp 23 Bafoussam', 27) ;

 

 

 

Remarque : lorsque le champ à remplir est de type VARCHAR ou TEXT il faut indiquer le texte entre guillemet simple. En revanche, lorsque la colonne est un numérique tel que INT ou BIGINT il n’y a pas besoin d’utiliser de guillemet, il suffit juste d’indiquer le nombre

 

 1.1.2. Modification de lignes

L'opération de mise à jour de données consiste en la modification des colonnes, appartenant à une table, qui contiennent des données qui ne sont plus conformes à la réalité.La commande du langage SQL permettant de modifier le contenu d'une table est la commande UPDATE.

 

Syntaxe :

UPDATEnom_table

SET Nom_colonne1 = Nouvelle_valeur 1 [, Nom_colonne2 = Nouvelle_valeur 2 …]

[WHERE condition]

 

-La modification peut toucher une ou plusieurs colonnes.

- Les valeurs des colonnes identifiées par Nom_colonne1, Nom_colonne2, …, Nom_colonne n sont modifiées dans toutes les lignes qui vérifient la condition de la clause WHERE. Dans le  cas où cette dernière  est absente, toutes les lignes de la table sont mises à jour.

 

Exemple 1 : Suite à une baisse des effectifs, le Fondateur après des grandes négociations, décide de mettre les salaires de tous les professeurs à 100 000 FRANCS.Il faut modifier le champ salaire de la table professeur.Cette modification concerne une seule colonne et plusieurs lignes.

 Code SQL :

UPDATE professeur

SETsalaire_prof=100000;

 

 

Exemple 2 : Le professeur TALLA dont le numéro matricule est égal à 580097-Y vient d’avoir un an de plus. Son âge passe de 45 à 46 ans. Cette modification concerne une seule colonne et une ligne.

Code SQL :

UPDATE professeur

SETage_prof= 46 ;

WHEREmatricule_prof = ‘580097-Y’;

 

 

Exemple 3 :Suite à une reprise de l’activité, le Fondateur décide de réhausser les salaires des professeurs dont le grade est PLEG.Il faut modifier la colonne salaire. Cette modification concerne une colonne et plusieurs lignes vérifiant une condition.

Code SQL :

UPDATE professeur

SETsalaire_prof=120 000 ;

WHEREgrade_prof = ‘PLEG’;

 

 

NB : On peut bien évidemment modifier plusieurs colonnes.

 

1.1.3. Suppression de lignes

L'opération de suppression de données consiste à éliminer une ou plusieurs lignes existantes à partir d'une table.La commande du langage SQL permettant de supprimer des lignes d'une table est la commande DELETE.

Syntaxe :

DELETE FROM nom_table

[WHERE condition]

-Le paramètre condition qui apparait dans la clause WHERE sert à indiquer la condition qui doit être vérifiée par les lignes à supprimer.

-En cas ou la commande de suppression ne respecte pas une contrainte d'intégrité référentielle, elle ne peut pas aboutir.

-La suppression d'une ligne appartenant à une table donnée peut entrainer la suppression d'autres lignes appartenant à d'autres tables lorsqu'il existe des contraintes d'intégrité référentielles de suppression en cascade : utilisation de la clause ON DELETE CASCADE dans la définition des clés étrangères. 1

ON DELETE CASCADE est une option qui permet de maintenir l'intégrité référentielleen supprimant automatiquement les valeurs d'une clé étrangère dépendant d'une valeur d'une clé primaire si cette dernière est supprimée.

 

Exemple 1 :On souhaite supprimer tous les professeurs de la table :

Code SQL :

DELETE FROM professeur ;

 

Exemple 2 :

On désire supprimer le professeur  ayant le numéro matricule 456087-U.

Code SQL :

DELETE FROM professeur

WHEREmatricule_prof = ‘456087-U ‘;

 

 

NB : WHERE est indispensable pour indiquer quelle(s) entrée(s) doivent être supprimés, sinon toutes les entrées seront supprimées ! Cela équivaut à vider la table.

Exemple 3

2. Recherche de données : requêtes

La recherche de données est une opération qui consiste à récupérer des données appartenant à la base. Cette recherche peut être réalisée en faisant référence à une ou à plusieurs tables. Elle peut se référer à une partie ou à la totalité des colonnes des tables concernées tout en étant, éventuellement, assorti d’une condition.

Une recherche peut consister à effectuer :

- Une projection sur certaines colonnes d'une table

- Une sélection sur certaines lignes d'une table

- Une jointure sur deux tables

- Toute combinaison de projection, sélection et jointure.

La commande permettant d'effectuer une recherche dans une base de données est la commande SELECT.

 

2.1. Recherche de colonnes à partir d'une table : projection

On appelle projection une opération SQL qui permet de ne récupérer qu’un sous-ensemble des colonnes d'une table. C’est donc une instruction permettant de sélectionner un ensemble de colonnes dans une table. Elle ne concerne qu'une seule table de la base de données. Le résultat de la projection doit comporter au moins une colonne de la table. Cependant, toutes les lignes relatives à cette colonne feront partie du résultat.     

Syntaxe :

SELECT [DISTINCT] * / liste_Nom_colonne

FROM nom_table

                                              

-Le paramètre liste_Nom_colonnesert à préciser la liste des colonnes, que l'on veut voir affichées.

-Le paramètre liste_Nom_colonnepeut être remplace par * qui désigne toutes les colonnes de la table.

-L'ordre d'affichage du résultat donné par le contenu des colonnes est le même que celui indiqué par le paramètre liste_Nom_colonne.

-Dans le cas où le paramètre * est donné, l'ordre sera celui spécifie dans la structure de la table.

-Le paramètre DISTINCT sert à éliminer les lignes en double dans le résultat puisque la commande SELECT peut ramener des doublons si la clé primaire ne figure pas dans la liste des colonnes à afficher.

-Le paramètre nom_table sert à se référer à la table concernée par l'opération de sélection.

-Le résultat de la commande SELECT est une nouvelle table résultat de l'opération de projection de la table initiale.

-Par défaut, les colonnes de la table résultat portent les mêmes noms que ceux de la table de départ.

-Il est possible de donner aux colonnes de la table résultat des noms différents de ceux de la table d'origine. On doit dans ce cas donner un entête de colonne juste après son nom donné par le paramètre liste_Nom_colonne. Cet entête estgénéralement appelé Alias.

 

Exemple1 :On souhaite afficher des colonnes appartenant à la table professeur.Donner les matricules, noms et prénoms de tous les professeurs.

Code SQL :

SELECTmatricule_prof, Nom_prof, Prénom_prof

FROM professeur ; 

 

 

 

Remarque : Les colonnes demandées par la commande SELECT seront affichées avec commeentêtes des colonnes d'affichage les noms des colonnes de la table professeur.

Exemple2 : Donner les caractéristiques de tous les professeurs.

Code SQL :

SELECT *

FROM professeur;

 

 

Remarque : Le symbole (*) implique que toutes les colonnes de la table professeur seront affichées.

 

Exemple 3 : 4Donner les chiffres différents salaires de tous les professeurs. L'affichage d'un montant doit se faire une seule fois en cas d’égalité de certains montants. On utilise la commande DISTINCT pour éliminer les redondances.

Code SQL :

SELECT DISTINCTsalaire_prof

FROM professeur ;

 

 

 

2.2. Recherche de lignes à partir d'une table : Sélection

On appelle sélection l’opération qui consiste à ne récupérer qu’un sous-ensemble des lignes d'une table. Elle ne concerne qu'une seule table de la base de données. La différence par rapport à l'opération de projection c'est que le résultat est composé d'un sous-ensemble de lignes de la table et de toutes ses colonnes. La sélection est souvent combinée avec la projection. Dans ce cas le résultat est un sous ensemble de lignes et de colonnes de cette table.

Syntaxe :

SELECT [DISTINCT] * / liste_Nom_colonne

FROMnom_table

WHERE condition

 

-Le paramètre condition sert à préciser le critère qui doit être vérifié par les lignes à afficher. Cette condition est donnée sous la forme d'une expression logique.

-Si le résultat de l'évaluation de l'expression logique est vrai, pour une ligne, celle-ci fera partie du résultat.

-Dans l'expression logique, on peut utiliser en particulier :

*Les opérateurs de comparaison : =, >, <, >=, <= et <>.

*L'opérateur BETWEEN pour les intervalles de valeurs, bornes incluses.

*L'opérateur IN pour les listes de valeurs.

*L'opérateur IS NULL et IS NOT NULL pour les valeurs indéterminées.

*L'opérateur LIKE pour filtrer une chaine de caractères.

*Les opérateurs logiques : AND, OR et NOT. Exemples

 

Exemple :

On souhaite afficher certaines lignes appartenant à la table professeur.

Donner la liste des professeurs qui ont un salaire qui atteint ou dépasse un montant de 100000 FCFA.

Code SQL :

SELECT *

FROM professeur

WHEREsalaire_prof>= 100000;

Exemple 1

 

Remarque : (*) voudrait dire que toutes les colonnes de la table professeur sont concernées, mais seules les lignes qui vérifient la condition indiquée feront partie du résultat.Remarque

 

Exemple 2 :On demande la même liste que dans  l'exemple précédent sauf qu'on ne s'intéresse qu'aux colonnes relatives aux noms et aux prénoms.

Code SQL :

SELECTnom_prof 'Nom du professeur', prenom_prof 'Prénom du   professeur'

FROM professeur

WHEREsalaire_prof>= 100000;

 

 

3-APPLICATION :

Pour gérer l’établissement, le proviseur du Lycée décide de créer une base de données nommée lycee_2020 contenant les tables suivantes :

-eleves (matricule_eleve, nom_eleve, prenom_eleve, classe)

-enseignants (matricule_ens, nom_ens, prenom_ens, matiere_ens, grade)

-salles (no_salle, capacite, batiment, commentaires)

 

3.1-Charger les données dans les tables

            Après la création de la table, on a besoin de la remplir, les commandes LOAD DATA et INSERT INTO sont utiles.

Syntaxe :

INSERT INTOnom de la table (nom du champ1, nom du champ2, nom du champ3,…)

VALUES (« valeur du champ1 », « valeur du champ2 », « valeur du champ3 », …) ;

Exemple :

Code SQL :

INSERT INTOeleves (matricule_eleve, nom_eleve, prenom_eleve, age, classe, adresse_parents)

VALUES (0001, « nana», « leon », 19,  3, « BP 2310 YAOUNDE  ») ;

INSERT INTOeleves (matricule_eleve, nom_eleve, prenom_eleve, age, classe, adresse_parents)

VALUES (0002, « ada », « paul », 15, 4, « BP 23 YAOUNDE Tel 652838129 ») ;

INSERT INTOeleves (matricule_eleve, nom_eleve, prenom_eleve, age, classe, adresse_parents)

VALUES (0003, « kamga», « David », 13, 3, « BP 123 YAOUNDE Tel 676838129 ») ;

INSERT INTOeleves (matricule_eleve, nom_eleve, prenom_eleve, age, classe, adresse_parents)

VALUES (0004, « ngo », « blandine », 12, 6, « BP 89 DOUALA Tel 698998129  ») ;

NB :

i) Le champ matricule_eleve est l’identifiant (ID), il est auto- incrémenté. MySQL mettra le numéro d’id lui-même. On pouvait même se passer du champ id dans la requête ;

ii) Les chaines de caractères sont mises entre guillemets.

iii) Eviter les caractères accentués.

 

3.2-Recupérer des informations à partir d’une table

            La commande SELECT est utilisée pour récupérer des informations à partir d’une table.

Syntaxe :                                                                              

SELECTnom du champ1, nom du champ2,…FROMnom de la table;

Exemple:

SELECTmatricule_eleve, nom_eleve, prenom_eleve,age,classe,adresse_parents

FROMeleves;

 

matricule_eleve

nom_eleve

prenom_eleve

age

classe

adresse_parents

0001

Nana

Leon

19

3è

BP 2310 Yaoundé

0002

Ada

Paul

15

4è

 BP 23 Yaoundé

Tel 652838129

0003

Kamga

David

13

3è

BP 123 Yaoundé Tel 676838129

0004

Ngo

Blandine

12

6è

BP 89 Douala Tel 698998129

 

NB:Comme on veutafficher la totalité des colnnes,on pouvaitécrire:

SELECT * FROMeleves;

 

Utiliserl’option DISTINCT:

La commande SELECT peut afficher des lignes en doubles. Pour éviter des redondances dans les résultats, il faut tout simplement ajouter DISTINCT après le mot SELECT.

Syntaxe :

SELECT DISTINCT nom du champ FROM nom de la table ;

 

3.3-Modifier les données d’une table

            La requête UPDATE permet de modifier une entrée.

Syntaxe :

UPDATEnom de la table

SET noms des champs à modifier

WHERE critère ;

Exemple :

 Un élève est admis en classe supérieure, il faut modifier la classe et l’âge.

Code SQL :

UPDATEeleves

SETage=16, classe= 3

WHEREmatricule_eleve=0002 ;       

 

 

3.4-Supprimer des données dans une table

La commande DELETE permet de supprimer une entrée.

Syntaxe :

DELETE FROMnom de la table

WHERE condition ;

Exemple : L’élève de matricule 0051 est exclu du lycée, il faut le supprimer.

Code MySQL :

DELETE FROMeleves

WHEREmatricule_eleve=0004 ;

 

NB : WHERE est indispensable pour indiquer quelle(s) entrée(s) doivent être supprimés, sinon toutes les entrées seront supprimées !

 

EXERCICES

 

CONTROLE DE CONNAISSANCES :

1. Définir : base des données, table, attribut,

2. Cite 04 modèles de base de données.

3. Comment appelle-t-on le modèle qui consiste à représenter aussi bien les objets que les liens à l'aide d'une structure appelée table ?

4. Comment appelle-t-on une colonne ou un groupe de colonne permettant d’identifier de façon unique chaque ligne de la table ?

5. Comment appelle-t-on une règle appliquée à une colonne ou à une table qui doit être toujours vérifiée ?

6. Que signifie l’expression « clé étrangère » entre deux tables d’une base de données ? quel est son rôle ?

 

EXERCICE I: Cocher la (les) réponse (s) juste(s)

1) La clé primaire :

a) ne peut être que du type numérique

b) est l’identifiant unique de l’information

c) est une ligne de la table

d) est référencée par zéro ou plusieurs clés étrangères

2) La clé étrangère est :

a) un attribut

b) une colonne de la table qui correspond à la clé primaire d’une autre table

c) un enregistrement

d) un n-uplet

3-Une table créée qui répond aux contraintes d’intégrité,

a) Doit contenir une clé primaire.

b) Contient au moins une colonne.

c) Peut avoir une clé dont les valeurs ne sont pas obligatoires.

d) doit comporter au moins 02 clés étrangères

4-Dans une table, une colonne

a) Peut être calculée ou déduite à partir d’autres colonnes.

b) Doit avoir une taille et un type.

C) est un enregistrement.

d) Décrit les propriétés relatives au sujet représenté par la table.

5-Dans une table, une ligne

a) est aussi appelée attribut

 b) est un enregistrement

c)correspond à une occurrence du sujet (entité)

d) est un n-uplet

6-Une base de données garde les informations

a)    volatiles

b)    permanentes

c)    temporaires

d)    persistantes

EXECICE II : On donne les tables articles et commandes d’une base de données

C ARTICLES

Code_Article                               

 

Désignation article

Prix unitaire            

Quantité en stock

V10

Vis 50x3

40

2500

V20

Vis 20x2

20

1300

B100

Boulon 90x15

450

100

C60

Clou 60x2

5

05000

 

COMMANDES

N°Commande           

Date Commande                   

Reference_article#           

Quantité commandée

 

100

01/03/2006

V10

500

101

15/04/2006

V20

30

102

17/04/2006

B100

120

103

25/04/2006                             

C60

180

 

1-Identifier la clé primaire de la table ARTICLES.

2-Identifier une clé secondaire.

3-Donner une représentation textuelle de ces tables.

4-Donner une représentation graphique de ces tables.

 

EXERCICE  III:

Pour décrire les élèves du lycée inscrits dans le club « musique », la table suivante a été créée :

musique

N°Elève

Nom

Prénom

Date_ de_ naissance

Code_ classe

Désignation

Nombre_ d’élèves

Mat 0090

Maffo

Paul

22/11/90

3M3

3ème moderne3

28

Mat 0055

Ada

Pierre

11/12/88

2nde C

Seconde C

29

Mat 0053

Tene

Marc

30/10/92

4M2

4ème moderne 2

30

Mat 0054

Ngo

Jeanne

01/01/89

3M3

3ème moderne3

30

Mat 0055

Bisseck

Bandine

12/12/90

3M3

3ème moderne3

28

Mat 0056

Kamga

Alice

11/09/87

PD

Première D

30

Mat 0057

Vobam

Adrien

13/12/85

Tle C

Terminale C

30

Mat 0058

Talla

André

14/10/86

Tle C

Terminale C

30

 

1-Identifier les anomalies de cette structure

2-Pour éviter les redondances, proposez une autre façon de structurer cette table. (Représentation textuelle).

EXERCICE IV:

Pour décrire les employés d’une entreprise et leurs répartitions entre les différents

Services, on présente le contenu de deux tables « Employé » et « Service » comme suit :

 

table : Service

Num_service       

Nom_service

 

10

Administratif

 

20   

Financier

30    

Juridique

 

 

Table : Employe

code_emp      

nom prénom  

salaire 

num_serv

123

Abanda paul                     

756,500

 20

426

Tsafack etienne               

987,900

 10

456

Tounsi Léon                    

     400,000             

40

123

    Mbede Jean Paul            

798,322

30

456

    Tounsi Léon

380,500           

20

NB : On suppose qu’un employé est affecté à un seul service

 

Questions :

1) Définir le terme « contrainte d’intégrité » :

2) En se basant sur les contenus de ces tables, il apparait que des contraintes d’intégrité n’ont pas été respectées. Lesquelles ?

3) On désire mettre en place un logiciel permettant d’assurer le respect des contraintes d’intégrité lors de la manipulation d’une base de données.

 a-Comment appelle-t-on ce logiciel

,b- en donner une définition,

 c-citer ses composants

d- ses fonctions.

 

EXERCICE  V:

La base de données d’un complexe des salles de cinéma, contenant des informations sur les films et les séances, est composée des tables suivantes :

FILM (Code_film, Nom_film, Producteur, Année_sortie,)

SALLE (N°Salle, Nom_Salle, Nb_places)

SEANCE (Code_film, N°Salle, Date, Heure_debut,)

1-Identifier pour chaque table la clé primaire.

2-Identifier les liens éventuels entre les tables.

3-Donner la représentation graphique de la base de données finale.

 

EXERCICE VI:

On souhaite créer une base de données concernant une entreprise. Une première étude a mis en évidence trois relations. Pour chacune des relations, la clé est soulignée.

EMPLOYE (NumEmp, Nom, Prénom, Adresse, Téléphone, Qualification)

SERVICE (NomService, Responsable, Téléphone)

PROJET (NumProjet, NomProjet, DateDeb, DateFin, NumEmp)

Travail demandé :

En considérant les possibilités offertes par ce schéma, répondre aux questions suivantes en justifiant vos réponses par rapport à la sémantique intuitive des relations :

1 - Un employé peut-il avoir plusieurs qualifications ?

2 - Un employé peut-il faire plusieurs projets en même temps ?

3 - Une personne peut-elle être responsable de plusieurs services ?

4 - Un service peut-il avoir plusieurs responsables ?

 

EXERCICE VII :

Soit la description textuelle d’une base de données :

Professeur (CodeProf, NomProf, Tel)

Matière (CodeMat, Libellé#, Coef, NbHeure)

Enseigne (CodeProf#, Codematiere#)

Libelle_matiere (libellé)

 

Travail demandé :

1) Déduire à partir de cette description la description graphique de la base.

2) En considérant les possibilités offertes par ce schéma, répondez aux questions suivantes :

2-1 Un professeur peut-il enseigner plusieurs matières ? Justifier

2-2 Une matière possède elle plusieurs libellés ? Justifier

2-3 Une matière peut-elle être enseignée par plusieurs professeurs ? Justifier

 

EXERCICE  VIII:

Soit la table candidat suivante:

Nom

Prénom

Classe

Age

EKASSI

Jean Blaise

6ème 2

10

AKOA

Pierre Paul

Tle C

17

FOTSO

Jean de Dieu

2 C

15

 

1-Donner le résultat de la requête suivante :

SELECT  Nom FROM Liste WHERE Age=17 ;

2-Donner la commande SQL permettant de récupérer les informations concernant FOTSO Jean de Dieu.

3-Comment appelle-t-on ce type de recherche des données ?

 

EXERCICE IX :

Soit la base de données nommée EXAMENS, constituée d’une seule table.

Table : candidats

Code_candidat

Nom

Prénom

Examen

Moyenne

Décision

Etablissement

14OBC001

ELIMBI

Gustave

BAA4

14,42

ADMIS

Lycée d’Akwa

14OBC014

AMADOU

Ibrahim

BAD

10,25

ADMIS

Collège de Mazenod

14OBC094

WAMBA

Paulin

BAA4

9 ,02

REFUSE

Collège Jean Tabi

 

1-Dans un SGBD, comment appelle-t-on le langage utilisé pour établir une interaction entre le logiciel et l’utilisateur.

2-Que signifie le sigle SGBD ? Citer un exemple de logiciel permettant de créer cette base de données.

3-Citer deux avantages que peut procurer l’utilisation d’une base de données.

4-Compléter la requête ci-dessus permettant de sélectionner les établissements des candidats admis :

Select (…………) from …….…..where…………..=…………….. ;

(Bac 2016)

EXERCICE X:

1-Définir :base de données

2-Donner le rôle d’un SGBD et citer 02 exemples

3-On considère la requête suivante :

CREATE TABLE client (Numéro INT(5),  Nom VARCHAR(20), ville VARCHAR(10), Sexe VARCHAR(10))

a-Que fait cette requête ?

b-Présenter le résultat de cette requête sous forme de table sachant que le client numéro 1, Eboutou Yvan, homme, habite la ville de Yaoundé ; le client numéro 2, Embolo Valérie, femme, habite la ville de mbalmayo ; le client numéro 3, Fossi Alain, homme, habite la ville de Bafoussam ; le client numéro 4, Kesseng Laure, femme, habite la ville de Yaoundé.

c-Donner une requête pour récupérer des informations sur Kesseng Laure.

 

EXERCICE IX: Soit la base de données relative à l'exploitation des chambres d'un hôtel définie comme suit :

Chambre (Num_Chambre, Prix, Nbr_Lit, Nbr_Pers, Confort, Equ)

Client (Num_Client, Nom, Prenom, Adresse)

Reservation (Num_Client#, Num_Chambre#, Date_Arr, Date_Dep)

Table Chambre

Num_Chambre

Prix

Nbr_Lit

Nbr_Pers

Confort

Equ

10

80

1

2

WC

Non

20

100

2

2

Douche

Non

25

180

3

3

Bain

TV

 

Table Client

Num_Client

Nom

Prénom

Adresse

1000

Sahli

Mohamed

Sousse

1001

Chatti

Saleh

Tunis

 

Table Réservation

Num_Client

Num_Chambre

Date_Arr

Date_Dép

1000

20

25/12/2006

01/01/2007

1001

10

01/01/2007

 

 

Exprimer les requêtes suivantes en SQL :

1. Les numéros de chambres avec TV.

2. Les numéros de chambres et leurs capacités.

3. La capacité théorique d'accueil de l'hôtel.

4. Le prix par personne des chambres avec TV.

5. Les numéros des chambres et le numéro des clients ayant réservé des chambres

pour le 25/12/2006.

6. Les numéros des chambres coûtant au maximum 80 euros ou ayant un bain et valant au maximum 120 euros.

7. Les noms, prénoms et adresses des clients dont les noms commencent par ≪SA≫

8.Le nombre de chambres dont le prix est entre 85 et 120 euros.

9. Les noms des clients n'ayant pas fixé la date de départ.

 

EXERCICE X:

Soit le code SQL de la table client ci-dessous :

CREATE TABLE client(

code_client       VARCHAR(20) PRIMARY KEY,

nom_client       VARCHAR (20) NOT NULL,

prenom_client  VARCHAR(30),

adr_client         VARCHAR (100) NOT NULL,

tel_client          INT (10),

email_client     VARCHAR (50),

chiffre_affaire_annee_encours     DECIMAL(10,3),

cumul_chiffre_affaire       DECIMAL(12,3)); IPPARTIE A

PARTIE A :

1-Insérer en précisant explicitement toutes les colonnes, le client suivant :

code_client =1000, nom_client=Ndende, prénom_client=Paul, adr_client =BP 123 Yaoundé, tel_client=676 14 00 34, email_client=ndende@gmail.com, chiffre_affaire_annee_encours=0, cumul_chiffre_affaire=0.

2-Refaire la même chose sans préciser explicitement toutes les colonnes.

3-Insérer un client sachant qu’on ne dispose que de valeurs relatives à certaines colonnes de cette table. Ces colonnes sont par exemple : code_client =1020, nom_client=SCB Cameroun et  adr_client=BP 345 Douala.

4-Insérer une ligne au sein de la table client, en précisant explicitement les valeurs relatives à son code =1040,nom=tatio, son prénom=alphonse.

5-Insérer une ligne au sein de la table Client, en précisant explicitement les valeurs relatives à son nom=Socsuba SA et son adresse=BP 2367 Yaoundé.

6-Insérer une ligne au sein de la table Client, en précisant explicitement les valeurs relatives au code client=1040, à son nom=BICEC et à son adresse=BP987 Yaoundé.

7-Modifier le prénom d’Alphonse par Pierre-Alphonse.

PARTIE B :

1-Donner une requête qui permet d’afficher des colonnes appartenant à la table client de : codes, noms et prénoms de tous les clients.

2-Donner une requête qui permet de donner les caractéristiques de tous les clients.

3-Donner une requête qui permet d’afficher les codes, noms et prénoms de tous les clients. Au moment de l'affichage, les entêtes des colonnes doivent être respectivement 'Code du Client', 'Nom du Client' et  'Prénom du Client'.

4-Donner une requête qui permet d’afficher les chiffres d'affaires de l'année en cours de tous les clients.

5-Donner une requête qui permet d’afficher les chiffres d'affaires de l'année en cours de tous les clients. L'affichage d'un montant doit se faire une seule fois en cas d’égalité de certains montants.

6-Donner une requête qui permet d’afficher la liste des clients qui ont un cumul des chiffres d'affaires compris entre 500000F et 800000F.

 

EXERCICE XI : On donne les codes des  tables article et commande suivants :

Table article

CREATE TABLE article(

code_art VARCHAR(20) PRIMARY KEY,

des_art   VARCHAR (50) NOT NULL,

PU          DECIMAL(8, 3) CHECK (PU > 0),

qte_stock INT(5) DEFAULT 0

);

Table commande

CREATE TABLE commande(

num_comm  VARCHAR(20) PRIMARY KEY,

date_comm  DATE NOT NULL,

ref_art            VARCHAR(20) REFERENCESarticle(code_art),

qte_comm     INT(5)

);

PARTIE A :

On suppose, que les commandes suivantes ont été exécutées :
INSERT INTO article VALUES (7050,'Informatique 4ème Année Mathématiques',4000,500);
INSERT INTO article VALUES (1020,'4 Stylos Feutres Pointes Fines : Rouge, Jaune, Vert et Bleu',1500,3000);

INSERT INTO Commande VALUES (50010,'09/05/2007',1010);
INSERT INTO Commande VALUES (70150,'20/07/2007',1000);

1-Donner une requête qui permet d’afficher la liste des produits et pour chacun, calculer la valeur du stock.

2-Donner une requête qui permet d’afficher le numéro et la date des commandes d’un article donné.

 

PARTIE B :

1-Donner une requête qui permet d’afficher le nombre d’articles dont la quantité en stock est supérieure à 40.

2-Créer une nouvelle colonne valeur_stock.

3-Donner une requête qui permet d’afficher la somme des quantités en stock de tous les articles.

3-Pour l'ensemble des articles, donner une requête qui permet d’afficher la plus grande et la plus petite quantité en stock ainsi que la moyenne des quantités en stock.

 

CORRIGES

CONTROLE DE CONNAISSANCES :

1.- Une base de données est une collection de données structurées et organisées qui, après avoir été saisies une seule fois, sont accessibles aux différents utilisateurs.

- Une table est un ensemble de données relatives à un même sujet (ou entité) et structurées sous forme de tableau.

 - Dans une table, un attribut est une colonne qui correspond à une propriété élémentaire de l'objet décrit par cette table. On l’appelle aussi. Champ.

2. Modèle hiérarchique, Modèle en réseau, Modèle orienté objet, Modèle relationnel.

3. C’est le modèle relationnel.

4. C’est la clé primaire.

5. C’est une contrainte d’intégrité.

6. C’est une colonne d’une table fille qui se trouve être clé primaire dans une autre table dite table mère.

Son rôle est d’assurer le lien entre les deux tables.

EXERCICE I :1.b et d ; 2.a et b ; 3a et b ; 4b et d ; 5.b, c et d ; 6.b et d ;

EXECICE II :

1.Code_article

2. Reference_article#           

3. ARTICLE (Code_Article,Désignation article,Prix unitaire,Quantité en stock),                              

COMMANDES (N°Commande ,   Date Commande , reference_article#  ,Quantité commandée)

4.

EXERCICE III :

1-Incohérences :

- Pour la classe de 3M3, le nombre d’élèves diffèrent pour les élèves N° 1, 4,5.

-le matricule Mat0055 est attribué à deux élèves différents ada et bisseck.

Redondance : lorsque plusieurs élèves font la même classe, il y a beaucoup de répétitions qui occupent inutilement la place dans les supports de stockage.

Transitivité :il n’y a pas dépendance directe entre le numéro de. L’élève (clé primaire) et le nombre d’élèves par exemple

2-Il faut éclater cette table en deux

      ELEVES (N°Elève,Nom,Prénom,Date de naissance, code_Classe)

      CLASSE (Code_Classe ,Désignation,Nombre_d’eleves)

EXERCICE IV:

1.

2.Le service 40 n’est pas créé, l’employé 456 et 123 se trouvent deux fois chacun dans la clé primaire , Tounsi est dans deux services différents .

3.a-le système de gestion des bases de données

b-Le SGBD est constitué de deux composantes principales : un moteur et une interface.

c-Un SGBD a pour rôle de :

-Permettre l’accès aux données.

-Autoriser un accès aux informations à des multiples utilisateurs.

-Manipuler les données présentes dans la base de données (insertion, suppression, modification, etc.)

Il permet ainsi à l'utilisateur de manipuler une ou plusieurs bases de données dans des termes abstraits, sans tenir compte de la façon dont l'ordinateur les maintient.

d-Fonctions de définition d’un SGBD, Fonctions de manipulation des données  et Fonctions de contrôle des données.

 

EXERCICE V:

1-Code-film pour la table FILM

  -N°Salle pour la table SALLE

 -Code_film, N°Salle, pour la table SEANCE (ici la clé primaire est composée)

 

2. Liens entre les tables

- FILM → est lié à SEANCE par Code_film (clé étrangère dans SEANCE).

- SALLE → est lié à SEANCE par N°Salle (clé étrangère dans SEANCE). 

→ Cela forme deux relations 1,n :

  - Un film peut avoir plusieurs séances.

  - Une salle peut accueillir plusieurs séances

3.

EXERCICE VI:

1-Non

//La colonne Qualification est un attribut simple de la table EMPLOYE, donc un seul champ par employé. 

→ Si un employé pouvait avoir plusieurs qualifications, on aurait une relation à part comme : 

EMPLOYE_QUALIFICATION (NumEmp, Qualification)

 

2 – non

//Dans PROJET, il y a un champ NumEmp, ce qui signifie que chaque projet est affecté à un seul employé, mais un employé n’est pas lié à plusieurs projets dans ce sens. 

→ Pour gérer plusieurs employés sur un projet, on aurait une table intermédiaire : 

PARTICIPATION(NumEmp, NumProjet)

3 –oui : 

//Dans la table SERVICE, le champ Responsable (probablement une référence à NumEmp) n’est pas unique, donc un même NumEmp peut apparaître dans plusieurs lignes. 

→ Donc un même employé peut être responsable de plusieurs services.

4 – non

//Le champ Responsable est un attribut unique par service, donc un seul responsable par service est prévu. 

→ Pour gérer plusieurs responsables, il faudrait une relation : 

RESPONSABILITE(NumEmp, NomService)

 

 

 

EXERCICE VII :

1)

2)

 2-1 oui

 

//L’association Enseigne (CodeProf, CodeMat) permet à un même CodeProf d’apparaître avec plusieurs CodeMat → un prof peut enseigner plusieurs matières

     2-2 non

//Le champ Libellé est un attribut de Matière, donc chaque matière à un seul libellé.

 La ligne "Libelle_matiere(libellé)" semble redondante ou confuse, à moins qu’elle soit une entité séparée (ce qui n’est pas clair ici).

      2-3 oui

//L'association Enseigne permet que plusieurs CodeProf soient liés à un même CodeMat. 

Donc plusieurs professeurs peuvent enseigner la même matière.

 

EXERCICE VIII :

1-AKOA

2-SELECT (nom, prenom, classe, age) FROM candidat WHERE nom=”Fotso” ;

3- Recherche de lignes à partir d'une table: Sélection

EXERCICE IX :

 1- Langage de manipulation des données (LMD)

2-Système de Gestion des Bases de Données,

Access, MySQL, Oracle, SQL server, Inter base, Sybase, Postgres SQL, etc.

3-Avantages :-les données ne sont pas redondantes

-       Les données sont structurées

-       L’accès distant aux données par réseau ou Cloud

-       L’accès aux mêmes données par plusieurs utilisateurs simultanément

-       Contrôler l’intégrité entre plusieurs utilisateurs et la confidentialité des données

-       Assurer l’indépendance entre les données et les traitements

-       Assurer la sécurité des accès aux données

-       La possibilité d’actualiser les données

4-SELECT (Etablissement) FROM EXAMENS WHERE Décision= « ADMIS » ;

EXERCICE X :

1- Une base de données est une collection de données structurées et organisées qui, après avoir été saisies une seule fois, sont accessibles aux différents utilisateurs.

2-Rôles :-Permettre l’accès aux données.

-Autoriser un accès aux informations à des multiples utilisateurs.

-Manipuler les données présentes dans la base de données (insertion, suppression, modification, etc.)

Exemples : Access, MySQL, Oracle, SQL server, Inter base, Sybase, Postgres SQL, etc.

 

3-a) Cette requête crée une table nommée client contenant les colonnes Numero, Nom, ville et Sexe.

b)client

Numéro

Nom

Ville

Sexe

1

Eboutou Yvan

Yaoundé

homme

2

Embolo Valerie

Mbalmayo

Femme

3

Fossi   Alain

Bafoussam

homme

4

Kesseng Laure

Yaounde

femme

c)SELECT*FROM client WHERE Nom=”Kesseng Laure” ;

EXERCICE VIII:

1. Select Num_Chambre

From Chambre

Where Confort = 'TV' ;

2. Select Num_Chambre, Nbr_Pers

From Chambre ;

3. Select Sum(Nbr_Pers)

From Chambre ;

4. Select Prix/Nbr_Pers, Num_Chambre

From Chambre

Where Equ = 'TV'

5. Select Num_Chambre, Num_Client

From Réservation

Where Date_Arr <= '25/12/2006'

And (Date_Dep > '25/12/2006' Or Date_Dep is Null)

6. Select Num_Chambre

From Chambre

Where Prix<='80' Or (Confort='bain' And Prix <='120');

7. Select Nom

From Client

 Where Nom like 'SA%';

8. Select Count(Num_Chambre)

From  Chambre

Where Prix between 85 and 120;

9. Select Nom

From Reservation R, Client C Where Date_Dep is Null And R.Num_Client = R. Num_

EXERCICE IX:A

PARTIE A :

1-INSERT INTO client

(code_client, nom_client, prenom_client, adr_client, tel_client, email_client, chiffre_affaire_année_encours, cumul_chiffre_affaire)

VALUES (1000,'NDENDE', 'Paul','BP 123 Yaoundé',

676 14 00 34, ‘ndende@gmail.com‘, 0, 0);

Remarque : il faut tenir compte de l’ordre des colonnes.

2-Compte tenu du fait que chaque colonne de la table Client, reçoit une valeur et que l'ordre de ces valeurs est le même que celui des colonnes, on aurait pu dans ce cas omettre de préciser ces colonnes. La commande devient :

INSERT INTO Client

VALUES (1000,'NDENDE', 'Paul','BP 123 Yaoundé',

676 14 00 34, ‘ndende@gmail.com ‘, 0, 0);

3-INSERT INTO client (nom_client, code_client, adr_client)

VALUES ('SCB Cameroun',1020, 'BP 345 Douala');

Remarque : Les colonnes, de la table Client, qui sont absentes ne sont soumises à aucune des contraintes d'intégrité. Ces colonnes recevront une valeur NULL au moment de l'insertion de la ligne.

4-. INSERT INTO client (nom_client, prenom_client, code_client)

VALUES ('Tatio','Alphonse',1040);Remarque

Remarque : Cette commande ne peut pas être exécutée à cause de la contrainte d'intégrité

NOT NULL pour la colonne Adr_client qui n'a pas été respectée ici.

5- INSERT INTO client (nom_client, adr_client)

VALUES ('Socsuba SA','BP 2367 Yaoundé’);

Remarque : Cette commande ne peut pas être exécutée à cause de la contrainte d'intégrité

PRIMARY KEY qui est violée. Les attributs constituant la clé primaire doivent obligatoirement être présents.

6- INSERT INTO client (nom_client, code_client, adr_client)

VALUES ('BICEC’, 1040, 'BP 987 Yaoundé');

Remarque : Cette commande ne peut pas être exécutée à cause de la contrainte d'intégrité d'unicité de la clé primaire qui n'a pas été respectée. Le code client 1040 à été déjà attribué.

7- UPDATE client

SET prenom_client= « Pierre-Alphonse »

WHERE code_client=1040 ;

 

PARTIE B :

1- SELECT code_client, nom_client, prenom_client

FROM client ; 

2- SELECT *FROM client ;

3- SELECT code_client 'Code Client', nom_client 'Nom Client',prenom_client 'Prénom Client'

FROM client;

4- SELECT chiffre_affaire _annee_encours

FROM client;

5- SELECT DISTINCT chiffre_affaire _année_encours

FROM Client;E

6- SELECT *FROM Client

WHERE cumul_chiffre_affaires

BETWEEN 500000 AND 800000;

EXERCICE X :

PARTIE A :

1- SELECT des_art 'Désignation Produit', PU 'Prix Unitaire', Qte_stock 'Quantité Stock', PU*Qte_stock 'Valeur Stock'

FROM Article;xemple 6marque

Remarque : La colonne résultat, relative à la valeur du stock, sera calculée à partir du prix unitaire et la quantité en stock.

2- SELECT c.num_comm, date_comm

FROM commande c, article a

WHERE (c.ref_art = a.code_art);

3-. SELECT C.num_comm, date_comm, Qte_comm, des_art
FROM Commande C, détail_commande D, article A
WHERE (C.num_comm=D.num_comm) AND (D.code_art=A.code_art);

PARTIE B :

1-SELECT COUNT(*)

FROM article

WHERE qte_stock > 1000 ;

2-. SELECT SUM (qte_stock)

FROM article ;

3-SELECT MIN (qte_stock) 'stock Min',

MAX (qte_stock) 'stock Max',

AVG (qte_stock) 'stock Moyen'

FROM article;