Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T7-2»
(23 revisions intermèdies per 2 usuaris que no es mostren) | |||
Línia 1: | Línia 1: | ||
1.Quantitat de producte venuda a cada client. | 1.Quantitat de producte venuda a cada client. | ||
<pre> | <pre> | ||
− | SELECT o.custid, i.prodid, sum(i.qty) | + | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT o.custid, i.prodid, sum(i.qty) "Qtat Producte" | ||
FROM ord o, item i | FROM ord o, item i | ||
WHERE o.ordid = i.ordid | WHERE o.ordid = i.ordid | ||
Línia 7: | Línia 14: | ||
ORDER BY o.custid; | ORDER BY o.custid; | ||
</pre> | </pre> | ||
+ | --> | ||
2.Import total venut per cada empleat. | 2.Import total venut per cada empleat. | ||
<pre> | <pre> | ||
− | SELECT c.repid, sum(o.total) | + | SELECT c.repid, sum(o.total) "Total Venut" |
+ | FROM customer c NATURAL JOIN ord o | ||
+ | GROUP BY c.repid | ||
+ | ORDER BY c.repid; | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT c.repid, sum(o.total) "Total Venut" | ||
FROM customer c, ord o | FROM customer c, ord o | ||
WHERE c.custid = o.custid | WHERE c.custid = o.custid | ||
Línia 15: | Línia 30: | ||
ORDER BY c.repid; | ORDER BY c.repid; | ||
</pre> | </pre> | ||
+ | --> | ||
3.Empleats que han venut menys que la mitja de tots els empleats. | 3.Empleats que han venut menys que la mitja de tots els empleats. | ||
+ | <pre> | ||
+ | - 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); | ||
+ | |||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
- Obtenim el que ha venut cada empleat: | - Obtenim el que ha venut cada empleat: | ||
− | SELECT empno, SUM(nvl(o.total,0)) | + | |
+ | SELECT empno, SUM(nvl(o.total,0)) | ||
FROM emp e, customer c, ord o | FROM emp e, customer c, ord o | ||
WHERE e.empno = c.repid (+) AND c.custid = o.custid (+) | WHERE e.empno = c.repid (+) AND c.custid = o.custid (+) | ||
Línia 54: | Línia 110: | ||
GROUP BY empno); | GROUP BY empno); | ||
</pre> | </pre> | ||
+ | --> | ||
4.Quantitat de producte venut. Ordenat per producte. | 4.Quantitat de producte venut. Ordenat per producte. | ||
<pre> | <pre> | ||
− | SELECT i.prodid, sum(i.qty) | + | SELECT i.prodid, sum(i.qty) "Total Producte" |
FROM item i | FROM item i | ||
GROUP BY i.prodid | GROUP BY i.prodid | ||
Línia 62: | Línia 119: | ||
</pre> | </pre> | ||
5.Quantitat de producte venut. Ordenat per quantitat de forma descendent. Han d'aparèixer tots els productes. | 5.Quantitat de producte venut. Ordenat per quantitat de forma descendent. Han d'aparèixer tots els productes. | ||
+ | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
− | SELECT p.prodid, sum( | + | SELECT p.prodid, sum(i.qty) "Total Producte" |
FROM product p, item i | FROM product p, item i | ||
WHERE p.prodid = i.prodid (+) | WHERE p.prodid = i.prodid (+) | ||
Línia 69: | Línia 135: | ||
ORDER BY p.prodid DESC; | ORDER BY p.prodid DESC; | ||
</pre> | </pre> | ||
+ | --> | ||
6.Quants clients a cada estat? | 6.Quants clients a cada estat? | ||
<pre> | <pre> | ||
Línia 82: | Línia 149: | ||
ORDER BY TO_CHAR(orderdate, 'YYYY'); | ORDER BY TO_CHAR(orderdate, 'YYYY'); | ||
</pre> | </pre> | ||
+ | |||
8.Quantes ordres per any? | 8.Quantes ordres per any? | ||
<pre> | <pre> | ||
Línia 100: | Línia 168: | ||
</pre> | </pre> | ||
11.Màximes quantitats de cada producte venudes en una sola comanda. Ordenades de forma ascendent. | 11.Màximes quantitats de cada producte venudes en una sola comanda. Ordenades de forma ascendent. | ||
+ | |||
+ | <pre> | ||
+ | SELECT p.descrip, MAX(i.qty) "Max_Qty" | ||
+ | FROM product p NATURAL JOIN item i | ||
+ | GROUP BY p.descrip | ||
+ | ORDER BY "Max_Qty" ASC; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT p.descrip, MAX(i.qty) "Max_Qty" | SELECT p.descrip, MAX(i.qty) "Max_Qty" | ||
Línia 107: | Línia 183: | ||
ORDER BY "Max_Qty" ASC; | ORDER BY "Max_Qty" ASC; | ||
</pre> | </pre> | ||
+ | --> | ||
+ | 12.Mostrar quantitat de cada producte venut. Només ens interessen aquells productes dels que s'hagin venut més de 8 unitats. | ||
− | + | <pre> | |
+ | SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES" | ||
+ | FROM product p NATURAL JOIN item i | ||
+ | GROUP BY p.descrip | ||
+ | HAVING sum(i.qty) > 8; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES" | SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES" | ||
Línia 116: | Línia 200: | ||
HAVING sum(i.qty) > 8; | HAVING sum(i.qty) > 8; | ||
</pre> | </pre> | ||
+ | --> | ||
13.Quantitat de producte venut. Ordenat per producte. Han d'aparèixer tots els productes. | 13.Quantitat de producte venut. Ordenat per producte. Han d'aparèixer tots els productes. | ||
+ | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES" | SELECT p.descrip, SUM(i.qty) "UNITATS VENUDES" | ||
Línia 124: | Línia 218: | ||
ORDER BY p.descrip; | ORDER BY p.descrip; | ||
</pre> | </pre> | ||
+ | --> | ||
14.Quantitat de producte venut per empleat. | 14.Quantitat de producte venut per empleat. | ||
+ | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT p.descrip, e.ename, SUM(i.qty) "UNITATS VENUDES" | SELECT p.descrip, e.ename, SUM(i.qty) "UNITATS VENUDES" | ||
Línia 133: | Línia 239: | ||
ORDER BY p.descrip; | ORDER BY p.descrip; | ||
</pre> | </pre> | ||
+ | --> | ||
15.Número d'ordres de cada COMMPLAN. | 15.Número d'ordres de cada COMMPLAN. | ||
<pre> | <pre> | ||
Línia 140: | Línia 247: | ||
</pre> | </pre> | ||
16.Número de productes venuts a cada ordre. Han d'aparèixer totes les ordres. | 16.Número de productes venuts a cada ordre. Han d'aparèixer totes les ordres. | ||
+ | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT o.ordid, COUNT(i.itemid) | SELECT o.ordid, COUNT(i.itemid) | ||
Línia 147: | Línia 263: | ||
ORDER BY o.ordid; | ORDER BY o.ordid; | ||
</pre> | </pre> | ||
+ | --> | ||
17. Clients que han comprat més de 5 unitats d'algun producte en una comanda (EXISTS). | 17. Clients que han comprat més de 5 unitats d'algun producte en una comanda (EXISTS). | ||
+ | |||
+ | <pre> | ||
+ | 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); | ||
+ | </pre> | ||
+ | <!-- | ||
<pre> | <pre> | ||
SELECT custid | SELECT custid | ||
Línia 153: | Línia 279: | ||
WHERE EXISTS (SELECT o.ordid | WHERE EXISTS (SELECT o.ordid | ||
FROM ord o, item i | FROM ord o, item i | ||
− | WHERE o.custid = c.custid and o.ordid = i.ordid and i.qty > 10) | + | WHERE o.custid = c.custid and o.ordid = i.ordid and i.qty > 10); |
</pre> | </pre> | ||
+ | --> | ||
18. Quins clients han comprat més que la mitjana. | 18. Quins clients han comprat més que la mitjana. | ||
+ | <pre> | ||
+ | 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); | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | 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); | ||
+ | </pre> | ||
+ | --> | ||
+ | 19. Quants clients han comprat més que la mitjana. | ||
+ | |||
+ | <pre> | ||
+ | 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); | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT COUNT(COUNT(c.name)) "NUM CLIENTES" | ||
+ | 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); | ||
+ | </pre> | ||
+ | --> | ||
+ | 20. Quants empleats no cobren comissió a cada departament. Han d'apareixer tots els departaments. | ||
− | + | <pre> | |
+ | 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; | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT d.deptno, count(e.empno) "NO COMISSIÓ" | ||
+ | FROM dept d, emp e | ||
+ | WHERE d.deptno = e.deptno (+) and e.comm is null | ||
+ | GROUP BY d.deptno; | ||
+ | </pre> | ||
+ | --> | ||
+ | 21. Mostrar el prodid dels productes que no s'han venut mai (MINUS). | ||
+ | <pre> | ||
+ | SELECT prodid | ||
+ | FROM product | ||
+ | MINUS | ||
+ | SELECT prodid | ||
+ | FROM item; | ||
+ | </pre> | ||
+ | 22. Mostrar el prodid i la descripció dels productes que no s'han venut mai (MINUS). | ||
+ | |||
+ | <pre> | ||
+ | SELECT prodid, descrip | ||
+ | FROM product | ||
+ | MINUS | ||
+ | SELECT prodid, descrip | ||
+ | FROM item NATURAL JOIN product; | ||
+ | </pre> | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT prodid, descrip | ||
+ | FROM product | ||
+ | MINUS | ||
+ | SELECT product.prodid, descrip | ||
+ | FROM item, product | ||
+ | WHERE ITEM.PRODID=PRODUCT.PRODID; | ||
+ | </pre> | ||
+ | --> | ||
+ | <!----> |
Revisió de 13:06, 16 nov 2022
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;