-- ENSAM Meknès 2022-2023
--
-- Corrigé TP SQL – Bases de données relationnelles
-- – BD gestion de ventes –
--
-- Auteur : E. Zemmouri
-- Date : 2022-11-05
--
-- Partie 1 - Création du schéma
--
CREATE TABLE Employe(
Mat INT(4) PRIMARY KEY,
Nom VARCHAR(10) NOT NULL,
Fonction VARCHAR(20) ,
Sal DECIMAL(7,2) ,
Prime DECIMAL(7,2) ,
NumMag INT(2)
);
CREATE TABLE Magasin(
NumMag INT(2) PRIMARY KEY,
NomMag VARCHAR (25) NOT NULL ,
Ville VARCHAR (20),
MatChef INT(4)
);
CREATE TABLE Produit(
Ref INT PRIMARY KEY,
Designation VARCHAR (25) NOT NULL ,
Marque VARCHAR (10) ,
Categorie VARCHAR (20) ,
PU Decimal (12,2) NOT NULL
);
CREATE TABLE Vente (
ID INT PRIMARY KEY,
Ref INT NOT NULL,
Qte INT NOT NULL,
DateVente DATE NOT NULL,
NumMag INT(2) NOT NULL
);
-- le nom du magasin ne se répète pas dans la même ville
ALTER TABLE Magasin
ADD CONSTRAINT c UNIQUE (NomMag, Ville);
-- NumMag est clé étrangère dans la table Employé
ALTER TABLE Employe
ADD CONSTRAINT fk_Employe_NumMag FOREIGN KEY (NumMag) REFERENCES Magasin(NumMag);
-- MatChef est clé étrangère dans la table Magasin
ALTER TABLE Magasin
ADD CONSTRAINT FK_Magasin_MatChef FOREIGN KEY (MatChef) REFERENCES Employe(Mat);
-- NumMag et Ref sont des clés étrangères dans la table Vente
ALTER TABLE Vente
ADD CONSTRAINT FK_Vente_NumMag FOREIGN KEY (NumMag) REFERENCES Magasin(NumMag),
ADD CONSTRAINT FK_Vente_Ref FOREIGN KEY (Ref) REFERENCES Produit(Ref);
--
-- Partie 2 : Interrogation d’une seule Relation
--
-- Q8
SELECT nom, fonction, sal
FROM employe
WHERE fonction!='INGENIEUR' AND fonction!='MANAGER';
-- Ou bien
SELECT nom, fonction, sal
FROM employe
WHERE fonction NOT IN ('INGENIEUR' ,'MANAGER');
-- Q9
SELECT DISTINCT fonction
FROM employe;
-- Q10
SELECT *
FROM employe
WHERE sal >= 2000 AND sal <= 2500;
-- Ou bien
SELECT *
FROM employe
WHERE sal BETWEEN 2000 AND 2500;
-- Q13
SELECT *
FROM employe
WHERE prime IS NULL;
-- Q14
SELECT *
FROM employe
WHERE prime IS NOT NULL;
-- Les employes qui ont une prime non nulle (différente de 0)
SELECT *
FROM employe
WHERE prime != 0;
-- Q16
SELECT mat, nom, fonction, sal * 12 AS 'sal annuel'
FROM employe;
-- Q17
SELECT mat, nom, fonction, sal * 12 + prime AS 'sal annuel'
FROM employe
WHERE prime IS NOT NULL
UNION
SELECT mat, nom, fonction, sal * 12 AS 'sal annuel'
FROM employe
WHERE prime IS NULL;
-- Q18
SELECT *
FROM Employe
WHERE nom LIKE 'A%';
-- Q23
SELECT DISTINCT marque, categorie
FROM produit;
-- Q25
SELECT produit.* , pu*1.2 AS TTC
FROM produit
ORDER BY TTC DESC;
-- Q26
SELECT *
FROM produit
WHERE designation LIKE '%phone%';
-- Q28
SELECT mat
FROM employe
EXCEPT
SELECT matchef
FROM magasin;
-- Ou bien
SELECT mat
FROM employe
WHERE mat NOT IN (SELECT matchef FROM magasin);
-- Q30
SELECT *
FROM produit
WHERE categorie='Ordinateur'
ORDER BY marque ASC, pu DESC;
--
-- PArtie 3 : Jointures
--
-- Q2
SELECT nom, fonction, nommag, ville
FROM employe NATURAL JOIN magasin;
-- Ou bien
SELECT nom, fonction, nommag, ville
FROM employe JOIN magasin ON employe.nummag=magasin.nummag;
-- Ou bien
SELECT nom, fonction, nommag, ville
FROM employe , magasin
WHERE employe.nummag=magasin.nummag;
-- Q3
SELECT nummag, nommag, ville, nom
FROM magasin JOIN employe ON matchef=mat;
-- Ou bien
SELECT nummag, nommag, ville, nom
FROM magasin , employe
WHERE matchef=mat;
-- Q4
SELECT mat, nom
FROM employe NATURAL JOIN magasin
WHERE ville='MEKNES';
-- Q6
SELECT distinct mat, nom
FROM magasin JOIN employe ON matchef=mat;
-- Ou bien
SELECT mat, nom
FROM employe
WHERE mat IN (SELECT matchef FROM magasin);
-- Q7
SELECT mat, nom
FROM employe
WHERE mat NOT IN (SELECT matchef FROM magasin);
-- Q8
SELECT DISTINCT produit.*
FROM vente NATURAL JOIN produit
WHERE datevente LIKE '2022-01-%';
-- Q9
SELECT *
FROM produit
WHERE marque='HP' AND ref NOT IN (
SELECT ref FROM vente WHERE datevente BETWEEN '2022-01-01' AND '2022-03-31');
-- Q15
SELECT vente.*, qte*pu AS total, 0.15*qte*pu AS benefice
FROM vente NATURAL JOIN produit NATURAL JOIN magasin
WHERE ville = 'MEKNES';
-- Q16
SELECT *
FROM employe
WHERE fonction!='INGENIEUR' AND nummag IN (SELECT nummag FROM employe WHERE fonction='INGENIEUR');
-- Q18
SELECT a.nom, b.nom
FROM employe a NATURAL JOIN magasin JOIN employe b ON b.mat=matchef;
-- Q19
SELECT *
FROM magasin m JOIN employe e ON matchef=mat
WHERE m.nummag != e.nummag;
