20150223-SQL

1.
SELECT t_commande.*, t_client.societe, t_client.limite_credit FROM t_commande INNER JOIN t_client ON t_commande.client = t_client.num_client

2.
SELECT nom, ville, region
FROM t_vendeur
    INNER JOIN t_agence ON t_vendeur.num_agence = t_agence.num_agence
ORDER BY region,ville ASC;

3.
SELECT ville, nom, titre
FROM t_agence
    INNER JOIN t_vendeur ON t_vendeur.num_salarie = t_agence.chef;

4.
SELECT ville, nom, titre
FROM t_agence
    INNER JOIN t_vendeur ON t_vendeur.num_salarie = t_agence.chef
WHERE objectif > 400000;

5.
SELECT description, montant
FROM t_commande
    INNER JOIN t_produit ON t_commande.produit = t_produit.code_produit AND t_commande.fabricant = t_produit.code_fabricant

6.
SELECT num_commande AS commande, societe AS Client, nom AS Vendeur
FROM t_commande
    INNER JOIN t_client ON t_commande.client = t_client.num_client
    INNER JOIN t_vendeur ON t_commande.vendeur = t_vendeur.num_salarie
WHERE montant > 3850;

7.
SELECT region,ville
FROM t_agence
WHERE ventes > objectif;

8.
SELECT nom
FROM t_vendeur
WHERE ventes > 300000;

9.
SELECT nom
FROM t_vendeur
WHERE manager IS NULL;

10.
SELECT AVG(quota), AVG(ventes)
FROM t_vendeur;

11.
SELECT SUM(quota), SUM(ventes)
FROM t_vendeur;

12.
SELECT nom, SUM(montant)
FROM t_commande
    INNER JOIN t_vendeur ON t_commande.vendeur = t_vendeur.num_salarie
WHERE nom = "Bill Adam";

13.
SELECT AVG(prix)
FROM t_produit
WHERE code_fabricant = "ACI";

14.
SELECT MIN(quota), MAX(quota)
FROM t_vendeur;

15.
SELECT COUNT(*)
FROM t_client;

16.
SELECT COUNT(*)
FROM t_vendeur
WHERE ventes > quota;

17.
SELECT COUNT(*)
FROM t_commande
WHERE montant > 3850;

18.
SELECT COUNT(DISTINCT num_agence)
FROM t_vendeur
WHERE ventes > quota;

19.
SELECT nom, AVG(montant)
FROM t_commande
    INNER JOIN t_vendeur ON t_commande.vendeur = t_vendeur.num_salarie
GROUP BY nom;

20.
SELECT ville, COUNT(*)
FROM t_vendeur
    INNER JOIN t_agence ON t_vendeur.num_agence = t_agence.num_agence
GROUP BY t_vendeur.num_agence;