tp - sql
DESCRIPTION
TP - SQLTRANSCRIPT
Soit la base Magazine suivante: CLIENT (NumCli, Nom, Prénom, DateNaiss, Rue, CP, Ville) PRODUIT (NumProd, Desig, PU, #NumFour) FOURNISSEUR (NumFour, RaisonSoc) COMMANDE (#NumCli, #NumProd, DateC, Quantite)
1. Créez la base de données MAGAZINE
CREATE DATABASE MAGAZINE2;
2. Créez les tables : Client, Fournisseur, Produit et Commande en respectant les conditions suivants :
1. Prix unitaire ne doit pas dépasser 30000DH,2. La quantité commandée doit être supérieure à zéro.
3. Supprimer toutes les tables de la base de données et les recréez une deuxième fois
Table Client Table Fournisseur
CREATE TABLE CLIENT ( NumCli INT, Nom VARCHAR(30), PRENOM VARCHAR(30), DateNaiss DATE, Rue VARCHAR(100), CP INT, Ville VARCHAR(40), CONSTRAINT PK_CLIENT PRIMARY KEY (NumCli));
CREATE TABLE Fournisseur( NumFour INT, RaisonSoc VARCHAR(30), CONSTRAINT PK_FOURNISSEUR PRIMARY KEY (NumFour));
Table Produit Table Commande
CREATE TABLE PRODUIT ( NumProd INT, Desig VARCHAR(30), PU DOUBLE, NumFour INT Not NULL, CONSTRAINT PK_PRODUIT PRIMARY KEY (NumProd), CONSTRAINT FK_PRODUIT_NUMFOUR FOREIGN KEY (NumFour) REFERENCES Fournisseur (NumFour), CONSTRAINT CK_PRODUIT_PU CHECK (PU<30000));
CREATE TABLE COMMANDE ( NumCli INT, NumProd INT, DateC DATE , Quantite INT NOT NULL, CONSTRAINT PK_COMMANDE PRIMARY KEY (NumCli, NumProd, DateC), CONSTRAINT FK_COMMANDE_NUMCLI FOREIGN KEY (NumCli)
REFERENCES Client(NumCli),CONSTRAINT FK_COMMANDE_NUMPROD FOREIGN KEY (NumProd) REFERENCES Produit(NumProd), CONSTRAINT CK_COMMANDE_QTE CHECK (Quantite>0));
Show databases : fait la liste des bases de donnés sur le
serveur MySQL
Use base: indique à MySQL le nom de la base de données
par défaut
Show tables : fait la liste des tables sur une base donnée
Desc table : fait la liste des colonnes d'une table et les
types des colonnes.
1. Ajouter l’attribut « Adr » dans la table client
ALTER TABLE CLIENT ADD(Adr VARCHAR(50)) ;
2. Supprimer l’attribut « Rue » de la table client
ALTER TABLE CLIENT DROP COLUMN Rue ;
3. Modifier le type de l’attribut « Adr » VARCHAR(100)
ALTER TABLE CLIENT MODIFY Adr VARCHAR(100) ;
NumCli Nom Prénom DateNaiss CP Ville adr
1 Salmi Sami 1944- 02-12 100023 Rabat Rue 17
2 Imalla Kamel 1944-12-14 100024 Rabat3 Skfalli Ali 100025 Rabat Rue 28
INSERT INTO CLIENT VALUES (1, "SALMI","SAMI","1944-02-12", 100023, "Rabat","Rue 17") ;
INSERT INTO CLIENT (NumCli, Nom, Prenom, DateNaiss,CP, Ville) VALUES (2, "IMALLA","KAMEL", "1944-12-14",100024,"Rabat") ;
INSERT INTO CLIENT (NumCli, Nom, Prenom, CP, Ville,adr) VALUES (3, "Skfali","Ali",100025,"Rabat","Rue 28") ;
NumFour RaisonSoc
1 SOS SALMI
2 SOS INCONNU
3 SOS MED ALI
INSERT INTO FOURNISSEUR VALUES (1,"SOS SALMI");
INSERT INTO FOURNISSEUR VALUES (2,"SOS INCONNU");
INSERT INTO FOURNISSEUR VALUES (3,"SOS MED ALI");
NumProd Design PU NumFour10 PRO10 1100 120 PRO20 2045,25 3
INSERT INTO PRODUIT VALUES (10, "PRO10",1100,1);
INSERT INTO PRODUIT VALUES (20, "PRO20",2045.25,3);
NumCli NumProd DateC Quantite1 10 2011-02-12 23 20 2011-07-16 43 10 2011-09-20 61 20 2011-11-26 3
INSERT INTO COMMANDE VALUES (1,10,"2011-02-12",2);
INSERT INTO COMMANDE VALUES (3,20,"2011-07-16",4);
INSERT INTO COMMANDE VALUES (3,10,"2011-09-20",6);
INSERT INTO COMMANDE VALUES (1,20,"2011-11-26",3);
1. Donnez la liste de tous les clients par ordre alphabétique
inverse de nom.
SELECT * FROM client ORDER BY nom DESC ;
2. Calculez le prix TTC de tous les produits sachant que le TVA
= 20%.
SELECT PU+PU*1.8 FROM produit;
Ou
SELECT PU+PU*1.8 AS Prix_TTC FROM produit ;
3. Donnez les Noms et Prénoms de tous les clients (projection)
SELECT Nom, Prenom FROM Client ;
4. Donnez la liste des Clients qui habitent à Rabat,
SELECT * FROM client WHERE ville=‘Rabat’ ;
5. Donnez les commandes en quantité au moins égale à 3
SELECT * FROM Commande WHERE Quantite>=3;
6. Donnez les Produits dont le prix unitaire est compris entre 2000 ,00
DH et 2500,00 DH
SELECT * FROM produit WHERE PU BETWEEN 2000 AND 2500;
7. Donnez les Commandes en quantité indéterminée
SELECT * FROM Commande WHERE Quantite IS NULL;
8. Donnez les Clients habitant une ville dont le nom se termine par AT
SELECT * FROM client WHERE UPPER(Ville) LIKE ‘%AT’;9. Donnez les Prénoms des clients dont le nom est SAMI, SALAH ou ALI
SELECT prenom FROM client
WHERE UPPER (Nom) IN (‘SAMI’, ’SALAH’, ’ALI’);
10. Donnez la Moyenne des prix unitaire des produits
SELECT AVG (PU) FROM Produit;
11. Quel est Nombre total de commandes ?SELECT COUNT (*) FROM commande;
ou
SELECT COUNT(NumCli) FROM Commande;
12. Quel est le Nombre de clients ayant passé commande ?SELECT COUNT(DISTINCT NumCli) FROM Commande;
13. Donnez la Liste des noms et prénoms des clients qui ont lancé
des commandes.
SELECT Nom, Prenom FROM Client, Commande
WHERE Client.NumCli = Commande.NumCli;
14. Donnez la Liste des numéros, noms et prénoms des clients qui ont lancé des commandes
SELECT cli.NumCli, Nom, Prenom From Client cli, commande cmd
WHERECli.NumCli=cmd.NumCli;
15. Donnez la Liste des numéros, noms et prénoms des clients qui ont commandé le 26-11-2011
SELECT Cli.NumCli, Nom, Prenom FROM Client Cli, Commande Cmd WHERE
Cli.NumCli = Cmd.NumCli AND DateC = '2011-11-26';
16. Donnez la Liste des clients qui ont passé au moins une commandeSELECT * FROM Client cli
WHEREEXISTS (SELECT * FROM Commande cmd
WHERE Cmd.NumCli = cli.NumCli);
16. Quantité totale commandée par chaque client.
SELECT NumCli, SUM(Quantite)
FROM Commande
GROUP BY NumCli;
17. Nombre de produits différents commandés par chaque client
SELECT NumCli, COUNT(DISTINCT NumProd)
FROM Commande
GROUP BY NumCli;
18. Numéro des produits qui soit, ont un prix inférieur à 2000 DH, soit
ont été commandés par client N°2.
SELECT NumProd FROM Produit WHERE PU<2000
UNION
SELECT NumProd FROM Commande WHERE NumCli=2;