Consultes SQL \u2013 LAN Party Intranet<\/title>\n<style>\n * { margin:0; padding:0; box-sizing:border-box; }\n body {\n font-family: 'Segoe UI', system-ui, sans-serif;\n background:#f0f2f5; color:#2c2c2c; line-height:1.65;\n }\n\n \/* \u2500\u2500 HEADER \u2500\u2500 *\/\n header {\n background: linear-gradient(135deg, #1a1a2e 0%, #16213e 60%, #0f3460 100%);\n color:#fff; padding:48px 24px 38px; text-align:center; position:relative; overflow:hidden;\n }\n header::before {\n content:''; position:absolute; inset:0;\n background: radial-gradient(circle at 75% 40%, rgba(233,69,96,.3) 0%, transparent 55%);\n }\n header h1 { font-size:2.1rem; font-weight:700; position:relative; }\n header h1 span { color:#e94560; }\n header p { color:#a8a8b3; margin-top:5px; font-size:.93rem; position:relative; }\n .badge-top {\n display:inline-block; background:#e94560; color:#fff;\n padding:4px 16px; border-radius:20px; font-size:.77rem;\n font-weight:600; margin-top:12px; position:relative;\n }\n\n \/* \u2500\u2500 WRAP \u2500\u2500 *\/\n .wrap { max-width:1020px; margin:32px auto; padding:0 20px 56px; }\n\n \/* \u2500\u2500 RESUM T\u00c8CNIQUES (dalt) \u2500\u2500 *\/\n .resum-bloc {\n background:#fff; border-radius:12px; box-shadow:0 2px 10px rgba(0,0,0,.08);\n padding:22px 26px; margin-bottom:28px;\n }\n .resum-bloc h3 { color:#0f3460; margin-bottom:12px; font-size:1rem; }\n .resum-grid {\n display:grid; grid-template-columns: repeat(auto-fill, minmax(175px,1fr)); gap:10px;\n }\n .resum-item {\n background:#f0f4f8; border-radius:8px; padding:10px 14px;\n border-left:4px solid #e94560; font-size:.85rem;\n }\n .resum-item strong { color:#0f3460; display:block; font-size:.78rem; text-transform:uppercase; letter-spacing:.4px; margin-bottom:2px; }\n\n \/* \u2500\u2500 SECCIONS \u2500\u2500 *\/\n .secci\u00f3 {\n background:#fff; border-radius:12px;\n box-shadow:0 2px 12px rgba(0,0,0,.07);\n margin-bottom:24px; overflow:hidden;\n }\n .secci\u00f3-header {\n background:#1a1a2e; color:#fff;\n padding:13px 22px; display:flex; align-items:center; gap:14px;\n }\n .secci\u00f3-header .ico { font-size:1.3rem; }\n .secci\u00f3-header h2 { font-size:1rem; font-weight:600; margin-bottom:2px; }\n .\u00e0rea-badge {\n display:inline-block; background:#e94560; color:#fff;\n padding:2px 10px; border-radius:10px; font-size:.7rem; font-weight:600;\n }\n .secci\u00f3-cos { padding:20px 22px; }\n\n \/* \u2500\u2500 JUSTIFICACI\u00d3 \u2500\u2500 *\/\n .justificacio {\n background:#f8fafc; border-left:4px solid #0f3460;\n border-radius:0 8px 8px 0; padding:12px 16px; margin-bottom:12px;\n font-size:.9rem;\n }\n .justificacio strong { color:#0f3460; }\n\n \/* \u2500\u2500 T\u00c8CNIQUES \u2500\u2500 *\/\n .tec-row { margin-bottom:12px; }\n .tec-row strong { font-size:.82rem; color:#555; }\n .tec {\n display:inline-block; background:#e8f0fe; color:#1a55b8;\n padding:3px 9px; border-radius:5px; font-size:.76rem;\n font-weight:600; margin:2px 3px 2px 0;\n }\n\n \/* \u2500\u2500 SQL \u2500\u2500 *\/\n .bloc-sql {\n background:#1e1e2e; border-radius:8px; padding:14px 18px; margin-bottom:14px;\n }\n .bloc-sql strong { color:#a8a8b3; font-size:.8rem; display:block; margin-bottom:6px; }\n .bloc-sql pre {\n color:#c9d1d9; font-size:.82rem; font-family:'Consolas','Courier New',monospace;\n white-space:pre-wrap; line-height:1.55;\n }\n\n \/* \u2500\u2500 EVID\u00c8NCIA (taula de resultats) \u2500\u2500 *\/\n .bloc-evidencia { margin-top:4px; }\n .bloc-evidencia > strong { font-size:.84rem; color:#2e7d32; display:block; margin-bottom:8px; }\n .bloc-evidencia table {\n width:100%; border-collapse:collapse; font-size:.82rem;\n border:1px solid #e2e8f0; border-radius:6px; overflow:hidden;\n }\n .bloc-evidencia th {\n background:#0f3460; color:#fff; padding:8px 11px;\n text-align:left; font-weight:600; font-size:.76rem;\n text-transform:uppercase; letter-spacing:.4px; white-space:nowrap;\n }\n .bloc-evidencia td {\n padding:7px 11px; border-bottom:1px solid #eee;\n vertical-align:top; white-space:nowrap;\n }\n .bloc-evidencia tr:nth-child(even) td { background:#f8fafc; }\n .bloc-evidencia tr:last-child td { border-bottom:none; }\n\n \/* \u2500\u2500 FOOTER \u2500\u2500 *\/\n footer {\n text-align:center; padding:28px; color:#a8a8b3; font-size:.8rem;\n }\n\n \/* \u2500\u2500 PRINT \u2500\u2500 *\/\n @media print {\n body { background:#fff; }\n .secci\u00f3 { box-shadow:none; border:1px solid #ddd; page-break-inside:avoid; }\n header { background:#1a1a2e !important; -webkit-print-color-adjust:exact; }\n }\n<\/style>\n<\/head>\n<body>\n\n<header>\n <h1>\ud83d\udcca Consultes SQL \u2013 <span>Extracci\u00f3 d’Informaci\u00f3<\/span><\/h1>\n <p>Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies<\/p>\n <div class=\"badge-top\">Febrer 2026<\/div>\n<\/header>\n\n<div class=\"wrap\">\n\n \n <div class=\"resum-bloc\">\n <h3>\ud83c\udfaf Resum de t\u00e8cniques SQL implementades<\/h3>\n <div class=\"resum-grid\">\n <div class=\"resum-item\"><strong>JOINs<\/strong>INNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta<\/div>\n <div class=\"resum-item\"><strong>Agregaci\u00f3n<\/strong>COUNT, SUM, MIN, MAX, COUNT(DISTINCT)<\/div>\n <div class=\"resum-item\"><strong>Filtrat<\/strong>GROUP BY, HAVING, WHERE amb condicions complexes<\/div>\n <div class=\"resum-item\"><strong>Subqueries<\/strong>Subqueries correlades dins SELECT i WHERE<\/div>\n <div class=\"resum-item\"><strong>Funcions<\/strong>COALESCE, ROUND, CASE WHEN, GROUP_CONCAT<\/div>\n <div class=\"resum-item\"><strong>Evid\u00e8ncies<\/strong>12 consultes amb resultats reals de la BBDD<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C1 \u2013 Usuaris amb el nom del rol assignat<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 1 \u2013 USUARIS I ROLS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Necessitem veure en una sola taula qui \u00e9s cada usuari i quines funcions t\u00e9 dins del sistema. Utilitzem un <strong>INNER JOIN<\/strong> entre <code>usuaris<\/code> i <code>rols<\/code> perqu\u00e8 cada usuari t\u00e9 exactament un rol (FK obligat\u00f2ria). Ordenem per rol per agrupar visuament admins, jugadors i espectadors.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.id_usuari, u.nom, u.nick_joc, u.email,\n r.nom_rol, r.descripcio AS descripcio_rol\n FROM usuaris u\n INNER JOIN rols r ON u.id_rol = r.id_rol\n WHERE u.actiu = 1\n ORDER BY r.id_rol ASC, u.nom ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (10 files):<\/strong><table><tr><th>id_usuari<\/th><th>nom<\/th><th>nick_joc<\/th><th>email<\/th><th>nom_rol<\/th><th>descripcio_rol<\/th><\/tr><tr><td>9<\/td><td>Albert Coma<\/td><td>AlbertAce<\/td><td>albert.coma@gmail.com<\/td><td>admin<\/td><td>Administrador amb control total del sistema<\/td><\/tr><tr><td>5<\/td><td>Dani Sol\u00e0<\/td><td>DaniStar<\/td><td>dani.sola@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>7<\/td><td>Gerard Font<\/td><td>GerardGG<\/td><td>gerard.font@yahoo.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>3<\/td><td>Jordi Puig<\/td><td>JordiX<\/td><td>jordi.puig@yahoo.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>6<\/td><td>Laia Valls<\/td><td>LaiaMaster<\/td><td>laia.valls@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>1<\/td><td>Marc L\u00f3pez<\/td><td>MarcL_Pro<\/td><td>marc.lopez@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>8<\/td><td>Neus Camps<\/td><td>NeusFPS<\/td><td>neus.camps@hotmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>4<\/td><td>Paula Roca<\/td><td>PaulaRock<\/td><td>paula.roca@hotmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>2<\/td><td>Sara Mart\u00ednez<\/td><td>SaraGamer<\/td><td>sara.martinez@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>10<\/td><td>Rita Mas<\/td><td>RitaPlay<\/td><td>rita.mas@yahoo.com<\/td><td>espectador<\/td><td>Usuari que nom\u00e9s pot veure contingut i fer xat<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C2 \u2013 Nombre d'usuaris per cada rol<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 1 \u2013 USUARIS I ROLS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet saber la distribuci\u00f3 de rols dins del sistema: quants admins tenim, quants jugadors actius i espectadors. Molt \u00fatil per a l’administrador per controlar el balan\u00e7 de la comunitat. Utilitzem <strong>GROUP BY<\/strong> amb <strong>COUNT<\/strong> i <strong>LEFT JOIN<\/strong> per incloure rols sense usuaris.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">COUNT<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT r.nom_rol, r.descripcio,\n COUNT(u.id_usuari) AS total_usuaris\n FROM rols r\n LEFT JOIN usuaris u ON r.id_rol = u.id_rol AND u.actiu = 1\n GROUP BY r.id_rol, r.nom_rol, r.descripcio\n ORDER BY total_usuaris DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (3 files):<\/strong><table><tr><th>nom_rol<\/th><th>descripcio<\/th><th>total_usuaris<\/th><\/tr><tr><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><td>8<\/td><\/tr><tr><td>admin<\/td><td>Administrador amb control total del sistema<\/td><td>1<\/td><\/tr><tr><td>espectador<\/td><td>Usuari que nom\u00e9s pot veure contingut i fer xat<\/td><td>1<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C3 \u2013 Usuaris inscrits amb estat de pagament<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 2 \u2013 INSCRIPCIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> L’organitzador necessita saber qui s’ha inscrit a cada poble i si ha pagat la entrada. Utilitzem un <strong>doble JOIN<\/strong> (inscripcions \u2192 usuaris, inscripcions \u2192 lan_party) i <strong>CASE WHEN<\/strong> per convertir els valors booleens (0\/1) en texto llegible (‘S\u00ed’\/’No’).<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, u.nick_joc, u.nom,\n CASE WHEN i.ha_pagat = 1 THEN 'S\u00ed' ELSE 'No' END AS ha_pagat,\n CASE WHEN i.ha_assistit = 1 THEN 'S\u00ed' ELSE 'No' END AS ha_assistit,\n i.data_inscripcio\n FROM inscripcions i\n INNER JOIN usuaris u ON i.id_usuari = u.id_usuari\n INNER JOIN lan_party lp ON i.id_lan_party = lp.id_lan_party\n ORDER BY lp.id_lan_party ASC, i.ha_pagat DESC, u.nom ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (13 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>nick_joc<\/th><th>nom<\/th><th>ha_pagat<\/th><th>ha_assistit<\/th><th>data_inscripcio<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>GerardGG<\/td><td>Gerard Font<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>JordiX<\/td><td>Jordi Puig<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>PaulaRock<\/td><td>Paula Roca<\/td><td>No<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>AlbertAce<\/td><td>Albert Coma<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>No<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C4 \u2013 Resum d'inscripcions per LAN Party<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 2 \u2013 INSCRIPCIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet saber el resum de cada poble: quants inscrits, quants han pagat i quin percentatge d’assist\u00e8ncia. El <strong>HAVING<\/strong> filtra sols les LAN Parties amb almenys 3 inscripcions. Usem <strong>COUNT condicional amb CASE<\/strong> i <strong>ROUND<\/strong> per calcular el percentatge d’assist\u00e8ncia.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">SUM<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ROUND<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, lp.capacitat_maxima,\n COUNT(i.id_inscripcio) AS total_inscrits,\n SUM(CASE WHEN i.ha_pagat = 1 THEN 1 ELSE 0 END) AS han_pagat,\n SUM(CASE WHEN i.ha_assistit = 1 THEN 1 ELSE 0 END) AS han_assistit,\n ROUND(SUM(CASE WHEN i.ha_assistit=1 THEN 1 ELSE 0 END)*100.0\/COUNT(i.id_inscripcio),1) AS pct_assistencia\n FROM lan_party lp\n LEFT JOIN inscripcions i ON lp.id_lan_party = i.id_lan_party\n GROUP BY lp.id_lan_party, lp.nom_\u00e9v\u00e9niment, lp.capacitat_maxima\n HAVING COUNT(i.id_inscripcio) >= 3\n ORDER BY total_inscrits DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (2 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>capacitat_maxima<\/th><th>total_inscrits<\/th><th>han_pagat<\/th><th>han_assistit<\/th><th>pct_assistencia<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>100<\/td><td>8<\/td><td>7<\/td><td>5<\/td><td>62.5<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>80<\/td><td>5<\/td><td>4<\/td><td>0<\/td><td>0.0<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C5 \u2013 Tornejos amb el joc i la LAN Party<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 JOCS I TORNEJOS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Vista resum de tots els tornejos amb el joc i l’poble associat. Molt \u00fatil per a la p\u00e0gina principal de la intranet. Usem un <strong>doble JOIN<\/strong>: tornejos \u2192 jocs i tornejos \u2192 lan_party, i ordenem per estat per mostrar primer els en curs.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.id_torneig, t.nom_torneig, j.nom_joc, j.genere,\n lp.nom_\u00e9v\u00e9niment, t.format, t.max_participants,\n t.premi, t.estat, t.data_inici, t.data_fi\n FROM tornejos t\n INNER JOIN jocs j ON t.id_joc = j.id_joc\n INNER JOIN lan_party lp ON t.id_lan_party = lp.id_lan_party\n ORDER BY t.estat ASC, t.data_inici ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (6 files):<\/strong><table><tr><th>id_torneig<\/th><th>nom_torneig<\/th><th>nom_joc<\/th><th>genere<\/th><th>nom_\u00e9v\u00e9niment<\/th><th>format<\/th><th>max_participants<\/th><th>premi<\/th><th>estat<\/th><th>data_inici<\/th><th>data_fi<\/th><\/tr><tr><td>4<\/td><td>Torneig FIFA 24 \u2013 1v1<\/td><td>FIFA 24<\/td><td>Esports<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 30\u20ac<\/td><td>en_curs<\/td><td>2026-02-16 14:00<\/td><td>2026-02-16 16:00<\/td><\/tr><tr><td>1<\/td><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>FPS<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>8<\/td><td>regalo valorat en 60\u20ac<\/td><td>finalitzat<\/td><td>2026-02-15 11:00<\/td><td>2026-02-15 14:00<\/td><\/tr><tr><td>2<\/td><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>FPS<\/td><td>LAN Party Hivern 2026<\/td><td>lliga<\/td><td>6<\/td><td>regalo valorat en 40\u20ac<\/td><td>finalitzat<\/td><td>2026-02-15 15:00<\/td><td>2026-02-15 18:00<\/td><\/tr><tr><td>3<\/td><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>MOBA<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 80\u20ac<\/td><td>finalitzat<\/td><td>2026-02-16 10:00<\/td><td>2026-02-16 13:00<\/td><\/tr><tr><td>5<\/td><td>Torneig CS2 \u2013 Primavera<\/td><td>Counter-Strike 2<\/td><td>FPS<\/td><td>LAN Party Primavera 2026<\/td><td>lliga<\/td><td>6<\/td><td>regalo valorat en 50\u20ac<\/td><td>obert<\/td><td>2026-04-10 10:00<\/td><td>2026-04-10 13:00<\/td><\/tr><tr><td>6<\/td><td>Torneig Rocket League<\/td><td>Rocket League<\/td><td>Esports<\/td><td>LAN Party Primavera 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 35\u20ac<\/td><td>obert<\/td><td>2026-04-11 10:00<\/td><td>2026-04-11 12:00<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C6 \u2013 Popularitat de jocs per participacions<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 JOCS I TORNEJOS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet analitzar quins jocs s\u00f3n els m\u00e9s populars. Comptem tornejos i participants per joc. El <strong>HAVING<\/strong> elimina els jocs sense participacions. Usem <strong>COUNT(DISTINCT)<\/strong> per comptador \u00fanics de tornejos i <strong>COALESCE<\/strong> per manejar NULLs.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN (x2)<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">COUNT(DISTINCT)<\/span><span class=\"tec\">MAX<\/span><span class=\"tec\">COALESCE<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT j.nom_joc, j.genere,\n COUNT(DISTINCT t.id_torneig) AS num_tornejos,\n COUNT(pt.id_participacio) AS num_participacions,\n COALESCE(MAX(pt.puntuacio), 0) AS puntuacio_maxima\n FROM jocs j\n LEFT JOIN tornejos t ON j.id_joc = t.id_joc\n LEFT JOIN participants_torneig pt ON t.id_torneig = pt.id_torneig\n GROUP BY j.id_joc, j.nom_joc, j.genere\n HAVING COUNT(pt.id_participacio) > 0\n ORDER BY num_participacions DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (4 files):<\/strong><table><tr><th>nom_joc<\/th><th>genere<\/th><th>num_tornejos<\/th><th>num_participacions<\/th><th>puntuacio_maxima<\/th><\/tr><tr><td>Counter-Strike 2<\/td><td>FPS<\/td><td>2<\/td><td>6<\/td><td>100<\/td><\/tr><tr><td>Valorant<\/td><td>FPS<\/td><td>1<\/td><td>5<\/td><td>90<\/td><\/tr><tr><td>FIFA 24<\/td><td>Esports<\/td><td>1<\/td><td>4<\/td><td>0<\/td><\/tr><tr><td>League of Legends<\/td><td>MOBA<\/td><td>1<\/td><td>4<\/td><td>100<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C7 \u2013 Classificaci\u00f3 global per puntuaci\u00f3 total<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> La classificaci\u00f3 general \u00e9s una de les p\u00e0gines clau de la intranet. Suma les puntuacions de tots els tornejos amb <strong>SUM<\/strong>, i usem <strong>MIN<\/strong> per mostrar la millor posici\u00f3 aconseguida. GROUP BY sobre participants_torneig amb JOIN cap a usuaris.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">SUM<\/span><span class=\"tec\">COUNT(DISTINCT)<\/span><span class=\"tec\">MIN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.nick_joc, u.nom,\n COUNT(DISTINCT pt.id_torneig) AS tornejos_jugats,\n SUM(pt.puntuacio) AS puntuacio_total,\n MIN(pt.posicio_final) AS millor_posicio\n FROM usuaris u\n INNER JOIN participants_torneig pt ON u.id_usuari = pt.id_usuari\n GROUP BY u.id_usuari, u.nick_joc, u.nom\n ORDER BY puntuacio_total DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (8 files):<\/strong><table><tr><th>nick_joc<\/th><th>nom<\/th><th>tornejos_jugats<\/th><th>puntuacio_total<\/th><th>millor_posicio<\/th><\/tr><tr><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>3<\/td><td>225<\/td><td>1<\/td><\/tr><tr><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>3<\/td><td>220<\/td><td>1<\/td><\/tr><tr><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>205<\/td><td>2<\/td><\/tr><tr><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>3<\/td><td>120<\/td><td>3<\/td><\/tr><tr><td>NeusFPS<\/td><td>Neus Camps<\/td><td>3<\/td><td>70<\/td><td>4<\/td><\/tr><tr><td>JordiX<\/td><td>Jordi Puig<\/td><td>2<\/td><td>20<\/td><td>6<\/td><\/tr><tr><td>PaulaRock<\/td><td>Paula Roca<\/td><td>1<\/td><td>0<\/td><td>NULL<\/td><\/tr><tr><td>GerardGG<\/td><td>Gerard Font<\/td><td>1<\/td><td>0<\/td><td>NULL<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C8 \u2013 Jugadors amb almenys 2 vict\u00f2ries<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Identificar els jugadors amb millor rendiment competitiu. Usem <strong>subqueries correlades<\/strong> dins del SELECT i del WHERE per comptador vict\u00f2ries i partides jugades sense necesitat de GROUP BY complex. El WHERE filtra sols els jugadors amb \u2265 2 vict\u00f2ries.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">Subquery correlada (x3)<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.nick_joc, u.nom,\n (SELECT COUNT(*) FROM partides p\n WHERE p.id_guanyador = u.id_usuari AND p.estat='finalitzada') AS victor\u00edes,\n (SELECT COUNT(*) FROM partides p\n WHERE (p.id_jugador1=u.id_usuari OR p.id_jugador2=u.id_usuari)\n AND p.estat='finalitzada') AS partides_jugades\n FROM usuaris u\n WHERE (SELECT COUNT(*) FROM partides p\n WHERE p.id_guanyador=u.id_usuari AND p.estat='finalitzada') >= 2\n ORDER BY victor\u00edes DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (3 files):<\/strong><table><tr><th>nick_joc<\/th><th>nom<\/th><th>victor\u00edes<\/th><th>partides_jugades<\/th><\/tr><tr><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>5<\/td><td>6<\/td><\/tr><tr><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>4<\/td><td>6<\/td><\/tr><tr><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>6<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C9 \u2013 Classificaci\u00f3 dins de cada torneig<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Cada torneig necessita mostrar la seva pr\u00f2pia classificaci\u00f3 amb nick i posici\u00f3. Usem un <strong>triple JOIN<\/strong> (participants_torneig \u2192 tornejos \u2192 jocs \u2192 usuaris) i ordenem per torneig i posici\u00f3 final ascendent.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x3)<\/span><span class=\"tec\">WHERE IS NOT NULL<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.nom_torneig, j.nom_joc, u.nick_joc, u.nom,\n pt.posicio_final, pt.puntuacio\n FROM participants_torneig pt\n INNER JOIN tornejos t ON pt.id_torneig = t.id_torneig\n INNER JOIN jocs j ON t.id_joc = j.id_joc\n INNER JOIN usuaris u ON pt.id_usuari = u.id_usuari\n WHERE pt.posicio_final IS NOT NULL\n ORDER BY t.id_torneig ASC, pt.posicio_final ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (15 files):<\/strong><table><tr><th>nom_torneig<\/th><th>nom_joc<\/th><th>nick_joc<\/th><th>nom<\/th><th>posicio_final<\/th><th>puntuacio<\/th><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>1<\/td><td>100<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>2<\/td><td>80<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>60<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>4<\/td><td>40<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>5<\/td><td>30<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>JordiX<\/td><td>Jordi Puig<\/td><td>6<\/td><td>20<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>1<\/td><td>90<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>2<\/td><td>70<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>3<\/td><td>55<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>4<\/td><td>40<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>5<\/td><td>25<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>1<\/td><td>100<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>2<\/td><td>75<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>3<\/td><td>50<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>4<\/td><td>25<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C10 \u2013 Detall de partides amb guanyador<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 PARTIDES<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Vista completa de partides finalitzades amb noms llegibles. Necessita <strong>4 JOINs<\/strong>: torneig, jugador1, jugador2 i guanyador. El guanyador pot ser NULL, per tant usem <strong>LEFT JOIN<\/strong> i <strong>COALESCE<\/strong> per mostrar ‘Empat \/ Pendent’.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x3)<\/span><span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">COALESCE<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.nom_torneig, p.ronda,\n u1.nick_joc AS jugador_1, u2.nick_joc AS jugador_2,\n p.puntuacio_j1, p.puntuacio_j2,\n COALESCE(ug.nick_joc, 'Empat \/ Pendent') AS guanyador,\n p.estat, p.data_hora\n FROM partides p\n INNER JOIN tornejos t ON p.id_torneig = t.id_torneig\n INNER JOIN usuaris u1 ON p.id_jugador1 = u1.id_usuari\n INNER JOIN usuaris u2 ON p.id_jugador2 = u2.id_usuari\n LEFT JOIN usuaris ug ON p.id_guanyador = ug.id_usuari\n WHERE p.estat = 'finalitzada'\n ORDER BY t.id_torneig ASC, p.ronda ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (12 files):<\/strong><table><tr><th>nom_torneig<\/th><th>ronda<\/th><th>jugador_1<\/th><th>jugador_2<\/th><th>puntuacio_j1<\/th><th>puntuacio_j2<\/th><th>guanyador<\/th><th>estat<\/th><th>data_hora<\/th><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>MarcL_Pro<\/td><td>JordiX<\/td><td>16<\/td><td>10<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 11:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>SaraGamer<\/td><td>LaiaMaster<\/td><td>14<\/td><td>9<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 11:30<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>DaniStar<\/td><td>NeusFPS<\/td><td>13<\/td><td>11<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-15 12:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>2<\/td><td>MarcL_Pro<\/td><td>SaraGamer<\/td><td>16<\/td><td>14<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 13:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>3<\/td><td>MarcL_Pro<\/td><td>DaniStar<\/td><td>16<\/td><td>12<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 13:45<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>SaraGamer<\/td><td>MarcL_Pro<\/td><td>13<\/td><td>8<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 15:00<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>DaniStar<\/td><td>LaiaMaster<\/td><td>13<\/td><td>10<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-15 15:30<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>NeusFPS<\/td><td>SaraGamer<\/td><td>10<\/td><td>13<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 16:00<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>2<\/td><td>SaraGamer<\/td><td>DaniStar<\/td><td>13<\/td><td>11<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 16:30<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>1<\/td><td>MarcL_Pro<\/td><td>SaraGamer<\/td><td>1<\/td><td>0<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-16 10:00<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>1<\/td><td>DaniStar<\/td><td>LaiaMaster<\/td><td>1<\/td><td>0<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-16 10:45<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>2<\/td><td>MarcL_Pro<\/td><td>DaniStar<\/td><td>1<\/td><td>0<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-16 11:30<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C11 \u2013 Missatges del xat amb autor<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 4 \u2013 COMUNICACI\u00d3<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> El sistema de xat necessita mostrar missatges en ordre cronol\u00f2gic amb el nick de l’autor i a quina LAN Party pertanyen. Usem <strong>JOIN<\/strong> amb usuaris i lan_party i <strong>CASE WHEN<\/strong> per mostrar l’estat de lectura llegiblement.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, u.nick_joc AS autor, m.contingut, m.data_hora,\n CASE WHEN m.llegit = 1 THEN 'Llegit' ELSE 'No llegit' END AS estat_lectura\n FROM missatges_xat m\n INNER JOIN usuaris u ON m.id_usuari = u.id_usuari\n INNER JOIN lan_party lp ON m.id_lan_party = lp.id_lan_party\n ORDER BY lp.id_lan_party ASC, m.data_hora DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (8 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>autor<\/th><th>contingut<\/th><th>data_hora<\/th><th>estat_lectura<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>Hola a tots! Qui vol jugar CS2 primer?<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>SaraGamer<\/td><td>Jo! Tinc les ganes del m\u00f3n<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>DaniStar<\/td><td>Compte, vaig a guanyar-vos a tots \ud83d\ude04<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>LaiaMaster<\/td><td>On \u00e9s la sala de jocs?<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>NeusFPS<\/td><td>Sala 3, primera planta<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>GG tots! Gran torneig<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>SaraGamer<\/td><td>Ens veiem a la primavera?<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>MarcL_Pro<\/td><td>S\u00ed! Ja m'han confirmat la inscripci\u00f3<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C12 \u2013 Notificacions no llegides per tipus<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 4 \u2013 COMUNICACI\u00d3<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> L’usuari necessita saber quantes notificacions pendents t\u00e9 de cada categoria. Agrupa per tipus amb <strong>GROUP BY<\/strong> i <strong>HAVING<\/strong> per sols mostrar les que tenim pendents. Usem <strong>GROUP_CONCAT<\/strong> per llistar els t\u00edtols dins d’una sola fila.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">COUNT<\/span><span class=\"tec\">GROUP_CONCAT<\/span><span class=\"tec\">WHERE<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT n.tipus,\n COUNT(*) AS total_no_llegides,\n GROUP_CONCAT(n.titol, ' | ') AS titols_pendents\n FROM notificacions n\n WHERE n.llegida = 0\n GROUP BY n.tipus\n HAVING COUNT(*) >= 1\n ORDER BY total_no_llegides DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (4 files):<\/strong><table><tr><th>tipus<\/th><th>total_no_llegides<\/th><th>titols_pendents<\/th><\/tr><tr><td>torneig<\/td><td>2<\/td><td>Torneig FIFA en curs | Resultats del Torneig CS2<\/td><\/tr><tr><td>xat<\/td><td>1<\/td><td>Nou missatge en el xat<\/td><\/tr><tr><td>sistema<\/td><td>1<\/td><td>Actualitzaci\u00f3 del sistema<\/td><\/tr><tr><td>inscripcio<\/td><td>1<\/td><td>Inscripci\u00f3 confirmada<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n<\/div>\n\n<footer>\n Documentaci\u00f3 generada autom\u00e0ticament amb dades reals de <strong>lan_party.db<\/strong> \u00b7 LAN Party Intranet \u00b7 Febrer 2026\n<\/footer>\n\n<\/body>\n<\/html>\n","protected":false},"excerpt":{"rendered":"<p>Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1577,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"saved_in_kubio":false,"_dseop_meta":"","_dseop_selected_keywords":"","footnotes":""},"class_list":["post-1683","page","type-page","status-publish","hentry"],"kubio_ai_page_context":{"short_desc":"","purpose":"general"},"yoast_head":"\n<title>Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683\" \/>\n<meta property=\"og:locale\" content=\"ca_ES\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes\" \/>\n<meta property=\"og:description\" content=\"Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683\" \/>\n<meta property=\"og:site_name\" content=\"Adrian Jaimes\" \/>\n<meta property=\"article:modified_time\" content=\"2026-02-03T13:00:00+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Temps estimat de lectura\" \/>\n\t<meta name=\"twitter:data1\" content=\"11 minuts\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\",\"name\":\"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#website\"},\"datePublished\":\"2026-01-16T08:19:25+00:00\",\"dateModified\":\"2026-02-03T13:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683#breadcrumb\"},\"inLanguage\":\"ca\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Inici\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"1r SMX\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1084\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Repte 1.3\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1491\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"7\ufe0f\u20e3 – \ud83d\udd10 – Intranet de la LAN Party\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1577\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"Generar consultes per extreure informaci\u00f3 de la BBDD\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#website\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\",\"name\":\"Adrian Jaimes\",\"description\":\"S\u00f3c estudiant d'inform\u00e0tica i aqu\u00ed podr\u00e0s veure el meu cam\u00ed acad\u00e8mic, els projectes que he realitzat i els coneixements que he adquirit durant la meva formaci\u00f3. Aquesta p\u00e0gina \u00e9s un reflex de la meva passi\u00f3 per la tecnologia i el meu comprom\u00eds per seguir aprenent i millorant cada dia. Explora els meus treballs i seguiment acad\u00e8mic!\",\"publisher\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ca\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#organization\",\"name\":\"Adrian Jaimes\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ca\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/cropped-cropped-cropped-width_550_009.webp\",\"contentUrl\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/cropped-cropped-cropped-width_550_009.webp\",\"width\":512,\"height\":512,\"caption\":\"Adrian Jaimes\"},\"image\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#\\\/schema\\\/logo\\\/image\\\/\"}}]}<\/script>\n","yoast_head_json":{"title":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","og_locale":"ca_ES","og_type":"article","og_title":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","og_description":"Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]","og_url":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","og_site_name":"Adrian Jaimes","article_modified_time":"2026-02-03T13:00:00+00:00","twitter_card":"summary_large_image","twitter_misc":{"Temps estimat de lectura":"11 minuts"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","url":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","name":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","isPartOf":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#website"},"datePublished":"2026-01-16T08:19:25+00:00","dateModified":"2026-02-03T13:00:00+00:00","breadcrumb":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683#breadcrumb"},"inLanguage":"ca","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Inici","item":"https:\/\/ajaimes.inscastellbisbal.net\/"},{"@type":"ListItem","position":2,"name":"1r SMX","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1084"},{"@type":"ListItem","position":3,"name":"Repte 1.3","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1491"},{"@type":"ListItem","position":4,"name":"7\ufe0f\u20e3 – \ud83d\udd10 – Intranet de la LAN Party","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1577"},{"@type":"ListItem","position":5,"name":"Generar consultes per extreure informaci\u00f3 de la BBDD"}]},{"@type":"WebSite","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#website","url":"https:\/\/ajaimes.inscastellbisbal.net\/","name":"Adrian Jaimes","description":"S\u00f3c estudiant d'inform\u00e0tica i aqu\u00ed podr\u00e0s veure el meu cam\u00ed acad\u00e8mic, els projectes que he realitzat i els coneixements que he adquirit durant la meva formaci\u00f3. Aquesta p\u00e0gina \u00e9s un reflex de la meva passi\u00f3 per la tecnologia i el meu comprom\u00eds per seguir aprenent i millorant cada dia. Explora els meus treballs i seguiment acad\u00e8mic!","publisher":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ajaimes.inscastellbisbal.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ca"},{"@type":"Organization","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#organization","name":"Adrian Jaimes","url":"https:\/\/ajaimes.inscastellbisbal.net\/","logo":{"@type":"ImageObject","inLanguage":"ca","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#\/schema\/logo\/image\/","url":"https:\/\/ajaimes.inscastellbisbal.net\/wp-content\/uploads\/2025\/12\/cropped-cropped-cropped-width_550_009.webp","contentUrl":"https:\/\/ajaimes.inscastellbisbal.net\/wp-content\/uploads\/2025\/12\/cropped-cropped-cropped-width_550_009.webp","width":512,"height":512,"caption":"Adrian Jaimes"},"image":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#\/schema\/logo\/image\/"}}]}},"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1683"}],"version-history":[{"count":2,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683\/revisions"}],"predecessor-version":[{"id":1903,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683\/revisions\/1903"}],"up":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1577"}],"wp:attachment":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

{"id":1683,"date":"2026-01-16T08:19:25","date_gmt":"2026-01-16T08:19:25","guid":{"rendered":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683"},"modified":"2026-02-03T13:00:00","modified_gmt":"2026-02-03T13:00:00","slug":"generar-consultes-per-extreure-informacio-de-la-bbdd","status":"publish","type":"page","link":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","title":{"rendered":"Generar consultes per extreure informaci\u00f3 de la BBDD"},"content":{"rendered":"\n\n\n\n\n\nConsultes SQL \u2013 LAN Party Intranet<\/title>\n<style>\n * { margin:0; padding:0; box-sizing:border-box; }\n body {\n font-family: 'Segoe UI', system-ui, sans-serif;\n background:#f0f2f5; color:#2c2c2c; line-height:1.65;\n }\n\n \/* \u2500\u2500 HEADER \u2500\u2500 *\/\n header {\n background: linear-gradient(135deg, #1a1a2e 0%, #16213e 60%, #0f3460 100%);\n color:#fff; padding:48px 24px 38px; text-align:center; position:relative; overflow:hidden;\n }\n header::before {\n content:''; position:absolute; inset:0;\n background: radial-gradient(circle at 75% 40%, rgba(233,69,96,.3) 0%, transparent 55%);\n }\n header h1 { font-size:2.1rem; font-weight:700; position:relative; }\n header h1 span { color:#e94560; }\n header p { color:#a8a8b3; margin-top:5px; font-size:.93rem; position:relative; }\n .badge-top {\n display:inline-block; background:#e94560; color:#fff;\n padding:4px 16px; border-radius:20px; font-size:.77rem;\n font-weight:600; margin-top:12px; position:relative;\n }\n\n \/* \u2500\u2500 WRAP \u2500\u2500 *\/\n .wrap { max-width:1020px; margin:32px auto; padding:0 20px 56px; }\n\n \/* \u2500\u2500 RESUM T\u00c8CNIQUES (dalt) \u2500\u2500 *\/\n .resum-bloc {\n background:#fff; border-radius:12px; box-shadow:0 2px 10px rgba(0,0,0,.08);\n padding:22px 26px; margin-bottom:28px;\n }\n .resum-bloc h3 { color:#0f3460; margin-bottom:12px; font-size:1rem; }\n .resum-grid {\n display:grid; grid-template-columns: repeat(auto-fill, minmax(175px,1fr)); gap:10px;\n }\n .resum-item {\n background:#f0f4f8; border-radius:8px; padding:10px 14px;\n border-left:4px solid #e94560; font-size:.85rem;\n }\n .resum-item strong { color:#0f3460; display:block; font-size:.78rem; text-transform:uppercase; letter-spacing:.4px; margin-bottom:2px; }\n\n \/* \u2500\u2500 SECCIONS \u2500\u2500 *\/\n .secci\u00f3 {\n background:#fff; border-radius:12px;\n box-shadow:0 2px 12px rgba(0,0,0,.07);\n margin-bottom:24px; overflow:hidden;\n }\n .secci\u00f3-header {\n background:#1a1a2e; color:#fff;\n padding:13px 22px; display:flex; align-items:center; gap:14px;\n }\n .secci\u00f3-header .ico { font-size:1.3rem; }\n .secci\u00f3-header h2 { font-size:1rem; font-weight:600; margin-bottom:2px; }\n .\u00e0rea-badge {\n display:inline-block; background:#e94560; color:#fff;\n padding:2px 10px; border-radius:10px; font-size:.7rem; font-weight:600;\n }\n .secci\u00f3-cos { padding:20px 22px; }\n\n \/* \u2500\u2500 JUSTIFICACI\u00d3 \u2500\u2500 *\/\n .justificacio {\n background:#f8fafc; border-left:4px solid #0f3460;\n border-radius:0 8px 8px 0; padding:12px 16px; margin-bottom:12px;\n font-size:.9rem;\n }\n .justificacio strong { color:#0f3460; }\n\n \/* \u2500\u2500 T\u00c8CNIQUES \u2500\u2500 *\/\n .tec-row { margin-bottom:12px; }\n .tec-row strong { font-size:.82rem; color:#555; }\n .tec {\n display:inline-block; background:#e8f0fe; color:#1a55b8;\n padding:3px 9px; border-radius:5px; font-size:.76rem;\n font-weight:600; margin:2px 3px 2px 0;\n }\n\n \/* \u2500\u2500 SQL \u2500\u2500 *\/\n .bloc-sql {\n background:#1e1e2e; border-radius:8px; padding:14px 18px; margin-bottom:14px;\n }\n .bloc-sql strong { color:#a8a8b3; font-size:.8rem; display:block; margin-bottom:6px; }\n .bloc-sql pre {\n color:#c9d1d9; font-size:.82rem; font-family:'Consolas','Courier New',monospace;\n white-space:pre-wrap; line-height:1.55;\n }\n\n \/* \u2500\u2500 EVID\u00c8NCIA (taula de resultats) \u2500\u2500 *\/\n .bloc-evidencia { margin-top:4px; }\n .bloc-evidencia > strong { font-size:.84rem; color:#2e7d32; display:block; margin-bottom:8px; }\n .bloc-evidencia table {\n width:100%; border-collapse:collapse; font-size:.82rem;\n border:1px solid #e2e8f0; border-radius:6px; overflow:hidden;\n }\n .bloc-evidencia th {\n background:#0f3460; color:#fff; padding:8px 11px;\n text-align:left; font-weight:600; font-size:.76rem;\n text-transform:uppercase; letter-spacing:.4px; white-space:nowrap;\n }\n .bloc-evidencia td {\n padding:7px 11px; border-bottom:1px solid #eee;\n vertical-align:top; white-space:nowrap;\n }\n .bloc-evidencia tr:nth-child(even) td { background:#f8fafc; }\n .bloc-evidencia tr:last-child td { border-bottom:none; }\n\n \/* \u2500\u2500 FOOTER \u2500\u2500 *\/\n footer {\n text-align:center; padding:28px; color:#a8a8b3; font-size:.8rem;\n }\n\n \/* \u2500\u2500 PRINT \u2500\u2500 *\/\n @media print {\n body { background:#fff; }\n .secci\u00f3 { box-shadow:none; border:1px solid #ddd; page-break-inside:avoid; }\n header { background:#1a1a2e !important; -webkit-print-color-adjust:exact; }\n }\n<\/style>\n<\/head>\n<body>\n\n<header>\n <h1>\ud83d\udcca Consultes SQL \u2013 <span>Extracci\u00f3 d’Informaci\u00f3<\/span><\/h1>\n <p>Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies<\/p>\n <div class=\"badge-top\">Febrer 2026<\/div>\n<\/header>\n\n<div class=\"wrap\">\n\n \n <div class=\"resum-bloc\">\n <h3>\ud83c\udfaf Resum de t\u00e8cniques SQL implementades<\/h3>\n <div class=\"resum-grid\">\n <div class=\"resum-item\"><strong>JOINs<\/strong>INNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta<\/div>\n <div class=\"resum-item\"><strong>Agregaci\u00f3n<\/strong>COUNT, SUM, MIN, MAX, COUNT(DISTINCT)<\/div>\n <div class=\"resum-item\"><strong>Filtrat<\/strong>GROUP BY, HAVING, WHERE amb condicions complexes<\/div>\n <div class=\"resum-item\"><strong>Subqueries<\/strong>Subqueries correlades dins SELECT i WHERE<\/div>\n <div class=\"resum-item\"><strong>Funcions<\/strong>COALESCE, ROUND, CASE WHEN, GROUP_CONCAT<\/div>\n <div class=\"resum-item\"><strong>Evid\u00e8ncies<\/strong>12 consultes amb resultats reals de la BBDD<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C1 \u2013 Usuaris amb el nom del rol assignat<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 1 \u2013 USUARIS I ROLS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Necessitem veure en una sola taula qui \u00e9s cada usuari i quines funcions t\u00e9 dins del sistema. Utilitzem un <strong>INNER JOIN<\/strong> entre <code>usuaris<\/code> i <code>rols<\/code> perqu\u00e8 cada usuari t\u00e9 exactament un rol (FK obligat\u00f2ria). Ordenem per rol per agrupar visuament admins, jugadors i espectadors.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.id_usuari, u.nom, u.nick_joc, u.email,\n r.nom_rol, r.descripcio AS descripcio_rol\n FROM usuaris u\n INNER JOIN rols r ON u.id_rol = r.id_rol\n WHERE u.actiu = 1\n ORDER BY r.id_rol ASC, u.nom ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (10 files):<\/strong><table><tr><th>id_usuari<\/th><th>nom<\/th><th>nick_joc<\/th><th>email<\/th><th>nom_rol<\/th><th>descripcio_rol<\/th><\/tr><tr><td>9<\/td><td>Albert Coma<\/td><td>AlbertAce<\/td><td>albert.coma@gmail.com<\/td><td>admin<\/td><td>Administrador amb control total del sistema<\/td><\/tr><tr><td>5<\/td><td>Dani Sol\u00e0<\/td><td>DaniStar<\/td><td>dani.sola@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>7<\/td><td>Gerard Font<\/td><td>GerardGG<\/td><td>gerard.font@yahoo.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>3<\/td><td>Jordi Puig<\/td><td>JordiX<\/td><td>jordi.puig@yahoo.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>6<\/td><td>Laia Valls<\/td><td>LaiaMaster<\/td><td>laia.valls@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>1<\/td><td>Marc L\u00f3pez<\/td><td>MarcL_Pro<\/td><td>marc.lopez@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>8<\/td><td>Neus Camps<\/td><td>NeusFPS<\/td><td>neus.camps@hotmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>4<\/td><td>Paula Roca<\/td><td>PaulaRock<\/td><td>paula.roca@hotmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>2<\/td><td>Sara Mart\u00ednez<\/td><td>SaraGamer<\/td><td>sara.martinez@gmail.com<\/td><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><\/tr><tr><td>10<\/td><td>Rita Mas<\/td><td>RitaPlay<\/td><td>rita.mas@yahoo.com<\/td><td>espectador<\/td><td>Usuari que nom\u00e9s pot veure contingut i fer xat<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C2 \u2013 Nombre d'usuaris per cada rol<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 1 \u2013 USUARIS I ROLS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet saber la distribuci\u00f3 de rols dins del sistema: quants admins tenim, quants jugadors actius i espectadors. Molt \u00fatil per a l’administrador per controlar el balan\u00e7 de la comunitat. Utilitzem <strong>GROUP BY<\/strong> amb <strong>COUNT<\/strong> i <strong>LEFT JOIN<\/strong> per incloure rols sense usuaris.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">COUNT<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT r.nom_rol, r.descripcio,\n COUNT(u.id_usuari) AS total_usuaris\n FROM rols r\n LEFT JOIN usuaris u ON r.id_rol = u.id_rol AND u.actiu = 1\n GROUP BY r.id_rol, r.nom_rol, r.descripcio\n ORDER BY total_usuaris DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (3 files):<\/strong><table><tr><th>nom_rol<\/th><th>descripcio<\/th><th>total_usuaris<\/th><\/tr><tr><td>jugador<\/td><td>Usuari que pot inscriure's i participar en tornejos<\/td><td>8<\/td><\/tr><tr><td>admin<\/td><td>Administrador amb control total del sistema<\/td><td>1<\/td><\/tr><tr><td>espectador<\/td><td>Usuari que nom\u00e9s pot veure contingut i fer xat<\/td><td>1<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C3 \u2013 Usuaris inscrits amb estat de pagament<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 2 \u2013 INSCRIPCIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> L’organitzador necessita saber qui s’ha inscrit a cada poble i si ha pagat la entrada. Utilitzem un <strong>doble JOIN<\/strong> (inscripcions \u2192 usuaris, inscripcions \u2192 lan_party) i <strong>CASE WHEN<\/strong> per convertir els valors booleens (0\/1) en texto llegible (‘S\u00ed’\/’No’).<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, u.nick_joc, u.nom,\n CASE WHEN i.ha_pagat = 1 THEN 'S\u00ed' ELSE 'No' END AS ha_pagat,\n CASE WHEN i.ha_assistit = 1 THEN 'S\u00ed' ELSE 'No' END AS ha_assistit,\n i.data_inscripcio\n FROM inscripcions i\n INNER JOIN usuaris u ON i.id_usuari = u.id_usuari\n INNER JOIN lan_party lp ON i.id_lan_party = lp.id_lan_party\n ORDER BY lp.id_lan_party ASC, i.ha_pagat DESC, u.nom ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (13 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>nick_joc<\/th><th>nom<\/th><th>ha_pagat<\/th><th>ha_assistit<\/th><th>data_inscripcio<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>GerardGG<\/td><td>Gerard Font<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>JordiX<\/td><td>Jordi Puig<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>S\u00ed<\/td><td>S\u00ed<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>PaulaRock<\/td><td>Paula Roca<\/td><td>No<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>AlbertAce<\/td><td>Albert Coma<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>S\u00ed<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>No<\/td><td>No<\/td><td>2026-02-03 12:46:33<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C4 \u2013 Resum d'inscripcions per LAN Party<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 2 \u2013 INSCRIPCIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet saber el resum de cada poble: quants inscrits, quants han pagat i quin percentatge d’assist\u00e8ncia. El <strong>HAVING<\/strong> filtra sols les LAN Parties amb almenys 3 inscripcions. Usem <strong>COUNT condicional amb CASE<\/strong> i <strong>ROUND<\/strong> per calcular el percentatge d’assist\u00e8ncia.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">SUM<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ROUND<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, lp.capacitat_maxima,\n COUNT(i.id_inscripcio) AS total_inscrits,\n SUM(CASE WHEN i.ha_pagat = 1 THEN 1 ELSE 0 END) AS han_pagat,\n SUM(CASE WHEN i.ha_assistit = 1 THEN 1 ELSE 0 END) AS han_assistit,\n ROUND(SUM(CASE WHEN i.ha_assistit=1 THEN 1 ELSE 0 END)*100.0\/COUNT(i.id_inscripcio),1) AS pct_assistencia\n FROM lan_party lp\n LEFT JOIN inscripcions i ON lp.id_lan_party = i.id_lan_party\n GROUP BY lp.id_lan_party, lp.nom_\u00e9v\u00e9niment, lp.capacitat_maxima\n HAVING COUNT(i.id_inscripcio) >= 3\n ORDER BY total_inscrits DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (2 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>capacitat_maxima<\/th><th>total_inscrits<\/th><th>han_pagat<\/th><th>han_assistit<\/th><th>pct_assistencia<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>100<\/td><td>8<\/td><td>7<\/td><td>5<\/td><td>62.5<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>80<\/td><td>5<\/td><td>4<\/td><td>0<\/td><td>0.0<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C5 \u2013 Tornejos amb el joc i la LAN Party<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 JOCS I TORNEJOS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Vista resum de tots els tornejos amb el joc i l’poble associat. Molt \u00fatil per a la p\u00e0gina principal de la intranet. Usem un <strong>doble JOIN<\/strong>: tornejos \u2192 jocs i tornejos \u2192 lan_party, i ordenem per estat per mostrar primer els en curs.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.id_torneig, t.nom_torneig, j.nom_joc, j.genere,\n lp.nom_\u00e9v\u00e9niment, t.format, t.max_participants,\n t.premi, t.estat, t.data_inici, t.data_fi\n FROM tornejos t\n INNER JOIN jocs j ON t.id_joc = j.id_joc\n INNER JOIN lan_party lp ON t.id_lan_party = lp.id_lan_party\n ORDER BY t.estat ASC, t.data_inici ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (6 files):<\/strong><table><tr><th>id_torneig<\/th><th>nom_torneig<\/th><th>nom_joc<\/th><th>genere<\/th><th>nom_\u00e9v\u00e9niment<\/th><th>format<\/th><th>max_participants<\/th><th>premi<\/th><th>estat<\/th><th>data_inici<\/th><th>data_fi<\/th><\/tr><tr><td>4<\/td><td>Torneig FIFA 24 \u2013 1v1<\/td><td>FIFA 24<\/td><td>Esports<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 30\u20ac<\/td><td>en_curs<\/td><td>2026-02-16 14:00<\/td><td>2026-02-16 16:00<\/td><\/tr><tr><td>1<\/td><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>FPS<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>8<\/td><td>regalo valorat en 60\u20ac<\/td><td>finalitzat<\/td><td>2026-02-15 11:00<\/td><td>2026-02-15 14:00<\/td><\/tr><tr><td>2<\/td><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>FPS<\/td><td>LAN Party Hivern 2026<\/td><td>lliga<\/td><td>6<\/td><td>regalo valorat en 40\u20ac<\/td><td>finalitzat<\/td><td>2026-02-15 15:00<\/td><td>2026-02-15 18:00<\/td><\/tr><tr><td>3<\/td><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>MOBA<\/td><td>LAN Party Hivern 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 80\u20ac<\/td><td>finalitzat<\/td><td>2026-02-16 10:00<\/td><td>2026-02-16 13:00<\/td><\/tr><tr><td>5<\/td><td>Torneig CS2 \u2013 Primavera<\/td><td>Counter-Strike 2<\/td><td>FPS<\/td><td>LAN Party Primavera 2026<\/td><td>lliga<\/td><td>6<\/td><td>regalo valorat en 50\u20ac<\/td><td>obert<\/td><td>2026-04-10 10:00<\/td><td>2026-04-10 13:00<\/td><\/tr><tr><td>6<\/td><td>Torneig Rocket League<\/td><td>Rocket League<\/td><td>Esports<\/td><td>LAN Party Primavera 2026<\/td><td>eliminacio_directa<\/td><td>4<\/td><td>regalo valorat en 35\u20ac<\/td><td>obert<\/td><td>2026-04-11 10:00<\/td><td>2026-04-11 12:00<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C6 \u2013 Popularitat de jocs per participacions<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 JOCS I TORNEJOS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Ens permet analitzar quins jocs s\u00f3n els m\u00e9s populars. Comptem tornejos i participants per joc. El <strong>HAVING<\/strong> elimina els jocs sense participacions. Usem <strong>COUNT(DISTINCT)<\/strong> per comptador \u00fanics de tornejos i <strong>COALESCE<\/strong> per manejar NULLs.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">LEFT JOIN (x2)<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">COUNT(DISTINCT)<\/span><span class=\"tec\">MAX<\/span><span class=\"tec\">COALESCE<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT j.nom_joc, j.genere,\n COUNT(DISTINCT t.id_torneig) AS num_tornejos,\n COUNT(pt.id_participacio) AS num_participacions,\n COALESCE(MAX(pt.puntuacio), 0) AS puntuacio_maxima\n FROM jocs j\n LEFT JOIN tornejos t ON j.id_joc = t.id_joc\n LEFT JOIN participants_torneig pt ON t.id_torneig = pt.id_torneig\n GROUP BY j.id_joc, j.nom_joc, j.genere\n HAVING COUNT(pt.id_participacio) > 0\n ORDER BY num_participacions DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (4 files):<\/strong><table><tr><th>nom_joc<\/th><th>genere<\/th><th>num_tornejos<\/th><th>num_participacions<\/th><th>puntuacio_maxima<\/th><\/tr><tr><td>Counter-Strike 2<\/td><td>FPS<\/td><td>2<\/td><td>6<\/td><td>100<\/td><\/tr><tr><td>Valorant<\/td><td>FPS<\/td><td>1<\/td><td>5<\/td><td>90<\/td><\/tr><tr><td>FIFA 24<\/td><td>Esports<\/td><td>1<\/td><td>4<\/td><td>0<\/td><\/tr><tr><td>League of Legends<\/td><td>MOBA<\/td><td>1<\/td><td>4<\/td><td>100<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C7 \u2013 Classificaci\u00f3 global per puntuaci\u00f3 total<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> La classificaci\u00f3 general \u00e9s una de les p\u00e0gines clau de la intranet. Suma les puntuacions de tots els tornejos amb <strong>SUM<\/strong>, i usem <strong>MIN<\/strong> per mostrar la millor posici\u00f3 aconseguida. GROUP BY sobre participants_torneig amb JOIN cap a usuaris.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN<\/span><span class=\"tec\">GROUP BY<\/span><span class=\"tec\">SUM<\/span><span class=\"tec\">COUNT(DISTINCT)<\/span><span class=\"tec\">MIN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.nick_joc, u.nom,\n COUNT(DISTINCT pt.id_torneig) AS tornejos_jugats,\n SUM(pt.puntuacio) AS puntuacio_total,\n MIN(pt.posicio_final) AS millor_posicio\n FROM usuaris u\n INNER JOIN participants_torneig pt ON u.id_usuari = pt.id_usuari\n GROUP BY u.id_usuari, u.nick_joc, u.nom\n ORDER BY puntuacio_total DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (8 files):<\/strong><table><tr><th>nick_joc<\/th><th>nom<\/th><th>tornejos_jugats<\/th><th>puntuacio_total<\/th><th>millor_posicio<\/th><\/tr><tr><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>3<\/td><td>225<\/td><td>1<\/td><\/tr><tr><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>3<\/td><td>220<\/td><td>1<\/td><\/tr><tr><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>205<\/td><td>2<\/td><\/tr><tr><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>3<\/td><td>120<\/td><td>3<\/td><\/tr><tr><td>NeusFPS<\/td><td>Neus Camps<\/td><td>3<\/td><td>70<\/td><td>4<\/td><\/tr><tr><td>JordiX<\/td><td>Jordi Puig<\/td><td>2<\/td><td>20<\/td><td>6<\/td><\/tr><tr><td>PaulaRock<\/td><td>Paula Roca<\/td><td>1<\/td><td>0<\/td><td>NULL<\/td><\/tr><tr><td>GerardGG<\/td><td>Gerard Font<\/td><td>1<\/td><td>0<\/td><td>NULL<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C8 \u2013 Jugadors amb almenys 2 vict\u00f2ries<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Identificar els jugadors amb millor rendiment competitiu. Usem <strong>subqueries correlades<\/strong> dins del SELECT i del WHERE per comptador vict\u00f2ries i partides jugades sense necesitat de GROUP BY complex. El WHERE filtra sols els jugadors amb \u2265 2 vict\u00f2ries.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">Subquery correlada (x3)<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT u.nick_joc, u.nom,\n (SELECT COUNT(*) FROM partides p\n WHERE p.id_guanyador = u.id_usuari AND p.estat='finalitzada') AS victor\u00edes,\n (SELECT COUNT(*) FROM partides p\n WHERE (p.id_jugador1=u.id_usuari OR p.id_jugador2=u.id_usuari)\n AND p.estat='finalitzada') AS partides_jugades\n FROM usuaris u\n WHERE (SELECT COUNT(*) FROM partides p\n WHERE p.id_guanyador=u.id_usuari AND p.estat='finalitzada') >= 2\n ORDER BY victor\u00edes DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (3 files):<\/strong><table><tr><th>nick_joc<\/th><th>nom<\/th><th>victor\u00edes<\/th><th>partides_jugades<\/th><\/tr><tr><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>5<\/td><td>6<\/td><\/tr><tr><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>4<\/td><td>6<\/td><\/tr><tr><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>6<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C9 \u2013 Classificaci\u00f3 dins de cada torneig<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 CLASSIFICACIONS<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Cada torneig necessita mostrar la seva pr\u00f2pia classificaci\u00f3 amb nick i posici\u00f3. Usem un <strong>triple JOIN<\/strong> (participants_torneig \u2192 tornejos \u2192 jocs \u2192 usuaris) i ordenem per torneig i posici\u00f3 final ascendent.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x3)<\/span><span class=\"tec\">WHERE IS NOT NULL<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.nom_torneig, j.nom_joc, u.nick_joc, u.nom,\n pt.posicio_final, pt.puntuacio\n FROM participants_torneig pt\n INNER JOIN tornejos t ON pt.id_torneig = t.id_torneig\n INNER JOIN jocs j ON t.id_joc = j.id_joc\n INNER JOIN usuaris u ON pt.id_usuari = u.id_usuari\n WHERE pt.posicio_final IS NOT NULL\n ORDER BY t.id_torneig ASC, pt.posicio_final ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (15 files):<\/strong><table><tr><th>nom_torneig<\/th><th>nom_joc<\/th><th>nick_joc<\/th><th>nom<\/th><th>posicio_final<\/th><th>puntuacio<\/th><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>1<\/td><td>100<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>2<\/td><td>80<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>3<\/td><td>60<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>4<\/td><td>40<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>5<\/td><td>30<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>Counter-Strike 2<\/td><td>JordiX<\/td><td>Jordi Puig<\/td><td>6<\/td><td>20<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>1<\/td><td>90<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>2<\/td><td>70<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>3<\/td><td>55<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>NeusFPS<\/td><td>Neus Camps<\/td><td>4<\/td><td>40<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>Valorant<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>5<\/td><td>25<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>MarcL_Pro<\/td><td>Marc L\u00f3pez<\/td><td>1<\/td><td>100<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>DaniStar<\/td><td>Dani Sol\u00e0<\/td><td>2<\/td><td>75<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>SaraGamer<\/td><td>Sara Mart\u00ednez<\/td><td>3<\/td><td>50<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>League of Legends<\/td><td>LaiaMaster<\/td><td>Laia Valls<\/td><td>4<\/td><td>25<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C10 \u2013 Detall de partides amb guanyador<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 3 \u2013 PARTIDES<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> Vista completa de partides finalitzades amb noms llegibles. Necessita <strong>4 JOINs<\/strong>: torneig, jugador1, jugador2 i guanyador. El guanyador pot ser NULL, per tant usem <strong>LEFT JOIN<\/strong> i <strong>COALESCE<\/strong> per mostrar ‘Empat \/ Pendent’.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x3)<\/span><span class=\"tec\">LEFT JOIN<\/span><span class=\"tec\">COALESCE<\/span><span class=\"tec\">WHERE<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT t.nom_torneig, p.ronda,\n u1.nick_joc AS jugador_1, u2.nick_joc AS jugador_2,\n p.puntuacio_j1, p.puntuacio_j2,\n COALESCE(ug.nick_joc, 'Empat \/ Pendent') AS guanyador,\n p.estat, p.data_hora\n FROM partides p\n INNER JOIN tornejos t ON p.id_torneig = t.id_torneig\n INNER JOIN usuaris u1 ON p.id_jugador1 = u1.id_usuari\n INNER JOIN usuaris u2 ON p.id_jugador2 = u2.id_usuari\n LEFT JOIN usuaris ug ON p.id_guanyador = ug.id_usuari\n WHERE p.estat = 'finalitzada'\n ORDER BY t.id_torneig ASC, p.ronda ASC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (12 files):<\/strong><table><tr><th>nom_torneig<\/th><th>ronda<\/th><th>jugador_1<\/th><th>jugador_2<\/th><th>puntuacio_j1<\/th><th>puntuacio_j2<\/th><th>guanyador<\/th><th>estat<\/th><th>data_hora<\/th><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>MarcL_Pro<\/td><td>JordiX<\/td><td>16<\/td><td>10<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 11:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>SaraGamer<\/td><td>LaiaMaster<\/td><td>14<\/td><td>9<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 11:30<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>1<\/td><td>DaniStar<\/td><td>NeusFPS<\/td><td>13<\/td><td>11<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-15 12:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>2<\/td><td>MarcL_Pro<\/td><td>SaraGamer<\/td><td>16<\/td><td>14<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 13:00<\/td><\/tr><tr><td>Torneig CS2 \u2013 Fase Final<\/td><td>3<\/td><td>MarcL_Pro<\/td><td>DaniStar<\/td><td>16<\/td><td>12<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-15 13:45<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>SaraGamer<\/td><td>MarcL_Pro<\/td><td>13<\/td><td>8<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 15:00<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>DaniStar<\/td><td>LaiaMaster<\/td><td>13<\/td><td>10<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-15 15:30<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>1<\/td><td>NeusFPS<\/td><td>SaraGamer<\/td><td>10<\/td><td>13<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 16:00<\/td><\/tr><tr><td>Torneig Valorant \u2013 Lliga<\/td><td>2<\/td><td>SaraGamer<\/td><td>DaniStar<\/td><td>13<\/td><td>11<\/td><td>SaraGamer<\/td><td>finalitzada<\/td><td>2026-02-15 16:30<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>1<\/td><td>MarcL_Pro<\/td><td>SaraGamer<\/td><td>1<\/td><td>0<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-16 10:00<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>1<\/td><td>DaniStar<\/td><td>LaiaMaster<\/td><td>1<\/td><td>0<\/td><td>DaniStar<\/td><td>finalitzada<\/td><td>2026-02-16 10:45<\/td><\/tr><tr><td>Torneig LoL \u2013 Eliminaci\u00f3<\/td><td>2<\/td><td>MarcL_Pro<\/td><td>DaniStar<\/td><td>1<\/td><td>0<\/td><td>MarcL_Pro<\/td><td>finalitzada<\/td><td>2026-02-16 11:30<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C11 \u2013 Missatges del xat amb autor<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 4 \u2013 COMUNICACI\u00d3<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> El sistema de xat necessita mostrar missatges en ordre cronol\u00f2gic amb el nick de l’autor i a quina LAN Party pertanyen. Usem <strong>JOIN<\/strong> amb usuaris i lan_party i <strong>CASE WHEN<\/strong> per mostrar l’estat de lectura llegiblement.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">INNER JOIN (x2)<\/span><span class=\"tec\">CASE WHEN<\/span><span class=\"tec\">ORDER BY<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT lp.nom_\u00e9v\u00e9niment, u.nick_joc AS autor, m.contingut, m.data_hora,\n CASE WHEN m.llegit = 1 THEN 'Llegit' ELSE 'No llegit' END AS estat_lectura\n FROM missatges_xat m\n INNER JOIN usuaris u ON m.id_usuari = u.id_usuari\n INNER JOIN lan_party lp ON m.id_lan_party = lp.id_lan_party\n ORDER BY lp.id_lan_party ASC, m.data_hora DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (8 files):<\/strong><table><tr><th>nom_\u00e9v\u00e9niment<\/th><th>autor<\/th><th>contingut<\/th><th>data_hora<\/th><th>estat_lectura<\/th><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>Hola a tots! Qui vol jugar CS2 primer?<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>SaraGamer<\/td><td>Jo! Tinc les ganes del m\u00f3n<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>DaniStar<\/td><td>Compte, vaig a guanyar-vos a tots \ud83d\ude04<\/td><td>2026-02-03 12:46:33<\/td><td>Llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>LaiaMaster<\/td><td>On \u00e9s la sala de jocs?<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>NeusFPS<\/td><td>Sala 3, primera planta<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Hivern 2026<\/td><td>MarcL_Pro<\/td><td>GG tots! Gran torneig<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>SaraGamer<\/td><td>Ens veiem a la primavera?<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><tr><td>LAN Party Primavera 2026<\/td><td>MarcL_Pro<\/td><td>S\u00ed! Ja m'han confirmat la inscripci\u00f3<\/td><td>2026-02-03 12:46:33<\/td><td>No llegit<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n <div class=\"secci\u00f3\">\n <div class=\"secci\u00f3-header\">\n <span class=\"ico\">\ud83d\udcca<\/span>\n <div><h2>C12 \u2013 Notificacions no llegides per tipus<\/h2><span class=\"\u00e0rea-badge\">\u00c0REA 4 \u2013 COMUNICACI\u00d3<\/span><\/div>\n <\/div>\n <div class=\"secci\u00f3-cos\">\n <div class=\"justificacio\"><strong>\ud83d\udcdd Justificaci\u00f3:<\/strong> L’usuari necessita saber quantes notificacions pendents t\u00e9 de cada categoria. Agrupa per tipus amb <strong>GROUP BY<\/strong> i <strong>HAVING<\/strong> per sols mostrar les que tenim pendents. Usem <strong>GROUP_CONCAT<\/strong> per llistar els t\u00edtols dins d’una sola fila.<\/div>\n <div class=\"tec-row\"><strong>T\u00e8cniques SQL:<\/strong> <span class=\"tec\">GROUP BY<\/span><span class=\"tec\">HAVING<\/span><span class=\"tec\">COUNT<\/span><span class=\"tec\">GROUP_CONCAT<\/span><span class=\"tec\">WHERE<\/span><\/div>\n <div class=\"bloc-sql\"><strong>\ud83d\udda5\ufe0f Consulta:<\/strong><pre>SELECT n.tipus,\n COUNT(*) AS total_no_llegides,\n GROUP_CONCAT(n.titol, ' | ') AS titols_pendents\n FROM notificacions n\n WHERE n.llegida = 0\n GROUP BY n.tipus\n HAVING COUNT(*) >= 1\n ORDER BY total_no_llegides DESC;<\/pre><\/div>\n <div class=\"bloc-evidencia\"><strong>\ud83d\udccb Resultats (4 files):<\/strong><table><tr><th>tipus<\/th><th>total_no_llegides<\/th><th>titols_pendents<\/th><\/tr><tr><td>torneig<\/td><td>2<\/td><td>Torneig FIFA en curs | Resultats del Torneig CS2<\/td><\/tr><tr><td>xat<\/td><td>1<\/td><td>Nou missatge en el xat<\/td><\/tr><tr><td>sistema<\/td><td>1<\/td><td>Actualitzaci\u00f3 del sistema<\/td><\/tr><tr><td>inscripcio<\/td><td>1<\/td><td>Inscripci\u00f3 confirmada<\/td><\/tr><\/table><\/div>\n <\/div>\n <\/div>\n \n\n<\/div>\n\n<footer>\n Documentaci\u00f3 generada autom\u00e0ticament amb dades reals de <strong>lan_party.db<\/strong> \u00b7 LAN Party Intranet \u00b7 Febrer 2026\n<\/footer>\n\n<\/body>\n<\/html>\n","protected":false},"excerpt":{"rendered":"<p>Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1577,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"saved_in_kubio":false,"_dseop_meta":"","_dseop_selected_keywords":"","footnotes":""},"class_list":["post-1683","page","type-page","status-publish","hentry"],"kubio_ai_page_context":{"short_desc":"","purpose":"general"},"yoast_head":"\n<title>Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683\" \/>\n<meta property=\"og:locale\" content=\"ca_ES\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes\" \/>\n<meta property=\"og:description\" content=\"Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683\" \/>\n<meta property=\"og:site_name\" content=\"Adrian Jaimes\" \/>\n<meta property=\"article:modified_time\" content=\"2026-02-03T13:00:00+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Temps estimat de lectura\" \/>\n\t<meta name=\"twitter:data1\" content=\"11 minuts\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\",\"name\":\"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#website\"},\"datePublished\":\"2026-01-16T08:19:25+00:00\",\"dateModified\":\"2026-02-03T13:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683#breadcrumb\"},\"inLanguage\":\"ca\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1683#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Inici\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"1r SMX\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1084\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Repte 1.3\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1491\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"7\ufe0f\u20e3 – \ud83d\udd10 – Intranet de la LAN Party\",\"item\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?page_id=1577\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"Generar consultes per extreure informaci\u00f3 de la BBDD\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#website\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\",\"name\":\"Adrian Jaimes\",\"description\":\"S\u00f3c estudiant d'inform\u00e0tica i aqu\u00ed podr\u00e0s veure el meu cam\u00ed acad\u00e8mic, els projectes que he realitzat i els coneixements que he adquirit durant la meva formaci\u00f3. Aquesta p\u00e0gina \u00e9s un reflex de la meva passi\u00f3 per la tecnologia i el meu comprom\u00eds per seguir aprenent i millorant cada dia. Explora els meus treballs i seguiment acad\u00e8mic!\",\"publisher\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ca\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#organization\",\"name\":\"Adrian Jaimes\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ca\",\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/cropped-cropped-cropped-width_550_009.webp\",\"contentUrl\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/cropped-cropped-cropped-width_550_009.webp\",\"width\":512,\"height\":512,\"caption\":\"Adrian Jaimes\"},\"image\":{\"@id\":\"https:\\\/\\\/ajaimes.inscastellbisbal.net\\\/#\\\/schema\\\/logo\\\/image\\\/\"}}]}<\/script>\n","yoast_head_json":{"title":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","og_locale":"ca_ES","og_type":"article","og_title":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","og_description":"Consultes SQL \u2013 LAN Party Intranet \ud83d\udcca Consultes SQL \u2013 Extracci\u00f3 d’Informaci\u00f3 Intranet LAN Party \u00b7 Base de Dades SQLite \u00b7 12 consultes amb evid\u00e8ncies Febrer 2026 \ud83c\udfaf Resum de t\u00e8cniques SQL implementades JOINsINNER JOIN, LEFT JOIN, fins a 4 JOINs en una sola consulta Agregaci\u00f3nCOUNT, SUM, MIN, MAX, COUNT(DISTINCT) FiltratGROUP BY, HAVING, WHERE amb […]","og_url":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","og_site_name":"Adrian Jaimes","article_modified_time":"2026-02-03T13:00:00+00:00","twitter_card":"summary_large_image","twitter_misc":{"Temps estimat de lectura":"11 minuts"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","url":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683","name":"Generar consultes per extreure informaci\u00f3 de la BBDD - Adrian Jaimes","isPartOf":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#website"},"datePublished":"2026-01-16T08:19:25+00:00","dateModified":"2026-02-03T13:00:00+00:00","breadcrumb":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683#breadcrumb"},"inLanguage":"ca","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1683#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Inici","item":"https:\/\/ajaimes.inscastellbisbal.net\/"},{"@type":"ListItem","position":2,"name":"1r SMX","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1084"},{"@type":"ListItem","position":3,"name":"Repte 1.3","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1491"},{"@type":"ListItem","position":4,"name":"7\ufe0f\u20e3 – \ud83d\udd10 – Intranet de la LAN Party","item":"https:\/\/ajaimes.inscastellbisbal.net\/?page_id=1577"},{"@type":"ListItem","position":5,"name":"Generar consultes per extreure informaci\u00f3 de la BBDD"}]},{"@type":"WebSite","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#website","url":"https:\/\/ajaimes.inscastellbisbal.net\/","name":"Adrian Jaimes","description":"S\u00f3c estudiant d'inform\u00e0tica i aqu\u00ed podr\u00e0s veure el meu cam\u00ed acad\u00e8mic, els projectes que he realitzat i els coneixements que he adquirit durant la meva formaci\u00f3. Aquesta p\u00e0gina \u00e9s un reflex de la meva passi\u00f3 per la tecnologia i el meu comprom\u00eds per seguir aprenent i millorant cada dia. Explora els meus treballs i seguiment acad\u00e8mic!","publisher":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ajaimes.inscastellbisbal.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ca"},{"@type":"Organization","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#organization","name":"Adrian Jaimes","url":"https:\/\/ajaimes.inscastellbisbal.net\/","logo":{"@type":"ImageObject","inLanguage":"ca","@id":"https:\/\/ajaimes.inscastellbisbal.net\/#\/schema\/logo\/image\/","url":"https:\/\/ajaimes.inscastellbisbal.net\/wp-content\/uploads\/2025\/12\/cropped-cropped-cropped-width_550_009.webp","contentUrl":"https:\/\/ajaimes.inscastellbisbal.net\/wp-content\/uploads\/2025\/12\/cropped-cropped-cropped-width_550_009.webp","width":512,"height":512,"caption":"Adrian Jaimes"},"image":{"@id":"https:\/\/ajaimes.inscastellbisbal.net\/#\/schema\/logo\/image\/"}}]}},"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1683"}],"version-history":[{"count":2,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683\/revisions"}],"predecessor-version":[{"id":1903,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1683\/revisions\/1903"}],"up":[{"embeddable":true,"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=\/wp\/v2\/pages\/1577"}],"wp:attachment":[{"href":"https:\/\/ajaimes.inscastellbisbal.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}