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 482: Línia 482:
  
 
8. Obtener una lista de los clientes contactados por Sue Smith que no han solicitado pedidos con importes superiores a 3.000.
 
8. Obtener una lista de los clientes contactados por Sue Smith que no han solicitado pedidos con importes superiores a 3.000.
 +
 
select c.*, r.nombre
 
select c.*, r.nombre
 
from cliente c, repventa r
 
from cliente c, repventa r
Línia 489: Línia 490:
  
 
     9. Obtener una lista de las oficinas en donde haya algún representante cuya cuota sea más del 55% del objetivo de la oficina.
 
     9. Obtener una lista de las oficinas en donde haya algún representante cuya cuota sea más del 55% del objetivo de la oficina.
 +
 
select *
 
select *
 
from oficina o
 
from oficina o
−
where ofinum  in ( select ofinum
+
where ofinum  in ( select r.ofinum
 
         from repventa  r
 
         from repventa  r
 
         where o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
 
         where o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
 +
 +
o
 +
 
select o.*
 
select o.*
−
from oficina o,repventa  r
+
from oficina o JOIN repventa  r
−
where  o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
+
ON (o.ofinum=r.ofinum) WHERE r.cuota > o.objetivo*0.55);
 +
 
 +
o
  
−
Altra forma de fer-lo
 
 
select *
 
select *
 
from oficina
 
from oficina
 
where ofinum  in ( select ofinum
 
where ofinum  in ( select ofinum
−
         from repventa  r, ofinum o
+
         from repventa  r JOIN ofinum o
−
         where o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
+
         ON (o.ofinum=r.ofinum) WHERE r.cuota > o.objetivo*0.55);
  
  
 
     10. Obtener una lista de los representantes que han tomado algún pedido cuyo importe sea más del 10% de de su cuota.
 
     10. Obtener una lista de los representantes que han tomado algún pedido cuyo importe sea más del 10% de de su cuota.
 +
 
Select *
 
Select *
 
from repventa
 
from repventa
Línia 512: Línia 519:
 
             from pedido
 
             from pedido
 
             where importe > 0.1*cuota);
 
             where importe > 0.1*cuota);
 +
 +
o
 +
 
select r.*
 
select r.*
−
from repventa r, pedido p
+
from repventa r JOIN pedido p
−
where r.repcod=p.repcod and importe > cuota*0.1;
+
ON (r.repcod=p.repcod) where and p.importe > r.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).

Revisió del 20:35, 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=12308»