SQL LAN Party – Araceli Saldaña

🗄️ Bases de Dades · SQL · LAN Party

Anàlisi i Consulta de Dades amb SQL

Institut Castellbisbal · Curs 2025–2027 · CFGM Sistemes Microinformàtics i Xarxes

📋 Introducció

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.

🎯 Objectius i Tècniques SQL aplicades

🎯 Objectius

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

⚙️ Tècniques SQL aplicades

Funcions i operadors

Unions i filtres

INNER JOIN LEFT JOIN WHERE HAVING ORDER BY

Funcions d’agregació

COUNT() SUM() MIN() MAX() ROUND()

Lògica i condicions

CASE WHEN COALESCE() GROUP BY

Avançades

Subconsultes GROUP_CONCAT() COUNT(DISTINCT)

🗄️ Consultes SQL — LAN Party

CONSULTA 1 Usuaris i rol assignat

📌 Necessitat funcional

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.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN WHERE ORDER BY

📊 Anàlisi dels resultats

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	email	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

CONSULTA 2 Distribució d’usuaris per rol

📌 Necessitat funcional

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.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

LEFT JOIN COUNT() GROUP BY ORDER BY DESC

📊 Anàlisi dels resultats

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

CONSULTA 3 Estat de les inscripcions

📌 Necessitat funcional

L’organització necessita controlar quins participants han completat correctament el procés d’inscripció i si han efectuat el pagament corresponent.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN CASE WHEN ORDER BY

📊 Anàlisi dels resultats

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

CONSULTA 4 Resum de participació per LAN Party

📌 Necessitat funcional

Resulta necessari disposar d’indicadors que permetin mesurar el nivell de participació de cada esdeveniment organitzat.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

LEFT JOIN COUNT() SUM() ROUND() GROUP BY

📊 Anàlisi dels resultats

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%

CONSULTA 5 Informació dels tornejos

📌 Necessitat funcional

Centralitzar la informació dels tornejos facilita la seva gestió i permet consultar ràpidament les característiques principals de cada competició.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN LEFT JOIN COUNT() GROUP BY

📊 Anàlisi dels resultats

Els resultats proporcionen una visió global dels tornejos disponibles, incloent el joc associat, l’esdeveniment on es desenvolupa i el seu estat actual.

CONSULTA 6 Popularitat dels jocs

📌 Necessitat funcional

Identificar els jocs amb major activitat ajuda a planificar futurs esdeveniments i adaptar l’oferta a les preferències dels participants.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

LEFT JOIN COUNT(DISTINCT) COUNT() GROUP BY HAVING

📊 Anàlisi dels resultats

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

CONSULTA 7 Classificació general

📌 Necessitat funcional

La plataforma necessita disposar d’un sistema de classificació que reflecteixi el rendiment acumulat dels jugadors.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN SUM() COUNT() MIN() GROUP BY LIMIT

📊 Anàlisi dels resultats

Els resultats mostren els participants amb millor rendiment global, tenint en compte la puntuació acumulada en diferents competicions.

CONSULTA 8 Jugadors amb més victòries

📌 Necessitat funcional

L’objectiu és identificar els usuaris amb millor rendiment competitiu dins de les partides registrades.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN COUNT() GROUP BY HAVING ORDER BY DESC

📊 Anàlisi dels resultats

La consulta permet detectar els jugadors amb més victòries acumulades i comparar el seu rendiment respecte a la resta de participants.

CONSULTA 9 Classificació per torneig

📌 Necessitat funcional

Cada torneig necessita disposar de la seva pròpia classificació per mostrar els resultats finals dels participants.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN (×3) ORDER BY múltiple

📊 Anàlisi dels resultats

Els resultats mostren les posicions finals obtingudes pels diferents jugadors dins de cada competició.

CONSULTA 10 Historial de partides

📌 Necessitat funcional

Disposar d’un registre detallat de les partides facilita el seguiment dels resultats i la revisió de les competicions disputades.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN (×4) LEFT JOIN COALESCE() Self-join (usuaris)

📊 Anàlisi dels resultats

La informació obtinguda permet consultar participants, marcadors i guanyadors de manera estructurada.

CONSULTA 11 Sistema de missatgeria

📌 Necessitat funcional

La comunicació entre participants és una funcionalitat important dins de la plataforma.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN LEFT JOIN COALESCE() Self-join (usuaris)

📊 Anàlisi dels resultats

Els resultats mostren els missatges registrats i permeten visualitzar l’activitat comunicativa associada als diferents esdeveniments.

CONSULTA 12 Notificacions pendents

📌 Necessitat funcional

Els usuaris necessiten conèixer les notificacions que encara no han estat revisades.

💻 Consulta SQL

-- 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;

⚙️ Tècniques aplicades

INNER JOIN WHERE COUNT() MAX() GROUP BY HAVING

📊 Anàlisi dels resultats

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

✅ Conclusions

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.

📊 Resum de tècniques — 12 consultes

Consultes SQL

Taules relacionades

Tècniques SQL

Funcions agregació

Generar consultes per extreure informació de la BBDD

Anàlisi i Consulta de Dades amb SQL

Context del projecte LAN Party

Síntesi del repte SQL — LAN Party