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 NATURAL JOIN item i
	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 NATURAL JOIN ord o
	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 e.empno, SUM(nvl(o.total,0)) 
        FROM emp e LEFT OUTER JOIN customer c ON e.empno = c.repid
                   LEFT OUTER JOIN  ord o ON c.custid = o.custid
        GROUP BY e.empno;

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

	SELECT AVG(SUM(nvl(o.total,0)))
        FROM emp e LEFT OUTER JOIN customer c ON e.empno = c.repid
                   LEFT OUTER JOIN  ord o ON c.custid = o.custid
        GROUP BY e.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 LEFT OUTER JOIN customer c ON e.empno = c.repid
                                LEFT OUTER JOIN  ord o ON 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 LEFT OUTER JOIN customer c ON e.empno = c.repid
                                LEFT OUTER JOIN  ord o ON 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 LEFT OUTER JOIN customer c ON e.empno = c.repid
                                                       LEFT OUTER JOIN  ord o ON 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(i.qty) "Total Producte"
	FROM product p LEFT OUTER JOIN item i
        ON 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 NATURAL JOIN item i 
	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 NATURAL JOIN item i 
	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 LEFT OUTER JOIN item i 
	ON 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 JOIN item i ON p.prodid = i.prodid
             JOIN ord o ON i.ordid = o.ordid
             JOIN customer c ON o.custid = c.custid
             JOIN emp e ON c.repid = e.empno
	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 LEFT OUTER JOIN item i
	     ON 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 ordid
	      FROM ord o NATURAL JOIN item i
              WHERE o.custid = c.custid and i.qty > 10);

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

SELECT c.name
FROM customer c NATURAL JOIN ord O
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)) "NUM CLIENTES"
FROM customer c NATURAL JOIN ord O
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) "NO COMISSIÓ"
FROM dept d LEFT OUTER JOIN emp e
     ON d.deptno = e.deptno
WHERE 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 prodid, descrip
FROM item NATURAL JOIN product;