M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T7-2

1.Quantitat de producte venuda a cada client.

	SELECT o.custid, i.prodid, sum(i.qty) “Qtat Producte”
 	FROM ord o, item i
	WHERE o.ordid = i.ordid
	GROUP BY o.custid, i.prodid
	ORDER BY o.custid;

2.Import total venut per cada empleat.

	
	SELECT c.repid, sum(o.total) “Total Venut”
	FROM customer c, ord o
	WHERE c.custid = o.custid
	GROUP BY c.repid
	ORDER BY c.repid;

3.Empleats que han venut menys que la mitja de tots els empleats.

	
	- Obtenim el que ha venut cada empleat:

	SELECT empno, SUM(nvl(o.total,0)) 
	FROM emp e, customer c, ord o
	WHERE e.empno = c.repid (+) AND c.custid = o.custid (+)
	GROUP BY empno;

	- Calculem el que han venut en mitja tots els empleats:

	SELECT AVG(SUM(nvl(o.total,0))) 
	FROM emp e, customer c, ord o
	WHERE e.empno = c.repid (+) AND c.custid = o.custid (+)
	GROUP BY empno;

	-  Cada empleat amb el que ha venut:
	
	SELECT e.empno, e.ename, avemp.totvenut
	FROM emp e, (SELECT empno, (SUM(nvl(o.total,0))) totvenut
           			  FROM emp e, customer c, ord o
           			  WHERE e.empno = c.repid (+) AND c.custid = o.custid (+)
           			  GROUP BY empno) avemp
	WHERE e.empno = avemp.empno;

	-  Unim-lo  tot:	

	SELECT e.empno, e.ename, avemp.totvenut
	FROM emp e, (SELECT empno, (SUM(nvl(o.total,0))) totvenut
           			  FROM emp e, customer c, ord o
           			  WHERE e.empno = c.repid (+) AND c.custid = o.custid (+)
           			  GROUP BY empno) avemp
           WHERE e.empno = avemp.empno
                         AND avemp.totvenut < (SELECT AVG(SUM(nvl(o.total,0))) 
                                                                FROM emp e, customer c, ord o
                                                                WHERE e.empno = c.repid (+) 
                                                                               AND c.custid = o.custid (+)
                                                                 GROUP BY empno);

4.Quantitat de producte venut. Ordenat per producte.

5.Quantitat de producte venut. Ordenat per quantitat de forma descendent. Han d'aparèixer tots els productes.

6.Quants clients a cada estat?

7.Quantes ordres per any i mes?

         SELECT TO_CHAR(orderdate, 'YYYY MM') "ANY i MES", COUNT(ordid) "Num. Ordres"
         FROM ord
         GROUP BY TO_CHAR(orderdate, 'YYYY MM')
         ORDER BY TO_CHAR(orderdate, 'YYYY MM');

8.Quantes ordres per any?

         SELECT TO_CHAR(orderdate, 'YYYY') "ANY", COUNT(ordid) "Num. Ordres"
         FROM ord
         GROUP BY TO_CHAR(orderdate, 'YYYY MM')
         ORDER BY TO_CHAR(orderdate, 'YYYY MM');

9.En quants productes surt la paraula "TENNIS"?

         SELECT COUNT(prodid) "Núm prod amb paraula TENNIS"
         FROM product
         WHERE upper(descrip) LIKE '%TENNIS%';

10.Increment del preus dels productes des del primer preu fins l'actual.

11.Màximes quantitats de cada producte venudes en una sola comanda. Ordenades de forma ascendent.

	SELECT p.descrip, MAX(i.qty) "Max_Qty"
	FROM product p, item i 
	WHERE p.prodid = i.prodid
	GROUP BY p.descrip
	ORDER BY "Max_Qty" ASC;

12.Mostrar quantitat de cada producte venut. Només ens interessen aquells productes dels que s'hagin venut més de 4 unitats.

	SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES"
	FROM product p, item i 
	WHERE p.prodid = i.prodid
	GROUP BY p.descrip
	HAVING sum(i.qty) > 4;

13.Quantitat de producte venut. Ordenat per producte. Han d'aparèixer tots els productes.

	SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES"
	FROM product p, item i 
	WHERE p.prodid = i.prodid (+)
	GROUP BY p.descrip
	ORDER BY p.descrip;

14.Quantitat de producte venut per empleat.

	SELECT p.descrip, e.ename, SUM(i.qty) "UNITATS VENUDES"
	FROM product p, item i, emp e, customer c, ord o
	WHERE p.prodid = i.prodid AND e.empno = c.repid 
		    AND c.custid = o.custid AND o.ordid = i.ordid
	GROUP BY p.descrip, e.ename
	ORDER BY p.descrip;

15.Número d'ordres de cada COMMPLAN.

	SELECT commplan, COUNT(ordid)
	FROM ord
	GROUP BY commplan; 	

16.Número de productes venuts a cada ordre. Han d'aparèixer totes les ordres.

	SELECT o.ordid, COUNT(i.itemid)
	FROM ord o, item i
	WHERE o.ordid = i.ordid (+)
	GROUP BY o.ordid
	ORDER BY o.ordid;