Examen UF2

De Lordwektabyte Wiki
Salta a la navegació Salta a la cerca

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)
Obtingut de «http://wiki.lordwektabyte.cat/index.php?title=ASIX/M02/UF2/A10/E0&oldid=139»