CORRIGE application 1 Gestion des ventes
Cours : CORRIGE application 1 Gestion des ventes. Recherche parmi 297 000+ dissertationsPar Tamera49 • 6 Avril 2020 • Cours • 489 Mots (2 Pages) • 459 Vues
CORRIGE application 1 Gestion des ventes
Requête 1
SELECT numvente, datevente, (puht*quant) AS montantht FROM VENTE; |
Requête 2
SELECT numvente, datevente, (puht*quant) AS montant, (montant*0.20) AS TVA, (montant+TVA) AS montantTTC FROM VENTE Order by 5 asc; |
Requête 3
SELECT COUNT(numvente) AS nbreventes FROM VENTE; |
Requête 4
SELECT sum(quant*puht) AS CATOTAL FROM VENTE; |
Ou 2ème solution :
SELECT sum(montantht) AS CAHT FROM requête1; |
Requête 5
SELECT sum(quant*puht*0.20) AS tvacollectée FROM VENTE; |
Requête 6
SELECT SUM(quant*puht) AS CAHT, (CAHT*0.20) AS TVA, (CAHT+TVA) AS TTC FROM VENTE; |
Requête 7
SELECT AVG(quant*puht) AS CAmoyen FROM VENTE; |
Requête 8
SELECT MAX(quant*puht) AS ventemaxi FROM VENTE; |
Requête 9
SELECT datevente, sum(quant*puht) AS CAtotal, AVG(quant*puht) AS CAmoyen FROM VENTE GROUP BY datevente; |
Requête 11
SELECT VENTE.codevendeur, nomvendeur, sum(quant*puht) AS CAtotal FROM VENTE, VENDEUR WHERE vente.codevendeur=vendeur.codevendeur GROUP BY VENTE.codevendeur, nomvendeur; |
Requête 12
SELECT codevendeur, nomvendeur,count(datevente) AS nbredeventes FROM VENTE,vendeur WHERE vente.codevendeur=vendeur.codevendeur GROUP BY codevendeur, nomvendeur; |
Requête 13
SELECT nomvendeur, sum(quant*puht) AS CA FROM VENTE, VENDEUR WHERE VENTE.codevendeur=VENDEUR.Codevendeur AND datevente=#02/02/2016# GROUP BY nomvendeur; |
Requête 14
SELECT nomvendeur, sum(quant*puht) AS CA FROM VENTE, VENDEUR WHERE vente.codevendeur=vendeur.codevendeur AND datevente=#2/02/2016# GROUP BY vente.codevendeur, nomvendeur HAVING sum(quant*puht)> 500; |
Requête 15
SELECT codeprod, designprod, sum(quant*puht) AS CA FROM VENTE,PRODUIT WHERE VENTE.codeprod=PRODUIT.codeprod GROUP BY codeprod, designprod HAVING sum(quant*puht) >600; |
Requête 16
SELECT nomprodprod, sum(quant*puht) AS CA FROM VENTE, PRODUIT WHERE VENTE.codeprod=PRODUIT.codeprod AND datevente=#2/02/2016# GROUP BY designprod HAVING sum(quant*puht) >600; |
CORRIGE application 2- Notes
Schéma relationnel
CENTRE(Numcentre, Nomcentre, Adresse1, Adresse2, Codepostal, Ville, Codecentre)
COMPOSITION (Numepreuve#,Numoption#)
EPREUVE (Numepreuve, Nomepreuve)
ETUDIANT (Numetudiant, Nometudiant, Prénom_étudiant, Adresse_étudiant,
Code_postal_étudiant, Code_postal_étudiant, Année_en_préparatoire, Numcentre#, Numlangue#, Numsession#, Numoption #)
HORAIRE (Numepreuve#, Numsession#, date, heure)
LANGUE (Numlangue, Nomlangue)
NOTATION (Numcandicat#, Numepreuve#, note)
OPTION (Numoption, Nomoption)
SESSION (Numsession, Nom_session)
Graphe des DF :
[pic 1]
Requête 1 SELECT nometudiant, prénom_étudiant FROM ETUDIANT, CENTRE WHERE ETUDIANT.numcentre=CENTRE.numcentre AND nomcentre="Hestrad" AND année_en_préparatoire=2; |
... Uniquement disponible sur LaDissertation.com
|