Discussion :

[Zaza91280]

1)Pour chaque catégorie, donner le nombre de commandes :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT COUNT(DISTINCT *) AS NbComs, P.CodeCateg
FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom=D.NoCom
INNER JOIN Produit AS P ON D.Refprod=C.Refprod
GROUP BY P.CodeCateg

2)Chiffre d'affaire généré pour chacun de ses produits :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT SUM(D.Qte*D.PrixUnit), D.Refprod
FROM DetailCommande AS D
INNER JOIN Produit AS P ON D.Refprod=P.Refprod
GROUP BY D.Refprod

Voilà pour la 4 mais j'ai pas essayé de regrouper le tout. Je le ferai une fois que vous aurez validé chaque sous requête .

Pour la 7 :

1)Clients italiens ayant au moins une commande :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT C.CodeCli
FROM Client AS C
INNER JOIN Commande AS O ON O.CodeCli=C.CodeCli
WHERE C.Pays='Italie'
GROUP BY C.CodeCli
HAVING COUNT(O.NoCom)>=1

Mais je ne vois pas comment les compter....

Y'aura aussi la 8 (la dernière :ouf mais vu qu'elle est formulée tout aussi bien que les autres, j'ai un peu de mal à la comprendre.

8)Quel est-le CA généré par catégorie alimentaire pour les employés sous la responsabilité du Chef des ventes « Patrick Emery », sur le Royaume-Uni, la France et les Etats-Unis ?

[CinePhil]

1)Pour chaque catégorie, donner le nombre de commandes :
Code sql :
SELECT COUNT(DISTINCT *) AS NbComs, P.CodeCateg
FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom=D.NoCom
INNER JOIN Produit AS P ON D.Refprod=C.Refprod
GROUP BY P.CodeCateg

Dans COUNT(*), l'étoile prend en compte toutes les colonnes sans distinction donc compte toutes les lignes.
COUNT(DISTINCT *) va compter les ensembles {toutes les colonnes} distincts.
Une commande avec 2 produits donc deux lignes de commande aura bien deux ensembles {toutes les colonnes} différents et sera comptée deux fois.
Il faut faire la distinction sur le numéro de commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT COUNT(DISTINCT NoCom) AS NbComs, P.CodeCateg
FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom = D.NoCom
  INNER JOIN Produit AS P ON D.Refprod = P.Refprod
GROUP BY P.CodeCateg

Attention à l'utilisation des bons alias, il y avait une erreur dans la seconde jointure.

2)Chiffre d'affaire généré pour chacun de ses produits :
Code sql :
SELECT SUM(D.Qte*D.PrixUnit), D.Refprod
FROM DetailCommande AS D
INNER JOIN Produit AS P ON D.Refprod=P.Refprod
GROUP BY D.Refprod

Là tu compte effectivement le CA par produit et non pas par catégorie de produit. J'avais signalé l'ambiguïté de l'énoncé dans mon précédent message.

Voilà pour la 4 mais j'ai pas essayé de regrouper le tout.

Du coup ça devient difficile de regrouper à la fois sur la catégorie de produits et sur le produit !
Soit on fait deux requêtes différentes, soit on regroupe sur le plus petit , c'est à dire le produit.

Pour la 7 :

1)Clients italiens ayant au moins une commande :
Code sql :
SELECT C.CodeCli
FROM Client AS C
INNER JOIN Commande AS O ON O.CodeCli=C.CodeCli
WHERE C.Pays='Italie' GROUP BY C.CodeCli
HAVING COUNT(O.NoCom)>=1

Il y a plus simple !
Un client n'ayant pas passé commande ne sera pas référencé dans la table des commandes donc n'apparaîtra pas dans le résultat d'une jointure interne entre les tables Client et Commande.
Il suffit de lister les clients italiens distinct en joignant la table Client à la table Commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT DISTINCT C.CodeCli
FROM Client AS C
INNER JOIN Commande AS O ON O.CodeCli = C.CodeCli
WHERE C.Pays = 'Italie'

Prends l'habitude d'aérer ton code en laissant des espaces entre les signes !

Mais je ne vois pas comment les compter....

Ben il suffit de les compter !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT COUNT (DISTINCT C.CodeCli)
FROM Client AS C
INNER JOIN Commande AS O ON O.CodeCli = C.CodeCli
WHERE C.Pays = 'Italie'

Y'aura aussi la 8 (la dernière :ouf mais vu qu'elle est formulée tout aussi bien que les autres, j'ai un peu de mal à la comprendre.

8)Quel est-le CA généré par catégorie alimentaire pour les employés sous la responsabilité du Chef des ventes « Patrick Emery », sur le Royaume-Uni, la France et les Etats-Unis ?

Et bien décompose le problème !

Je te laisse essayer, c'est un excellent exercice et cette 8ème requête va être costaud je pense !

Au fait, tu as peut-être le droit de lever les ambiguïtés des énoncés en posant des questions au prof ?

Courage !

[Zaza91280]

Pour la 4, j'ai décidé de regrouper par catégorie ou par produit :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT COUNT(DISTINCT C.NoCom) AS NbComs, SUM(D.Qte*D.PrixUnit), SUM(D.Qte), P.CodeCateg (ou D.Refprod)
FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom = D.NoCom
  INNER JOIN Produit AS P ON D.Refprod = P.Refprod
GROUP BY P.CodeCateg (ou D.Refprod)

Pour la 7:
1)Fournisseurs ayant travaillé avec les clients italiens :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT F.NoFour, F.Societe, F.Contact, F.Tel, F.Fax
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie' AND cl.Societe = F.Societe AND cl.Contact = F.Contact

Mais là je bloque : pour ne retenir que ceux qu'on a déterminé auparavant, on utilise quoi (NOT EXISTS ?) ?

Pour la 8 :
1) Les employés sous la responsabilité du Chef des ventes « Patrick Emery » sur UK, USA et France :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT E.NoEmp
FROM Employe AS E
INNER JOIN Employe_1 AS E1 ON E.RendCompteA = E1.RendCompteA
     INNER JOIN Commande AS C ON E.NoEmp = C.NoEmp
WHERE E1.Nom = 'Emery' AND E1.prenom = 'Patrick' AND E1.Fonction = 'Chef des ventes' AND (C.PaysLiv = 'Royaume-Uni' OR C.PaysLiv = 'Etats-Unis' OR C.PaysLiv = 'France')

Déjà ça .

[CinePhil]

Pour la 4, j'ai décidé de regrouper par catégorie ou par produit :
Code sql :
SELECT COUNT(DISTINCT C.NoCom) AS NbComs, SUM(D.Qte*D.PrixUnit), SUM(D.Qte), P.CodeCateg (ou D.Refprod) FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom = D.NoCom
INNER JOIN Produit AS P ON D.Refprod = P.Refprod
GROUP BY P.CodeCateg (ou D.Refprod)

Il est préférable de mettre un alias sur les colonnes calculées et il est d'usage de mettre en premier la colonne sur laquelle porte le regroupement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT P.CodeCateg -- (ou D.Refprod),
  COUNT(DISTINCT C.NoCom) AS NbComs, 
  SUM(D.Qte*D.PrixUnit) AS CA, 
  SUM(D.Qte) AS Quantite_globale  
FROM Commande AS C
INNER JOIN DetailCommande AS D ON C.Nocom = D.NoCom
  INNER JOIN Produit AS P ON D.Refprod = P.Refprod
GROUP BY P.CodeCateg -- (ou D.Refprod)

Comme tu le vois, le symbole pour les commentaires est une suite de deux tirets.

Pour la 7:
1)Fournisseurs ayant travaillé avec les clients italiens :
Code sql :
SELECT F.NoFour, F.Societe, F.Contact, F.Tel, F.Fax
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
INNER JOIN Commande AS C ON D.Nocom = C.NoCom
INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE cl.Pays = 'Italie' AND cl.Societe = F.Societe AND cl.Contact = F.Contact

Cette condition de restriction ne sera peut-être jamais vraie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND cl.Societe = F.Societe

Les fournisseurs qui sont en même temps clients sont plutôt rares.
Pour avoir les fournisseurs ayant travaillé avec les clients italiens, la restriction sur la nature du client (cl.Pays = 'Italie') est suffisante.

Mais là je bloque : pour ne retenir que ceux qu'on a déterminé auparavant, on utilise quoi (NOT EXISTS ?) ?

Récapitulons...
L'énoncé :

7)Quels sont les fournisseurs (N°, société, contact, tél et fax) ayant
travaillé avec tous les clients italiens ayant au moins une commande ?

Et nous avons déjà ceci :

a) Nombre de clients italiens ayant passé au moins une commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT COUNT (DISTINCT C.CodeCli)
FROM Client AS C
INNER JOIN Commande AS O ON O.CodeCli = C.CodeCli
WHERE C.Pays = 'Italie'

b) Fournisseurs ayant travaillé avec des clients italiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT F.NoFour, F.Societe, F.Contact, F.Tel, F.Fax
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie'

Je réduis la dernière requête car dans un premier temps, seul l'identifiant du fournisseur nous intéresse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT F.NoFour
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie'

c) Il nous faut maintenant savoir avec combien de clients italiens les fournisseurs ci-dessus ont travaillé. Pour ça, il suffit de nouveau de compter ces clients :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT F.NoFour, COUNT(DISTINCT cl.CodeCli) AS Nb_Clients_Italiens
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie'
GROUP BY F.NoFour

Puis on ne retient que les fournisseurs ayant travaillé avec tous les clients italiens en utilisant la requête a) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT F.NoFour, COUNT(DISTINCT cl.CodeCli) AS Nb_Clients_Italiens
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie'
GROUP BY F.NoFour
HAVING COUNT(DISTINCT cl1.CodeCli) = (
  SELECT COUNT (DISTINCT C1.CodeCli)
  FROM Client AS C1
  INNER JOIN Commande AS O ON O.CodeCli = C1.CodeCli
  WHERE C1.Pays = 'Italie'
)

Dans la mesure où toutes les informations demandées appartiennent à la table des fournisseurs, nous pouvons directement étendre le regroupement à toutes les colonnes demandées, lesquelles sont fonctionnellement dépendantes de l'identifiant du fournisseur.
Le COUNT dans le SELECT est devenu inutile puisqu'il n'est utilisé que pour le HAVING donc on l'enlève.
La requête finale devrait être celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT F.NoFour, F.Societe, F.Contact, F.Tel, F.Fax
FROM Fournisseur AS F
INNER JOIN Produit AS P ON F.NoFour = P.NoFour
    INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
          INNER JOIN Commande AS C ON D.Nocom = C.NoCom
                INNER JOIN Client AS cl ON C.CodeCli = cl.CodeCli
WHERE  cl.Pays = 'Italie'
GROUP BY F.NoFour, F.Societe, F.Contact, F.Tel, F.Fax
HAVING COUNT(DISTINCT cl1.CodeCli) = (
  SELECT COUNT (DISTINCT C1.CodeCli)
  FROM Client AS C1
  INNER JOIN Commande AS O ON O.CodeCli = C1.CodeCli
  WHERE C1.Pays = 'Italie'
)

Par contre, si des informations demandées provenaient d'autres tables, il aurait fallu faire un autre SELECT et utiliser la requête ci-dessus en sous-requête.

Pour la 8 :
1) Les employés sous la responsabilité du Chef des ventes « Patrick Emery » sur UK, USA et France :
Code sql :
SELECT E.NoEmp
FROM Employe AS E
INNER JOIN Employe_1 AS E1 ON E.RendCompteA = E1.RendCompteA
INNER JOIN Commande AS C ON E.NoEmp = C.NoEmp
WHERE E1.Nom = 'Emery' AND E1.prenom = 'Patrick' AND E1.Fonction = 'Chef des ventes' AND (C.PaysLiv = 'Royaume-Uni' OR C.PaysLiv = 'Etats-Unis' OR C.PaysLiv = 'France')

Je comprends mieux la présence de la table Employe_1 dans le schéma des relations d'Access !
Il s'agit en fait d'une autre instance de la table Employe !
En SQL pur, il faut donc plutôt faire l'auto-jointure de cette manière :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
FROM Employe AS E
INNER JOIN Employe AS E1

Par contre, ta condition de jointure est fausse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ON E.RendCompteA = E1.RendCompteA

Un employé rend compte à un autre.
La colonne RendCompteA est une clé étrangère faisant référence à l'identifiant de l'employé supérieur hiérarchique.
La condition de jointure est donc celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ON E.RendCompteA = E1.NoEmp

La première requête à faire est donc celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT E.NoEmp
FROM Employe AS E
INNER JOIN Employe AS E1 ON E.RendCompteA = E1.NoEmp
     INNER JOIN Commande AS C ON E.NoEmp = C.NoEmp
WHERE E1.Nom = 'Emery' 
  AND E1.prenom = 'Patrick' 
  AND E1.Fonction = 'Chef des ventes' 
  AND (
    C.PaysLiv = 'Royaume-Uni' 
    OR C.PaysLiv = 'Etats-Unis' 
    OR C.PaysLiv = 'France'
  )

Tu n'étais pas loin !

Je te laisse continuer...

[Zaza91280]

Ok merci beaucoup. Je continue la 8 demain après-midi.

CA généré par catégorie alimentaire :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT P.CodeCateg,  SUM(D.Qte*D.PrixUnit) AS CA
FROM Produit AS P
INNER JOIN DetailCommande AS D ON P.Refprod = D.Refprod
GROUP BY P.CodeCateg

J'avais pensé à faire ça pour le regroupement :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT P.CodeCateg,  SUM(D.Qte*D.PrixUnit) AS CA
FROM Produit AS P
INNER JOIN Employe AS E1 ON E.RendCompteA = E1.NoEmp
     INNER JOIN Commande AS C ON E.NoEmp = C.NoEmp AND D.Nocom = C.NoCom
          INNER JOIN DetailCommande AS D ON D.Refprod = P.Refprod
  WHERE E1.Nom = 'Emery' 
  AND E1.prenom = 'Patrick' 
  AND E1.Fonction = 'Chef des ventes' 
  AND (
    C.PaysLiv = 'Royaume-Uni' 
    OR C.PaysLiv = 'Etats-Unis' 
    OR C.PaysLiv = 'France'
  )
GROUP BY P.CodeCateg

Mais je vais avoir un soucis pour joindre les 2 tables employé.....

[Zaza91280]

On a eu notre DST hier et tout ce que vous m'avez aidé à faire m'a bien servi.
Donc je vous en remercie grandement Philippe et je reviens bientôt car un projet MCC MCT MOT nous a été distribué et comme tout est très clair avec vous, je vais en profiter .

[CinePhil]

je reviens bientôt car un projet MCC MCT MOT nous a été distribué.

C'est dans le forum Merise.