M2 - Bases de dades / Solucions UF2 BD Ventes: Llenguatges SQL

Solució Exercicis BD Ventas

Consultes simples

T5 Funciones de Grupo

1- Quina és la quota promig mostrada com a "prom_cuota" i la venda promig mostrades com a "prom_ventas" dels venedors?

SELECT AVG(cuota) AS prom_ventas, AVG(ventas) AS prom_ventas
	FROM repventas;

     prom_ventas     |     prom_ventas
---------------------+---------------------
 563509.400000000000 | 550000.000000000000

2- Quin és el rendiment de quota promig dels venedors (percentatge de les vendes respecte la quota)?

SELECT AVG(100*(VENTAS/CUOTA))
		FROM REPVENTAS;

3- Quines són les quotes totals com a "t_cuota" i vendes totals com a "t_ventas" de tots els venedors?

SELECT SUM(cuota) AS t_cuota, SUM(ventas) AS t_ventas
FROM repventas;

 t_cuota  |   t_ventas
----------+----------
2700000.00|2893532.00
(1 row)

4- Calcula el preu mig dels productes del fabricant amb identificador "aci".

SELECT AVG(precio) 
FROM productos 
WHERE id_fab='aci';

5- Quines són les quotes assignades mínima i màxima?

SELECT MIN(CUOTA), MAX(CUOTA)
FROM REPVENTAS;

min	|      max
---------+---------
200000.00|350000.00
(1 row)

6- Quina és la data de comanda més antiga?

SELECT MIN(FECHA_PEDIDO)
FROM PEDIDOS;

min
----------
01-04-1989
(1 row)

7- Quin és el major percentatge de rendiment de vendes respecte les quotes de tots els venedors?

SELECT MAX(100* VENTAS/CUOTA)
FROM REPVENTAS;

max
------------
135.44
(1 row)

8- Quants clients hi ha?

SELECT COUNT(NUM_CLIE)
FROM CLIENTES;

count
-----
   21
(1 row)

9- Quants venedors superen la seva quota?

SELECT COUNT(NOMBRE)
FROM REPVENTAS
WHERE VENTAS>CUOTA;

count
-----
    7
(1 row)

10- Quantes comandes amb un import superior a 25000 hi ha en els registres?

SELECT COUNT (*)
FROM PEDIDOS
WHERE IMPORTE>25000;

count
-----
    4
(1 row)

11- Trobar l'import mitjà de les comandes, l'import total de les comandes, l'import mitjà de les comandes com a percentatge del límit de crèdit del client i l'import mitjà de comandes com a percentatge de la quota del venedor.

SELECT AVG(IMPORTE), SUM(IMPORTE), 	(100*AVG(IMPORTE/LIMITE_CREDITO),
(100*AVG(IMPORTE/CUOTA);
FROM PEDIDOS, CLIENTES, REPVENTAS
WHERE CLIE=NUM_CLIE,
AND REP=NUM_EMPL;

12- Compta les files que hi ha a repventas, les files del camp vendes i les del camp quota.

SELECT COUNT(*), COUNT(VENTAS), COUNT(CUOTA)
	FROM REPVENTAS;

count|count|count
-----+-----+-----
   10|   10|    9
(1 row)
Observa què la tercera columna és nou i no pas deu.

13- Mostra que la suma de restar (vendes menys quota) és diferent que sumar vendes i restar-li la suma de quotes.

SELECT SUM(VENTAS),SUM(CUOTA),SUM(VENTAS-CUOTA),
 (SUM(VENTAS)-SUM(CUOTA))
FROM REPVENTAS;

       sum|       sum|      sum| ?column?
----------+----------+---------+---------
2893532.00|2700000.00|117547.00|193532.00

14- Quants títols diferents tenen els venedors?

SELECT COUNT(DISTINCT TITULO)
	FROM REPVENTAS;

15- Quantes oficines de vendes tenen venedors que superen les seves quotes?

SELECT COUNT(DISTINCT OFICINA_REP)
	FROM REPVENTAS
WHERE VENTAS>CUOTA;

16- De la taula clients quants clients diferents i venedors diferents hi ha.

SELECT COUNT(NUM_CLIE), COUNT(DISTINCT REP_CLIE) FROM CLIENTES;

17- De la taula comandes seleccionar quantes comandes diferents i clients diferents hi ha

18- Calcular la mitjana dels imports de les comandes.

SELECT AVG(IMPORT) FROM PEDIDOS;

19- Calcula la mitjana de l'import d'una comanda realitzada pel client amb nom d'empresa "Acme Mfg."

SELECT  AVG(importe)
FROM pedidos JOIN clientes ON clie=num_clie;

20- Quina és l'import promig de les comandes de cada venedor?

SELECT rep, avg(importe)
from pedidos
group by rep;

 rep |       avg
-----+------------------
 101 |  8876.0000000000
 102 |  5694.0000000000
 103 |  1350.0000000000
 105 |  7865.4000000000
 106 | 16479.0000000000
 107 | 11477.3333333333
 108 |  8376.1428571429
 109 |  3552.5000000000
 110 | 11566.0000000000
(9 rows) 

21- Quin és el rang (màxim i mínim) de quotes dels venedors per cada oficina?

SELECT oficina_rep, min(cuota), max(cuota)
from repventas
group by oficina_rep;

 oficina_rep |    min    |    max
-------------+-----------+-----------
          11 | 275000.00 | 300000.00
          12 | 200000.00 | 300000.00
          13 | 350000.00 | 350000.00
          21 | 350000.00 | 350000.00
          22 | 300000.00 | 300000.00
        NULL |      NULL |      NULL
(6 rows)

22- Quants venedors estan asignats a cada oficina?

SELECT oficina_rep, count(*)
from repventas
group by oficina_rep;

 oficina_rep | count
-------------+-------
          11 |     2
          12 |     3
          13 |     1
          21 |     2
          22 |     1
        NULL |     1
(6 rows)

23- Per cada venedor calcular quants clients diferents ha atès (ha fet comandes)?

select count(distinct clie), 'clients pel rep. de vendes' as "", rep
from pedidos
group by rep;

 count |                            | rep
-------+----------------------------+-----
     3 | clients pel rep. de vendes | 101
     3 | clients pel rep. de vendes | 102
     1 | clients pel rep. de vendes | 103
     2 | clients pel rep. de vendes | 105
     2 | clients pel rep. de vendes | 106
     2 | clients pel rep. de vendes | 107
     3 | clients pel rep. de vendes | 108
     1 | clients pel rep. de vendes | 109
     1 | clients pel rep. de vendes | 110
(9 rows)

24- Calcula el total dels imports de les comandes fetes per cada client a cada vendedor.

SELECT rep, clie, sum(importe)
from pedidos
group by rep, clie;

 rep | clie |   sum
-----+------+----------
 101 | 2102 |  3978.00
 101 | 2108 |   150.00
 101 | 2113 | 22500.00
 102 | 2106 |  4026.00
 102 | 2114 | 15000.00
 102 | 2120 |  3750.00
 103 | 2111 |  2700.00
 105 | 2103 | 35582.00
 105 | 2111 |  3745.00
 106 | 2101 |  1458.00
 106 | 2117 | 31500.00
 107 | 2109 | 31350.00
 107 | 2124 |  3082.00
 108 | 2112 | 47925.00
 108 | 2114 |  7100.00
 108 | 2118 |  3608.00
 109 | 2108 |  7105.00
 110 | 2107 | 23132.00
(18 rows)

25- El mateix que a la qüestió anterior, però ordenat per client i dintre de client per venedor.

SELECT clie, rep, sum(importe)
from pedidos
group by clie, rep
order by clie, rep; 

clie | rep |   sum
------+-----+----------
 2101 | 106 |  1458.00
 2102 | 101 |  3978.00
 2103 | 105 | 35582.00
 2106 | 102 |  4026.00
 2107 | 110 | 23132.00
 2108 | 101 |   150.00
 2108 | 109 |  7105.00
 2109 | 107 | 31350.00
 2111 | 103 |  2700.00
 2111 | 105 |  3745.00
 2112 | 108 | 47925.00
 2113 | 101 | 22500.00
 2114 | 102 | 15000.00
 2114 | 108 |  7100.00
 2117 | 106 | 31500.00
 2118 | 108 |  3608.00
 2120 | 102 |  3750.00
 2124 | 107 |  3082.00
(18 rows)