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;