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

encore plus d'exercice corriges ici !!!

Ajouter un commentaire

Nom

E-mail

Site Internet

Message

Aperçu

Anti-spam