Salta el contingut

PR0304 — Consultes SQL avançades

Informació de la pràctica

Camp Valor
Codi PR0304
mòdul M0372 — Gestió de Bases de Dades
RA RA4 — Consultes SQL
Durada estimada 5 hores
Modalitat Individual
Lliurament Fitxer SQL + informe PDF a Moodle
Qualificació 10 punts (rúbrica adjunta)

Descripció

Aquesta pràctica consisteix a escriure i executar consultes SQL progressivament complexos sobre la base de dades dissenyada i implementada a les pràctiques PR0302 i PR0303. L'objectiu és demostrar domini del SELECT en totes les seves formes.

Configuració

Assegureu-vos que el vostre contenidor Docker amb PostgreSQL (PR0303) està en marxa i té dades de prova:

docker start postgres-nom-cognom
docker exec -it postgres-nom-cognom psql -U gbd_user -d gbd_nom_cognom

Si la vostra BD de pràctiques no te prou dades, podeu afegir-ne amb els INSERT de la pràctica PR0302 o amb dades generades.

Bloc 1 — Consultes bàsiques (2 punts)

Escriviu una consulta SQL per a cadascun dels requisits:

  1. Llista de tots els usuaris ordenats per cognom i nom (alfabèticament).
  2. Instal·lacions actives, mostrant nom, tipus i preu per hora, ordenades per preu de mes car a mes econòmic.
  3. Reserves de l'any actual, mostrant el nom de l'usuari, la instal·lació i la data.
  4. El nombre total d'usuaris registrats al sistema.
  5. Les 5 instal·lacions mes barates.
  6. Usuaris que és van registrar en els últims 90 dies.
  7. Instal·lacions que no són ni "tenis" ni "padel" (useu NOT IN).
  8. Reserves on la durada es de més de 2 hores (calculada com EXTRACT o DATEDIFF entre hora_fi i hora_inici).

Bloc 2 — Agrupació i funcions (2 punts)

  1. Nombre de reserves per usuari (mostra el nom de l'usuari i el comptador). Inclou usuaris amb 0 reserves.
  2. Ingressos totals per instal·lació (agrupat per instal·lació, calculant preu_hora * durada per a cada reserva). Ordena de mes a menys.
  3. Nombre de reserves per mes i any (any-mes, nombre de reserves). Ordena cronologicament.
  4. Instal·lacions amb una ocupació mitjana superior a 1 hora per reserva.
  5. L'usuari que ha gastat més diners en reserves.
  6. Resum estadístic del preu de les reserves: mínim, màxim, mitjana i desviació estàndard.

Bloc 3 — JOINs (3 punts)

  1. Totes les reserves amb el nom complet de l'usuari, el nom de la instal·lació, la data i la durada. Ordena per data descendent.
  2. Usuaris i la seva última reserva (data i instal·lació). Inclou usuaris sense cap reserva.
  3. Instal·lacions que mai s'han reservat.
  4. Reserves en estat "confirmada" amb les dades completes de l'usuari i de la instal·lació.
  5. Si la vostra BD te monitors: reserves amb monitor assignat, mostrant el nom del monitor i la seva especialitat.
  6. Usuaris que han reservat més d'una instal·lació diferent (amb JOINs i GROUP BY).

Bloc 4 — Subconsultes i CTEs (3 punts)

  1. Instal·lacions amb un preu per hora superior a la mitja de totes les instal·lacions del mateix tipus.
  2. Usuaris que han fet almenys una reserva al mes actual (usa EXISTS).
  3. Usuaris que mai han fet cap reserva (usa NOT EXISTS).
  4. Les 3 instal·lacions mes reservades, amb el total d'ingressos generats (usa una CTE o derived table).
  5. Per a cada usuari, la instal·lació que ha reservat mes sovint. (Subconsulta correlacionada avançada — opcional, +0.5 punts extres)
  6. Reescriviu la consulta del punt 1 d'aquest bloc usant una CTE en lloc d'una subconsulta.

Format de lliurament

Creeu un fitxer consultes_pr0304.sql amb: - Cada consulta numerada i amb un comentari que indica el requisit. - Les consultes han d'executar-se sense errors a PostgreSQL. - Incloeu al final de cada consulta un comentari amb el nombre de files retornades per les vostres dades de prova.

-- Bloc 1, Consulta 1: Llista d'usuaris ordenada alfabèticament
SELECT nom, cognoms, email
FROM usuaris
ORDER BY cognoms, nom;
-- Resultat: 12 files

Adjunteu un informe PDF amb captures de pantalla dels resultats de les 5 consultes que considereu mes representatives.

Validació de les consultes

després d'escriure cada consulta, analitzeu el resultat: té sentit? El nombre de files és l'esperat? Si la consulta retorna moltes mes files de les esperades, pot ser que us falti una condicio de JOIN o que tingueu un producte cartesià accidental.