MediaTek V

[1 h]

1
Adresse (#num:integer, #rue:string, #cp:integer, #ville:string, #client => Client)
2
3
Type TClient <num:integer, nom:string, prenom:string, ddn:date, tel:string, genre:{'m','f'}>
4
Client de TClient (#num) 
5
6
Type TProduit <num:integer, designation:string, prix:float, stock:integer>
7
Produit de TProduit (#num)
8
9
Facture (#num:integer, #client =>o Client, #produit =>o Produit, qte:integer)
Question
Réalisez l'implémentation SQL3 sous Oracle.
Solution
Client
1
CREATE TYPE TClient AS OBJECT (
2
  num NUMBER(4),
3
  nom VARCHAR(20),
4
  prenom VARCHAR(20),
5
  ddn DATE,
6
  tel VARCHAR(10),
7
  genre CHAR(1)
8
);
9
/
10
CREATE TABLE Client OF TClient( 
11
  PRIMARY KEY (num),
12
  CHECK (genre IN ('m', 'f'))
13
); 
14
CREATE TABLE Adresse (
15
	num NUMBER(4),
16
	rue VARCHAR(255),
17
	cp NUMBER(5),
18
  ville VARCHAR(255),
19
  client NUMBER(4) REFERENCES Client (num),
20
  PRIMARY KEY (num, rue, cp, ville, client)
21
);
Produit
1
CREATE TYPE TProduit AS OBJECT ( 
2
  num NUMBER(10),
3
  designation VARCHAR(128), 
4
  prix NUMBER(5,2),
5
  stock NUMBER(4)
6
);
7
/
8
CREATE TABLE Produit of TProduit (PRIMARY KEY (num));
Facture
1
CREATE TABLE Facture ( 
2
  num NUMBER(10),
3
  client REF TClient,
4
  produit REF TProduit,
5
  qte NUMBER(4),
6
  SCOPE FOR (client) IS Client,
7
  SCOPE FOR (produit) IS Produit
8
);
Rappel
Sous Oracle on ne pourra créer de contrainte UNIQUE incluant un OID, donc ici on ne peut pas spécifier la contrainte (#num, #client, #produit).
Alimenter une base RO
Une fois la base créée, insérer des données pour l'alimenter.
On négligera la table Adresse pour la suite de l'exercice (aucun de nos clients n'aura d'adresse).
Question
Initialiser les tables client et produit avec les données de votre choix (au moins deux clients et deux produits).
Indice
Effectuez l'insertion comme en relationnel.
Solution
1
INSERT INTO Client (num, nom, prenom, ddn, tel, genre) 
2
VALUES (1, 'Colomb', 'Christophe', to_date('01091451', 'DDMMYYYY'), '0812456589' , 'm');
3
4
INSERT INTO Client (num, nom, prenom, ddn, tel, genre) 
5
VALUES (2, 'Morin', 'Bernard', to_date('27051931', 'DDMMYYYY'), '0126483621' , 'm');
6
7
INSERT INTO Produit (num, designation, prix, stock)
8
VALUES (1, 'The Matrix', 23.5, 100) ;
9
10
INSERT INTO Produit (num, designation, prix, stock)
11
VALUES (2, 'The Hobbit', 49.3, 100) ;
Question
Initialiser les factures avec les données de votre choix (deux factures de deux lignes chacune au moins).
Indice
Récupérer les OID pour insérer les références aux produits et clients au sein d'une procédure PL/SQL.
Insertion de références par OID (INSERT)
Blocs PL/SQL : Procédure, fonction, bloc anonyme
Indice
1
DECLARE
2
  oidclient1 REF TClient; 
3
  ...
4
5
BEGIN
6
  SELECT REF(c) INTO oidclient1
7
  FROM Client c
8
  WHERE c.num=...;
9
10
  ...
11
12
  INSERT INTO Facture (num, client, produit, qte) 
13
  VALUES (1,oidclient1, ..., 3);
14
  
15
  ...
16
 
17
END;
18
/
Solution
1
DECLARE
2
  oidclient1 REF TClient;
3
  oidproduit1 REF TProduit;
4
  oidclient2 REF TClient;
5
  oidproduit2 REF TProduit;
6
7
BEGIN
8
  SELECT REF(c) INTO oidclient1
9
  FROM Client c
10
  WHERE c.num=1;
11
12
  SELECT REF(c) INTO oidclient2
13
  FROM Client c
14
  WHERE c.num=2;
15
16
  SELECT REF(p) INTO oidproduit1
17
  FROM Produit p
18
  WHERE p.num=1;
19
20
  SELECT REF(p) INTO oidproduit2
21
  FROM Produit p
22
  WHERE p.num=2;
23
24
  INSERT INTO Facture (num, client, produit, qte) 
25
  VALUES (1, oidclient1, oidproduit1, 3);
26
27
  INSERT INTO Facture (num, client, produit, qte) 
28
  VALUES (1, oidclient1, oidproduit2, 2);
29
30
  INSERT INTO Facture (num, client, produit, qte) 
31
  VALUES (1, oidclient2, oidproduit2, 6);
32
33
  INSERT INTO Facture (num, client, produit, qte) 
34
  VALUES (1, oidclient2, oidproduit1, 1);
35
36
END;
37
/
Questions par navigation d'OID
Vous allez à présent  expérimenter la manipulation des OID pour naviguer d'enregistrement en enregistrement, sans utiliser de jointure.
Question
Écrivez une requête permettant d'afficher la liste des factures existantes, avec le nom et le prénom du client.
Indice
1
NUM CLIENT.NOM           CLIENT.PRENOM      
2
--- -------------------- --------------------
3
  1 Colomb               Christophe           
4
  1 Morin                Bernard        
Solution
1
COLUMN num FORMAT A3
2
SELECT f.num, f.client.nom, f.client.prenom
3
FROM Facture f
4
GROUP BY f.num, f.client.nom, f.client.prenom;
Question
Écrivez une requête permettant d'afficher le montant total de chaque facture, en rappelant le numéro du client pour chaque facture.
Indice
1
NUM CLI TOTAL
2
--- --- -----
3
  1   1 169.1 
4
  1   2 319.3 
Solution
1
COLUMN CLI FORMAT A3
2
SELECT f.num, f.client.num AS cli, SUM(f.qte * f.produit.prix) AS total
3
FROM Facture f
4
GROUP BY f.num, f.client.num
5
ORDER BY f.num, f.client.num
Question
Écrivez une requête permettant d'afficher pour chaque client (num et nom) les produits (num et désignation) qu'il a déjà acheté, avec la quantité correspondante.
Indice
1
CLIENT.NUM CLIENT.NOM           PRODUIT.NUM PRODUIT.DESIGNATION  QTE
2
---------- -------------------- ----------- -------------------- ---
3
         1 Colomb                         1 The Matrix             3 
4
         1 Colomb                         2 The Hobbit             2 
5
         2 Morin                          1 The Matrix             1 
6
         2 Morin                          2 The Hobbit             6 
Solution
1
COLUMN PRODUIT.NUM FORMAT A11
2
COLUMN PRODUIT.DESIGNATION FORMAT A20
3
SELECT f.client.num, f.client.nom, f.produit.num, f.produit.designation, SUM(f.qte) AS qte
4
FROM Facture f
5
GROUP BY f.client.num, f.client.nom, f.produit.num, f.produit.designation
6
ORDER BY f.client.num, f.client.nom, f.produit.num, f.produit.designation