M2 - Bases de dades / Solucions UF2 BD Ventes: Llenguatges SQL
Contingut
Solució Exercicis BD Ventas
Consultes simples
T5- Funciones de Grupo
1- 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)
2- 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)
3- 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)
4- 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)
5- 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)
6- 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
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
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;
