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.

	
	SELECT i.prodid, sum(i.qty) “Total Producte”
	FROM item i
	GROUP BY i.prodid
	ORDER BY i.prodid;

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

        SELECT p.prodid, sum(p.qty) “Total Producte”
	FROM product p, item i
        WHERE p.prodid = i.prodid (+)
	GROUP BY p.prodid
	ORDER BY p.prodid DESC;

6.Quants clients a cada estat?

        SELECT c.state, count(c.custid) "Qtat Clients"
        FROM customer c
        GROUP BY c.state;

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')
         ORDER BY TO_CHAR(orderdate, 'YYYY');

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 8 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) > 8;

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;

17. Clients que han comprat més de 5 unitats d'algun producte en una comanda (EXISTS).

SELECT custid
FROM customer c
WHERE EXISTS (SELECT o.ordid
	      FROM ord o, item i
              WHERE o.custid = c.custid and o.ordid = i.ordid and i.qty > 10);

18. Quins clients han comprat més que la mitjana.

SELECT c.name
FROM customer c, ord O
WHERE c.custid = o.custid 
GROUP BY c.name
HAVING SUM(o.total) > (SELECT AVG(SUM(o1.total))
                      FROM ord o1
                      GROUP BY o1.custid);

19. Quants clients han comprat més que la mitjana.

SELECT COUNT(COUNT(c.name))
FROM customer c, ord O
WHERE c.custid = o.custid 
GROUP BY c.name
HAVING SUM(o.total) > (SELECT AVG(SUM(o1.total))
                      FROM ord o1
                      GROUP BY o1.custid);

20. Quants empleats no cobren comissió a cada departament. Han d'apareixer tots els departaments.

SELECT d.deptno, count(e.empno)
FROM dept d, emp e
WHERE d.deptno = e.deptno (+) and e.comm is null
GROUP BY d.deptno;

21. Mostrar el prodid dels productes que no s'han venut mai (MINUS).

SELECT prodid
FROM product
MINUS
SELECT prodid
FROM item;

22. Mostrar el prodid i la descripció dels productes que no s'han venut mai (MINUS).

SELECT prodid, descrip
FROM product
MINUS
SELECT product.prodid, descrip
FROM item, product
WHERE ITEM.PRODID=PRODUCT.PRODID;