Discussion :

[Zaza91280]

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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 .

[SQLpro]

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 +

[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.

[Zaza91280]

Merci pour votre réponse .

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 ?

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

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

[Zaza91280]

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 .
Je regarde tout ça.

[Zaza91280]

J'ai refait la 3 et la 5 :

3)

Code sql :

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

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 sql :

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

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

[CinePhil]

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 !

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 :

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

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.

[Zaza91280]

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

[StringBuilder]

Tu as de très mauvais profs !

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

++

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 !

[Zaza91280]

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 sql :

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

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 )

2)

Code sql :

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

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 sql :

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

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 sql :

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

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 sql :

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

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 )
6)

Code sql :

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

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.

[CinePhil]

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

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 :

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

1
2
3
4
5
SELECT lescolonnes
FROM unetable
JOIN uneautretable ON condition_jointure
JOIN encoreuneautretable ON autrecondition_jointure
...

[Zaza91280]

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

[Zaza91280]

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 ) :

2)

Code sql :

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

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 sql :

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

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 .

[CinePhil]

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 :

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

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 :

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

1
2
FROM AXXXXX A 
JOIN BXXXXX B ON condition_jointure

[CinePhil]

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 :

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

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.

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 :

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

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 :

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

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 :

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

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é :

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é !

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 :

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

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 :

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

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 :

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

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) ?

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 :

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

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 :

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

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 :

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

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 :

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

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

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

Code :

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

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 !

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 :

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

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 :

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

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)

[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.
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.

[CinePhil]

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 !
Je veux bien leur donner des cours mais tout se paye !

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

[Zaza91280]

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

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

héhéhé je leur dirai que vous pouvez faire un tarif de groupe comme ça ils se remettent tous à jour (à moins qu'on le voit plus loin dans le programme car ils considèrent peut-être que c'est trop difficile à notre stade ).
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 .

[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.

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
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 sql :

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

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 sql :

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

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 .

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 :

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

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

[CinePhil]

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 !

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.

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 !

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

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 .

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.

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é :

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 !