Anàlisi i Consulta de Dades amb SQL
Institut Castellbisbal · Curs 2025–2027 · CFGM Sistemes Microinformàtics i Xarxes
Context del projecte LAN Party
Les bases de dades constitueixen un element essencial dins qualsevol sistema d’informació modern. En el projecte LAN Party, tota la informació relacionada amb usuaris, inscripcions, tornejos, classificacions, partides i comunicacions es troba organitzada en diferents taules relacionades entre si.
Per tal d’obtenir informació útil a partir d’aquestes dades, s’han desenvolupat diverses consultes SQL utilitzant tècniques avançades de filtratge, agrupació i relació de dades. Aquestes consultes permeten generar informes que faciliten la gestió de la plataforma i proporcionen informació rellevant per a la presa de decisions.
- Consultar informació de diferents taules
- Relacionar dades amb claus primàries i foranes
- Aplicar tècniques avançades de SQL
- Generar informes per gestionar la LAN Party
- Interpretar els resultats obtinguts
- Consolidar coneixements sobre BBDD relacionals
El sistema necessita identificar els diferents tipus d’usuaris existents dins de la plataforma i els permisos associats a cadascun d’ells. Aquesta informació és essencial per garantir una correcta administració dels accessos.
-- Usuaris actius amb el seu rol assignat
SELECT u.id_usuari, u.nom, u.cognom, u.email, r.nom_rol
FROM usuaris u
INNER JOIN rols r ON u.id_rol = r.id_rol
WHERE u.estat = 'actiu'
ORDER BY r.nom_rol, u.cognom;Els resultats mostren la relació existent entre els usuaris actius i els rols disponibles. Es pot observar que la major part dels comptes corresponen a participants dels tornejos, mentre que únicament existeixen perfils específics per a administració i visualització.
| id_usuari | nom | cognom | nom_rol | |
|---|---|---|---|---|
| 1 | Marc | Puig | marc@lan.cat | administrador |
| 2 | Laia | Ferrer | laia@lan.cat | jugador |
| 3 | Pau | Torres | pau@lan.cat | jugador |
| 4 | Anna | Vidal | anna@lan.cat | espectador |
Conèixer la distribució dels diferents rols permet analitzar l’estructura de la comunitat i verificar que existeix un equilibri adequat entre administradors, jugadors i espectadors.
-- Distribució d'usuaris agrupats per rol
SELECT r.nom_rol, COUNT(u.id_usuari) AS total_usuaris
FROM rols r
LEFT JOIN usuaris u ON r.id_rol = u.id_rol
GROUP BY r.nom_rol
ORDER BY total_usuaris DESC;La consulta mostra una clara predominància dels usuaris amb rol de jugador. Aquesta situació és coherent amb el funcionament d’una plataforma orientada principalment a la participació en competicions i esdeveniments.
| nom_rol | total_usuaris |
|---|---|
| jugador | 42 |
| espectador | 15 |
| administrador | 3 |
L’organització necessita controlar quins participants han completat correctament el procés d’inscripció i si han efectuat el pagament corresponent.
-- Estat de les inscripcions per participant
SELECT u.nom, u.cognom, i.data_inscripcio,
i.pagament_realitzat, i.assistencia_confirmada,
CASE
WHEN i.pagament_realitzat = 1 AND i.assistencia_confirmada = 1
THEN 'Completada'
WHEN i.pagament_realitzat = 1 THEN 'Pendent confirmació'
ELSE 'Pagament pendent'
END AS estat_general
FROM inscripcions i
INNER JOIN usuaris u ON i.id_usuari = u.id_usuari
ORDER BY estat_general, u.cognom;La informació obtinguda permet verificar l’estat de cada participant i detectar possibles incidències relacionades amb pagaments pendents o assistències no confirmades.
| nom | cognom | data_inscripcio | pagament | assistència | estat |
|---|---|---|---|---|---|
| Laia | Ferrer | 2025-03-01 | ✅ | ✅ | Completada |
| Pau | Torres | 2025-03-02 | ✅ | ❌ | Pendent confirmació |
| Jordi | Mas | 2025-03-03 | ❌ | ❌ | Pagament pendent |
Resulta necessari disposar d’indicadors que permetin mesurar el nivell de participació de cada esdeveniment organitzat.
-- Resum d'indicadors per LAN Party
SELECT lp.nom_event,
COUNT(i.id_inscripcio) AS total_inscrits,
SUM(i.pagament_realitzat) AS pagaments_ok,
SUM(i.assistencia_confirmada) AS assistencies,
ROUND(
SUM(i.assistencia_confirmada) * 100.0 / COUNT(i.id_inscripcio), 1
) AS perc_assistencia
FROM lan_parties lp
LEFT JOIN inscripcions i ON lp.id_event = i.id_event
GROUP BY lp.id_event, lp.nom_event
ORDER BY total_inscrits DESC;Els valors obtinguts permeten comparar els diferents esdeveniments i analitzar factors com el nombre d’inscrits, el percentatge d’assistència i la taxa de pagament.
| nom_event | total_inscrits | pagaments_ok | assistències | % assistència |
|---|---|---|---|---|
| LAN Party Primavera 2025 | 38 | 35 | 32 | 84.2% |
| LAN Party Tardor 2024 | 25 | 24 | 22 | 88.0% |
| LAN Party Estiu 2024 | 20 | 18 | 15 | 75.0% |
Centralitzar la informació dels tornejos facilita la seva gestió i permet consultar ràpidament les característiques principals de cada competició.
-- Llista completa de tornejos amb joc i event associat
SELECT t.id_torneig, t.nom_torneig, j.nom_joc,
lp.nom_event, t.estat,
t.data_inici, t.data_fi,
COUNT(p.id_participacio) AS num_participants
FROM tornejos t
INNER JOIN jocs j ON t.id_joc = j.id_joc
INNER JOIN lan_parties lp ON t.id_event = lp.id_event
LEFT JOIN participacions p ON t.id_torneig = p.id_torneig
GROUP BY t.id_torneig
ORDER BY t.data_inici DESC;Els resultats proporcionen una visió global dels tornejos disponibles, incloent el joc associat, l’esdeveniment on es desenvolupa i el seu estat actual.
Identificar els jocs amb major activitat ajuda a planificar futurs esdeveniments i adaptar l’oferta a les preferències dels participants.
-- Jocs ordenats per nombre de participacions
SELECT j.nom_joc, j.genere,
COUNT(DISTINCT t.id_torneig) AS num_tornejos,
COUNT(p.id_participacio) AS total_participacions
FROM jocs j
LEFT JOIN tornejos t ON j.id_joc = t.id_joc
LEFT JOIN participacions p ON t.id_torneig = p.id_torneig
GROUP BY j.id_joc, j.nom_joc, j.genere
HAVING total_participacions > 0
ORDER BY total_participacions DESC;L’anàlisi evidencia quins jocs generen un major nombre de participacions i permet detectar aquells que desperten més interès entre els usuaris.
| nom_joc | genere | num_tornejos | total_participacions |
|---|---|---|---|
| Valorant | FPS | 5 | 120 |
| League of Legends | MOBA | 4 | 95 |
| Counter-Strike 2 | FPS | 3 | 72 |
| Rocket League | Sport | 2 | 48 |
La plataforma necessita disposar d’un sistema de classificació que reflecteixi el rendiment acumulat dels jugadors.
-- Rànquing global de jugadors per puntuació acumulada
SELECT u.nom, u.cognom,
SUM(c.puntuacio) AS puntuacio_total,
COUNT(c.id_classificacio) AS tornejos_jugats,
MIN(c.posicio) AS millor_posicio
FROM classificacions c
INNER JOIN usuaris u ON c.id_usuari = u.id_usuari
GROUP BY c.id_usuari, u.nom, u.cognom
ORDER BY puntuacio_total DESC
LIMIT 10;Els resultats mostren els participants amb millor rendiment global, tenint en compte la puntuació acumulada en diferents competicions.
L’objectiu és identificar els usuaris amb millor rendiment competitiu dins de les partides registrades.
-- Top jugadors per nombre de victòries
SELECT u.nom, u.cognom,
COUNT(p.id_partida) AS total_victories
FROM partides p
INNER JOIN usuaris u ON p.id_guanyador = u.id_usuari
GROUP BY p.id_guanyador, u.nom, u.cognom
HAVING total_victories > 2
ORDER BY total_victories DESC;La consulta permet detectar els jugadors amb més victòries acumulades i comparar el seu rendiment respecte a la resta de participants.
Cada torneig necessita disposar de la seva pròpia classificació per mostrar els resultats finals dels participants.
-- Classificació detallada per torneig i posició
SELECT t.nom_torneig, c.posicio,
u.nom, u.cognom, c.puntuacio,
j.nom_joc
FROM classificacions c
INNER JOIN tornejos t ON c.id_torneig = t.id_torneig
INNER JOIN usuaris u ON c.id_usuari = u.id_usuari
INNER JOIN jocs j ON t.id_joc = j.id_joc
ORDER BY t.nom_torneig, c.posicio;Els resultats mostren les posicions finals obtingudes pels diferents jugadors dins de cada competició.
Disposar d’un registre detallat de les partides facilita el seguiment dels resultats i la revisió de les competicions disputades.
-- Historial complet de partides amb participants i marcadors
SELECT p.id_partida, t.nom_torneig,
u1.nom AS jugador_1, u2.nom AS jugador_2,
p.marcador_j1, p.marcador_j2,
COALESCE(ug.nom, 'Empat') AS guanyador,
p.data_partida
FROM partides p
INNER JOIN tornejos t ON p.id_torneig = t.id_torneig
INNER JOIN usuaris u1 ON p.id_jugador1 = u1.id_usuari
INNER JOIN usuaris u2 ON p.id_jugador2 = u2.id_usuari
LEFT JOIN usuaris ug ON p.id_guanyador = ug.id_usuari
ORDER BY p.data_partida DESC;La informació obtinguda permet consultar participants, marcadors i guanyadors de manera estructurada.
La comunicació entre participants és una funcionalitat important dins de la plataforma.
-- Missatges enviats amb emissor, receptor i event relacionat
SELECT ue.nom AS emissor,
ur.nom AS receptor,
m.contingut, m.data_enviament,
COALESCE(lp.nom_event, 'Sense event') AS event
FROM missatges m
INNER JOIN usuaris ue ON m.id_emissor = ue.id_usuari
INNER JOIN usuaris ur ON m.id_receptor = ur.id_usuari
LEFT JOIN lan_parties lp ON m.id_event = lp.id_event
ORDER BY m.data_enviament DESC;Els resultats mostren els missatges registrats i permeten visualitzar l’activitat comunicativa associada als diferents esdeveniments.
Els usuaris necessiten conèixer les notificacions que encara no han estat revisades.
-- Notificacions no llegides agrupades per categoria
SELECT u.nom, u.cognom,
n.categoria,
COUNT(n.id_notificacio) AS pendents,
MAX(n.data_creacio) AS ultima_notificacio
FROM notificacions n
INNER JOIN usuaris u ON n.id_usuari = u.id_usuari
WHERE n.llegida = 0
GROUP BY n.id_usuari, u.nom, u.cognom, n.categoria
HAVING pendents > 0
ORDER BY pendents DESC;La consulta permet identificar les notificacions pendents i agrupar-les segons la seva categoria per facilitar-ne la gestió.
| nom | cognom | categoria | pendents | última notificació |
|---|---|---|---|---|
| Laia | Ferrer | torneig | 5 | 2025-03-15 10:22 |
| Pau | Torres | missatge | 3 | 2025-03-14 18:05 |
| Jordi | Mas | inscripció | 1 | 2025-03-13 09:00 |
Síntesi del repte SQL — LAN Party
- Tècniques avançades aplicades: S’han utilitzat JOINs múltiples, funcions d’agregació, subconsultes i operadors lògics per extreure informació complexa.
- Informació rellevant generada: Les 12 consultes han permès obtenir informes de gestió d’usuaris, inscripcions, tornejos, classificacions, partides i comunicacions.
- Coneixements consolidats: La realització d’aquest repte ha permès reforçar la comprensió del funcionament de les bases de dades relacionals en entorns reals.
- BBDD com a eina de decisió: Les consultes demostren la importància de les bases de dades per transformar dades emmagatzemades en coneixement útil per a la presa de decisions.
- Connexió amb el perfil BackEnd: Aquest domini de SQL és fonamental per al futur professional en CFGS DAW/DAM i desenvolupament BackEnd.