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)
(→‎T6 Uniones)
 
(Hi ha 21 revisions intermèdies del mateix usuari que no es mostren)
Línia 2: Línia 2:
  
 
===Consultes simples===
 
===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====
 
====T5 Funciones de Grupo====
  
−
1- Quina és l'import promig de les comandes de cada venedor?
+
1- Quina és la quota promig mostrada com a "prom_cuota" i la venda promig mostrades com a "prom_ventas" dels venedors?
 +
 
 +
<pre>
 +
SELECT AVG(cuota) AS prom_ventas, AVG(ventas) AS prom_ventas
 +
FROM repventas;
 +
</pre>
 +
<pre>
 +
    prom_ventas    |    prom_ventas
 +
---------------------+---------------------
 +
563509.400000000000 | 550000.000000000000
 +
</pre>
 +
 
 +
2- Quin és el rendiment de quota promig dels venedors (percentatge de les vendes respecte la quota)?
 +
<pre>
 +
SELECT AVG(100*(VENTAS/CUOTA))
 +
FROM REPVENTAS;
 +
</pre>
 +
3- Quines són les quotes totals com a "t_cuota" i vendes totals com a "t_ventas" de tots els venedors?
 +
<pre>
 +
SELECT SUM(cuota) AS t_cuota, SUM(ventas) AS t_ventas
 +
FROM repventas;
 +
</pre>
 +
<pre>
 +
t_cuota  |  t_ventas
 +
----------+----------
 +
2700000.00|2893532.00
 +
(1 row)
 +
</pre>
 +
 
 +
4- Calcula el preu mig dels productes del fabricant amb identificador "aci".
 +
<pre>
 +
SELECT AVG(precio)
 +
FROM productos
 +
WHERE id_fab='aci';
 +
</pre>
 +
 
 +
5- Quines són les quotes assignades mínima i màxima?
 +
<pre>
 +
SELECT MIN(CUOTA), MAX(CUOTA)
 +
FROM REPVENTAS;
 +
</pre>
 +
<pre>
 +
min |      max
 +
---------+---------
 +
200000.00|350000.00
 +
(1 row)
 +
</pre>
 +
 
 +
6- Quina és la data de comanda més antiga?
 +
<pre>
 +
SELECT MIN(FECHA_PEDIDO)
 +
FROM PEDIDOS;
 +
</pre>
 +
<pre>
 +
min
 +
----------
 +
01-04-1989
 +
(1 row)
 +
</pre>
 +
 
 +
7- Quin és el major percentatge de rendiment de vendes respecte les quotes de tots els venedors?
 +
<pre>
 +
SELECT MAX(100* VENTAS/CUOTA)
 +
FROM REPVENTAS;
 +
</pre>
 +
<pre>
 +
max
 +
------------
 +
135.44
 +
(1 row)
 +
</pre>
 +
 
 +
8- Quants clients hi ha?
 +
<pre>
 +
SELECT COUNT(NUM_CLIE)
 +
FROM CLIENTES;
 +
</pre>
 +
<pre>
 +
count
 +
-----
 +
  21
 +
(1 row)
 +
</pre>
 +
9- Quants venedors superen la seva quota?
 +
<pre>
 +
SELECT COUNT(NOMBRE)
 +
FROM REPVENTAS
 +
WHERE VENTAS>CUOTA;
 +
</pre>
 +
<pre>
 +
count
 +
-----
 +
    7
 +
(1 row)
 +
</pre>
 +
10- Quantes comandes amb un import superior a 25000 hi ha en els registres?
 +
<pre>
 +
SELECT COUNT (*)
 +
FROM PEDIDOS
 +
WHERE IMPORTE>25000;
 +
</pre>
 +
<pre>
 +
count
 +
-----
 +
    4
 +
(1 row)
 +
</pre>
 +
 
 +
 
 +
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.
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
 
 +
12- Compta les files que hi ha a repventas, les files del camp vendes i les del camp quota.
 +
<pre>
 +
SELECT COUNT(*), COUNT(VENTAS), COUNT(CUOTA)
 +
FROM REPVENTAS;
 +
</pre>
 +
<pre>
 +
count|count|count
 +
-----+-----+-----
 +
  10|  10|    9
 +
(1 row)
 +
Observa què la tercera columna és nou i no pas deu.
 +
</pre>
 +
 
 +
13- Mostra que la suma de restar (vendes menys quota) és diferent que sumar vendes i restar-li la suma de quotes.
 +
<pre>
 +
SELECT SUM(VENTAS),SUM(CUOTA),SUM(VENTAS-CUOTA),
 +
(SUM(VENTAS)-SUM(CUOTA))
 +
FROM REPVENTAS;
 +
</pre>
 +
<pre>
 +
      sum|      sum|      sum| ?column?
 +
----------+----------+---------+---------
 +
2893532.00|2700000.00|117547.00|193532.00
 +
</pre>
 +
 
 +
14- Quants títols diferents tenen els venedors?
 +
<pre>
 +
SELECT COUNT(DISTINCT TITULO)
 +
FROM REPVENTAS;
 +
</pre>
 +
15- Quantes oficines de vendes tenen venedors que superen les seves quotes?
 +
<pre>
 +
SELECT COUNT(DISTINCT OFICINA_REP)
 +
FROM REPVENTAS
 +
WHERE VENTAS>CUOTA;
 +
</pre>
 +
 
 +
16- De la taula clients quants clients diferents i venedors diferents hi ha.
 +
<pre>
 +
SELECT COUNT(NUM_CLIE), COUNT(DISTINCT REP_CLIE) FROM CLIENTES;
 +
</pre>
 +
17- De la taula comandes seleccionar quantes comandes diferents i clients diferents hi ha
  
 +
18- Calcular la mitjana dels imports de les comandes.
 +
<pre>
 +
SELECT AVG(IMPORT) FROM PEDIDOS;
 +
</pre>
 +
19- Calcula la mitjana de l'import d'una comanda realitzada pel client amb nom d'empresa "Acme Mfg."
 +
<pre>
 +
SELECT  AVG(importe)
 +
FROM pedidos JOIN clientes ON clie=num_clie;
 +
</pre>
 +
 +
20- Quina és l'import promig de les comandes de cada venedor?
 +
<pre>
 
SELECT rep, avg(importe)
 
SELECT rep, avg(importe)
 
from pedidos
 
from pedidos
 
group by rep;
 
group by rep;
−
 
+
</pre>
 
<pre>
 
<pre>
−
 
 
  rep |      avg
 
  rep |      avg
 
-----+------------------
 
-----+------------------
Línia 26: Línia 202:
 
(9 rows)  
 
(9 rows)  
 
</pre>
 
</pre>
−
2- Quin és el rang (màxim i mínim) de quotes dels venedors per cada oficina?
+
21- Quin és el rang (màxim i mínim) de quotes dels venedors per cada oficina?
−
 
+
<pre>
 
SELECT oficina_rep, min(cuota), max(cuota)
 
SELECT oficina_rep, min(cuota), max(cuota)
 
from repventas
 
from repventas
 
group by oficina_rep;
 
group by oficina_rep;
−
 
+
</pre>
 
<pre>
 
<pre>
 
  oficina_rep |    min    |    max
 
  oficina_rep |    min    |    max
Línia 43: Línia 219:
 
(6 rows)
 
(6 rows)
 
</pre>
 
</pre>
−
3- Quants venedors estan asignats a cada oficina?
+
22- Quants venedors estan asignats a cada oficina?
−
 
+
<pre>
 
SELECT oficina_rep, count(*)
 
SELECT oficina_rep, count(*)
 
from repventas
 
from repventas
 
group by oficina_rep;
 
group by oficina_rep;
−
 
+
</pre>
 
<pre>
 
<pre>
 
  oficina_rep | count
 
  oficina_rep | count
Línia 60: Línia 236:
 
(6 rows)
 
(6 rows)
 
</pre>
 
</pre>
−
4- Per cada venedor calcular quants clients diferents ha atès (ha fet comandes)?
+
23- Per cada venedor calcular quants clients diferents ha atès (ha fet comandes)?
−
 
+
<pre>
 
select count(distinct clie), 'clients pel rep. de vendes' as "", rep
 
select count(distinct clie), 'clients pel rep. de vendes' as "", rep
 
from pedidos
 
from pedidos
 
group by rep;
 
group by rep;
 +
</pre>
 
<pre>
 
<pre>
 
  count |                            | rep
 
  count |                            | rep
Línia 79: Línia 256:
 
(9 rows)
 
(9 rows)
 
</pre>
 
</pre>
−
5- Calcula el total dels imports de les comandes fetes per cada client a cada vendedor.
+
24- Calcula el total dels imports de les comandes fetes per cada client a cada vendedor.
−
 
+
<pre>
 
SELECT rep, clie, sum(importe)
 
SELECT rep, clie, sum(importe)
 
from pedidos
 
from pedidos
 
group by rep, clie;
 
group by rep, clie;
 +
</pre>
 
<pre>
 
<pre>
 
  rep | clie |  sum
 
  rep | clie |  sum
Línia 107: Línia 285:
 
(18 rows)
 
(18 rows)
 
</pre>
 
</pre>
−
6- El mateix que a la qüestió anterior, però ordenat per client i dintre de client per venedor.
+
25- El mateix que a la qüestió anterior, però ordenat per client i dintre de client per venedor.
−
 
+
<pre>
 
SELECT clie, rep, sum(importe)
 
SELECT clie, rep, sum(importe)
 
from pedidos
 
from pedidos
 
group by clie, rep
 
group by clie, rep
 
order by clie, rep;  
 
order by clie, rep;  
 +
</pre>
 
<pre>
 
<pre>
 
clie | rep |  sum
 
clie | rep |  sum
Línia 138: Línia 317:
  
 
===Consultes Complejas===
 
===Consultes Complejas===
 +
  
 
====T6 Uniones====
 
====T6 Uniones====
−
 
+
<!--
 
1- Calcula la mitjana de l'import d'una comanda realitzada pel client amb nom d'empresa "Acme Mfg."
 
1- Calcula la mitjana de l'import d'una comanda realitzada pel client amb nom d'empresa "Acme Mfg."
 
<pre>
 
<pre>
Línia 191: Línia 371:
  
 
<pre>
 
<pre>
−
select productos.producto, count(distinct pedidos.clie) as "Numero Clients", productos.existencias
+
select productos.producto, count(distinct pedidos.clie) as "Numero Clients", productos.existencias, productos.descripcion
 
from pedidos
 
from pedidos
 
     join productos
 
     join productos
 
     on (pedidos.producto = productos.id_producto)
 
     on (pedidos.producto = productos.id_producto)
 
group by pedidos.producto, productos.existencias
 
group by pedidos.producto, productos.existencias
−
order by count(distinct pedidos.clie) desc, productos.existencias desc
+
order by count(distinct pedidos.clie) desc, productos.existencias desc, productos.descripcion
 
limit 5;
 
limit 5;
 
</pre>
 
</pre>
 +
<!--
 
<pre>
 
<pre>
 
  producto | Numero Clients | existencias
 
  producto | Numero Clients | existencias
Línia 208: Línia 389:
 
  41002    |              2 |        167
 
  41002    |              2 |        167
 
</pre>
 
</pre>
 +
 
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.
 
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.
  
Línia 214: Línia 396:
 
from clientes
 
from clientes
 
     join pedidos on (num_clie = clie)
 
     join pedidos on (num_clie = clie)
−
join productos on ((id_fab, id_producto) = (fab, producto))
+
join productos on ((id_fab=fab and id_producto= producto))
−
where descripcion not like '%bisagra%'
+
where descripcion not like '%isagra%'
−
    and not like '%Bisagra%'
 
 
group by num_clie, empresa
 
group by num_clie, empresa
 
having count(distinct fab) > 1;
 
having count(distinct fab) > 1;
 +
 
</pre>
 
</pre>
 
<pre>
 
<pre>
Línia 240: 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 r.ofinum
 +
        from repventa  r
 +
        where o.ofinum=r.ofinum and r.cuota > o.objetivo*0.55);
 +
 +
o
 +
 +
select o.*
 +
from oficina o JOIN repventa  r
 +
ON (o.ofinum=r.ofinum) WHERE r.cuota > o.objetivo*0.55);
 +
 +
o
 +
 +
select *
 +
from oficina
 +
where ofinum  in ( select ofinum
 +
        from repventa  r JOIN ofinum o
 +
        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.
 +
 +
Select *
 +
from repventas r
 +
where r.num_empl in (select rep
 +
            from pedidos
 +
            where importe > 0.1*r.cuota and rep=r.num_empl);
 +
 +
o
 +
 +
Select *
 +
from repventas r
 +
where r.cuota*0.1< any (select importe
 +
            from pedidos
 +
            where rep=r.num_empl);
 +
 +
o
 +
 +
select r.*
 +
from repventa r JOIN pedido p
 +
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).
 +
 +
select o.oficina, ciudad, objetivo
 +
from oficinas o JOIN repventas r
 +
ON (o.oficina=r.oficina_rep)
 +
group by o.oficina, ciudad, objetivo
 +
having sum(r.ventas) > o.objetivo*0.75;
 +
 +
o
 +
 +
select oficina, ciudad, objetivo
 +
from oficinas o where objetivo*0.75 < (select sum(ventas)
 +
            from repventa r
 +
            where r.oficina_rep=o.oficina );
 +
 +
o
 +
 +
select oficina, ciudad, objetivo
 +
from oficinas o
 +
where oficina in (select oficina_rep
 +
            from repventas r
 +
            where r.oficina_rep=o.oficina
 +
            group by oficina_rep
 +
            having sum(ventas) > o.objetivo*0.75);
 +
 +
-->
 +
 +
==== 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ó de 17:12, 22 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=12317»