Manipuler une BD relationnelle
Vous travaillez dans une agence immobilière qui a mis en place un modèle relationnel afin de gérer
son portefeuille client. Le schéma relationnel est le suivant :
• Client (codeclt, nomclt, adresseclt)
• Appartement (ref, superficie, prixvente, secteur, #coderep, #codeclt)
• Représentant (coderep, nomrep)
La table client
codeclt nomclt adresseclt
Cl01 karim Marrakech
Cl02 Fatma Rabat
Cl03 Fatima Fes
Cl04 ALi Casablanca
Cl05 Mohamed Tanger
Cl06 Hassan Agadir
Create table Client (
Codeclt char(5) primary key not null,
Nomclt varchar(25) not null,
Adresseclt varchar(25)
);
La table Appartement
ref superficie prixvente secteur #coderep #codeclt
Ref01 98 360000 Casablanca Rep01 Cl03
Ref02 87 254000 Rabat Rep01 Cl01
Ref03 51 167000 Fes Rep04 Cl05
Ref04 77 199000 Fes Rep01 Cl04
Ref05 97 299000 Marakech Rep02 Cl01
CREATE TABLE Appartement (
ref CHAR(5) PRIMARY KEY NOT NULL,
superficie DECIMAL(5, 2) NOT NULL,
prixVente DECIMAL(10, 2),
secteur VARCHAR(50),
CodeRep CHAR(10) ,
codecLt CHAR(5),
FOREIGN KEY (CodeRep) REFERENCES Representant(codeRep),
FOREIGN KEY (codecLt) REFERENCES Client(codecLt)
);
Ou
CREATE TABLE Appartement (
ref CHAR(5) PRIMARY KEY NOT NULL,
superficie DECIMAL(5, 2) NOT NULL,
prixVente DECIMAL(10, 2),
secteur VARCHAR(50),
CodeRep CHAR(10) references Representant(coderep),
codecLt CHAR(5) references Client(codeclt)
);
La table Représentant
coderep Nomrep
Rep01 Hamed
Rep02 Nadia
Rep03 Saida
Rep04 Mourad
Rep05 Samih
CREATE TABLE Représentant (
coderep CHAR(10) PRIMARY KEY,
nomrep VARCHAR(25) );
Exercice 0
Écrivez le script (commandes SQL) de création de tables et des différentes relations du modèle
relationnel ci-dessus en précisant les clés primaires et les clés étrangères.
Écrivez le script (commandes SQL) d’insertion des données sur les tables
INSERT INTO Client (CodeClt, NomClt, adresseClt)
VALUES
('Cl01', 'Karim', 'Marrakech'),('Cl02', 'Fatma', 'Rabat'),('Cl03', 'Fatima', 'Fes'),
('Cl04', 'Ali', 'Casablanca'),
('Cl05', 'Mohamed', 'Tanger'),
('Cl06', 'Hassan', 'Agadir') ;
INSERT INTO Appartement (ref, superficie, prixVente, secteur, CodeRep, codeClt)
VALUES
('Ref01', 98, 360000, 'Casablanca', 'Rep01', 'Cl03'),
('Ref02', 87, 254000, 'Rabat', 'Rep01', 'Cl01'),
('Ref03', 51, 167000, 'Fes', 'Rep04', 'Cl05'),
('Ref04', 77, 199000, 'Fes', 'Rep01', 'Cl04'),
('Ref05', 97, 299000, 'Marrakech', 'Rep02', 'Cl01');
INSERT INTO Representant (coderep, nomrep) VALUES
('Rep01', 'Hamed'),
('Rep02', 'Nadia'),
('Rep03', 'Saida'),
('Rep04', 'Mourad'),
('Rep05', 'Samih');
Exercice 1
Écrivez les requêtes SQL nécessaires qui répondent aux besoins suivants :
• La liste des clients (toutes les informations)
select * from Client ;
• La liste des clients habitant Casablanca (toutes les informations)
SELECT * FROM Client WHERE adressecLt = 'Casablanca';
• La liste des clients (toutes les informations) dont le nom commence par la lettre F
SELECT * FROM Client WHERE nomclt LIKE 'F%';
• La liste des appartements situés à Tanger
SELECT * FROM Appartement WHERE secteur = 'Tanger';
• La liste des appartements dont la superficie est supérieure à 90 m²
SELECT * FROM Appartements
WHERE Superficie > 90;
• La liste des appartements dont les prix varient entre 200 000 dirhams et 400 000 dirhams
SELECT * from Appartement
WHERE prixVente between 200000 and 400000;
• Les appartements situés dans les villes Rabat ou Marrakech
SELECT * FROM Appartement
WHERE secteur LIKE 'Rabat' OR secteur LIKE 'Marrakech';
Exercice 2
À vous de jouer !
Écrivez les requêtes SQL nécessaires qui répondent aux besoins suivants :
• La liste des clients qui ont des appartements à Rabat
• La liste des clients qui ont des appartements à Fès dont la superficie est supérieure à 80 m2
• La liste des appartements situés à Fès et gérés par Hamed (nomrep)
• La liste des clients habitant Rabat et possédant des appartements situés à Marrakech
• La liste des représentants qui gèrent des appartements dans les villes Fès et Marrakech
• La liste des représentants qui ne gèrent aucun appartement
Exercice 3
• Le code de l’appartement ayant un prix minimal
SELECT ref
FROM Appartement
WHERE prixvente = (SELECT MIN(prixvente) FROM Appartement);
• Le code de l’appartement ayant un prix maximal
SELECT ref, prixVente
FROM appartement
WHERE prixVente = (SELECT MAX(prixVente) FROM appartement);
• La moyenne des prix des appartements
SELECT AVG(PrixVente) AS MoyennePrix
FROM Appartements;
• La moyenne par ville des prix des appartements
SELECT AVG(PrixVente) AS MoyennePrix
FROM Appartements
GROUP BY SECTEUR;
• Donner le nombre d’appartements
SELECT COUNT(*) AS Nombre_Appartements
FROM Appartement;
Ou
SELECT COUNT(ref) AS Nombre_Appartements
FROM Appartement;
Donner le nombre d’appartements par client
SELECT COUNT(ref) AS Nombre_Appartements
FROM Appartement
Group by codeclt;
• Donner les clients dont le nombre d'appartements dépasse 1
• Le nombre d’appartements dont la superficie est supérieure à 80 m²
SELECT COUNT(*) AS nombre_appartements
FROM Appartement
WHERE superficie > 80;
• Les appartements (ref, superficie, prixvente, secteur) dont le prix est supérieur à la moyenne des
prix
Select ref, superficie, prixvente, secteur
FROM Appartement
WHERE prixvente > (SELECT AVG(prixvente) FROM Appartement);