Docstoc

AUTOMATE PROGRAMMABLE INDUSTRIEL INITIATION

Document Sample
AUTOMATE PROGRAMMABLE INDUSTRIEL INITIATION Powered By Docstoc
					IUT de NICE – Dépt GEII                                                                         Bases de Données – MC-II5




                                                 TD : Langage SQL


Partie 1 : Les commandes de type DDL

Organisation :
On travaille avec Microsoft Access 2003 :
    - Démarrer / Tous les programmes / Microsoft Office / Microsoft Office Access 2003
    - Menu Fichier / Nouvelle base de données
    - Dans la fenêtre "Nouveau fichier" ….. sélectionner "Base de données vide"
    - Enregistrer sous ……. Z: \ MCII5 \ TD_SQL \ Tests_SQL_Access.mdb
        Enregistrer
    - Sélectionner l'objet "Requêtes"
    - Sélectionner la commande "Créer une requête en mode Création"
    - Fermer la fenêtre "Afficher la table" (inutile, car aucune table n'existe encore dans notre base)
    - Agrandir la fenêtre "Microsoft Access – [Requête1 : Requête Sélection]"
On va écrire directement nos requêtes en langage SQL donc …… :
    - Menu Affichage / Mode SQL
On peut alors saisir l'instruction SQL dans la fenêtre de travail
    Pour exécuter cette instruction :
    -   Menu Requête / Exécuter ……. ou directement l'icône

On peut évidemment sauvegarder chacune des requêtes SQL que nous allons tester, mais je vous conseille plutôt, afin d'avoir une
trace exploitable du travail réalisé :
     o Soit de faire un compte-rendu dans lequel vous notez l'instruction (une fois qu'elle a été mise au point)
     o Ou mieux, de réaliser un copier/coller de l'instruction SQL de la fenêtre "Microsoft Access – [Requête1 : ……. ]
         dans un fichier "Corrigé du TD SQL" édité avec WordPad (par exemple)

Exo : Créer une table nommée "Contact" et comportant une colonne (champ) nommée "Nom" de
type chaîne de 50 caractères maxi
    -   Déterminer l'instruction SQL nécessaire
    -   Exécuter l'instruction SQL
    -   Corriger (éventuellement) l'instruction SQL jusqu'à ne plus obtenir de message d'erreur
    -   Copier cette commande ( Ctrl + c )
    -   Vérifier :
            o Activer la fenêtre "Tests_SQL_Access : Base de données ………."
            o Sélectionner l'objet "Tables"
            o Eventuellement : Menu Affichage / Actualiser les tables ou touche de fonction F5
            o Constater qu'une table "Contact" a bien été créée
             o   Vérifier sa forme : Clic sur le bouton de commande

    -   Fermer la table "Contact"
    -   Réactiver la fenêtre "Requête 1 : …………"
    -   Relancer une seconde fois la commande SQL précédente (refaire la manip décrite ci-dessus)
    -   Constatation ?
    -   Coller ( Ctrl + v ) cette première commande SQL dans un fichier "Corrigé du TD SQL" en la commentant, c'est-à-dire
        en expliquant sa fonction (il suffit de noter le titre de l'exo ….. tout simplement)

La démarche est similaire pour tous les exercices à suivre ……………
CREATE TABLE Contact
(Nom VARCHAR(50)) ;

Exo : Supprimer la table "Contact" ……… par une instruction SQL ……. of course … !
DROP TABLE CONTACT


TD SQL                                                                                                                            1
Exo : Créer la table "Contact" avec les caractéristiques suivantes :
    -   Champ "Nom" – Chaîne de 50 caractères maxi – Clé primaire
CREATE TABLE Contact
(Nom VARCHAR(50) PRIMARY KEY)                        ;


Exo : Créer la table "Contact" avec les caractéristiques suivantes :
    -   Champ "Num" – Numéro Automatique
CREATE TABLE Contact
(Num COUNTER) ;


Exo : Créer la table "Contact" avec les caractéristiques suivantes :
    -   Champ "Nom" – Chaîne de 50 caractères maxi – Saisie obligatoire (ne peut être NULL)
CREATE TABLE Contact
(Nom VARCHAR(50) NOT NULL)                     ;


Exo : MODIFIER la table "Contact" pour :
    - Rajouter le champ "Prénom" – Chaîne de 50 caractères maxi
    Puis
    - Rajouter le champ "BirthDay" – Type "Date"
    Puis
    - Rajouter une clé primaire sur le champ "Nom"
    Puis
    - Supprimer le champ "BirthDay"
ALTER TABLE Contact
ADD Prenom VARCHAR(50)                  ;
    Puis
    - Rajouter le champ "BirthDay" – Type "Date"
ALTER TABLE Contact
ADD BirthDay DATE ;
    Puis
    - Rajouter une clé primaire sur le champ "Nom"
ALTER TABLE Contact
ADD PRIMARY KEY(Nom) ;
    Puis
    - Supprimer le champ "BirthDay"
ALTER TABLE Contact
DROP BirthDay ;


Exo : Créer la table "Telephone" avec les caractéristiques suivantes :
    -   Champ "Num" – Numéro Automatique
    -   Champ "Contact" – Chaîne de 50 caractères maxi – Saisie obligatoire (ne peut être NULL)
    -   Champ "Tel" – Chaîne de 15 caractères maxi – Saisie obligatoire (ne peut être NULL)
CREATE TABLE Telephone
( Num COUNTER ,
  Contact VARCHAR(50) NOT NULL ,
  Tel VARCHAR(15) NOT NULL ) ;


Exo : Créer une RELATION entre la clé primaire de la table "Contact" et le champ "Contact" de la
table "Telephone"
ALTER TABLE Telephone
ADD FOREIGN KEY (Contact) REFERENCES Contact (Nom);



2                                                                                                 TD SQL
Partie 2 : Les commandes de type DML

Exo : Créer un enregistrement dans la table "Contact" de caractéristiques :
Nom = Boksonaz             Prenom = Terry
INSERT INTO Contact (Nom,Prenom)
VALUES('Boksonaz','Terry') ;


Exo : Modifier le champ "Prenom" de l'enregistrement précédent :
Prenom = Merry
UPDATE Contact
SET Prenom = 'Merry'
WHERE Nom='Boksonaz'


Exo : Effacer l'enregistrement ayant pour prénom :
Prenom = Merry
DELETE FROM Contact
WHERE Prenom='Merry'


Questions :
    -    Que se passe t-il lorsque l'on supprime le dernier enregistrement de la table ?
         Rien, la table est vide mais non supprimée
    -    Que se passe t-il si l'on oublie la clause WHERE dans une instruction DELETE ?
         On efface TOUS les enregistrements de la table à laquelle s'applique l'instruction DELETE !
    -    Que se passe t-il si l'on oublie la clause WHERE dans une instruction UPDATE ?
         On modifie TOUS les enregistrements de la table à laquelle s'applique l'instruction UPDATE !
    -    Les valeurs des champs sont-elles "sensibles à la casse" ?
         NON 'Merry' est similaire à 'merry'

Nous allons maintenant utiliser des requêtes SQL commençant par la clause SELECT.
Afin de travailler sur des tables possédant un nombre significatif d'enregistrements, récupérer le projet Scolarite_v1.mdb sur le
serveur Poseidon

Synoptique des tables Etudiant, Devoir, Note :

   ETUDIANT                                       DEVOIR                                            NOTE
  Nom               (VARCHAR(50))               Num               COUNTER                       Num               COUNTER
  Prenom            (VARCHAR(50))               Nom               (VARCHAR(10))                 Devoir            (VARCHAR(10))
                                                Matiere           (VARCHAR(20))                 Etudiant          (VARCHAR(50))
                                                Coef              NUMERIQUE /                   Note              NUMERIQUE /
                                                                  REEL SIMPLE                                     REEL SIMPLE

Remarques :       Ces 3 tables ne sont pas reliées entre-elles
                  Elles ne constituent pas une base de données cohérente
                  Elles nous servent juste à tester les différentes requêtes SQL

Exo : Sélectionner et visualiser tous les enregistrements de la table "Etudiant"
    -    On conçoit évidemment la requête en mode SQL (c'est le but du TD d'apprendre ce langage …….)
    -    On visualise le résultat dans une feuille de données
SELECT * FROM ETUDIANT                      ;




TD SQL                                                                                                                              3
Exo : Sélectionner et visualiser les colonnes "Devoir" et "Matiere" de tous les enregistrements de la
table "Devoir" dont la matière correspond à "Math"
SELECT Nom,Matiere
FROM Devoir
WHERE Matiere = 'Math' ;


Exo : Sélectionner et visualiser les colonnes "Note" "Nom" et "Devoir" de tous les enregistrements de
la table "Note" dont la note est supérieure ou égale à 10.5
SELECT Etudiant, Devoir,Note
FROM [Note]
WHERE Note >= 10.5 ;
Remarque : il faut encadrer le texte Note par des [ ] …. car Not ….. est un mot réservé de SQL

Exo : Sélectionner et visualiser les colonnes "Note" "Nom" et "Devoir" de tous les enregistrements de
la table "Note" dont la note est comprise entre 8.5 et 15.5
SELECT Etudiant, Devoir, Note
FROM [Note]
WHERE Note BETWEEN 8.5 AND 15.5 ;


 Nous allons maintenant travailler sur les regroupements
Exo : Regroupement des notes d'un étudiant, calcul d'une moyenne :
Afficher à partir de la table "Note" :
    - la moyenne de toutes les notes d'un étudiant
    - le nom de l'étudiant
SELECT Etudiant , AVG(Note) as MOY
FROM [Note]
GROUP BY Etudiant ;


Exo : Regroupement des notes d'un étudiant, calcul de valeurs minimale/maximale :
Afficher à partir de la table "Note" :
    - la note minimale de toutes les notes d'un étudiant
    - la note maximale de toutes les notes d'un étudiant
    - le nom de l'étudiant
SELECT Etudiant , MAX(Note) as Maxi , MIN(Note) as Mini
FROM [Note]
GROUP BY Etudiant ;


Exo : Déterminer le nombre de devoir des différentes matières :
Afficher à partir de la table "Devoir" :
    - le nombre de devoir de chaque matière
    - le nom de la matière
SELECT Matiere, COUNT(Devoir) as NB
FROM Devoir
GROUP BY Matiere ;


Exo : Déterminer le nombre de devoir de la matière 'Math'
SELECT Matiere, COUNT(Nom) as NB
FROM Devoir
WHERE Matiere = 'Math'
GROUP BY Matiere ;

4                                                                                               TD SQL
Exo : Déterminer le nombre de devoir des différentes matières, n'afficher que les matières ayant un
nombre de devoir supérieur ou égale à 3
SELECT Matiere, COUNT(Nom) as NB
FROM Devoir
GROUP BY Matiere
HAVING COUNT(Nom) >= 3 ;


Exo : Déterminer la somme des coefficients des devoirs d'une matière
Afficher à partir de la table "Devoir" :
    - la somme des coefficients des devoirs d'une même matière
    - le nom de la matière
SELECT Matiere, SUM(Coef) as Total
FROM Devoir
GROUP BY Matiere ;


 Nous allons maintenant travailler sur les jointures
Exo : Etablir l'équi-jointure représentée ci-dessous
    -    On souhaite obtenir tous les enregistrements dont les champs ETUDIANT.Nom et NOTE.Etudiant sont égaux
    -    Afficher Nom de l'étudiant, Note du devoir, Nom du devoir
    -    Réaliser cette équi-jointure de 2 manières différentes (en SQL1 sans la clause INNER JOIN puis en SQL2/3)
    -    Vérifier que l'étudiant de nom "Nom13" (qui n'a pas de notes), n'apparaît pas dans la feuille de données résultat de la
         requête


        ETUDIANT                                                                           NOTE
    Nom                (VARCHAR(50))                                                   Num               COUNTER
    Prenom             (VARCHAR(50))                                                   Devoir            (VARCHAR(10))
                                                                                       Note              Numé / Réel simp
                                                                                       Etudiant          (VARCHAR(50))

Remarques :
   - Lorsque la jointure est réalisée par une instruction SQL versions 2 ou 3 (utilisation de la clause INNER JOIN), vous
      pouvez visualiser graphiquement la jointure réalisée dans la fenêtre de l'assistant graphique de requête
           o Pour voir la requête en SQL :                  Menu Affichage / Mode SQL
           o Pour voir les résultats de la requête :        Menu Affichage / Mode Feuilles de données
           o Pour voir graphiquement la jointure :          Menu Affichage / Mode création

    -    Dans le mode création on peut obtenir davantage de renseignements sur le type de jointure :
            o Clic droit sur la jointure
            o Propriétés de la jointure (jointure gauche, jointure droite ou équi-jointure)
SELECT Etudiant.Nom , Note.Devoir, Note.Note
FROM Etudiant, [Note]
WHERE Etudiant.Nom = Note.Etudiant ;

SELECT Etudiant.Nom , Note.Devoir, Note.Note
FROM Etudiant INNER JOIN [Note] ON Etudiant.Nom = Note.Etudiant                                                           ;

Exo : Etablir la jointure gauche représentée ci-dessus
    -    On souhaite obtenir TOUS les enregistrements de la table ETUDIANT ET les enregistrements de la table NOTE dont
         les champs NOTE.Etudiant et ETUDIANT.Nom sont égaux
    -    Afficher Nom de l'étudiant, Note du devoir, Nom du devoir
    -    Vérifier que l'étudiant de nom "Nom13" (qui n'a pas de notes), apparaît dans la feuille de données résultat de la requête
         (dans cet enregistrement seul le champ Nom est renseigné, les champs Devoir et Note sont vides)
SELECT Etudiant.Nom , Note.Devoir, Note.Note
FROM Etudiant LEFT JOIN    [Note] ON Etudiant.Nom = Note.Etudiant                                                           ;
TD SQL                                                                                                                               5
Exo : Etablir les équi-jointures représentées ci-dessous
    -    On souhaite obtenir tous les enregistrements dont les champs
             o ETUDIANT.Nom                  et       NOTE.Etudiant sont égaux
             o DEVOIR.Nom                    et       NOTE.Devoir sont égaux
    -    Afficher Nom de l'étudiant, Note du devoir, Nom du devoir
    -    Réaliser ces équi-jointeurs de 2 manières différentes (en SQL1 sans la clause INNER JOIN puis en SQL2/3)


         DEVOIR
    Num               COUNTER                                                         NOTE
    Devoir            (VARCHAR(10))
    Matiere           (VARCHAR(20))                                               Num              COUNTER
    Coef              Numé / Réel simp                                            Devoir           (VARCHAR(10))
                                                                                  Note             Numé / Réel simp
                                                                                  Nom              (VARCHAR(50))
        ETUDIANT
    Nom               (VARCHAR(50))
    Prenom            (VARCHAR(50))


SELECT Note.Devoir, Note.Etudiant, Note.Note
FROM Etudiant INNER JOIN ( Note]
                             INNER JOIN Devoir
                             ON Note.Devoir = Devoir.Nom )
               ON Etudiant.Nom = Note.Etudiant ;
OU ENCORE
SELECT Note.Devoir, Note.Etudiant, Note.Note
FROM Devoir INNER JOIN ( [Note]
                          INNER JOIN Etudiant
                          ON Etudiant.Nom = Note.Etudiant )
            ON Note.Devoir = Devoir.Nom   ;

OU ENCORE
SELECT            Note.Devoir, Note.Etudiant, Note.Note
FROM              [Note], Devoir, Etudiant
WHERE             (((Note.Devoir)=[Devoir].[Nom]) AND
                  ((Note.Etudiant)=[Etudiant].[Nom]));


Exo : Dans le schéma ci-dessus, afficher les notes de mathématiques de l'étudiant de nom 'Nom1'
SELECT Note.Devoir, Note.Etudiant, Note.Note
FROM Etudiant INNER JOIN ( [Note]
                                         INNER JOIN Devoir
                                         ON Note.Devoir = Devoir.Nom )
                   ON Etudiant.Nom = Note.Etudiant
WHERE ETUDIANT.Nom = 'Nom1' AND Devoir.Nom LIKE 'M*' ;


Exo : Dans le schéma ci-dessus, calculer la somme des notes supérieures à 2, de l'étudiant de nom
'Nom1'
SELECT Note.Etudiant, SUM(Note.Note) as Total
FROM Etudiant INNER JOIN ( [Note]
                                           INNER JOIN Devoir
                                           ON Note.Devoir = Devoir.Nom )
                   ON Etudiant.Nom = Note.Etudiant
WHERE Note.Note > 2
GROUP BY Note.Etudiant
HAVING Note.Etudiant = 'Nom1' ;
6                                                                                                                   TD SQL

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:39
posted:9/24/2011
language:French
pages:6