1- Définition des tables en SQL avec les contraintes d'intégrité :
CREATE TABLE PIECE (
NOP INTEGER PRIMARY KEY,
DESIGNATION VARCHAR(50) NOT NULL,
COULEUR VARCHAR(20),
POIDS FLOAT CHECK (POIDS > 0)
);
CREATE TABLE SERVICE (
NOS INTEGER PRIMARY KEY,
INTITULE VARCHAR(50) NOT NULL,
LOCALISATION VARCHAR(50)
);
CREATE TABLE ORDRE (
NOP INTEGER REFERENCES PIECE(NOP),
NOS INTEGER REFERENCES SERVICE(NOS),
QUANTITE INTEGER CHECK (QUANTITE > 0),
PRIMARY KEY (NOP, NOS)
);
2- Création de la vue :
CREATE VIEW PIECES_INFO AS
SELECT NOP, DESIGNATION FROM PIECE;
3- Requête pour les numéros de services ayant en commande la pièce P1 avec une quantité > à 10 dans l'ordre croissant :
SELECT NOS FROM ORDRE
WHERE NOP = 'P1' AND QUANTITE > 10
ORDER BY NOS ASC;
4- Requête pour le nombre total de services :
SELECT COUNT(*) FROM SERVICE;
5- Requête pour la quantité moyenne commandée pour la pièce P3 :
SELECT AVG(QUANTITE) FROM ORDRE
WHERE NOP = 'P3';
6- Requête pour le nombre de services ayant des commandes :
SELECT COUNT(DISTINCT NOS) FROM ORDRE;
7- Requête pour la liste des pièces commandées par le service S1 avec leur libellé et leurs poids :
SELECT PIECE.NOP, DESIGNATION, POIDS FROM PIECE
JOIN ORDRE ON PIECE.NOP = ORDRE.NOP
WHERE NOS = 'S1';
8- Requête pour les numéros des services qui ont commandé la pièce P3 avec une quantité inférieure à la quantité moyenne commandée pour cette pièce :
SELECT NOS FROM ORDRE
WHERE NOP = 'P3' AND QUANTITE < (SELECT AVG(QUANTITE) FROM ORDRE WHERE NOP = 'P3');
9- Requête pour les caractéristiques de chaque pièce ayant un poids < à la moyenne des poids des pièces de leur couleur :
SELECT PIECE.NOP, DESIGNATION, COULEUR, POIDS FROM PIECE
WHERE POIDS < (SELECT AVG(POIDS) FROM PIECE WHERE COULEUR = PIECE.COULEUR);
10- Requête pour les numéros et la désignation des pièces qui sont commandées par le service 'Diffusion' :
Avec une jointure :
SELECT PIECE.NOP, DESIGNATION FROM PIECE
JOIN ORDRE ON PIECE.NOP = ORDRE.NOP
JOIN SERVICE ON SERVICE.NOS = ORDRE.NOS
WHERE INTITULE = 'Diffusion';
Avec le prédicat IN :
SELECT NOP, DESIGNATION FROM PIECE
WHERE NOP IN (SELECT NOP FROM ORDRE WHERE NOS = (SELECT NOS FROM SERVICE WHERE INTITULE = 'Diffusion'));
11- Requête pour les numéros des services ayant commandé au moins une pièce en quantité strictement supérieur à chacune des quantités des pièces commandées par le service S1 :
SELECT DISTINCT o2.NOS
FROM ORDRE o1, ORDRE o2
WHERE o1.NOP = o2.NOP
AND o1.NOS = 'S1'
AND o2.QUANTITE > ALL (SELECT o3.QUANTITE FROM ORDRE o3 WHERE o3.NOP = o1.NOP AND o3.NOS <> 'S1');