[SQL] Aide pour mes requêtes

Bonjour à tous,

Je suis actuellement en DUT Info 1ère année et la prof nous a demandé de préparer des requêtes SQL (8) en vue du DST.
J'en ai déjà rédigées 5 mais je souhaiterais avoir vos retours car je pense avoir fait des erreurs :?.

Voici d'abord la base sous Access :
http://img36.imageshack.us/img36/8029/sqlj.png

Et mes requêtes :

1. Quels sont les produits (N°, nom, description et prix unitaire) de la
catégorie Boissons proposés par des fournisseurs français ou italiens ?

Code:

---

1 2 3 4 5

SELECT DISTINCT P.Refprod, P.Nomprod, P.PrixUnit FROM Categorie C, Fournisseur F, Produit P WHERE F.NoFour=P.NoFour AND C.CodeCateg=P.CodeCateg AND C.NomCateg="Boissons" AND (F.Pays="France" OR F.Pays="Italie") ORDER BY 1,2,3

---

2. Quels sont les fournisseurs (N°, société, contact et adresse) situés
dans le même pays et avec le même contact (fonction) que le
fournisseur de nom « Escargots Nouveaux» ? Afficher également les
catégories de produits qu’ils proposent.

Code:

---

1 2 3

SELECT F.NoFour, F.Contact, F.Societe, F.Adresse, P.CodeCateg FROM Fournisseur F, Fournisseur C, Produit P WHERE F.Societe="Escargots Nouveaux" AND F.Pays=C.Pays AND F.Contact=C.Contact AND F.NoFour=P.NoFour

---

3. Quels sont les fournisseurs (N°, société, contact) proposant des
produits qui n’ont pas été encore commandés ou qui sont
indisponibles.

Code:

---

1 2 3 4 5

SELECT DISTINCT F.NoFour, F.Societe, F.Contact FROM Fournisseur F, Produit P WHERE F.NoFour=P.NoFour AND (P.UnitesStock=0 OR P.UnitesCom=0) ORDER BY 1,2,3

---

5. Donner pour chaque catégorie, les fournisseurs correspondant aux
produits les moins chers avec les coordonnées du fournisseur, la
référence, le nom et le prix du produit.

Code:

---

1 2 3 4

SELECT F.Adresse, F.Ville, F.Region, F.CodePostal, F.Pays, F.Tel, P.Refprod, P.Nomprod, P.PrixUnit FROM Fournisseur F, Produit P, Categorie C,(SELECT CodeCateg, Min(PrixUnit) AS MinPrix FROM Produit GROUP BY CodeCateg) WHERE C.CodeCateg=P.CodeCateg AND P.NoFour=F.NoFour ORDER BY 9,8,7,1,2,3,4,5,6

---

6. Quel la moyenne des frais de port des commandes de 2001
concernant un client italien et un fournisseur allemand ?

Code:

---

1 2 3 4

SELECT Avg(O.Port) AS MoyFDP FROM Commande O , Fournisseur F, Client C, DetailCommande D, Produit P, (SELECT CodeCli FROM Commande O WHERE YEAR(DateCom)=2001) WHERE C.CodeCli=O.CodeCli AND O.NoCom=D.Nocom AND D.Refprod=P.Refprod AND P.NoFour=F.NoFour AND (F.Pays="Allemagne" AND C.Pays="Italie")

---

Merci d'avance à tous :D.

1) Les requêtes 1, 2 et 6 sont syntaxiquement fausses, à cause des valeurs de types chaine de caractères.
2) les jointures se font dans une clause JOIN et non dans le WHERE. La syntaxe imbécile duproduit cartésien avec resriction dans le where date d'avant 1986 !!!
3) quand vous avez un "qui ne sont pas..." il faut utyiliser un NOT IN ou un NOT EXISTS
4) la 5 est fausse car vous devez faire une jointure avec la table dérivée contenant le MIN.

et encore bien d'autres choses !

A +

Bonjour,

j'ai regardé rapidement ce que vous aviez fait :

1) à par le fait que pour moi, ce sont des ' à la place des " c'est bon.

2) N'ayant pas compris pourquoi il y a le mot fonction entre parenthèse, je partirai comme vous sur les contacts, sinon, là, vous n'allez affichez que le fournisseur "escargot nouveaux" et ce, plusieurs fois ;)

3)Si les deux restrictions que vous faites représente bien le nombre de commande et le nombre de stock, pour moi, ça passe (mais j'aurai comme sqlpro cherché dans les commandes)
Je suis désolé, mais n'est pas le temps pour les autres (j'éditerai peut être).

Par contre, j'aimerai tempérer le point 2 de Sqlpro, car en entreprise, il y a beaucoup d'existant avec cette syntaxe, et beaucoup de personne à ne pas choquer, donc il est bon de connaitre toutes les écriture possible, même celle qui sont moins lisibles.

Merci pour votre réponse :D.

Citation:

---

Envoyé par SQLpro

1) Les requêtes 1, 2 et 6 sont syntaxiquement fausses, à cause des valeurs de types chaine de caractères.

---

Qu'entendez-vous par la notion de chaine de caractères ?

Citation:

---

Envoyé par SQLpro

2) les jointures se font dans une clause JOIN et non dans le WHERE. La syntaxe imbécile duproduit cartésien avec resriction dans le where date d'avant 1986 !!!

---

Je rejoins l'avis de icsor car nous dans toutes nos requêtes, même dans les corrigés des profs, nous n'avons jamais utilisé la clause JOIN :(

Citation:

---

Envoyé par SQLpro

3) quand vous avez un "qui ne sont pas..." il faut utyiliser un NOT IN ou un NOT EXISTS
4) la 5 est fausse car vous devez faire une jointure avec la table dérivée contenant le MIN.

---

Je regarde tout ça ;)

Citation:

---

Envoyé par icsor

Bonjour,

j'ai regardé rapidement ce que vous aviez fait :

1) à par le fait que pour moi, ce sont des ' à la place des " c'est bon.

2) N'ayant pas compris pourquoi il y a le mot fonction entre parenthèse, je partirai comme vous sur les contacts, sinon, là, vous n'allez affichez que le fournisseur "escargot nouveaux" et ce, plusieurs fois ;)

3)Si les deux restrictions que vous faites représente bien le nombre de commande et le nombre de stock, pour moi, ça passe (mais j'aurai comme sqlpro cherché dans les commandes)
Je suis désolé, mais n'est pas le temps pour les autres (j'éditerai peut être).

Par contre, j'aimerai tempérer le point 2 de Sqlpro, car en entreprise, il y a beaucoup d'existant avec cette syntaxe, et beaucoup de personne à ne pas choquer, donc il est bon de connaitre toutes les écriture possible, même celle qui sont moins lisibles.

---

Merci pour votre réponse :D.
Je regarde tout ça.

J'ai refait la 3 et la 5 :

3)

Code:

---

1 2 3 4 5

SELECT DISTINCT F.NoFour, F.Societe, F.Contact FROM Fournisseur F, Produit P WHERE F.NoFour IN (SELECT NoFour FROM Produit WHERE UnitesStock=0) AND P.Refprod NOT IN (SELECT Refprod FROM DetailCommande D) ORDER BY 1,2,3

---

5)

Code:

---

1 2 3 4

SELECT DISTINCT F.Adresse, F.Ville, F.Region, F.CodePostal, F.Pays, F.Tel, P.Refprod, P.Nomprod, P.PrixUnit FROM Fournisseur F, Produit P, Produit O, Categorie C,(SELECT CodeCateg, Min(PrixUnit) AS MinPrix FROM Produit O GROUP BY CodeCateg) WHERE C.CodeCateg=P.CodeCateg AND P.NoFour=F.NoFour AND P.CodeCateg=O.CodeCateg ORDER BY 9,8,7,1,2,3,4,5,6

---

Citation:

---

Envoyé par Zaza91280

Je rejoins l'avis de icsor car nous dans toutes nos requêtes, même dans les corrigés des profs, nous n'avons jamais utilisé la clause JOIN :(

---

Tu as de très mauvais profs ! :aie:

Je trouve scandaleux qu'on enseigne encore aujourd'hui uniquement la syntaxe des jointures obsolète depuis 20 ans !

Apprends la bonne syntaxe au plus vite et renseigne toi auprès du ou des profs pour savoir si elle sera acceptée lors du passage du diplôme. Ce serait un comble si ce n'est pas le cas ! Au besoin, refile l'URL du cours de SQLPro à ton prof pour qu'il se mette à jour.

L'ancienne syntaxe :
- complique la lecture des requêtes en mélangeant ce qui tient de la jointure de ce qui tient de la restriction sur les données ;
- peut engendrer facilement des erreurs plus difficiles à débugguer qu'avec JOIN, notamment quand il faut plusieurs alias d'une table et qu'il manque des conditions de jointures ;
- peut produire dans certains cas des résultats différents et donc faux ! Voir le point III.D.3 du tuto de SQLPro.

En cadeau, voici la première requête réécrite avec la bonne syntaxe :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT DISTINCT P.Refprod, P.Nomprod, P.PrixUnit FROM Produit P INNER JOIN Categorie C ON C.CodeCateg = P.CodeCateg INNER JOIN Fournisseur F ON F.NoFour = P.NoFour WHERE C.NomCateg = 'Boissons' AND ( F.Pays = 'France' OR F.Pays = 'Italie' ) ORDER BY P.Refprod, P.Nomprod, P.PrixUnit

---

A noter que le ORDER BY n'est pas très utile ! A chaque Refprod n'est en principe associé qu'un seul NomProd et un seul PrixUnit.
ORDER BY P.RefProd serait largement suffisant.

Ok merci beaucoup pour vos éclaircissements je vais me mettre au JOIN alors si c'est mieux :D.

Citation:

---

Envoyé par CinePhil

Tu as de très mauvais profs ! :aie:

Je trouve scandaleux qu'on enseigne encore aujourd'hui uniquement la syntaxe des jointures obsolète depuis 20 ans !

---

++

Citation:

---

Envoyé par CinePhil

L'ancienne syntaxe :
- complique la lecture des requêtes en mélangeant ce qui tient de la jointure de ce qui tient de la restriction sur les données ;
- peut engendrer facilement des erreurs plus difficiles à débugguer qu'avec JOIN, notamment quand il faut plusieurs alias d'une table et qu'il manque des conditions de jointures ;
- peut produire dans certains cas des résultats différents et donc faux ! Voir le point III.D.3 du tuto de SQLPro.

---

Et surtout, rend les "LEFT/RIGHT OUTER JOIN" incompatibles d'un SGBD à l'autre (pas la même notation entre Oracle et SQL Server par exemple), et interdit tout recours au "FULL OUTER JOIN", rarement utilisé, certes, mais bien existant !

Voilà, j'ai refait le tout en essayant de tenir compte des remarques des uns et des autres, dites moi si vous voyez encore des incohérences :

1)

Code:

---

1 2 3 4 5 6 7 8 9

SELECT DISTINCT P.Refprod, P.Nomprod, P.PrixUnit FROM Produit P INNER JOIN Categorie C ON C.CodeCateg = P.CodeCateg INNER JOIN Fournisseur F ON F.NoFour = P.NoFour WHERE C.NomCateg = 'Boissons' AND ( F.Pays = 'France' OR F.Pays = 'Italie' ) ORDER BY P.Refprod, P.Nomprod, P.PrixUnit

---

(merci à CinePhil :D)

2)

Code:

---

1 2 3 4

SELECT C.NoFour, C.Contact, C.Societe, C.Adresse, P.CodeCateg FROM Fournisseur F, Produit P INNER JOIN Fournisseur C ON F.Pays=C.Pays AND F.Contact=C.Contact WHERE F.Societe="Escargots Nouveaux" AND C.NoFour=P.NoFour

---

3)

Code:

---

1 2 3 4 5

SELECT DISTINCT F.NoFour, F.Societe, F.Contact FROM Fournisseur F WHERE F.NoFour IN (SELECT NoFour FROM Produit WHERE UnitesStock=0) AND P.Refprod NOT IN (SELECT Refprod FROM DetailCommande D) ORDER BY 1,2,3

---

5)

Code:

---

1 2 3 4

SELECT DISTINCT F.Adresse, F.Ville, F.Region, F.CodePostal, F.Pays, F.Tel, P.Refprod, P.Nomprod, P.PrixUnit FROM Fournisseur F, Produit P, Categorie C,(SELECT CodeCateg, Min(PrixUnit) AS MinPrix FROM Produit P GROUP BY CodeCateg) AS PM WHERE C.CodeCateg=P.CodeCateg AND P.NoFour=F.NoFour AND P.CodeCateg=PM.CodeCateg AND P.PrixUnit=PM.MinPrix ORDER BY 9,8,7,1,2,3,4,5,6

---

6)

Code:

---

1 2 3 4

SELECT Avg(O.Port) AS MoyFDP FROM Commande O , Fournisseur F, Client C, DetailCommande D, Produit P, (SELECT CodeCli FROM Commande O WHERE YEAR(DateCom)=2001) WHERE C.CodeCli=O.CodeCli AND O.NoCom=D.Nocom AND D.Refprod=P.Refprod AND P.NoFour=F.NoFour AND (F.Pays="Allemagne" AND C.Pays="Italie")

---

Et la 6 avec les INNER JOIN (ce n'est pas bon, mais je la poste quand même pour que vous puissiez me dire mes erreurs :aie:)
6)

Code:

---

1 2 3 4 5 6 7

SELECT Avg(Commande.Port) AS MoyFDP FROM Commande O, (SELECT CodeCli FROM Commande O WHERE YEAR(DateCom)=2001) INNER JOIN Client C ON C.CodeCli=O.CodeCli INNER JOIN DetailCommande D ON D.Nocom=O.NoCom INNER JOIN Produit P ON P.Refprod=D.Refprod INNER JOIN Fournisseur F ON F.NoFour=C.NoFour WHERE (F.Pays="Allemagne" AND C.Pays="Italie")

---

PS:Je sais que j'ai fait beaucoup d'erreurs au niveau des JOIN mais est-ce que vous pourriez m'en dire plus car j'ai pas bien compris le tuto fourni par CinePhil, notamment que fait-t-on quand les 2 tables où se fait la jointure sont déjà présentes dans le FROM.

Pas le temps de répondre en détail maintenant mais sur ça :

Citation:

---

Envoyé par Zaza91280

que fait-t-on quand les 2 tables où se fait la jointure sont déjà présentes dans le FROM.

---

Justement, avec la syntaxe normalisée, on ne met qu'une seule table dans le FROM !

Code:

---

1 2 3 4 5

SELECT lescolonnes FROM unetable JOIN uneautretable ON condition_jointure JOIN encoreuneautretable ON autrecondition_jointure ...

---

Donc même si j'ai :
Select A.XXXX, B.XXXX
dans le FROM je met que AXXXXX A ou BXXXXX B ?

Voilà, j'ai réécrit la 2 et la 5 avec INNER JOIN (mais si vous avez le temps, essayez de regarder mon post de 15h43 aussi svp car j'y ai refait les autres :P) :

2)

Code:

---

1 2 3 4 5

SELECT C.NoFour, C.Contact, C.Societe, C.Adresse, P.CodeCateg FROM Fournisseur F INNER JOIN Fournisseur C ON C.Pays= F.Pays AND C.Contact=F.Contact INNER JOIN Produit P ON P.NoFour= C.NoFour WHERE F.Societe="Escargots Nouveaux"

---

5)

Code:

---

1 2 3 4 5

SELECT DISTINCT F.Adresse, F.Ville, F.Region, F.CodePostal, F.Pays, F.Tel, P.Refprod, P.Nomprod, P.PrixUnit FROM Categorie C, (SELECT CodeCateg, Min(PrixUnit) AS MinPrix FROM Produit P GROUP BY CodeCateg) AS PM INNER JOIN Produit P ON P.CodeCateg = C.CodeCateg AND P.PrixUnit=PM.MinPrix AND P.CodeCateg=PM.CodeCateg INNER JOIN Fournisseur F ON F.NoFour = P.NoFour ORDER BY 9,8,7,1,2,3,4,5,6

---

J'attends donc vos remarques :D.

Citation:

---

Envoyé par Zaza91280

Donc même si j'ai :
Select A.XXXX, B.XXXX
dans le FROM je met que AXXXXX A ou BXXXXX B ?

---

Oui, parce que ceci :

Code:

---

Select A.XXXX, B.XXXX

---

Veut dire que les colonnes viennent de deux tables et qu'il faut les joindre. Donc la suite sera :

Code:

---

1 2	FROM AXXXXX A JOIN BXXXXX B ON condition_jointure

---

Citation:

---

1. Quels sont les produits (N°, nom, description et prix unitaire) de la
catégorie Boissons proposés par des fournisseurs français ou italiens ?

Code:

---

1 2 3 4 5 6 7 8 9

SELECT DISTINCT P.Refprod, P.Nomprod, P.PrixUnit FROM Produit P INNER JOIN Categorie C ON C.CodeCateg = P.CodeCateg INNER JOIN Fournisseur F ON F.NoFour = P.NoFour WHERE C.NomCateg = 'Boissons' AND ( F.Pays = 'France' OR F.Pays = 'Italie' ) ORDER BY P.Refprod, P.Nomprod, P.PrixUnit

---

---

C'était ma requête, on va dire que c'est bon, je ne revérifie pas. 8-)

Citation:

---

2. Quels sont les fournisseurs (N°, société, contact et adresse) situés
dans le même pays et avec le même contact (fonction) que le
fournisseur de nom « Escargots Nouveaux» ? Afficher également les
catégories de produits qu’ils proposent.

Code:

---

1 2 3 4 5

SELECT C.NoFour, C.Contact, C.Societe, C.Adresse, P.CodeCateg FROM Fournisseur F INNER JOIN Fournisseur C ON C.Pays= F.Pays AND C.Contact=F.Contact INNER JOIN Produit P ON P.NoFour= C.NoFour WHERE F.Societe="Escargots Nouveaux"

---

---

Déjà, SQLPro t'a dit que les valeurs textuelles doivent être entourées d'apostrophes et non pas de guillemets.

Décomposons le problème...
1) Le pays et le contact du fournisseur 'Escargots Nouveaux' :

Code:

---

1 2 3

SELECT Contact, Pays FROM Fournisseur WHERE Societe = 'Escargots Nouveaux'

---

2) Les fournisseurs ayant le même pays et le même contact, en indiquant les catégories de produits qu'ils proposent :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT F.NoFour, F.Contact, F.Societe, F.Adresse, P.CodeCateg FROM Fournisseur AS F INNER JOIN Produit AS P ON P.NoFour = F.NoFour INNER JOIN ( SELECT e.Contact, e.Pays FROM Fournisseur AS e WHERE e.Societe = 'Escargots Nouveaux' ) AS t ON F.Contact = t.Contact AND F.Pays = t.Pays

---

J'ai cependant l'impression qu'il manque quelque chose dans ton schéma de relations, à cause de ce détail de l'énoncé :

Citation:

---

avec le même contact (fonction)

---

Ne manquerait-il pas une relation entre les fournisseurs et les employés, ce qui permettrait d'avoir la fonction du contact et non le contact ?
Pas hyper clair l'énoncé ! :?

Citation:

---

3. Quels sont les fournisseurs (N°, société, contact) proposant des
produits qui n’ont pas été encore commandés ou qui sont
indisponibles.

Code:

---

1 2 3 4 5

SELECT DISTINCT F.NoFour, F.Societe, F.Contact FROM Fournisseur F WHERE F.NoFour IN (SELECT NoFour FROM Produit WHERE UnitesStock=0) AND P.Refprod NOT IN (SELECT Refprod FROM DetailCommande D) ORDER BY 1,2,3

---

---

Je t'ai déjà signalé je crois que lorsqu'il y a une dépendance fonctionelle d'une colonne par rapport à une autre, inutile de mettre les deux colonnes dans le ORDER BY.
Ici, à un NoFour correspond une seule Societe et un seul Contact donc ORDER BY F.NoFour est suffisant (ou ORDER BY F.Societe).
Evite les ORDER BY numéros_colonnes, ça peut donner des résultats inattendus si tu changes l'ordre des colonnes dans le SELECT ou si tu en rajoutes.

Décomposaons le problème de nouveau...
1) Quels sont les produits non encore commandés ou indisponibles ?

Code:

---

1 2 3 4 5 6 7 8

SELECT p.RefProd FROM Produit AS p WHERE p.Indisponible = TRUE OR NOT EXISTS ( SELECT * FROM DetailCommande AS d WHERE d.RefProd = p.RefProd )

---

2) Quels sont les fournisseurs de ces produits ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT DISTINCT F.NoFour, F.Societe, F.Contact FROM Fournisseur AS F INNER JOIN ( SELECT p.RefProd FROM Produit AS p WHERE p.Indisponible = TRUE OR NOT EXISTS ( SELECT * FROM DetailCommande AS d WHERE d.RefProd = p.RefProd ) ) AS t ON F.NoFour = t.NoFour ORDER BY F.Societe

---

Il n'y a pas de 4) ?

Citation:

---

5. Donner pour chaque catégorie, les fournisseurs correspondant aux
produits les moins chers avec les coordonnées du fournisseur, la
référence, le nom et le prix du produit.

Code:

---

1 2 3 4 5

SELECT DISTINCT F.Adresse, F.Ville, F.Region, F.CodePostal, F.Pays, F.Tel, P.Refprod, P.Nomprod, P.PrixUnit FROM Categorie C, (SELECT CodeCateg, Min(PrixUnit) AS MinPrix FROM Produit P GROUP BY CodeCateg) AS PM INNER JOIN Produit P ON P.CodeCateg = C.CodeCateg AND P.PrixUnit=PM.MinPrix AND P.CodeCateg=PM.CodeCateg INNER JOIN Fournisseur F ON F.NoFour = P.NoFour ORDER BY 9,8,7,1,2,3,4,5,6

---

---

Là aussi, il faut décomposer le problème pour y arriver...
1) Quel est le prix le moins cher par catégorie ?

Code:

---

1 2 3

SELECT p.CodeCateg, MIN(p.PrixUnit) AS Prix_mini FROM Produit AS p GROUP BY p.CodeCateg

---

2) Quels sont les produits les moins chers par catégorie ?
Ce sont les produits ayant ce prix dans la catégorie :

Code:

---

1 2 3 4 5 6 7 8

SELECT p1.RefProd, p1.NomProd, p1.PrixUnit, p1.CodeCateg, p1.NoFour FROM Produit AS p1 INNER JOIN ( SELECT p.CodeCateg, MIN(p.PrixUnit) AS Prix_mini FROM Produit AS p GROUP BY p.CodeCateg ) t ON p1.CodeCateg = t.CodeCateg AND p1.PrixUnit = t.Prix_mini

---

3) On ajoute les infos du fournisseur de ces produits :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT p1.RefProd, p1.NomProd, p1.PrixUnit, p1.CodeCateg, p1.NoFour, F.Societe, F.Adresse, F.CodePostal, F.Ville, F.Pays, F.Tel, F.Fax FROM Produit AS p1 INNER JOIN ( SELECT p.CodeCateg, MIN(p.PrixUnit) AS Prix_mini FROM Produit AS p GROUP BY p.CodeCateg ) t ON p1.CodeCateg = t.CodeCateg AND p1.PrixUnit = t.Prix_mini INNER JOIN Fournisseur AS F ON p.NoFour = F.NoFour ORDER BY p1.CodeCateg, F.Societe, p1.NomProd

---

Citation:

---

6. Quel la moyenne des frais de port des commandes de 2001
concernant un client italien et un fournisseur allemand ?

Code:

---

1 2 3 4 5 6 7

SELECT Avg(Commande.Port) AS MoyFDP FROM Commande O, (SELECT CodeCli FROM Commande O WHERE YEAR(DateCom)=2001) INNER JOIN Client C ON C.CodeCli=O.CodeCli INNER JOIN DetailCommande D ON D.Nocom=O.NoCom INNER JOIN Produit P ON P.Refprod=D.Refprod INNER JOIN Fournisseur F ON F.NoFour=C.NoFour WHERE (F.Pays="Allemagne" AND C.Pays="Italie")

---

---

Beurk ! :aie:

L'énoncé est ambigü !

Y a t-il deux informations à donner ?
- La moyenne pour les clients italiens ;
- La moyenne pour les fournisseurs allemands.

Ou bien faut-il donner la moyenne des frais de ports pour les commandes des clients italien contenant des produits de fournisseurs allemands ?

Comme tu n'as fait qu'une moyenne, je vais faire comme toi.

On décompose...
1) Quelles sont les commandes de 2001 de clients italiens contenant des produits de fournisseurs allemands ?

Code:

---

1 2 3 4 5 6 7 8 9

SELECT DISTINCT cm.NoCom FROM Commande AS cm INNER JOIN Client AS cl ON cm.CodeCli = cl.CodeCli INNER JOIN DetailCommande AS dc ON cm.NoCom = dc.NoCom INNER JOIN Produit AS p ON dc.RefProd = p.RefProd INNER JOIN Fournisseur AS f ON p.NoFour = f.NoFour WHERE cl.Pays = 'Italie' AND f.Pays = 'Allemagne' AND YEAR(cm.DateCom) = 2001

---

Je mets le DISTINCT parce qu'il peut y avoir plusieurs produits de fournisseurs allemands dans une commande et il ne faut pas compter plusieurs fois les commandes.

2) Quelle est la moyenne de frais de port de ces commandes ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT AVG(cd.Port) AS Moyenne_frais_port FROM Commande AS cd INNER JOIN ( SELECT DISTINCT cm.NoCom FROM Commande AS cm INNER JOIN Client AS cl ON cm.CodeCli = cl.CodeCli INNER JOIN DetailCommande AS dc ON cm.NoCom = dc.NoCom INNER JOIN Produit AS p ON dc.RefProd = p.RefProd INNER JOIN Fournisseur AS f ON p.NoFour = f.NoFour WHERE cl.Pays = 'Italie' AND f.Pays = 'Allemagne' AND YEAR(cm.DateCom) = 2001 ) AS t ON cd.NoCom = t.NoCom

---

Voilà ! J'espère que tu as compris le prinicpe : Décompose !

Je ne sais pas si le schéma de relation t'a été donné ou si c'est toi qui l'a fait mais j'aurais aussi des remarques à son sujet...
1) Les codes, références, numéros, matricules... pouvant être alphanumériques sont de mauvaises clés primaires.
Il vaut mieux ajouter un identifiant anonyme, invariable, jamais présenté à l'utilisateur mais utilisé par le programme applicatif et surtout dans les jointures entre les tables. Il sera de type entier et auto-incrémenté, sauf pour les tables issues d'associations (comme DetailCommande) qui reçoivent pour clé primaire composée l'ensemble des identifiants des tables participant à l'association.

2) Les pays, les régions, les villes ou même les codes postaux devraient être externalisés dans des tables de référence. De même les titres de courtoisie, les fonctions...
Je me demande d'ailleurs ce qu'est la fonction d'un client ou d'un fournisseur !

3) Pourquoi y a t-il deux tables Employe ?

4) Que contient UnitesCom dans la table Produit ?
Les unités qui ont été commandées ?
=> C'est une quantité calculable qui n'a pas à être stockée.

5) Eviter le % dans le nom d'une colonne (Remise% dans la table DetailCommande)

Merci beaucoup d'avoir pris le temps de fouiller autant votre réponse.
Tout d'abord, la base nous a été fournie dans son intégralité par nos profs.
Ensuite, il manque des requêtes car on en 8 en tout mais j'avais fait que celles qui me semblaient les plus simples.
Pour le reste je regarde ça ce soir car je suis en cours.

Citation:

---

Envoyé par Zaza91280

Merci beaucoup d'avoir pris le temps de fouiller autant votre réponse.
Tout d'abord, la base nous a été fournie dans son intégralité par nos profs.

---

Eh ben ! Ils ont besoin de cours de modélisation ! :aie:
Je veux bien leur donner des cours mais tout se paye ! 8-)

J'imagine la tête de tes potes quand tu vas leur montrer cette discussion ! :lol:

Citation:

---

Envoyé par CinePhil

Eh ben ! Ils ont besoin de cours de modélisation ! :aie:
Je veux bien leur donner des cours mais tout se paye ! 8-)

J'imagine la tête de tes potes quand tu vas leur montrer cette discussion ! :lol:

---

héhéhé je leur dirai que vous pouvez faire un tarif de groupe comme ça ils se remettent tous à jour :P (à moins qu'on le voit plus loin dans le programme car ils considèrent peut-être que c'est trop difficile à notre stade :roll:).
Bref je ne sais pas mais en tout cas un grand merci pour m'avoir aussi bien expliqué comment structurer mes requêtes et surtout je m'attèle à faire les 3 restantes sur ce modèle le mieux possible ;).

Voici la requête N°4 :
4)Pour chaque catégorie, donner le nombre de commandes, la quantité
globale commandée et le chiffre d'affaire généré pour chacun de ses produits.

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

Code:

---

1 2 3 4

SELECT count(*) AS NbComs, P.CodeCateg FROM Produit AS P INNER JOIN DetailCommande AS D ON D.Refprod=P.Refprod GROUP BY P.CodeCateg

---

2)La quantité globale commandée :

Code:

---

1 2 3 4

SELECT SUM(Qte), P.CodeCateg FROM DetailCommande AS D INNER JOIN Produit AS P ON D.Refprod=P.Refprod GROUP BY P.CodeCateg

---

3)Le CA c'est le (nombre de produits commandés*prix unitaire) et donc j'ai réussi à déterminer le nombre de produits commandés par type de produit :

Code:

---

1 2 3 4

SELECT count(*) AS NbProds, P.Refprod FROM Produit AS P INNER JOIN DetailCommande AS D ON D.Refprod=P.Refprod GROUP BY P.Refprod

---

Mais je ne vois pas après comment multiplier cette quantité par le PrixUnit puis grouper par Catégories distinctes :cry:.

Voici la requête N°7 :
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 ?

1) Tous les clients italiens ayant au moins une commande :

Code:

---

1 2 3 4 5

SELECT count(*) AS NbComs, C.CodeCli FROM Commande AS C INNER JOIN Client AS cl ON C.CodeCli=cl.CodeCli WHERE cl.Pays='Italie' AND NbComs>=1 GROUP BY C.CodeCli

---

Citation:

---

Envoyé par Zaza91280

Voici la requête N°4 :
4)Pour chaque catégorie, donner le nombre de commandes, la quantité
globale commandée et le chiffre d'affaire généré pour chacun de ses produits.

---

Enoncé encore ambigü :
Ca commence par "Pour chaque catégorie" et ça finit par "pour chacun de ces produits" !
Encore un informaticien qui a négligé les cours de français ton prof ! :aie:

Citation:

---

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

---

Avec ta requête, tu comptes le nombre de lignes de commande par catégorie mais dans une commande il peut y avoir plusieurs produits de même catégories donc cette commande sera comptée plusieurs fois.
Il faut faire une jointure avec la table des commandes et compter les commandes distinctes par catégorie de produit.
Je te laisse essayer avant de te donner la réponse.

Citation:

---

2)La quantité globale commandée :
Code sql :
SELECT SUM(Qte), P.CodeCateg
FROM DetailCommande AS D
INNER JOIN Produit AS P ON D.Refprod=P.Refprod
GROUP BY P.CodeCateg

---

Si l'exercice est bien à faire par catégorie de produit et non par produit, c'est bizarre de vouloir additionner les quantités de produits différents, qui en plus ne sont peut-être pas vendus selon le même colisage ou la même unité !
S'il s'agit de la catégorie "visserie", il peut y en avoir des petites vendues par cent et des grosses vendues à l'unité. Du coup ça ne donnera pas le nombre de vis vendues !
C'est bien une idée de prof ça ! :aie:

Bon à part ça la requête est bonne. :ccool:

Citation:

---

3)Le CA c'est le (nombre de produits commandés*prix unitaire) et donc j'ai réussi à déterminer le nombre de produits commandés par type de produit :
Code sql :
SELECT count(*) AS NbProds, P.Refprod
FROM Produit AS P
INNER JOIN DetailCommande AS D ON D.Refprod=P.Refprod
GROUP BY P.Refprod

Mais je ne vois pas après comment multiplier cette quantité par le PrixUnit puis grouper par Catégories distinctes :cry:.

---

Avec COUNT(*), tu comptes le nombre de lignes regroupées par catégorie.
Ici il faut additionner les (nombre de produits commandés*prix unitaire).
C'est beaucoup moins tordu que tu ne crois. Essaie encore.

Et essaie de rassembler le tout en une seule requête, ce qui est moins évident mais doit être faisable.

Citation:

---

Voici la requête N°7 :
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 ?

1) Tous les clients italiens ayant au moins une commande :
Code sql :
SELECT count(*) AS NbComs, C.CodeCli
FROM Commande AS C
INNER JOIN Client AS cl ON C.CodeCli=cl.CodeCli
WHERE cl.Pays='Italie' AND NbComs>=1 GROUP BY C.CodeCli

---

Ta requête compte le nombre de commandes par client italien ayant passé au moins une commande. Ce n'est pas ce qui est demandé.

Il y a un petit piège dans l'énoncé :

Citation:

---

ayant travaillé avec tous les clients italiens

---

a) Il faut commencer par compter combien il y a de clients italiens ayant passé au moins une commande.
b) Ensuite chercher les fournisseurs ayant travaillé avec les clients italiens puis ne retenir que ceux qui ont travaillé avec tous ces clients trouvés en a).

Bon courage !