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
(→‎T6 Uniones)
(→‎Consultes Complejas)
Línia 422: Línia 422:
 
order by count(distinct clie) desc, directores.nombre asc;
 
order by count(distinct clie) desc, directores.nombre asc;
 
</pre>
 
</pre>
 +
-->
 +
=== T7 Subconsultas ===
 +
<!--
 +
1. Obtener una lista de los representantes cuyas cuotas son iguales ó superiores al objetivo de la oficina de Atlanta.
 +
Select nombre, cuota
 +
From repventa
 +
where cuota>= (select objetivo
 +
        from oficina
 +
        where lower(ciudad)='atlanta');
 +
Aquest exercici no es pot fer amb joins
 +
2. Obtener una lista de todos los clientes que fueron contactados por primera vez por Bill Adams.
 +
 +
Select  r.nombre
 +
from cliente c, repventa r
 +
where c.repcod=r.repcod and lower(nombre)='bill adams';
 +
Select  nombre
 +
from cliente
 +
where repcod = (    select repcod
 +
          from repventa
 +
          where lower(nombre)='bill adams');
 +
 +
3. Obtener una lista de todos los productos del fabricante ACI cuyas existencias superan a las existencias del producto 41004 del mismo fabricante.
 +
select *
 +
from producto
 +
where lower(fabcod)='aci' and exist > (    select exist
 +
                        from producto
 +
                        where lower(fabcod)='aci'
 +
                            and prodcod='41004');
 +
select p2.*
 +
from producto p1, producto p2
 +
where lower(p1.fabcod)='aci' and  lower(p2.fabcod)='aci' and p1.prodcod='41004' and p2.exist > p1.exist;
 +
4. Obtener una lista de los representantes que trabajan en las oficinas que han logrado superar su objetivo de ventas.
 +
select nombre
 +
from repventa
 +
where ofinum in (select ofinum
 +
        from oficina 
 +
        where ventas  >  objetivo);
 +
 +
 +
select nombre
 +
from repventa r, oficina o
 +
where r.ofinum=o.ofinum and o.ventas  >  objetivo;
 +
    5. Obtener una lista de los representantes que no trabajan en las oficinas dirigidas por Larry Fitch.
 +
select repcod,nombre,ofinum
 +
from repventa
 +
where ofinum  not in (select o.ofinum
 +
            from oficina o, repventa r
 +
            where o.director= r.repcod and lower(nombre)='larry fitch');
 +
OJO!!!  No és equivalent aquest codi!!
 +
select repcod,nombre,o.ofinum
 +
from repventa r, oficina o
 +
where o.director= r.repcod and lower(nombre)!='larry fitch';
 +
6. Obtener una lista de todos los clientes que han solicitado pedidos del fabricante ACI entre enero y junio de 1990.
 +
select nombre, fecha
 +
from cliente c, pedido p
 +
where c.cliecod = p.cliecod and lower(fabcod)='aci' and
 +
    to_char(fecha,'yyyy-mm') between '1990-01' and '1990-06';
 +
7. Obtener una lista de los productos de los que se ha tomado un pedido de 25.000  ó más.
 +
 +
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
 +
from cliente c, repventa r
 +
where c.repcod=r.repcod and lower(r.nombre)='sue smith' and
 +
        c.cliecod not in  (    select cliecod                                        from pedido                                            where importe > 3000);
 +
 +
 +
    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 *
 +
from oficina o
 +
where ofinum  in ( select ofinum
 +
        from repventa  r
 +
        where o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
 +
select o.*
 +
from oficina o,repventa  r
 +
where  o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
 +
 +
Altra forma de fer-lo
 +
select *
 +
from oficina
 +
where ofinum  in ( select ofinum
 +
        from repventa  r, ofinum o
 +
        where o.ofinum=r.ofinum and 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.
 +
Select *
 +
from repventa
 +
where repcod in (select repcod
 +
            from pedido
 +
            where importe > 0.1*cuota);
 +
select r.*
 +
from repventa r, pedido p
 +
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).
 +
 +
select o.ofinum, ciudad, objetivo
 +
from oficina o, repventa r
 +
where o.ofinum=r.ofinum
 +
group by o.ofinum, ciudad, objetivo
 +
having sum(r.ventas) > objetivo*0.75;
 +
select ofinum, ciudad, objetivo
 +
from oficina o
 +
where objetivo*0.75 < (select sum(ventas)
 +
            from repventa
 +
            where ofinum=o.ofinum );
 +
select ofinum, ciudad, objetivo
 +
from oficina o
 +
where ofinum in (select ofinum
 +
            from repventa
 +
            where ofinum=o.ofinum
 +
            group by ofinum
 +
            having sum(ventas) > o.objetivo*0.75);
 +
Intersección,unión y diferencia
 +
    12. Obtener una lista de todos los productos cuyo precio exceda de 3500 y de los cuales hay algún pedido con un importe superior a 35000.
 +
select fabcod, prodcod, descrip
 +
from producto
 +
where precio >3500
 +
    intersect
 +
select pe.fabcod,pe.prodcod,descrip
 +
from pedido pe, producto pr
 +
where pe.fabcod||pr.prodcod = pr.fabcod||pr.prodcod and importe > 35000 ;
 +
en lloc de:
 +
select  distinct pe.fabcod||pe.prodcod Codi ,descrip producte
 +
from pedido pe, producto pr
 +
where pe.fabcod||pe.prodcod = pr.fabcod||pr.prodcod and importe > 35000 and precio >3500 ;
 +
13. Obtener una lista de todos los productos cuyo precio más IVA exceda de 3.500 o bien haya algún pedido cuyo importe más IVA exceda de 30.000.
 +
select fabcod, prodcod, descrip
 +
from producto
 +
where precio >3500
 +
    union
 +
select pe.fabcod,pe.prodcod,descrip
 +
from pedido pe, producto pr
 +
where pe.fabcod||pr.prodcod = pr.fabcod||pr.prodcod and importe > 35000 ;
 +
 +
select  distinct pe.fabcod||pe.prodcod Codi ,descrip producte
 +
from pedido pe, producto pr
 +
where pe.fabcod||pe.prodcod = pr.fabcod||pr.prodcod and importe > 35000 or precio >3500 ;
 +
14. Obtener los códigos de los representantes que son directores de oficina y que no han tomado ningún pedido.
 +
select director
 +
from oficina
 +
    except
 +
select repcod
 +
from pedido;
 +
-->
 +
 +
=== T8 Operadores de definición ===
 +
 +
<!--
 +
1. Obtener una lista de todos los productos cuyo precio exceda de 3500 y de los cuales hay algún pedido con un importe superior a 35000.
 +
select fabcod, prodcod, descrip
 +
from producto
 +
where precio >3500
 +
    intersect
 +
select pe.fabcod,pe.prodcod,descrip
 +
from pedido pe, producto pr
 +
where pe.fabcod||pr.prodcod = pr.fabcod||pr.prodcod and importe > 35000 ;
 +
en lloc de:
 +
select  distinct pe.fabcod||pe.prodcod Codi ,descrip producte
 +
from pedido pe, producto pr
 +
where pe.fabcod||pe.prodcod = pr.fabcod||pr.prodcod and importe > 35000 and precio >3500 ;
 +
2. Obtener una lista de todos los productos cuyo precio más IVA exceda de 3.500 o bien haya algún pedido cuyo importe más IVA exceda de 30.000.
 +
select fabcod, prodcod, descrip
 +
from producto
 +
where precio >3500
 +
    union
 +
select pe.fabcod,pe.prodcod,descrip
 +
from pedido pe, producto pr
 +
where pe.fabcod||pr.prodcod = pr.fabcod||pr.prodcod and importe > 35000 ;
 +
 +
select  distinct pe.fabcod||pe.prodcod Codi ,descrip producte
 +
from pedido pe, producto pr
 +
where pe.fabcod||pe.prodcod = pr.fabcod||pr.prodcod and importe > 35000 or precio >3500 ;
 +
 +
3. Obtener los códigos de los representantes que son directores de oficina y que no han tomado ningún pedido.
 +
select director
 +
from oficina
 +
    except
 +
select repcod
 +
from pedido;
 
-->
 
-->

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