Diferència entre revisions de la pàgina «M2 - Bases de dades / Solucions UF2 BD Ventes: Llenguatges SQL»
(→Consultes Complejas) |
(→Consultes Complejas) |
||
| Línia 318: | Línia 318: | ||
===Consultes Complejas=== | ===Consultes Complejas=== | ||
| − | + | ||
====T6 Uniones==== | ====T6 Uniones==== | ||
Revisió del 20:27, 6 nov 2019
Contingut
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
1- 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;
2- Es desitja un llistat dels productes amb les seves descripcions, ordenat per la suma total d'imports facturats (pedidos) de cada producte de l'any 1989.
select id_fab, id_producto, descripcion
from productos
join pedidos
on ((id_fab, id_producto) = (fab, producto))
where fecha_pedido between '1989-1-1' and '1989-12-31'
group by id_producto, descripcion
order by sum(importe);
id_producto | descripcion | suma de importe -------------+-------------------+----------------- 41002 | Articulo Tipo 2 | 760.00 2a45c | V Stago Trinquete | 1896.00 2a44g | Pasador Bisanum_cliegra | 2100.00 41004 | Articulo Tipo 4 | 7956.00 4100z | Montador | 15000.00 4100y | Extractor | 27500.00 2a44l | Bisagra Izqda. | 31500.00 (7 filas)
3-Per a cada director (de personal, no d'oficina) excepte per al gerent (el venedor que no té director), vull saber el total de vendes dels seus subordinats. Mostreu codi i nom dels directors.
select director.num_empl, director.nombre, sum(subordinado.ventas)
from repventas as director
join repventas as subordinado
on (subordinado.director = director.num_empl)
where director.director is not null
group by director.num_empl, director.nombre;
num_empl | nombre | sum
----------+-------------+-----------
101 | Dan Roberts | 75985.00
104 | Bob Smith | 960359.00
108 | Larry Fitch | 660092.00
(3 filas)
4- Quins són els 5 productes que han estat venuts a més clients diferents? Mostreu el número de clients per cada producte. A igualtat de nombre de clients es volen ordenats per ordre decreixent d'existències i, a igualtat d'existències, per descripció. Mostreu tots els camps pels quals s'ordena.
select productos.producto, count(distinct pedidos.clie) as "Numero Clients", productos.existencias, productos.descripcion
from pedidos
join productos
on (pedidos.producto = productos.id_producto)
group by pedidos.producto, productos.existencias
order by count(distinct pedidos.clie) desc, productos.existencias desc, productos.descripcion
limit 5;
producto | Numero Clients | existencias ----------+----------------+------------- 41003 | 3 | 207 xk47 | 3 | 38 41003 | 3 | 3 2a45c | 2 | 210 41002 | 2 | 167
5- Es vol llistar el clients (codi i empresa) tals que no hagin comprat cap tipus de frontissa ("bisagra" en castellà, figura a la descripció) i hagin comprat articles de més d'un fabricant diferent.
select num_clie, empresa
from clientes
join pedidos on (num_clie = clie)
join productos on ((id_fab, id_producto) = (fab, producto))
where descripcion not like '%bisagra%'
and not like '%Bisagra%'
group by num_clie, empresa
having count(distinct fab) > 1;
num_clie | empresa
----------+-----------
2103 | Acme Mfg.
(1 fila)
6- Llisteu les oficines per ordre descendent de nombre total de clients diferents amb comandes (pedidos) realizades pels venedors d'aquella oficina, i, a igualtat de clients, ordenat per ordre ascendent del nom del director de l'oficina. Només s'ha de mostrar el codi i la ciutat de l'oficina.
select oficina, ciudad
from oficinas
join repventas as trabajadores
on (oficina = trabajadores.oficina_rep)
join pedidos
on (trabajadores.num_empl = rep)
join repventas as directores
on (dir = directores.num_empl)
group by oficina, ciudad, directores.nombre
order by count(distinct clie) desc, directores.nombre asc;
-->
