EXERCICES CORRIGES BASE DE DONNEE | EXERCICE CORRIGE SQL
Exercice SQL : Soit la base Fabrication :
PIECE (NOP, DESIGNATION, COULEUR, POIDS)
SERVICE(NOS, INTITULE, LOCALISATION)
ORDRE(NOP*, NOS*, QUANTITE)
En faisant les suppositions suivantes :
• La quantité doit être supérieur à zéro
• Si un service est supprimé, l’ensemble de l’information qui en dépend doit être supprimée de la base
• Si une pièce est supprimée, l’ensemble de l’information qui en dépend ne doit pas être
supprimée de la base.
Répondre aux requêtes suivantes en utilisant le SQL :
1- Proposez une définition des trois tables en SQL qui prennent en compte les contraintes d’intégrités ?
2- Créer une vue des numéros et désignation des pièces.
3- Donner les numéros de services ayant en commande la pièce P1 avec une quantité > à
10 dans l’ordre croissant.
4- Quel est le nombre total des services.
5- Quelle est la quantité moyenne commandée pour la pièce P3.
6- Quel est le nombre de services ayant des commandes.
7- Quelle est la liste des pièces commandées par le service S1 avec leur libellé et leurs poids.
8- Quels sont 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.
9- Quelles sont les caractéristiques de chaque pièce ayant un poids < à la moyenne des poids des pièces de leur couleur.
10- Quels sont les numéros et la désignation des pièces qui sont commandées par le service ‘Diffusion’ ? Donnez 2 solutions : en utilisant une jointure et le prédicat IN.
11- 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 ?
Correction :
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');
Si vous avez trouvé les exercices corrigés en SQL de Mr JoëlYk intéressants et utiles, pourquoi ne pas les partager avec d'autres personnes qui pourraient également en bénéficier ? Partagez ce lien sur les réseaux sociaux ou envoyez-le à vos amis et collègues. Vous pourriez aider quelqu'un à améliorer ses compétences en programmation ou à trouver des solutions à des problèmes complexes. N'oubliez pas que la connaissance doit être partagée pour grandir. Merci pour votre soutien et votre partage !
Contact WhatsApp : +237 658395978 | Réaliser Par Joël_Yk
Ajouter un commentaire