Diferència entre revisions de la pàgina «ASIX/M02/UF2/A10/E0»
Salta a la navegació
Salta a la cerca
m (Guillem ha mogut M02/UF2/A10/E0 a ASIX/M02/UF2/A10/E0 sense deixar una redirecció: Crear subnivell ASIX) |
Revisió de 11:19, 15 abr 2020
Contingut
- 1 Guillem Solà i Boeck (host A10BPC05)
- 1.1 Descripció dels articles en stock superior a més de 3 vegades el seu stock mínim i preu superior a 6€
- 1.2 Codi, data i descompte de les factures sense IVA (iva null o zero)
- 1.3 Descompte mitjà aplicat a les factures
- 1.4 Nom de les poblacions dels clients en el que el seu codi postal comença per "08"
- 1.5 Import mig per factura (amb 2 decimals) sense considerar descomptes ni impostos. L'import es calcula sumant la quantitat del producte pel seu preu
- 1.6 Quantitats totals venudes per cada article, dels que el seu codi comença per "F". La quantitat total es calcula sumant les quantitats de totes les línies de factura
- 1.7 Nom de client i nom de població dels clients de la província de "Barcelona"
- 1.8 Nom de les poblacions de Barcelona que tenen un nom igual al d'alguna altra població d'una altra província
- 1.9 Nombre de client de cada població de Catalunya (codis provincia 08, 17, 43, 25) amb el nomb de població i província
- 1.10 Número de clients que no tenen cap factura
Guillem Solà i Boeck (host A10BPC05)
Descripció dels articles en stock superior a més de 3 vegades el seu stock mínim i preu superior a 6€
MariaDB [examenuf2]> SELECT descrip as Descripció FROM articles WHERE preu>6 AND (stock>3*stock_min); +-----------------------------------+ | Descripció | +-----------------------------------+ | PROLONGADOR SENCILLO 10.0 M | | TUBO FLUORESCENTE 18W TLD | | CLAVIJA LEGRAND II POLOS 20A | | CRUZAMIENTO 1 M. LEGRAND SERIE MO | | BASE ENCHUFE NORMAL 2 M. LEGRAND | | CAJA SUPERFICIE VILAPLANA 100 | | CAJA SUPERFICIE VILAPLANA 80 | +-----------------------------------+ 7 rows in set (0.01 sec)
Codi, data i descompte de les factures sense IVA (iva null o zero)
MariaDB [examenuf2]> SELECT codfac, data, dto FROM factures WHERE iva IS NULL OR iva=0; +--------+------------+------+ | codfac | data | dto | +--------+------------+------+ | 3 | 1998-10-03 | 20 | | 11 | 2000-09-28 | 50 | | 12 | 2001-07-21 | 10 | | 15 | 1999-09-21 | 10 | | 19 | 2000-02-18 | 0 | | 25 | 1998-02-08 | 0 | | 27 | 1998-10-06 | 10 | | 29 | 2001-02-05 | 10 | | 30 | 1999-12-19 | NULL | | 31 | 2000-01-25 | 25 | | 33 | 2000-12-02 | 50 | | 36 | 2001-03-02 | 0 | | 39 | 2001-08-11 | 50 | | 41 | 1998-10-14 | 0 | | 47 | 2000-06-07 | 20 | | 49 | 1999-10-04 | 0 | | 51 | 1998-04-01 | 25 | | 52 | 2002-08-11 | 20 | | 55 | 2000-01-29 | 25 | | 56 | 2001-07-13 | 10 | | 57 | 1999-05-04 | 10 | | 58 | 1998-10-02 | 10 | | 59 | 2001-04-09 | 50 | | 63 | 2002-10-17 | 10 | | 66 | 1998-02-23 | 50 | | 73 | 2001-04-28 | 10 | | 76 | 1998-03-26 | 0 | | 88 | 2000-08-16 | 25 | | 89 | 1998-08-12 | 50 | | 90 | 1999-03-19 | 10 | | 91 | 2002-07-20 | 50 | | 95 | 2000-04-09 | 0 | | 97 | 1998-08-20 | 25 | | 99 | 2000-12-19 | 10 | | 100 | 2002-08-31 | 20 | | 101 | 2002-06-30 | 20 | | 102 | 2002-02-25 | 10 | | 105 | 2001-01-11 | 25 | | 112 | 2002-10-02 | 20 | | 113 | 1999-07-12 | 10 | | 114 | 2002-10-09 | NULL | | 115 | 1999-04-21 | 20 | | 116 | 2000-09-09 | 20 | | 118 | 1999-04-29 | 10 | | 121 | 2000-02-25 | 10 | | 122 | 2001-04-11 | 50 | | 124 | 2002-02-15 | 50 | | 127 | 2001-11-22 | 25 | | 131 | 2001-09-25 | 20 | | 136 | 2000-01-15 | 20 | | 143 | 2000-09-19 | 10 | | 149 | 1998-02-02 | 0 | | 155 | 1998-10-17 | 0 | | 160 | 2001-08-10 | 20 | | 165 | 2000-11-20 | 20 | | 167 | 1998-09-23 | 25 | | 170 | 2001-12-10 | 10 | | 177 | 1999-11-03 | 20 | | 183 | 2001-09-07 | 0 | | 185 | 1998-12-05 | 50 | | 189 | 2001-02-13 | 20 | | 190 | 2001-09-14 | 50 | | 191 | 2001-11-21 | 25 | | 196 | 2001-01-18 | 20 | | 197 | 2002-10-01 | 0 | | 203 | 2001-01-17 | 20 | | 212 | 2000-05-30 | 20 | | 213 | 1999-02-26 | 20 | | 215 | 2002-02-17 | 50 | | 235 | 2002-01-26 | 0 | | 236 | 2000-07-17 | 50 | | 238 | 1998-05-28 | NULL | | 243 | 2001-09-27 | 20 | | 245 | 2000-10-06 | 20 | | 248 | 1999-09-08 | 0 | | 251 | 2002-07-16 | 10 | | 254 | 2002-03-07 | 20 | | 255 | 2002-11-24 | 25 | | 257 | 1999-07-24 | 20 | | 258 | 2001-05-21 | 10 | | 261 | 1998-09-09 | 10 | | 263 | 1998-05-04 | 50 | | 264 | 2000-03-01 | 20 | | 265 | 2002-02-06 | 25 | | 266 | 2001-06-14 | 25 | | 278 | 1998-09-17 | 10 | | 279 | 1998-05-22 | 20 | | 286 | 1998-08-26 | 10 | | 287 | 1998-10-06 | 10 | | 288 | 2001-02-08 | 50 | | 291 | 2002-09-20 | 25 | | 296 | 2001-07-09 | NULL | | 297 | 2001-03-29 | 0 | | 309 | 2000-04-10 | 25 | | 312 | 1998-08-17 | 20 | | 313 | 2000-06-04 | 10 | | 316 | 1999-01-02 | 50 | | 317 | 2000-02-11 | 25 | | 322 | 1999-10-21 | 25 | | 330 | 2002-07-17 | 20 | | 331 | 2002-10-30 | 20 | | 332 | 2001-07-30 | 20 | | 334 | 1999-11-25 | 10 | | 335 | 2002-10-04 | 25 | | 338 | 1998-06-21 | 10 | | 339 | 2001-02-10 | 10 | | 340 | 2000-10-08 | 50 | | 343 | 2000-02-13 | 0 | | 350 | 1998-08-19 | 0 | | 352 | 2001-01-29 | 25 | | 354 | 2001-04-12 | 0 | | 355 | 2000-03-22 | 50 | | 358 | 2002-03-20 | 0 | | 366 | 2002-01-17 | 20 | | 367 | 1998-01-24 | 20 | | 376 | 1999-03-07 | 20 | | 380 | 2000-04-03 | 10 | | 382 | 1999-07-29 | 20 | | 387 | 2001-08-14 | 10 | | 390 | 1998-04-13 | 0 | | 392 | 2002-09-20 | 20 | | 393 | 2001-12-26 | 0 | | 395 | 1999-01-01 | 10 | | 397 | 1999-02-23 | 20 | | 398 | 2000-02-01 | 50 | | 406 | 1999-11-02 | 0 | | 410 | 2002-04-30 | NULL | | 426 | 2000-04-29 | 20 | | 428 | 2001-11-23 | 20 | | 429 | 2000-02-09 | 50 | | 432 | 2001-12-02 | 0 | | 439 | 1999-10-27 | 20 | | 445 | 1998-04-04 | 0 | | 449 | 2000-12-19 | 10 | | 450 | 2001-12-31 | 50 | | 451 | 2002-01-25 | 10 | | 453 | 2001-08-05 | 25 | | 455 | 2002-10-25 | 50 | | 459 | 2000-06-10 | 20 | | 462 | 2001-07-08 | 0 | | 465 | 1998-07-14 | NULL | | 466 | 2000-02-04 | NULL | | 469 | 2000-03-07 | 0 | | 470 | 2001-10-05 | 50 | | 472 | 1998-06-13 | 25 | | 475 | 2000-12-27 | NULL | | 478 | 1998-12-30 | 10 | | 480 | 1998-03-22 | 25 | | 482 | 2002-10-03 | 20 | | 484 | 1998-12-24 | 10 | | 485 | 1998-06-07 | 10 | | 487 | 1999-07-01 | 50 | | 489 | 1999-11-14 | 0 | | 508 | 1998-08-03 | 25 | | 510 | 2002-02-09 | 0 | | 511 | 2002-08-04 | 25 | | 512 | 2001-05-14 | 50 | | 513 | 2000-03-26 | 25 | | 518 | 2000-02-01 | 10 | | 521 | 2000-04-27 | 20 | | 527 | 1999-10-12 | 25 | | 529 | 2001-10-22 | NULL | | 532 | 2001-06-21 | 25 | | 536 | 2000-06-21 | 10 | | 537 | 2001-10-10 | 10 | | 539 | 1998-06-30 | 10 | | 541 | 2000-11-10 | 50 | | 554 | 1999-06-29 | 0 | | 569 | 2001-12-10 | 25 | | 571 | 1999-11-13 | 0 | | 572 | 2001-04-29 | 10 | | 573 | 2001-02-17 | 50 | | 576 | 1999-10-15 | 20 | | 578 | 1998-06-05 | 25 | | 581 | 1998-08-20 | 0 | | 583 | 1998-06-27 | 10 | | 586 | 2000-02-24 | 25 | | 587 | 2002-09-29 | 10 | | 593 | 1999-04-05 | 0 | | 599 | 1998-05-17 | NULL | | 602 | 2001-11-06 | 0 | | 604 | 2001-10-10 | 0 | | 606 | 2000-12-29 | 10 | | 613 | 1998-09-19 | 20 | | 622 | 1999-07-27 | 20 | | 625 | 1998-12-21 | 25 | | 626 | 2002-11-29 | NULL | | 628 | 2001-04-06 | 10 | | 631 | 2002-06-27 | 10 | | 634 | 2000-04-25 | 25 | | 642 | 2001-09-04 | NULL | | 646 | 2001-03-20 | 25 | | 653 | 2000-09-29 | 20 | | 655 | 2000-06-12 | 0 | | 656 | 2002-10-04 | 0 | | 657 | 2002-10-07 | 0 | | 659 | 2002-03-28 | 20 | | 662 | 2002-04-27 | 25 | | 665 | 1998-01-22 | 0 | | 666 | 2002-01-05 | NULL | | 667 | 1999-05-26 | 50 | | 668 | 1999-06-22 | NULL | | 674 | 1998-02-15 | 25 | | 680 | 2002-03-31 | 10 | | 694 | 1998-10-26 | 20 | | 697 | 1998-11-11 | 0 | | 702 | 2001-05-14 | 20 | | 705 | 2002-02-10 | 20 | | 709 | 2001-10-12 | 20 | | 710 | 2001-07-06 | NULL | | 711 | 2001-01-06 | 20 | | 712 | 1998-08-19 | 10 | | 723 | 1999-12-03 | 0 | | 726 | 2002-10-02 | NULL | | 728 | 2001-02-22 | 20 | | 731 | 1999-03-25 | 25 | | 732 | 2002-07-10 | 25 | | 733 | 1998-02-25 | 0 | | 734 | 2001-10-03 | 50 | | 735 | 1998-05-21 | 0 | | 736 | 2001-10-27 | 20 | | 746 | 2000-10-04 | 50 | | 749 | 2002-09-25 | 10 | | 750 | 2000-09-20 | 0 | | 751 | 2000-07-16 | 25 | | 759 | 2002-06-25 | 25 | | 762 | 2001-08-06 | 0 | | 763 | 2002-07-28 | 10 | | 770 | 2002-11-03 | 0 | | 771 | 2002-05-24 | 25 | | 773 | 2002-03-05 | 20 | | 782 | 2002-06-14 | 20 | | 786 | 1999-08-31 | 0 | | 788 | 2002-12-12 | 20 | | 794 | 2002-12-27 | 25 | | 795 | 2000-07-02 | 10 | | 797 | 1999-08-29 | 10 | | 810 | 2000-08-19 | 25 | | 812 | 2000-08-03 | 10 | | 813 | 1998-02-27 | 0 | | 814 | 2000-08-01 | 20 | | 815 | 1999-05-20 | NULL | | 821 | 2001-08-21 | 25 | | 826 | 1999-12-05 | 10 | | 830 | 2002-03-25 | 50 | | 839 | 1999-11-26 | 20 | | 843 | 2000-04-30 | NULL | | 849 | 2000-11-23 | 25 | | 853 | 2001-12-08 | 20 | | 855 | 2002-08-26 | 0 | | 856 | 1999-02-14 | NULL | | 857 | 2001-11-19 | 10 | | 858 | 2001-06-14 | 10 | | 861 | 2000-05-21 | 0 | | 863 | 2002-03-07 | 10 | | 864 | 1998-05-04 | 20 | | 867 | 1998-10-27 | 20 | | 880 | 2002-09-08 | NULL | | 884 | 2000-02-05 | 25 | | 892 | 1998-04-24 | 0 | | 894 | 2001-12-06 | 50 | | 899 | 2001-02-27 | NULL | | 902 | 2000-01-12 | 25 | | 906 | 1999-08-01 | 0 | | 909 | 1998-12-19 | 0 | | 910 | 2001-01-09 | NULL | | 917 | 2002-10-24 | 50 | | 919 | 1999-05-02 | 0 | | 921 | 2002-05-26 | 25 | | 923 | 2002-08-31 | 10 | | 925 | 1998-10-04 | 50 | | 930 | 1998-07-07 | 25 | | 932 | 2001-10-15 | 10 | | 933 | 2000-02-19 | 10 | | 938 | 2000-12-08 | 25 | | 942 | 1999-06-16 | 25 | | 945 | 2002-12-10 | NULL | | 946 | 2002-03-05 | NULL | | 951 | 2000-05-17 | 0 | | 958 | 2001-09-29 | NULL | | 962 | 2000-12-02 | 10 | | 965 | 2000-04-29 | 20 | | 966 | 2000-04-18 | 10 | | 970 | 2001-12-19 | 50 | | 973 | 2000-10-31 | 20 | | 976 | 1999-04-09 | 20 | | 977 | 2001-05-13 | 20 | | 978 | 2000-07-13 | 25 | | 981 | 2002-11-08 | 0 | | 991 | 2001-03-30 | 10 | | 994 | 1999-05-29 | 0 | | 995 | 1998-09-23 | 10 | | 997 | 1998-04-17 | 10 | | 999 | 2002-04-09 | 50 | +--------+------------+------+ 294 rows in set (0.00 sec)
Descompte mitjà aplicat a les factures
MariaDB [examenuf2]> SELECT avg(dto) FROM factures; +----------+ | avg(dto) | +----------+ | 17.8263 | +----------+ 1 row in set (0.02 sec)
Nom de les poblacions dels clients en el que el seu codi postal comença per "08"
MariaDB [examenuf2]> SELECT pobles.nom FROM pobles JOIN clients ON clients.codpob=pobles.codpob WHERE clients.codpostal LIKE '08%'; +--------------------+ | nom | +--------------------+ | CALLUS | | CINT (EL) | | MAS D'EN ROCA | | PLANASSA (LA) | | VALLS DE TORRUELLA | +--------------------+ 5 rows in set (0.01 sec)
Import mig per factura (amb 2 decimals) sense considerar descomptes ni impostos. L'import es calcula sumant la quantitat del producte pel seu preu
Quantitats totals venudes per cada article, dels que el seu codi comença per "F". La quantitat total es calcula sumant les quantitats de totes les línies de factura
MariaDB [examenuf2]> SELECT articles.codart, articles.descrip, sum(linees.quantitat) as "suma articles" FROM articles JOIN linees on articles.codart=linees.codart WHERE linees.codart LIKE "F%" GROUP BY articles.codart; +---------+----------------------------------+---------------+ | codart | descrip | suma articles | +---------+----------------------------------+---------------+ | FAF36L | LAMPARA ESF R14 60W CLARA | 31 | | FELM3 | PLACA CIEGA TICINO TEKNE | 28 | | FO5/140 | INTERRUPTOR DIFERENCIAL 4 P, 25 | 10 | | FO5/141 | INTERRUPTOR DIFERENCIAL 4 P, 25 | 57 | | FO5/143 | INTERRUPTOR DIFERENCIAL 4 P, 40 | 43 | | FO5/16 | INTERRUPTOR DIFERENCIAL 4 P, 40 | 35 | | FO5/17 | INTERRUPTOR DIFERENCIAL 4 P, 63 | 35 | | FO5/50 | INTERRUPTOR DIFERENCIAL 4 P, 63 | 32 | +---------+----------------------------------+---------------+ 8 rows in set (0.01 sec)
Nom de client i nom de població dels clients de la província de "Barcelona"
MariaDB [examenuf2]> SELECT clients.nom AS Client, pobles.nom AS Poble FROM clients JOIN pobles on clients.codpob=pobles.codpob WHERE pobles.codpro = (SELECT codpro FROM provincies WHERE nom="Barcelona"); +------------------------------+--------------------+ | Client | Poble | +------------------------------+--------------------+ | CASTELLANOS ANDRES, JULIO | CALLUS | | CUBEDO ANICETO, JAVIER RUBEN | CINT (EL) | | FORTUNYO GRANGEL, JOSE | MAS D'EN ROCA | | LOPEZ DOBON, VICENTE RAMON | PLANASSA (LA) | | FERNANDEZ MARTIN, CRISTINA | VALLS DE TORRUELLA | +------------------------------+--------------------+ 5 rows in set (0.03 sec)
Nom de les poblacions de Barcelona que tenen un nom igual al d'alguna altra població d'una altra província
MariaDB [examenuf2]> SELECT DISTINCT pobles.nom FROM pobles JOIN provincies ON pobles.codpro=provincies.codpro WHERE provincies.nom="Barcelona" AND pobles.nom IN (SELECT altres.nom FROM pobles AS altres JOIN provincies ON altres.codpro=provincies.codpro WHERE provincies.nom!="Barcelona"); +---------------------+ | nom | +---------------------+ | ASPA | | BELLAVISTA | | BONAVISTA | | BORGONYA | | CALELLA | | CAN FONT | | CANTALLOPS | | CANYELLES | | CASC ANTIC | | CASC URBA | | CASETES (LES) | | CASTELL (EL) | | CASTELLET (EL) | | COMA (LA) | | COSTA (LA) | | COSTES (LES) | | ESTACION (LA) | | ESTANY (L') | | FLIX | | FLORESTA (LA) | | GARRIGA (LA) | | GRANOLLERS | | GUARDIA (LA) | | GUARDIOLA | | MALLORQUINES | | MARATA | | MARTORELL | | MASSANES | | MIRAMBELL | | MOLI (EL) | | MOLINA (LA) | | MONTCLAR | | MONTJUIC | | MONTNEGRE | | NUCLEO | | PINEDA (LA) | | PLA (EL) | | PLANA (LA) | | POAL (EL) | | POBLE SEC | | POLIGONO INDUSTRIAL | | PUIG (EL) | | PUJALT | | RAPITA (LA) | | RATERA | | REMEI | | RIERA (LA) | | RIERAL (EL) | | ROCA (LA) | | RUBIO | | SALINES (LES) | | SALLENT | | SALUT (LA) | | SAMUNTA | | SANT BARTOMEU | | SANT CRISTOFOL | | SANT GENIS | | SANT JULIA | | SANT MIQUEL | | SANT PERE | | SANT RAMON | | SANT ROC | | SANTA CREU | | SANTA EUGENIA | | SANTA LLUCIA | | SANTA MARGARIDA | | SERRA (LA) | | TORDERA | | TORRE (LA) | | TORREBLANCA | | TORRENTS (ELS) | | VALL (LA) | | VALLDEPERAS | | VALLS | | VILA-SECA | | VIVER | +---------------------+ 76 rows in set (0.02 sec)
Nombre de client de cada població de Catalunya (codis provincia 08, 17, 43, 25) amb el nomb de població i província
Número de clients que no tenen cap factura
MariaDB [examenuf2]> SELECT nom FROM clients WHERE NOT EXISTS (SELECT codcli FROM factures WHERE clients.codcli = factures.codcli); +-------------------------------------+ | nom | +-------------------------------------+ | MIRAVET SALA, MARIA MERCEDES | | SAMPEDRO SIMO, MARIA MERCEDES | | GISBERT MIRALLES, BEATRIZ LAURA | | DE LA CRUZ AZNAR, CONCHITA PATRICIA | | VILLALONGA SANCHIS, MILAGROS | | PITARCH MONSONIS, MARIA CARMEN | | GARCIA CASADO, YOLANDA | | ADELL GALMES, MERCEDES ROSARIO | | HERRERA SALA, ANA | | MARTI MOLTO, CONCHITA | | SOS CARRETERO, JESUS | | MIGUEL ARCHILES, OSCAR RAMON | | CHALER SORIANO, MANUEL DIEGO | | PINEL HUERTA, VICENTE | | LOPEZ BOTELLA, MAURO | | PALAU MARTINEZ, JORGE | | RINCON VERNIA, DAVID | | MURIA VINAIZA, JOSE | | HUGUET PERIS, JUAN ANGEL | | FABREGA GARGORI, JAVIER SEBASTIAN | | VILLALONGA RAMIREZ, DIEGO SERGIO | | BADENES CEPRIA, ANDRES RICARDO | | BOTELLA CATALA, JUAN | | LOPEZ LLORENS, SANCHEZ MARCOS | | LOPEZ RINCON, LUIS MIGUEL | | GUIMERA AGOST, LUIS | | GUILLOT BELDA, FRANCISCO JOSE | | LOPEZ GUITART, XAVIER | | TUR MARTIN, MANUEL FRANCISCO | | AZNAR MONFERRER, ADRIAN | +-------------------------------------+ 30 rows in set (0.00 sec)
