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;