Base des Données Mohamed MARS
Correction TD SQL
Schéma relationnel d’une base de données de gestion de stock :
Article(Code_art, Des_art, PU, Qte_stock)
Client(Code_client, Nom_client, Prenom_client, Adr_client, Tel_client, email_client,
Chiffre_affaires_annee_encours, Cumul_chiffre_affaires)
Commande(Num_comm, date_comm, #code_client)
Détail_Command(#Num_comm,#Code_art,Qte_comm)
Création d’une base de données
1. Structure de la base de données
-- Création de la base de données
CREATE DATABASE Stock;
--Création de la table “Article”
CREATE TABLE Article
(
Code_art VARCHAR(20),
Des_art VARCHAR(50) NOT NULL,
PU NUMBER(8,3),
Qte_stock NUMBER(5) DEFAULT 0,
CONSTRAINT chkQtes CHECK Qte_stock>=0
);
--Création de la table “Client”
CREATE TABLE Client
(
Code_client VARCHAR(20),
Nom_client VARCHAR(20) NOT NULL,
Prenom_client VARCHAR(30),
Adr_client VARCHAR(20) NOT NULL,
Tel_client NUMBER(8),
Email_client VARCHAR(50),
Chiffre_affaires_annee_en_cours NUMBER(10,3),
Cumul_chiffre_affaires NUMBER(12,3),
);
--Création de la table “Commande”
CREATE TABLE Commande
(
Num_comm VARCHAR(20) Primary key,
Date_comm DATE,
Code_client VARCHAR(20),
CONSTRAINT ChkDatecmd CHECK (Date_comm<=SYSDATE),
);
--Création de la table “Detail_commande”
CREATE TABLE Detail_commande
(
Num_comm VARCHAR(20),
Code_art VARCHAR(20),
Qte_comm NUMBER(3),
CONSTRAINT ChkQtecmd CHECK (Qte_comm>0),
);
Page
1/4
Base des Données Mohamed MARS
2. Ajouter les contraintes des clés primaires et étrangères des tables déjà créées.
--Ajout de la contrainte de la clé primaire de la table Article
ALTER TABLE Article
ADD CONSTRAINT PKArt PRIMARY KEY(Code_art);
--Ajout de la contrainte de la clé primaire de la table Client
ALTER TABLE Client
ADD CONSTRAINT PKClt PRIMARY KEY(Code_client);
--Ajout de la contrainte de la clé primaire de la table Commande
ALTER TABLE Commande
ADD CONSTRAINT PKCmd PRIMARY KEY(Num_comm);
--Ajout de la contrainte de la clé étrangère de la table Commande
ALTER TABLE Commande
ADD CONSTRAINT FKCmdClt FOREIGN KEY(Code_client)
REFERENCES Client(Code_client);
--Ajout de la contrainte de la clé primaire de la table Detail_commande
ALTER TABLE Detail_commande
ADD CONSTRAINT PKDetailCmd PRIMARY KEY(Num_comm,Code_art);
--Ajout de la contrainte de la clé étrangère de la table Detail_commande
ALTER TABLE Detail_commande
ADD CONSTRAINT FKCmd FOREIGN KEY(Num_comm)
REFERENCES Client(Num_comm);
--Ajout de la contrainte de la clé étrangère de la table Detail_commande
ALTER TABLE Detail_commande
ADD CONSTRAINT FKArt FOREIGN KEY(Code_art)
REFERENCES Article(Code_art);
3. Créer une copie de la table Client nommé "Clientele"
CREATE TABLE Clientele AS SELECT * FROM Client;
4. Renommer la table "Detail_commande" par "Ligne_commande".
1ère solution
RENAME Detail_commande TO Ligne_commande;
ème
2 solution
ALTER TABLE Detail_commande RENAME TO Ligne_commande;
5. Ajouter à la table "Ligne_Commande" le champ "PrixVente" de Type Numérique(8,3);
ALTER TABLE Ligne_commande
ADD (PrixVente NUMBER(8,3)) ;
6. Augmenter la taille du champ "Adresse" de la table "Client" de 20 à 50 caractères;
ALTER TABLE Client
MODIFY Adresse VARCHAR(50) ;
Page
2/4
Base des Données Mohamed MARS
7. Ajouter une valeur 'prénom inconnu' comme valeur par défaut au champ
"Prenom_client" de la table "Client";
ALTER TABLE Client
MODIFY Prenom_client DEFAULT ‘prénom inconnu’ ;
8. Ajouter une contrainte du domaine sur les champs "Adr_client" de la table "Client"
pour qu'elle soit limiter aux trois régions : 'Kef', 'Jendouba' et 'Beja'.
ALTER TABLE Client
ADD CONSTRAINT ChkAdr CHECK (Adr_client IN ('Kef','Jendouba','Beja');
9. Modifier le type de données du champ "Tel_client" en Numérique(taille 8).
ALTER TABLE Client
MODIFY Tel_client NUMBER(8);
10. Supprimer le champ "Email_client" de la table Client.
ALTER TABLE Client
DROP Email_client;
11. Supprimer la table "Client".
DROP TABLE Client;
Mise à jour de la base de données
INSERT INTO Client
VALUES ('S0010','BRINSI','Sami','Jendouba',66536658,'[Link]@[Link]',
15679.355,123765.540);
--ou
-- INSERT INTO Client (Code_client Nom_client,Prenom_client, Adr_client,
-- Tel_client,Email_client,
-- Chiffre_affaires_annee_en_cours,Cumul_chiffre_affaires)
-- VALUES('S0010','BRINSI','Sami','Jendouba',66536658,
-- '[Link]@[Link]',15679.355,123765.540);
INSERT INTO Client
VALUES ('T0122','ABIDI','Nadia','Tunis',66335297,'[Link]@[Link]',
54987.210,339807.250);
INSERT INTO Client
VALUES ('M523','HAMDI','Amine','Beja',54578789,'[Link]@[Link]',
22765.705,564700.590);
--********************************************************************
INSERT INTO Article VALUES (' Dis312','Disquette 3-1/2',1.200,0);
--Comme la valeur par défaut pour Qte_stock = 0 on peut écrire
-- INSERT INTO Article (Code_art,Des_art,PU)
-- VALUES ('Dis312',' Disquette 3-1/2',1.200);
INSERT INTO Article VALUES ('CD700','CDROM Vierge 700 Mo',NULL,15);
--Comme on peut écrire aussi
-- INSERT INTO Article (Code_art,Des_art,Qte_stock)
-- VALUES ('CD700','CDROM Vierge 700 Mo',15);
Page
3/4
Base des Données Mohamed MARS
INSERT INTO Article (Code_art,Des_art,PU)
VALUES ('ClaBil','Clavier Bilingue',12.200);
INSERT INTO Article (Code_art,Des_art,PU)
VALUES ('SouPS2','Souris',5.500);
INSERT INTO Article (Code_art,Des_art,Qte_stock)
VALUES ('AppPho','Appareil Photo Numérique',20);
INSERT INTO Article (Code_art,Des_art)
VALUES ('LanCard','Carte Réseau');
--********************************************************************
INSERT INTO Commande (Num_comm,Date_comm,Code_client)
VALUES ('000100/2013',TO_DATE('20/02/2013', 'dd-mm-yyyy'), 'T0122');
--ou
--INSERT INTO Commande
--VALUES ('000100/2013',TO_DATE('20/02/2013', 'dd-mm-yyyy'), 'T0122');
INSERT INTO Commande
VALUES ('002087/2013',TO_DATE('01/03/2013', 'dd-mm-yyyy'), 'S0010');
INSERT INTO Commande
VALUES ('001123/2013',TO_DATE('15/03/2013', 'dd-mm-yyyy'), 'M5423');
INSERT INTO Commande
VALUES ('003400/2013',TO_DATE('24/03/2013', 'dd-mm-yyyy'), ' T0122');
--********************************************************************
INSERT INTO Detail_commande
VALUES ('001123/2013',' CD700', 5);
-- La requête d’insertion suivante ne sera pas exécutée par le SGBD
-- vu qu’il y’a une intégrité référentielle entre les deux tables Commande
-- et Detail_commande. Les valeurs autorisées pour le Num_comm
-- de la table fille Detail_commande doivent exister dans
-- le champ Num_comm de la table mère Commande.
--INSERT INTO Detail_commande
--VALUES ('007845/2013','AppPho', 4);
UPDATE Article
SET PU = 0.500, Qte_stock=100
WHERE Code_art ='CD700';
DELETE Commande WHERE Num_comm = '003400/2013';
Page
4/4
