UD06 · Consultes sobre una taula i funcions

Resum del tema

Consultar és l’operació més freqüent en qualsevol base de dades. En esta unitat aprendràs la sentència SELECT aplicada a una sola taula: triar columnes (projecció), filtrar files (selecció), ordenar, eliminar duplicats, tractar els valors nuls i transformar les dades amb les funcions que proporciona Oracle. A la UD07 combinarem diverses taules i agruparem resultats.

Tots els exemples s’executen sobre l’esquema de referència EDUGEST carregat amb els scripts del projecte, i mostren el resultat que has d’obtindre.

Temporalització

La unitat ocupa 23 hores d’aula (12 de teoria i 11 de pràctica). És la unitat amb més hores del curs: ací s’adquirix la destresa amb SELECT que s’utilitzarà fins al final del mòdul.

Les consultes s’aprenen escrivint-les. Abans d’executar cadascuna, prediu quantes files tornarà i per què; després comprova-ho. Els laboratoris interactius simulen el comportament d’Oracle sobre les dades reals d’EduGest, però no substituïxen un SGBD.

Objectius d’aprenentatge

En acabar esta unitat seràs capaç de:

• Identificar les eines i les clàusules de la sentència SELECT.
• Seleccionar columnes, calcular expressions i usar àlies.
• Filtrar files amb operadors de comparació, lògics, BETWEEN, IN, LIKE i IS NULL.
• Ordenar resultats i limitar el nombre de files.
• Raonar amb la lògica de tres valors que introduïx NULL.
• Usar funcions de text, numèriques, de data, de conversió i condicionals d’Oracle.

Prepara la teua sessió perquè les dates i els números es vegen com en estos apunts. Executa en connectar-te:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

El format de presentació no canvia les dades guardades, només com es mostren.

1. Eines i sentències per a consultar

Per a consultar una base de dades Oracle pots usar:

La sentència per a consultar és SELECT. La seua forma general, amb totes les clàusules que veuràs en esta unitat i en la següent, és:

SELECT    [DISTINCT] columnes o expressions     -- 5. quines columnes
FROM      taula(es)                              -- 1. d'on
WHERE     condició sobre les files               -- 2. quines files
GROUP BY  columnes d'agrupació                   -- 3. com agrupar          (UD07)
HAVING    condició sobre els grups               -- 4. quins grups          (UD07)
ORDER BY  criteris d'ordenació                   -- 6. en quin ordre
FETCH FIRST n ROWS ONLY;                         -- 7. quantes files

L’ordre en què s’escriu una consulta no és l’ordre en què s’avalua. El SGBD processa primer FROM, després WHERE, GROUP BY, HAVING, SELECT i finalment ORDER BY. Per això un àlies definit en el SELECT es pot usar en ORDER BY però no en WHERE.

2. Projecció: triar columnes

2.1 Totes les columnes o només algunes

SELECT * FROM ciclo;

4 files

* és útil per a explorar, però en una aplicació o un informe escriu sempre les columnes que necessites: el resultat és més clar, transferix menys dades i no canvia si algú afig una columna a la taula.

SELECT codigo, nombre, horas
FROM   modulo
WHERE  cod_ciclo = 'DAM';

2.2 Expressions i àlies

En el SELECT es poden calcular expressions. Un àlies dóna nom a la columna resultant; si conté espais o majúscules, va entre cometes dobles.

SELECT codigo,
       nombre,
       horas,
       horas / 32            AS horas_semana,
       ROUND(horas / 32, 1)  AS "Horas/semana"
FROM   modulo
WHERE  cod_ciclo = 'DAW' AND curso = 2;

4 files

2.3 Concatenació

L’operador || unix cadenes:

SELECT apellidos || ', ' || nombre AS alumno, localidad
FROM   alumno
WHERE  cod_grupo = '2DAW'
ORDER  BY alumno;

5 files

2.4 Eliminar duplicats amb DISTINCT

SELECT DISTINCT localidad FROM alumno ORDER BY localidad;

6 files

DISTINCT s’aplica a la fila completa del resultat: SELECT DISTINCT localidad, cod_grupo torna les combinacions distintes de les dues columnes.

2.5 La taula DUAL

DUAL és una taula especial d’Oracle amb una sola fila. Servix per a avaluar expressions que no depenen de cap taula:

SELECT SYSDATE, 7 * 24 AS horas_semana, UPPER('edugest') FROM dual;

Des d’Oracle 23ai la clàusula FROM és opcional en estos casos: SELECT SYSDATE; també funciona. En versions anteriors (i en l’examen, si uses 19c) escriu FROM dual.

3. Ordenació: ORDER BY

SELECT codigo, nombre, cod_ciclo, horas
FROM   modulo
WHERE  horas > 150
ORDER  BY horas DESC, codigo;

10 files

• ASC (per defecte) ordena de menor a major i DESC de major a menor.
• Amb diverses columnes, la segona només decidix quan hi ha empat en la primera.
• Es pot ordenar per un àlies o per una expressió.
• En Oracle, els NULL van al final en ordre ascendent i al principi en descendent. Pots canviar-ho amb NULLS FIRST o NULLS LAST.

Sense ORDER BY, l’ordre de les files no està garantit. Encara que hui isquen ordenades per la clau primària, demà poden eixir en un altre ordre.

A més, l’ordenació de textos amb accents depén del paràmetre de sessió NLS_SORT. Amb el valor per defecte (BINARY), «Álex» s’ordena després de «Zoe». Amb ALTER SESSION SET NLS_SORT = SPANISH; s’ordena com en un diccionari. Si els teus resultats apareixen en un altre ordre que en els apunts, revisa este paràmetre.

4. Selecció: filtrar files amb WHERE

4.1 Operadors de comparació

SELECT nombre, apellidos, localidad
FROM   alumno
WHERE  localidad = 'Mutxamel'
ORDER  BY apellidos;

6 files

Les comparacions de text distingeixen majúscules i minúscules: localidad = 'mutxamel' no torna res. Per a comparar sense distingir-les usa UPPER(localidad) = 'MUTXAMEL'.

4.2 Patrons amb LIKE

SELECT nombre, apellidos
FROM   alumno
WHERE  apellidos LIKE 'B%'
ORDER  BY apellidos;

4 files

Per a buscar un % o un _ literals s’usa ESCAPE: email LIKE '%\_%' ESCAPE '\' busca correus que continguen un guió baix.

4.3 Operadors lògics i precedència

NOT s’avalua abans que AND, i AND abans que OR. Usa parèntesis sempre que barreges AND i OR.

-- Matrícules suspeses dels mòduls 2 o 8
SELECT id_matricula, id_alumno, nota_final
FROM   matricula
WHERE  nota_final < 5
AND    id_modulo IN (2, 8)
ORDER  BY id_matricula;

3 files

Laboratori de SELECT

Construïx una consulta sobre la taula ALUMNO triant columnes, condicions i ordre. Compara el resultat amb el que vas predir.

WHERE a OR b AND c no s’avalua d’esquerra a dreta: AND té major precedència que OR. Usa parèntesis sempre que barreges ambdós.

5. El valor NULL

NULL significa «valor desconegut o no aplicable». No és un zero ni una cadena buida: és l’absència de valor.

5.1 Lògica de tres valors

Qualsevol comparació amb NULL dóna com a resultat desconegut (UNKNOWN), i WHERE només torna les files la condició de les quals és vertadera.

Conseqüències pràctiques:

SELECT COUNT(*) FROM alumno WHERE cod_grupo = NULL;      -- 0 files: sempre!
SELECT COUNT(*) FROM alumno WHERE cod_grupo IS NULL;     -- 3 files
SELECT COUNT(*) FROM alumno WHERE cod_grupo <> '1DAM';   -- 22: els 3 sense grup NO ixen

SELECT nia, nombre, apellidos
FROM   alumno
WHERE  cod_grupo IS NULL;

3 files

Simulador de lògica de tres valors

Combina TRUE, FALSE i UNKNOWN amb AND, OR i NOT, i comprova quines files conserva WHERE.

5.2 Funcions per a tractar els nuls

SELECT id_alumno, nombre, NVL(email, '(sin correo)') AS email
FROM   alumno
WHERE  email IS NULL OR telefono IS NULL
ORDER  BY id_alumno;

8 files

Qualsevol operació aritmètica amb NULL dóna NULL: nota_final + 1 és NULL si no hi ha nota. I en Oracle, '' és NULL. Comprova-ho amb SELECT NVL('', 'era nulo') FROM dual;.

6. Limitar el nombre de files

-- Les cinc millors notes (amb desempat per id_matricula)
SELECT id_matricula, id_alumno, id_modulo, nota_final
FROM   matricula
WHERE  nota_final IS NOT NULL
ORDER  BY nota_final DESC, id_matricula
FETCH FIRST 5 ROWS ONLY;

5 files

FETCH FIRST és SQL estàndard i està en Oracle des de la versió 12c. En codi antic veuràs la pseudocolumna ROWNUM (WHERE ROWNUM <= 5), que s’avalua abans de l’ORDER BY i produïx errors si no s’usa amb una subconsulta. MySQL i PostgreSQL usen LIMIT 5.

7. Funcions de fila

Les funcions de fila s’apliquen a cada fila i tornen un valor per fila. Formen part de les funcions que proporciona el sistema gestor (RA5.e). Les funcions de grup (COUNT, SUM, AVG…), que resumixen diverses files, s’estudien a la UD07.

7.1 Funcions de text

-- Usuari del correu del professorat (allò que va abans de la @)
SELECT nombre, email, SUBSTR(email, 1, INSTR(email, '@') - 1) AS usuario
FROM   profesor
WHERE  id_departamento = 2;

SELECT UPPER(apellidos) || ', ' || nombre AS alumno, LENGTH(nombre) AS letras
FROM   alumno
WHERE  cod_grupo = '1ASIR'
ORDER  BY apellidos;

7.2 Funcions numèriques

-- Notes arredonides a l'enter, com apareixen al butlletí
SELECT id_matricula, nota_final, ROUND(nota_final) AS nota_boletin, TRUNC(nota_final) AS nota_truncada
FROM   matricula
WHERE  id_alumno = 1
ORDER  BY id_matricula;

Fixa’t en la matrícula 10002: amb ROUND, un 4,75 apareix com a 5 en el butlletí, però el mòdul està suspés. Les decisions (aprovar o no) es prenen sempre sobre el valor real, mai sobre el valor formatat.

7.3 Funcions de data

En Oracle, restar dues dates dóna el nombre de dies entre elles, i sumar un número a una data afig dies.

-- Edat de l'alumnat de 2DAW a data 6 d'octubre de 2026
SELECT id_alumno, nombre, fecha_nacimiento,
       TRUNC(MONTHS_BETWEEN(DATE '2026-10-06', fecha_nacimiento) / 12) AS edad
FROM   alumno
WHERE  cod_grupo = '2DAW'
ORDER  BY fecha_nacimiento;

En una aplicació real s’usaria SYSDATE en lloc de DATE '2026-10-06'. Ací es fixa la data perquè el resultat no canvie segons el dia en què executes la consulta. Els literals de data DATE 'AAAA-MM-DD' són SQL estàndard i no depenen de la configuració de la sessió: usa’ls sempre en lloc de cadenes com '15/09/2025'.

Comparar dates que tenen hora. Com que DATE guarda l’hora, per a buscar «el del dia 15» compara amb un interval semiobert:

SELECT * FROM matricula
WHERE  fecha_matricula >= DATE '2025-09-15'
AND    fecha_matricula <  DATE '2025-09-16';

7.4 Funcions de conversió

Elements de format de data més usats: DD (dia), MM (mes), MON/MONTH (nom del mes), YYYY (any), HH24 (hora), MI (minuts), SS (segons), D (dia de la setmana), Q (trimestre).

SELECT nombre, apellidos,
       TO_CHAR(fecha_alta, 'DD/MM/YYYY') AS alta,
       EXTRACT(YEAR FROM DATE '2026-10-06') - EXTRACT(YEAR FROM fecha_alta) AS cursos_aprox
FROM   profesor
WHERE  fecha_alta < DATE '2015-01-01'
ORDER  BY fecha_alta;

Evita les conversions implícites: WHERE fecha_alta < '01/01/2015' funciona o falla segons el NLS_DATE_FORMAT de cada sessió, i WHERE nia = 10450037 (sense cometes) obliga Oracle a convertir totes les files de la columna, la qual cosa impedix usar el seu índex.

7.5 Funcions condicionals: CASE i DECODE

CASE és estàndard SQL i permet triar un valor segons condicions.

SELECT id_matricula, id_modulo, nota_final,
       CASE
           WHEN nota_final IS NULL THEN 'Sin calificar'
           WHEN nota_final < 5     THEN 'Insuficiente'
           WHEN nota_final < 6     THEN 'Suficiente'
           WHEN nota_final < 7     THEN 'Bien'
           WHEN nota_final < 9     THEN 'Notable'
           ELSE                         'Sobresaliente'
       END AS calificacion
FROM   matricula
WHERE  id_alumno = 1
ORDER  BY id_modulo;

5 files

Les condicions s’avaluen en ordre i s’usa la primera que es complix. Per això basta amb nota_final < 6 en la segona branca: si fora menor que 5, ja s’hauria quedat en la primera.

CASE simple (compara una expressió amb valors) i el seu equivalent propi d’Oracle, DECODE:

SELECT cod_grupo,
       CASE turno WHEN 'M' THEN 'Mañana' WHEN 'T' THEN 'Tarde' END AS turno,
       DECODE(turno, 'M', 'Mañana', 'T', 'Tarde', 'Desconocido') AS turno_decode
FROM   grupo
ORDER  BY cod_grupo;

6 files

7.6 Expressions regulars

Oracle inclou funcions d’expressions regulars per a cerques més potents que LIKE: REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_COUNT.

-- Professorat amb DNI que comença per 2 i acaba en C o E
SELECT nombre, dni FROM profesor WHERE REGEXP_LIKE(dni, '^2[0-9]{7}[CE]$') ORDER BY nombre;

8. Errors freqüents

9. Bones pràctiques

• Escriu les paraules reservades en majúscules i alinea les clàusules: la consulta es llig millor.
• Anomena explícitament les columnes; usa * només per a explorar.
• Usa àlies clars per a les expressions calculades.
• Usa literals DATE 'AAAA-MM-DD' i evita les conversions implícites.
• Comenta les consultes que lliures: quina pregunta responen i quines decisions has pres.

10. Resum

• SELECT tria columnes (projecció), WHERE filtra files (selecció) i ORDER BY ordena.
• El SGBD avalua FROM → WHERE → SELECT → ORDER BY.
• NULL introduïx una lògica de tres valors: es compara amb IS NULL i es tracta amb NVL, COALESCE o NVL2.
• FETCH FIRST n ROWS ONLY limita el nombre de files.
• Oracle proporciona funcions de text, numèriques, de data, de conversió i condicionals (CASE, DECODE).

11. Autoavaluació

Referències

• Oracle AI Database 26ai: SQL Language Reference, SELECT.
• Oracle AI Database 26ai: SQL Language Reference, Single-Row Functions.
• Oracle AI Database 26ai: Format Models.