Diferència entre revisions de la pàgina «M2 - Bases de dades / Solucions UF2 BD Ventes: Llenguatges SQL»

De wikiserver
Dreceres ràpides: navegació, cerca
(→‎T7 Subconsultas)
(→‎T7 Subconsultas)
Línia 515: Línia 515:
 
from repventa r, pedido p
 
from repventa r, pedido p
 
where r.repcod=p.repcod and importe > cuota*0.1;
 
where r.repcod=p.repcod and importe > cuota*0.1;
−
     11. Obtener una lista de las oficinas en las cuales el total de ventas de sus representantes han alcanzado un importe de ventas que supera el 75 % del objetivo de la oficina. Mostrar también el objetivo de cada oficina (suponed que el campo ventas de oficina no existe).
+
      
 +
11. Obtener una lista de las oficinas en las cuales el total de ventas de sus representantes han alcanzado un importe de ventas que supera el 75 % del objetivo de la oficina. Mostrar también el objetivo de cada oficina (suponed que el campo ventas de oficina no existe).
  
 
select o.ofinum, ciudad, objetivo
 
select o.ofinum, ciudad, objetivo
−
from oficina o, repventa r
+
from oficina o JOIN repventa r
−
where o.ofinum=r.ofinum
+
ON (o.ofinum=r.ofinum)
 
group by o.ofinum, ciudad, objetivo
 
group by o.ofinum, ciudad, objetivo
−
having sum(r.ventas) > objetivo*0.75;
+
having sum(r.ventas) > o.objetivo*0.75;
 +
 
 +
o
 +
 
 
select ofinum, ciudad, objetivo
 
select ofinum, ciudad, objetivo
−
from oficina o
+
from oficina o where objetivo*0.75 < (select sum(ventas)
−
where objetivo*0.75 < (select sum(ventas)
+
             from repventa r
−
             from repventa
+
             where r.ofinum=o.ofinum );
−
             where ofinum=o.ofinum );
+
 
 +
o
 +
 
 
select ofinum, ciudad, objetivo
 
select ofinum, ciudad, objetivo
 
from oficina o
 
from oficina o
 
where ofinum in (select ofinum
 
where ofinum in (select ofinum
−
             from repventa
+
             from repventa r
−
             where ofinum=o.ofinum
+
             where r.ofinum=o.ofinum
 
             group by ofinum
 
             group by ofinum
 
             having sum(ventas) > o.objetivo*0.75);
 
             having sum(ventas) > o.objetivo*0.75);

Revisió del 20:31, 20 nov 2019

Solució Exercicis BD Ventas

Consultes simples

T1 Recuperación de datos

T2 Restricción y ordenación de datos

T3 Funciones de una sola Fila

T4 Funciones de Conversion

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)

Consultes Complejas

T6 Uniones

T7 Subconsultas

T8 Operadores de definición

Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Solucions_UF2_BD_Ventes:_Llenguatges_SQL&oldid=12306»