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 la descripció dels productes que no s'han venut mai (MINUS).

SELECT prodid, descrip
FROM product
MINUS
SELECT product.prodid, descrip
FROM item, product;