Travaux pratiques 2 : SGBDR - krizkardiak

Travaux pratiques 3 : SGBDR.
(select ... from... where)
Objectif : - exécuter un script SQL
- Apprendre progressivement le SQL et la construction des
requêtes en SQL
(cet apprentissage se fera par plusieurs travaux pratiques) Qu'est ce qu'un fichier script ?
Un script est un fichier texte qui contient une séquence d'instructions
SQL.
Ce fichier doit être ASCII pur. (Il ne peut pas provenir d'un traitement de
texte qui injecte des caractères de mise en page)
Chaque instruction est séparée d'un point virgule.
Si on souhaite indiquer un commentaire, on l'entoure ainsi : /*
...commentaire.......*/
Exécuter un fichier script produit le même effet qu'écrire et exécuter
successivement chacune de ses instructions en mode commande.
( Observez le script (création et insertion de données) à la page suivante. Quelques remarques générales à propos des scripts.
1. Les erreurs présentes dans un script sont signalées à son exécution.
Seules les instructions qui ont généré ces erreurs ne sont pas exécutées. Si vous relancer dès lors le script après avoir corrigé ces erreurs, vous
engendrez de nouvelles erreurs puisque vous tentez d'exécuter à nouveau
les instructions qui avaient correctement été exécutées la première fois.
2. Vous pouvez extraire un script d'une base de données existante via
'utilitaires', Générer DDL', 'Créer un script',...... Voici le Script de création et d'insertion de données d'une base de données
:
CREATE TABLE J (ID_J CHAR(4) NOT NULL,
JNAME char(20),
CITY char(10),
constraint pk_J PRIMARY KEY (ID_J)); CREATE TABLE P (ID_P CHAR(4) NOT NULL,
PNAME char(20),
COLOR char(8),
WEIGHT integer,
CITY char(10),
constraint pk_P PRIMARY KEY (ID_P)); CREATE TABLE S (ID_S CHAR(4) NOT NULL,
SNAME char(20),
STATUS CHAR(2),
CITY char(10),
constraint pk_S PRIMARY KEY (ID_S)); CREATE TABLE SPJ (ID_S CHAR(4) NOT NULL,
ID_P CHAR(4) NOT NULL,
ID_J CHAR(4) NOT NULL,
QTY integer,
DATE_DERNIERE_LIVRAISON date,
constraint pk_SPJ PRIMARY KEY (ID_S, ID_P, ID_J));
constraint pk_SPJ_S FOREIGN KEY (ID_S) REFERENCES S(ID_S);
constraint pk_SPJ_P FOREIGN KEY (ID_P) REFERENCES P(ID_P);
constraint pk_SPJ_J FOREIGN KEY (ID_J) REFERENCES J(ID_J); /* chargement de la table S */
insert into S values('S1','Smith','20','London');
insert into S values('S2','Jones','10','Paris');
insert into S values('S3','Blake','30','Paris');
insert into S values('S4','Clark','20','London');
insert into S values('S5','Adams','30','Athens'); /* chargement de la table P */ insert into P values('P1','Nut','Red',12,'London');
insert into P values('P2','Bolt','Green',17,'Paris');
insert into P values('P3','Screw','Blue',17,'Rome');
insert into P values('P4','Screw','Red',14,'London');
insert into P values('P5','Cam','Blue',12,'Paris');
insert into P values('P6','Cog','Red',19,'London'); /* chargement de la table J */
insert into J values('J1','Sorter','Paris');
insert into J values('J2','Display','Rome');
insert into J values('J3','OCR','Athens');
insert into J values('J4','Console','Athens');
insert into J values('J5','RAID','London');
insert into J values('J6','EDS','Oslo');
insert into J values('J7','Tape','London');
/* chargement de la table SPJ */
insert into SPJ values('S1','P1','J1',200,'05-10-2001');
insert into SPJ values('S1','P1','J4',700,'10-05-2001');
insert into SPJ values('S2','P3','J1',400,'20-05-2001');
insert into SPJ values('S2','P3','J2',200,'30-07-2000');
insert into SPJ values('S2','P3','J3',200,'10-05-2001');
insert into SPJ values('S2','P3','J4',500,'03-10-2001');
insert into SPJ values('S2','P3','J5',600,'20-09-2001');
insert into SPJ values('S2','P3','J6',400,'12-05-2000');
insert into SPJ values('S2','P3','J7',800,'23-08-2001');
insert into SPJ values('S2','P5','J2',100,'23-06-2000');
insert into SPJ values('S3','P3','J1',200,'07-07-2001');
insert into SPJ values('S3','P4','J2',500,'18-05-2001');
insert into SPJ values('S4','P6','J3',300,'10-05-2001');
insert into SPJ values('S4','P6','J7',300,'16-09-2001');
insert into SPJ values('S5','P2','J2',200,'10-11-2001');
insert into SPJ values('S5','P2','J4',100,'17-04-2001');
insert into SPJ values('S5','P5','J5',500,'08-02-2001');
insert into SPJ values('S5','P5','J7',100,'25-06-2001');
insert into SPJ values('S5','P6','J2',200,'09-02-2001');
insert into SPJ values('S5','P1','J4',100,'18-03-2000');
insert into SPJ values('S5','P3','J4',200,'19-05-2001');
insert into SPJ values('S5','P4','J4',800,'10-05-2001');
insert into SPJ values('S5','P5','J4',400,'16-12-2001');
insert into SPJ values('S5','P6','J4',500,'10-10-2001');
commit;
Création et exécution d'un fichier script (avant propos.
Ce premier travail consiste à créer la base de données qui servira pour
tous vos travaux pratiques.
Créez un utilisateur FOURNISSEUR dont le mot de passe est 'rapide'
Loguez vous maintenant comme FOURNISSEUR.
Un script est exécuté depuis 'SQL',Script SQL', 'Telécharger vers
l'amont',.....
Si l'exécution du script ne se déroule pas correctement, un message
comportant les erreurs s'affiche.
Ces messages sont souvent peu explicites mais guident quand même un peu la
correction de l'erreur. ( Travail 1:
Exécutez le script mis à votre disposition et vérifier que tout c'est bien
passé en exécutant
select * from S.
Vous devrez voir apparaître les contenus suivants : La suite du travail se présente sous forme de tutoriel.
Vous êtes invité à lire les informations données et à taper les
instructions mises en italique. N'oubliez pas d'éxcuter chaque instruction une par une. SELECT ... FROM Select liste des champs à afficher from la table ou jointure de
table où prendre ces champs .
Exemples : 1) affiche la colonne sname de la table s
construction logique: S[sname]
implementation sql : Select sname from s
2) affiche les colonnes sname et city de la table S
S[sname,city] Select sname,city from s 3) affiche la colonne sname en l'appelant nom
S [sname]
Select sname nom from s
4) afficher les noms des pièces et les quantités qui ont chaque fois étés
livrés.
(P join SPJon p.id_p =spj.id_p )[pname,qty]
select pname, qty from p join spj on p.id_p= spj.id_p Remarque : quand les noms de champs sont identiques, on doit
obligatoirement préfixer les noms des champs par les noms des tables. 5) Affichez les villes des fournisseurs
S[city]
Select city from S
Ou
select distinct city from S /* distinct évite les affichages de
doublons */
( Travail 2: Imaginez mentalement ce que l'instruction affichera et exécutez la ensuite
pour vérifier ? 1. Select * from J
2. Select sname city from S
3. Select sname nom,city ville from S
4. select sname nom ,id_p,id_j,qty from s join spj on s.id_s=spj.id_s
Exercice 2 : On souhaite obtenir l'affichage des livraisons avec le nom du fournisseur
à la place de son identifiant, ainsi que le nom de la pièce à la place de
son identifiant et le nom du projet à la place de son identifiant. La dete
de dernière livraison ne doit pas figurer dans le résultat. Par contre
l'entête affiché doit être NOM, PIECE, PROJET ET QUANTITE NOM PIECE PROJET QUANTITE
Smith Nut Sorter 200
Smith Nut Console 700
Adams Nut Console 100
Adams Bolt Display 200
Adams Bolt Console 100
Jones Screw Sorter 400
Jones Screw Display 200
Jones Screw OCR 200
Jones Screw Console 500
Jones Screw RAID 600
Jones Screw EDS 400
Jones Screw Tape 800
Blake Screw Sorter 200
Adams Screw Console 200
Blake Screw Display 500
Adams Screw Console 800
Jones Cam Display 100
Adams Cam RAID 500
Adams Cam Tape 100
Adams Cam Console 400
Clark Cog OCR 300
Clark Cog Tape 300
Adams Cog Display 200
Adams Cog Console 500
SELECT ... FROM..... WHERE .... La clause where exprime la condition de sélection (restriction) à effectuer
sur le résultat obtenu de select... from.
Principe :
1. On construit la table correspondante à select ... from
2. On ne conserve que les lignes pour lesquelles l'évaluation de la
condition est vrai.
En réalité, l'instruction SQL sera optimisée par le système. Rien ne dit
que le travail s'effectuera vraiment de cette manière. La seule certitude
qu'on ait est que le résultat sera semblable si on effectuait le travail de
cette manière. La condition peut être une condition composée s'exprimant entre autre à
l'aide des opérateurs =,>,