4. DQL - Consultes

4.1. Consultes simples

4.1.1. Introducció

La sentència SELECT, que s'utilitza per a expressar consultes SQL, és la més potent i complexa de les sentències de SQL. Este capítol discutix les consultes SQL més simples: aquelles que recuperen dades d'una única taula de la BD.

La sentència SELECT consta de 6 clàusules:

La clàusula SELECT llista els elements a recuperar. Poden ser columnes de la BD o expressions a partir de les columnes, etc.
La clàusula FROM llista les taules on estan els elements a recuperar per la consulta. Les consultes que extrauen les seues dades d'una única taula es descriuen en este capítol. Les de més de 2 taules les vorem més avant.
La clàusula WHERE diu a SQL que incloga només certes files (registres) de dades en els resultats de la consulta. S'utilitza una condició de recerca per a especificar les files desitjades.
La clàusula GROUP BY especifica una consulta sumària. En comptes de produir una fila de resultats per cada fila de dades, una consulta sumària agrupa totes les files semblants i després produïx una fila de resultats per cada grup.
La clàusula HAVING diu a SQL que incloga només certs grups produïts per la clàusula GROUP BY en els resultats de la consulta. Igual que la clàusula WHERE, utilitza una condició de recerca per a especificar els grups desitjats.
La clàusula ORDER BY ordena els resultats de la consulta en base a les dades d'una o més columnes. Si s'omet, els resultats de la consulta no apareixen ordenats.

4.1.2. La clàusula SELECT

Esta clàusula consta de la paraula SELECT, seguida per la llista d'elements que volem obtindre (separats per comes). Cada element de la llista pot ser: un nom de columna, una constant, una expressió, el símbol * o funcions internes:

a) Un nom de columna

SQL
SELECT cod AS 'CODI_ART', preu
    FROM articles

CODI_ART	preu
Tomaques	2.50
Creïlles	1.25
Pomes	1.90
Cebes	1.50

L'àlies (AS) és el nom que volem que aparega en la visualització de les dades en compte del nom de la columna.

b) Una constant

Constants numèriquesConstants de cadenaConstants de data i horaConstants simbòliques

SQL
SELECT codi, preu, 21 AS 'IVA'
    FROM articles

codi	preu	IVA
Tomaques	2.50	21
Creïlles	1.25	21
Pomes	1.90	21
Cebes	1.50	21

Van sense cometes. El separador decimal és el punt. Poden portar els signes + o -:

Text Only
1	`21 -375 2000.00 +497500.8778`

SQL
SELECT codi, "del Mareny" AS 'origen', preu
    FROM articles

codi	origen	preu
Tomaques	del Mareny	2.50
Creïlles	del Mareny	1.25
Pomes	del Mareny	1.90
Cebes	del Mareny	1.50

Entre cometes simples o dobles.

Si volem posar una cadena que conté un apòstrof, ho tancarem entre cometes dobles. Si volem posar dins unes cometes dobles, ho posarem entre cometes simples:

Text Only
1	`'Del "Marenyet" ' "L'Alcúdia"`

Entre cometes simples o dobles. S'indica any, mes i dia separats per la barra (/), guió (-) o dos punts (:). No té molt de sentit posar-les en la clàusula SELECT però sí en la WHERE:

SQL
SELECT codi, preu, data
    FROM articles
    WHERE data > '2015/7/8';  -- o bé: "2015-7-8" o '2015:07:08'

codi	preu	data
Tomaques	2.50	2015/7/9
Creïlles	1.25	2015/9/4
Pomes	1.90	2015/9/2
Cebes	1.50	2016/1/9

Investiga

Esbrina si la data es pot posar en altre format, i la forma de posar les hores.

SQL inclou constants simbòliques especials que tornen valors de dades mantingudes pel propi SGBD. Algunes són: CURRENT_DATE (per a la data), CURRENT_TIME (per a l'hora), CURRENT_TIMESTAMP (per a data i hora). No requerixen la clàusula FROM.

SQL
SELECT CURRENT_DATE, CURRENT_TIME;

CURRENT_DATE	CURRENT_TIME
2016-01-25	19:32:34

c) Una expressió

Les expressions s'utilitzen en el llenguatge SQL per a calcular valors derivats dels que hi ha en la BD (i per a realitzar recerques en la BD). Els operadors matemàtics són els normals: suma ( + ), resta ( – ), multiplicació ( * ) i divisió ( / ).

SQL
SELECT quant, preu, (quant * preu) * 1.21
    FROM vendes
    WHERE (quant * preu) > 100

quant	preu	(quant * preu) * 1.21
3	3.00	10.89
2	4.00	9.68
3	2.00	7.26
4	5.00	24.20

d) El símbol *

Serveix per a mostrar totes les columnes d'una taula:

SQL
SELECT *
    FROM autors;

codi	nom	pais
1	Umberto Eco	Itàlia
3	Camilo J. Cela	Espanya
4	Homer	Grècia
5	M. Cervantes	Espanya

e) Funcions internes

MySQL inclou algunes funcions internes. Per exemple:

Sobre datesSobre cadenes

SQL
SELECT data_naix, DAY(data_naix), MONTH(data_naix),
       YEAR(data_naix), DAYNAME(data_naix)
    FROM alumnes
    WHERE YEAR(data_naix) > 1960;

data_naix	DAY(data_naix)	MONTH(data_naix)	YEAR(data_naix)	DAYNAME(data_naix)
1970-12-30	30	12	1970	Wednesday
1975-11-03	3	11	1975	Monday
1985-10-18	18	10	1985	Friday

SQL
SELECT nom, CHAR_LENGTH(nom) AS longitud,
       CONCAT(cog1, " ", cog, ", ", nom) AS 'Nom complet',
       LOWER(nom) AS Minúscules, UPPER(nom) AS Majúscules
    FROM alumnes

nom	longitud	Nom complet	Minúscules	Majúscules
Pep	3	Garcia Garcia, Pep	pep	PEP
Jaume	5	Peris Martí, Jaume	jaume	JAUME
Sebastià	8	Pi Garcia, Sebastià	sebastià	SEBASTIÀ

Investiga

Mira en Internet altres funcions per a treballar en les dates, cadenes, etc.

Exercicis. Funcions internes (BD empresa)

1) Per a fer els següents exercicis de funcions, primer afig la data de naixement (d_naix) a cada empleat (amb ALTER TABLE). Després, posa dates de naixement als empleats existents (amb diferents UPDATE).

2) Mostra totes les dades de la taula d'empleats i, a més, de cadascun d'ells: la longitud del seu nom, la comissió en tant per 1 (en la taula està com a %), el dia de la setmana en què nasqueren, la data de naixement expressat amb format: "19 del 3 del 1970".

3) Mostra els noms dels departaments en minúscula i en majúscula.

f) Funcions d'agregat

Les funcions que hem vist mostren un resultat per cada fila de la taula. Les funcions d'agregat que anem a vore mostren un únic resultat per a tota la taula.

SQL
SELECT MAX(preu), MIN(preu), AVG(preu), SUM(preu), COUNT(*), COUNT(preu)
    FROM llibres

MAX(preu)	MIN(preu)	AVG(preu)	SUM(preu)	COUNT(*)	COUNT(preu)
26.00	10.50	17.00	136.0	8	6

Explicació:

MAX(preu): Calcula el màxim preu d'entre totes les files (entre tots els llibres).
MIN(preu): Calcula el mínim preu.
AVG(preu): Calcula el preu mig (entre aquells que el preu no és NULL).
SUM(preu): Calcula la suma de tots els preus.
COUNT(*): Compta quantes files (quants llibres en este cas) hi ha.
COUNT(preu): Compta quantes files (quants llibres) tenen el preu no NULL.

Notes

Si en la select hi haguera un where, els càlculs s'aplicarien només tenint en compte les files seleccionades en eixe where.

També podem tindre operadors dins de les funcions d'agregat:

SQL
SELECT SUM(preu_venda - preu_cost)
    FROM articles;

I també podem operar amb les funcions d'agregat:

SQL
SELECT MAX(preu_venda) - MIN(preu_cost)
    FROM articles;

Exercicis. Funcions d'agregat (BD lliga1213)

A partir d'ara treballarem amb la base de dades lliga1213. Per a crear-la (amb totes les seues taules i registres) executa l'script lliga1213.sql que trobaràs al Moodle. Pega una miradeta per les taules i columnes per a familiaritzar-te amb eixa BD i llig el fitxer adjunt que explica el contingut de la base de dades. En acabant, resol els següents exercicis. T'aconselle que tingues davant l'esquema de les taules amb les respectives columnes del fitxer que explica la base de dades.

Nota

Els 2 primers no són de funcions d'agregat però ens serviran per a entrar en contacte amb esta base de dades.

Mostra tota l'estadística d'aquells golejadors que han marcat algun penal.
De cada partit mostra la jornada, l'equip de casa i el de fora, els gols de cada equip, el total de gols, la possessió de l'equip de casa i la de l'equip de fora. Posa noms coherents a les columnes.
Gols marcats pel pitxitxi (només els gols; el nom del jugador no).
Mitja de gols per partit en tota la lliga.
Mostra els següents sous: el més car, el més barat i la mitjana.
Total de gols marcats en tota la lliga.

Nota

Esta informació està en 2 taules: en els resultats dels partits i en els gols marcats per cada golejador. No quadra perquè hi ha gols que no els han marcat els golejadors (sinó porters, defenses, en pròpia porta...).

a) Total de gols marcats en tots els partits.

b) Total de gols marcats per tots els golejadors.
Mostra quants partits hi ha, quants s'han jugat i quants no s'han jugat.
Mostra la diferència entre el major pressupost i el menor.
Mostra la data més antiga i la més recent de les jornades.

4.1.3. La clàusula FROM

Esta clàusula ja l'hem utilitzada. Serveix per a dir de quina taula anem a mostrar les dades de la sentència SELECT.

Ara bé, fins ara només hem fet consultes que s'obtenen d'una única taula, però més endavant vorem com fer sentències per a obtindre informació de més d'una taula. En eixos casos després de la paraula clau FROM, posarem totes les taules necessàries separades per comes.

Ací tenim un exemple de com es faria, però no vos preocupeu per entendre-ho. Ja ho vorem més endavant:

SQL
SELECT proveidors.nom AS "Nom proveïdor", articles.descripcio AS "Descr. article"
    FROM proveidors, proveir, articles
    WHERE proveidors.codi = proveir.pro
      AND proveir.art = articles.codi

Nom proveïdor	Descr. article
ANECOOP	tomaques
COPSEMAR	arròs
UNIANA	taronges

La clàusula FROM és obligatòria en les sentències SELECT, llevat de casos molt especials com, per exemple, mostrar el valor de variables del sistema o alguna operació matemàtica:

SQL
SELECT SYSDATE();

SELECT 10/3;

4.1.4. La clàusula ORDER BY

SQL
SELECT editorial, preu, titol
    FROM llibre
    ORDER BY editorial DESC, titol ASC

Això mostrarà els llibres ordenats per l'editorial, de forma descendent. Si hi ha diverses files de la mateixa editorial, les ordenarà pel títol, de forma ascendent.

editorial	preu	titol
Plaza & Janés	26.00	Aplique SQL
Plaza & Janés	17.00	Atles d'Espanya
Planeta	12.00	Canyes i fang
McGraw-Hill	10.50	Cinc hores amb Màrius
McGraw-Hill	23.50	El Quixot
Altaya	21.00	El nom de la rosa
Altaya	15.00	L'odissea
Altaya	11.00	La Barraca

Podem ordenar respecte a qualsevol element de la clàusula SELECT o per qualsevol camp de la taula del FROM (encara que no estiga en la clàusula SELECT).

Si no s'indica DESC ni ASC, per defecte és ASC.

Per a indicar la columna en l'ORDER BY, en compte de posar el nom de la columna podem posar el número en el qual apareix eixa columna en la clàusula SELECT. El resultat serà el mateix. En el nostre exemple:

SQL
SELECT editorial, preu, titol
    FROM llibres
    ORDER BY 1 DESC, 3 ASC

La clàusula ORDER BY va al final de tot de la sentència SELECT, però hi ha altres clàusules entre el FROM i l'ORDER BY que ja anirem veient (com GROUP BY, HAVING i UNION).

Exercicis. ORDER BY (BD lliga1213)

Mostra el nom llarg de cada equip i el seu pressupost, ordenat pel pressupost, de menor a major.
De cada partit, mostra la jornada, l'equip de casa i els seus gols. Primer eixiran els que han marcat més gols.
De cada partit, mostra la jornada, l'equip de casa i els seus gols. Estarà ordenat per equip (de menor a major). Els partits de cada equip estaran ordenats de més gols a menys.
Mostra totes les dades dels partits però primer eixiran els partits que s'hagen marcat més gols. En cas d'igualtat, eixiran primer els que es marcaren més gols a casa. En cas d'igualtat eixiran ordenats pel codi de l'equip de casa (de menor a major). En cas d'igualtat eixiran ordenats per la jornada.

4.1.5. Resultats de consultes

El resultat d'una consulta SQL és semblant a una taula de la BD, amb files i columnes. En compte de mostrar eixe resultat per pantalla, el podem aprofitar per a diverses coses:

a) Inserir-ho en altra taula existent (ja ho hem vist en l'apartat de l'insert)

SQL
INSERT INTO vendes_febrer           -- Taula ja existent
    SELECT *
        FROM vendes
        WHERE mes = 2;

Estem afegint en la taula vendes_febrer (que ja existia) els registres de la taula vendes que complien una determinada condició. Els camps de la taula vendes_febrer hauran de coincidir amb quantitat i tipus de les columnes de la clàusula select.

b) Crear una altra taula amb les files i columnes seleccionades

SQL
CREATE TABLE socis_sense_tel AS   -- Taula que creem ara
    SELECT codi_soci, nom_soci
        FROM socis
        WHERE telefon IS NULL

Estem creant la taula socis_sense_tel amb 2 camps (codi_soci i nom_soci) i amb les dades resultants d'aplicar la "select" a la taula socis.

c) Crear una vista amb les files i columnes seleccionades

Ja ho vorem més endavant, però seria així:

SQL
CREATE VIEW socis_sense_tel AS
    SELECT codi_soci, nom_soci
        FROM socis
        WHERE telefon IS NULL

Ni estava creada la taula socis_sense_tel ni l'estem creant ara, sinó que li estem posant un nom al resultat de la select, de forma que ho podrem utilitzar com si fóra una altra taula però no ocupa espai en disc dur, ja que les files són les mateixes que la taula original (socis).

Exercicis. Resultats d'una consulta (BD institut)

En els següents exercicis vorem els passos per a passar les dades d'una taula a altres, de diverses formes.

graph LR
    A[alumnes] --> B[a_dam]
    A --> C[a_eso]

1) Crea la BD institut.

2) Crea en ella la taula alumnes amb els camps codi (enter), nom i estudis (4 caràcters).

3) Introdueix 3 alumnes amb estudis de PQPI, 3 d'ESO i 3 de DAM.

4) A partir d'eixa taula, crea la taula a_dam amb els alumnes de DAM (codi i nom) (amb la sentència de crear taula a partir d'una select).

5) Crea la taula a_eso amb codi (enter) i nom.

6) Introdueix 3 alumnes d'ESO en a_eso (amb codis que no estiguen en taula d'alumnes).

7) Copia els alumnes d'ESO de la taula alumnes a la taula a_eso (amb la sentència d'inserció de files a partir d'una select).

8) Esborra de la taula alumnes els d'ESO i DAM.

9) La taula alumnes ara s'ha de dir a_pqpi.

10) Elimina el camp estudis de la taula a_pqpi.

4.1.6. Mostrar només les files distintes (DISTINCT)

A vegades, una consulta traurà files repetides.

SQL
SELECT país, llengua
    FROM autors

país	llengua
Espanya	Castellà
Espanya	Català
Argentina	Castellà
Espanya	Català
Espanya	Basc
Argentina	Castellà

Si després de la paraula SELECT posem DISTINCT, no mostrarà eixes files repetides:

SQL
SELECT DISTINCT país, llengua
    FROM autors

país	llengua
Espanya	Castellà
Espanya	Català
Espanya	Basc
Argentina	Castellà

Encara que la clàusula SELECT tinga més d'un camp (com en l'exemple anterior), només es posa un DISTINCT, el qual descartarà les files repetides en tots els camps de la SELECT.

La funció d'agregat COUNT també admet el DISTINCT. S'usa quan volem comptar quants elements distints tenim. Per exemple:

SQL
SELECT COUNT(*), COUNT(país), COUNT(DISTINCT país), COUNT(DISTINCT(llengua))
    FROM autors

count(*)	count(país)	count(distinct país)	count(distinct llengua)
6	6	2	3

Exercicis. DISTINCT (BD empresa)

1) Mostra els departaments que tenen algun empleat. En la taula dept estan tots els departaments però potser algun no té empleats. Per tant, ho hauràs de traure a partir de la taula emp. Que no apareguen repetits els departaments.

2) Mostra les parelles de comissió i departament (de la taula d'empleats) sense que apareguen parelles repetides.

3) Mostra en quants pobles hi ha departaments.

4.1.7. La clàusula WHERE

La clàusula WHERE s'utilitza quan no volem mostrar dades de totes les files de la taula, sinó només d'algunes. Per exemple: Mostra'm els alumnes menors d'edat.

SQL
SELECT nom, edat
    FROM alumnes
    WHERE edat < 18        -- Condició de recerca: edat < 18

nom	edat
Pep	17
Pepa	16
Toni	15
Maria	17

La clàusula WHERE consta de la paraula clau WHERE seguida d'una condició de recerca que especifica les files a recuperar. Per cada fila de la taula seleccionada, la condició de recerca pot produir un d'estos tres resultats:

Si la condició de recerca és true (certa), la fila SÍ que ix en la consulta.
Si la condició de recerca és false (falsa), la fila NO ix en la consulta.
Si la condició de recerca té un valor NULL, la fila NO ix en la consulta. En l'exemple anterior, els alumnes que no tenien posada la seua edat (tenien un NULL), la condició de NULL < 18 té com a resultat el valor NULL i, per tant, eixos alumnes NO eixirien en la consulta. Ja vorem més endavant com consultar en la WHERE si un camp té el valor NULL.

La condició de recerca és allò que podem posar dins de la clàusula WHERE (o del HAVING, ja ho vorem). Què podem posar en una condició de recerca?

Test de comparació
Test de rang
Test de pertinença a un conjunt
Test de correspondència amb patró
Test de valor nul

A més, la condició de recerca pot ser composta (tindre diverses condicions). Per a això usarem els operadors relacionals AND, OR i NOT.

Test de comparació ( =, <>, <, <=, >, >= )

Compara expressions (noms de columnes, constants, expressions aritmètiques o funcions).

SQL
... WHERE iva < 16
... WHERE nom = 'Pep'
... WHERE (entrades - sortides) <> saldo
... WHERE preu_euros > (preu_ptes / 166.386)

Test de rang (BETWEEN)

SQL
SELECT codi_soci
    FROM prestecs
    WHERE data BETWEEN "2013/02/01" AND "2013/02/15"

Això mostrarà els socis amb un préstec en la primera quinzena de febrer de 2013. La condició contrària seria: WHERE data NOT BETWEEN "2013/02/01" AND "2013/02/15". O bé: WHERE NOT (data BETWEEN "2013/02/01" AND "2013/02/15").

El BETWEEN no és realment necessari, ja que podríem reemplaçar-ho amb expressions amb AND i OR:

Text Only
1 2	`A BETWEEN B AND C → (A >= B) AND (A <= C) NOT A BETWEEN B AND C → (A < B) OR (A > C)`

Test de pertinença a un conjunt ( IN )

SQL
SELECT codi_llibre, codi_soci, data_pre
    FROM prestecs
    WHERE data_pre IN ('2015/09/02', '2015/04/05', '2016/01/09')

Mostrarà les dades dels préstecs que siguen d'alguna d'eixes dates.

codi_llibre	codi_soci	data_pre
0001	1	2015/09/02
0002	4	2016/01/09
0002	3	2015/04/05
0007	1	2015/09/02

Per a obtindre els altres resultats, caldria utilitzar el NOT IN (però recordem que no eixirien aquells préstecs que tenen un NULL en la seua data).

Igual que el test BETWEEN, el test IN tampoc és necessari, ja que la condició:

Text Only
1	`x IN (a, b, c)`

...és equivalent a:

Text Only
1	`(x = a) OR (x = b) OR (x = c)`

Test de correspondència amb patró (LIKE)

S'utilitza per a buscar cadenes de caràcters que s'ajusten a un patró.

SQL
nom_columna [NOT] LIKE patró

El patró és una cadena que pot incloure un o més caràcters comodins:

_ (símbol barra baixa) → qualsevol caràcter (però 1 i només 1).
% (símbol del percentatge) → qualsevol seqüència de 0, 1 o més caràcters.

Exemple 1: alumnes que tinguen de cognom Escrivà o Escribà

SQL
SELECT nom, cognoms
    FROM alumnes
    WHERE cognoms LIKE '%Escri_à%'

nom	cognoms
Pep	Pons Escrivà
Pepa	Escribà Garcia

Exemple 2: alumnes que tenen un nom de 3 lletres

SQL
SELECT nom, cognoms
    FROM alumnes
    WHERE nom LIKE '___';

nom	cognoms
Pep	Pons Escrivà
Roc	Garcia i Garcia

Es poden localitzar cadenes que no s'ajusten a un patró utilitzant NOT LIKE.

Si el valor d'eixa columna en alguna fila és NULL, tant si posem LIKE com NOT LIKE, la fila no eixirà en el resultat.

Caràcter d'escapament

Si volguérem buscar si apareix el símbol del % o el _ en una cadena de text, caldria utilitzar un caràcter d'escapament, que en MySQL és \.

SQL
SELECT titol
    FROM articles
    WHERE titol LIKE "%\%%"

Això traurà títols com: "El 50% dels alumnes són la meitat".

Test de valor nul (IS NULL)

Un camp d'una taula que no conté cap valor es diu que conté un valor nul. No és que el valor siga zero o que siga una cadena buida, sinó que no té valor.

Per exemple, posarem valors nuls en els següents casos:

Quan desconeixem el valor (per exemple, la data de naixement d'un client).
Quan no té valor (per exemple, el correu electrònic d'algú que no en té).
Quan no té sentit el valor (per exemple, el nom del pare d'un alumne major d'edat).

Per a consultar on apareixen valors nuls cal usar una sentència de comparació específica IS NULL. No és correcte fer comparacions amb el comparador d'igualtat =. Per exemple, si volem saber els clients que no ens deuen res o aquells que no sabem el que ens deuen, faríem:

SQL
SELECT codi, nom, deute
    FROM socis
    WHERE (deute = 0) OR (deute IS NULL)

codi	nom	deute
3	Pep	0
5	Pepa	NULL
7	Maria	NULL
9	Tomàs	0

I per a consultar on apareixen valors no nuls, usarem IS NOT NULL:

SQL
SELECT codi, nom, deute
    FROM socis
    WHERE deute IS NOT NULL

Que és el mateix que ... WHERE NOT (deute IS NULL).

Lògica trivaluada

Cal tindre en compte que els valors NULL creen una lògica trivaluada per a les condicions de recerca en SQL. És a dir, quan SQL compara els valors de dos expressions en el test de comparació, per a una fila determinada, es poden produir tres resultats: TRUE, FALSE o NULL.

Si la comparació és certa → el resultat és TRUE.
Si la comparació és falsa → el resultat és FALSE.
Si el valor d'algun camp de la comparació és NULL → el resultat és NULL.

La clàusula WHERE només seleccionarà els resultats TRUE.

Vegem-ho amb exemples. Suposem que tenim la taula d'alumnes següent:

nom	edat
Pep	20
Pepa	NULL
Pepet	15

Per a mostrar els alumnes majors d'edat farem:

SQL
SELECT nom FROM alumnes WHERE edat >= 18;             -- Pep

Per a mostrar els menors d'edat, caldria indicar la condició contrària:

SQL
SELECT nom FROM alumnes WHERE NOT (edat >= 18);       -- Pepet

Però veiem que Pepa no ha eixit ni com a menor d'edat ni com a major. Per a mostrar aquells que no tenen edat, hem d'utilitzar IS NULL:

SQL
SELECT nom FROM alumnes WHERE edat IS NULL;           -- Pepa

I per a mostrar els que sí que tenen edat:

SQL
SELECT nom FROM alumnes WHERE edat IS NOT NULL;       -- Pep i Pepet

Si volguérem mostrar els menors d'edat però també els que no tenen ficada l'edat:

SQL
SELECT nom FROM alumnes WHERE edat < 18 OR edat IS NULL;  -- Pepa i Pepet

Cal anar en compte i NO utilitzar la condició següent:

SQL
... WHERE telèfon = NULL

És a dir: per a comparar amb el NULL cal usar IS i no el signe =.

Condicions de recerca compostes: AND, OR i NOT

Ja hem vist en algun exemple que en l'apartat del WHERE ja han aparegut alguns operadors relacionals. Els que usa MySQL són AND, OR i NOT. És a dir: les condicions de recerca poden ser compostes i així combinar diferents condicions simples. També podem fer ús de parèntesis, sobretot quan volem trencar l'ordre de prioritat dels operadors, que és el següent:

NOT (el més prioritari)
AND
OR (el menys prioritari)

La sentència SELECT calcularà eixa condició composta per cada fila de la taula i retornarà aquelles files que el resultat siga vertader.

SQL
SELECT nom, domicili
    FROM amics
    WHERE ciutat = 'Sueca' AND data_naix < '01/01/1970'

SQL
SELECT títol
    FROM llibres
    WHERE autor = 'Joan Fuster' OR tipus = 'assaig'

SQL
SELECT nom, nacionalitat
    FROM autors
    WHERE NOT (nom LIKE '%Delibes%')

SQL
SELECT títol_disc, grup
    FROM discos
    WHERE (tipus = 'rock' OR tipus = 'pop')
        AND NOT (llengua IN ('castellà', 'català', 'anglés'))

Exercicis. WHERE (BD empresa)

1) Mostra nom i comissió d'aquells empleats que NO tinguen comissió (pensa que pot haver empleats amb comissió 0 i empleats amb comissió NULL).

2) Mostra nom i comissió dels empleats que siguen del departament 2 amb una comissió superior al 10%.

3) Mostra nom i comissió dels empleats que tenen una comissió entre el 20 i el 50%. Fes-ho de 2 formes: amb BETWEEN i sense BETWEEN.

4) Mostra el nom dels empleats dels departaments 2, 5 i 6. Mostra també el departament de cadascun. Fes-ho de 2 formes: amb IN i sense IN.

5) Mostra totes les dades dels empleats que el nom comence per A, que acaben amb E i que la tercera lletra del seu nom siga una I.

Exercicis. WHERE (BD lliga1213)

1) Partits que ha perdut el Barça (codi bar) jugant a casa.

2) Partits que ha perdut el Barça jugant fora.

3) Quantitat de partits que ha perdut el Barça.

4) Partits on s'han marcat més de 5 gols.

5) Quines jornades es jugaren en febrer.

6) Quants partits encara no ha jugat el València (no ha jugat si no estan posats els gols).

7) Partits on el Madrid (codi rma) ha rebut 3 gols o més.

8) Partits on el Madrid ha perdut per més d'un gol de diferència.

9) Partits on un equip ha tingut més del 60% de possessió.

10) Partits on un equip ha tingut més del 60% de possessió i ha perdut el partit.

11) Mostra la quiniela de la primera jornada (equip casa, equip fora, 1x2). Hauràs d'utilitzar la funció IF. Exemple d'ús:

nom	cognoms	edat
Pep	Garcia	20
Pepet	Martí	17
Pepa	Granell	18
Pepona	Albors	19

SQL
SELECT nom, cognoms, IF(edat >= 18, "Sí", "No") AS "Major d'edat"
    FROM alumnes;

nom	cognoms	Major d'edat
Pep	Garcia	Sí
Pepet	Martí	No
Pepa	Granell	Sí
Pepona	Albors	Sí

Nota

Podem usar una funció IF dins d'una altra.

4.1.8. La clàusula GROUP BY

Suposem que tenim esta taula de llibres:

titol	editorial	preu
L'odissea	Altaya	15.00
Cinc hores amb Màrius	McGraw-Hill	10.50
La Barraca	Altaya	11.00
Canyes i fang	Planeta	12.00
El nom de la rosa	Altaya	21.00
El Quixot	McGraw-Hill	23.50
Atles d'Espanya	Plaza & Janés	17.00
Aplique SQL	Plaza & Janés	26.00

Veiem que cada llibre pertany a una editorial (i que una editorial té molts llibres). La taula llibres té tants llibres com files. Però imagina que no volem obtindre informació de cada llibre (de cada fila) sinó de cada editorial (de cada GRUP de llibres de la mateixa editorial). Per exemple, de cada editorial volem saber (a més del nom): el preu del llibre més car d'eixa editorial, el preu del llibre més barat d'eixa editorial, el preu mig dels llibres d'eixa editorial, la suma dels preus de tots els llibres d'eixa editorial i la quantitat de llibres que té eixa editorial.

És a dir: voldrem obtindre la següent informació:

editorial	preu màxim	preu mínim	preu mig	suma de preus	quantitat de llibres
Altaya	21.00	11.00	15.67	47.00	3
McGraw-Hill	23.50	10.50	17.00	34.00	2
Planeta	12.00	12.00	12.00	12.00	1
Plaza & Janés	26.00	17.00	21.50	43.00	2

Com obtenim això amb una sentència select?

Haurem de dir a la select que no mostre informació de cada fila (de cada llibre) sinó de cada grup de files de la mateixa editorial. I, per a mostrar la informació, usarem unes funcions de MySQL que actuen sobre grups de files (funcions d'agregat): max, min, avg, sum, count.

És a dir: haurem de dir a la select que, internament, agrupe els llibres per l'editorial... i, així, obtindre una fila de resultats per cada grup. La sentència seria esta:

SQL
SELECT editorial, MAX(preu), MIN(preu), AVG(preu), SUM(preu), COUNT(*)
    FROM llibres
    GROUP BY editorial

Notes

Les funcions d'agregat també admeten expressions. Per exemple: MAX(preu_venda - preu_compra).
COUNT(*) compta les files del grup. COUNT(preu) comptaria files del grup on el preu no és null.

La sintaxi de la sentència select amb la clàusula group by és:

SQL
SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...

En la SELECT podrem posar o bé els noms de les columnes per les quals estem agrupant o bé funcions d'agregat (max, min, avg, sum, count) o bé expressions formades per estes.
En el GROUP BY posarem el nom de la columna (o columnes separades per comes) per la qual volem agrupar. És a dir: totes les files d'eixe grup tindran el mateix valor d'eixa columna.

Nota

Recordem que si usem funcions d'agregat però no posem el GROUP BY, només ens mostrarà una única fila de resultats per a tota la taula:

SQL
SELECT MAX(preu), MIN(preu), AVG(preu), SUM(preu), COUNT(*), editorial
    FROM llibres

MAX(preu)	MIN(preu)	AVG(preu)	SUM(preu)	COUNT(*)	editorial
26.00	10.50	17.00	136.0	8	(qualsevol)

Com en este cas no estem agrupant per l'editorial, no té sentit que ens mostre l'editorial, ja que mostraria el nom d'una editorial qualsevol de tota la taula.

Agrupament per més d'una columna

També podem agrupar per més d'una columna. En eixe cas, cada grup tindrà els mateixos valors en cadascuna d'eixes columnes.

ALUMNES: núm	nom	curs	grup	poble	edat
1	Pep	1	A	Sueca	17
2	Pepa	1	A	Sueca	17
3	Pepet	1	A	Tavernes	18
4	Pepeta	1	A	Tavernes	19
5	Pepot	1	B	Sueca	17
6	Pepota	1	B	Sueca	18
7	Pepiu	1	B	Sueca	17
8	Josep	1	B	Tavernes	17
9	Josepa	2	A	Sueca	18
10	Josepet	2	A	Sueca	18
11	Josepeta	2	A	Tavernes	21
12	Josepot	2	B	Tavernes	19

Quantes més columnes posem en el group by, més grups eixiran (però amb menys files cadascun, clar).

SQL
SELECT curs, count(*), min(edat), max(edat)
    FROM ALUMNES
    GROUP BY curs;

curs	count(*)	min(edat)	max(edat)
1	8	17	19
2	4	18	21

SQL
SELECT curs, grup, count(*), min(edat), max(edat)
    FROM ALUMNES
    GROUP BY curs, grup;

curs	grup	count(*)	min(edat)	max(edat)
1	A	4	17	19
1	B	4	17	18
2	A	3	18	21
2	B	1	19	19

SQL
SELECT curs, grup, poble, count(*), min(edat), max(edat)
    FROM ALUMNES
    GROUP BY curs, grup, poble;

curs	grup	poble	count(*)	min(edat)	max(edat)
1	A	Sueca	2	17	17
1	A	Tavernes	2	18	19
1	B	Sueca	3	17	18
1	B	Tavernes	1	17	17
2	A	Sueca	2	18	18
2	A	Tavernes	1	21	21
2	B	Tavernes	1	19	19

Norma

Si en la clàusula select hi ha funcions d'agregat (sum, max, min, count, avg), tots els altres camps de la clàusula select han d'aparéixer també a la clàusula group by.

Exercicis. GROUP BY (BD lliga1213)

Mostra de cada equip: el codi, sou màxim, mínim, la suma de tots els sous, quants jugadors hi ha, de quants jugadors es coneix el sou, la mitjana de sous entre els que sabem el sou i la mitjana de sous entre tots els jugadors.
Mostra quants jugadors té cada equip en cada posició.
Gols marcats en total en cada jornada.
Mitja de gols per partit en cada jornada.
Gols marcats pel pitxitxi de cada equip. És a dir: cal mostrar el codi de l'equip i els gols marcats pel seu màxim golejador.
Gols marcats en total per cada equip en casa.
Gols que ha rebut en total cada equip com a visitant.
Quants partits ha guanyat cada equip jugant en casa.

4.1.9. La clàusula HAVING

SQL
SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING condició_de_recerca

Si la clàusula WHERE descartava files, la clàusula HAVING descarta grups. És a dir: en la condició de filtrat del HAVING indicarem què ha de complir un grup per a que no siga descartat. No pot haver clàusula HAVING si no hi ha GROUP BY.

Per exemple, modifiquem la SELECT d'abans perquè només mostre les editorials que tenen més d'un llibre i que la mitja dels seus preus siga major de 16 euros:

SQL
SELECT editorial, MAX(preu), MIN(preu), AVG(preu), SUM(preu), COUNT(*)
    FROM llibres
    GROUP BY editorial
    HAVING (COUNT(*) > 1) AND (AVG(preu) > 16)

Nota

Les condicions sobre funcions d'agregat NO poden anar en el where perquè no s'han d'aplicar a cada fila de la taula, sinó a cada grup de files.

El resultat serà este:

editorial	preu màxim	preu mínim	preu mig	suma de preus	quantitat de llibres
McGraw-Hill	23.50	10.50	17.00	34.00	2
Plaza & Janés	26.00	17.00	21.50	43.00	2

Exercicis. HAVING (BD lliga1213)

Comprova si hi ha algun nom de jugador repetit. És a dir: cal mostrar el nom del jugador i quantes voltes apareix però només per a aquells jugadors que tinguen el nom repetit.
Jornades en les quals s'han marcat més de 35 gols. Ha d'aparéixer el número de la jornada i la quantitat total de gols corresponent.
Volem saber la mitja de possessió del baló de cada equip jugant a casa d'aquells equips on la seua mínima possessió jugant en casa és major de 40. Ordenat de major a menor possessió. La mitja de la possessió ha d'eixir sense decimals.

4.1.10. Regles per a processament de consultes de taula única

Veiem els passos que segueix el SGBD per a processar una sentència SELECT:

1) Seleccionarà totes les files de la taula que hi ha a la clàusula FROM.

2) Si hi ha clàusula WHERE, aplicarà la seua condició de recerca a cada fila de la taula. Si per a una fila la condició de recerca és TRUE, la seleccionarà. Però si és FALSE o NULL, la descartarà.

3) Si hi ha clàusula GROUP BY, les files seleccionades anteriorment les classifica en grups (on cada grup té els mateixos valors en els camps del GROUP BY).

4) Si hi ha HAVING, descarta els grups anteriors que no compleixen la condició de recerca del HAVING.

5) Calcula el valor de cada element de la clàusula SELECT per a cada fila seleccionada (o bé, per a cada grup si hi havia GROUP BY).

6) Si s'especifica SELECT DISTINCT, elimina les files duplicades dels resultats que s'hagueren produït.

7) Si hi ha una clàusula ORDER BY, ordena els resultats de la consulta.

Estes regles seran ampliades posteriorment per a incloure sentències SELECT més complexes (amb més d'una taula, etc.).

4.1.11. Combinació de resultats de consulta (UNION)

Podem "unir" dos o més consultes en una sola. És a dir, a partir del conjunt de resultats d'una SELECT i del conjunt de resultats d'una altra SELECT, podem obtindre un únic conjunt de resultats.

1a consulta: Llista tots els productes que valguen més de 200 €

SQL
SELECT codi, nom
    FROM articles
    WHERE preu > 200

CODI	NOM
ACI	4100I
REI	2A44L
ACI	4100Z
REI	2A44R

2a consulta: Llista tots els productes que s'hagen venut per un import de més de 3.000 €.

SQL
SELECT DISTINCT article, descripcio
    FROM vendes
    WHERE import > 3000

ARTICLE	DESCRIPCIO
IMM	775C
REI	2A44L
REI	2A44R

Unió: Llista tots els productes que valguen més de 200 € o que s'hagen venut per un import de més de 3.000 €.

SQL
SELECT codi AS ID_FAB, nom AS ID_PRODUCTE
    FROM articles
    WHERE preu > 200
UNION
SELECT DISTINCT article, descripcio
    FROM vendes
    WHERE import > 3000
ORDER BY 1

ID_FAB	ID_PRODUCTE
ACI	4100I
ACI	4100Z
IMM	775C
REI	2A44L
REI	2A44R

Algunes consideracions sobre el UNION

UNION lleva els resultats duplicats. Si no volem que els lleve cal posar UNION ALL.

Es poden posar més de dos SELECT, separades per UNION:

SQL
SELECT * FROM a
UNION
(SELECT * FROM b  UNION  SELECT * FROM c)

Les SELECT han de tindre el mateix nombre de columnes i ser del mateix tipus.
Els noms de columnes de les SELECT no tenen per què ser igual. Si són diferents, el nom de les columnes que es mostraran en el resultat final seran les de la primera SELECT.
Cap de les SELECT té la clàusula ORDER BY, però se'n pot posar una al final de tot.

4.1.12. Resum de les consultes simples

La sentència SELECT s'utilitza per a expressar una consulta SQL. Tota sentència SELECT produïx una taula de resultats que conté una o més columnes i zero o més files.
La clàusula FROM especifica les taules que tenen les dades a recuperar en una consulta.
La clàusula SELECT especifica les columnes de dades a incloure en els resultats de la consulta, que poden ser columnes de dades de la BD o columnes calculades.
La clàusula WHERE selecciona les files a incloure en els resultats aplicant una condició de recerca a les files de la BD.
Una condició de recerca pot seleccionar files mitjançant comparació de valors (<, >, = ...), mitjançant comparació de valor amb un rang (BETWEEN) o un grup de valors (IN), per correspondència amb un patró de columna (LIKE) o per comprovació de valors nuls (IS NULL).
Les condicions de recerca simples poden combinar-se mitjançant AND, OR i NOT per a formar condicions de recerca més complexes.
La clàusula ORDER BY especifica que els resultats de la consulta han de ser ordenats en sentit ascendent o descendent, basant-se en els valors d'una o més columnes.
L'operació UNION pot ser utilitzada dins d'una sentència SELECT per a combinar dos o més conjunts de resultats i formar un únic conjunt.

Exercicis. UNION (BD lliga1213)

Mostra la quiniela de la primera jornada (equip casa, equip fora, 1x2). Este exercici ja es va fer però amb la funció IF. Ara fes-lo sense usar eixa funció. I ordenat pel codi de l'equip que juga en casa.

EQUIPC EQUIPF RESULTAT

ath bet 2

bar rso 1

cel mga 2

dep osa 1

lev atm x

mal esp 1

ray gda 1

rma val x

sev get 1

zar vad 2
Quants uns, quantes x i quants 2 en la primera jornada.

RESULTAT QUANTITAT

1 5

X 2

2 3
Quants uns, quantes x i quants 2 en cada jornada (ordenat per la jornada).

JORNADA RESULTAT QUANTITAT

1 1 5

1 X 2

1 2 3

2 1 4

2 X 2

2 2 3

... ... ...
Quants partits li queda per jugar a cada equip en casa i quants fora? Mostra la informació ordenada per equip. Dins de cada equip, primer els de casa.

EQUIP PARTITS LLOC

ath 1 casa

ath 2 fora

atm 1 casa

atm 1 fora

bar 2 casa

bar 1 fora

... ... ...

Quants partits ha guanyat/empatat/perdut cada equip jugant en casa/fora. Ordenat per equip:

EQUIP	PARTITS	RESULTAT
atm	14	guanyats a casa
atm	1	empatats a casa
atm	3	perduts a casa
atm	8	guanyats fora
atm	5	empatats fora
atm	5	perduts fora
bar	13	guanyats fora
bar	3	empatats fora
bar	2	perduts fora
bar	16	guanyats a casa
bar	1	empatats a casa
bar	0	perduts a casa
...	...	...

Nota

Voràs que no ixen les línies on la quantitat de partits és 0. No et preocupes. Per a que isquen caldria usar subconsultes (encara no ho hem vist).

4.2. Consultes compostes (multitaula)

4.2.1. Introducció

Moltes consultes sol·liciten dades procedents de dos o més taules en la BD. SQL permet recuperar dades que responen a estes peticions mitjançant consultes compostes o multitaula.

Per exemple, tenim estes dos taules:

Text Only
1 2 3 4	`POBLES = codi_postal + nom ALUMNES = num + nom + grup + cp C. Ali: cp → POBLES (codi_postal)`

Volem fer una consulta on aparega el nom de l'alumne i el nom del poble on viu. Com que la informació està en 2 taules, haurem de:

Posar les 2 taules en el FROM.
Posar en el WHERE com estan relacionades les 2 taules: sol ser una condició sobre la clau aliena.

SQL
SELECT alumnes.nom, pobles.nom
    FROM alumnes, pobles
    WHERE alumnes.cp = pobles.codi_postal

NOM	NOM
Pep Garcia	Sueca
Pepa Sales	Sueca
Andreu Albors	Sollana
Martí Manyes	Cullera

Notes

Si hi ha una columna que està en les 2 taules (conflicte de noms) cal usar noms qualificats (nom_taula.nom_columna).
En el FROM posarem les taules necessàries separades per ",". Dóna igual l'ordre.
En el WHERE posarem l'enllaç entre les taules del FROM. Per regla general és la clau aliena d'una taula amb la clau primària de l'altra taula.

4.2.2. Explicació de per què posar l'enllaç entre les taules

Imaginem que en la taula d'alumnes tenim 3 registres i en la taula de pobles, 2:

ALUMNES: num	nom	cp
1	Pep	46410
2	Pepa	46410
3	Pepet	46760

POBLES: cpostal	nom	comarca
46410	Sueca	Ribera Baixa
46760	Tavernes	La Valldigna

Text Only
C. Ali: ALUMNES(cp) → POBLES(cpostal)

Si volem obtindre en una consulta totes les dades dels alumnes i les corresponents dades dels pobles de cada alumne, si no posem l'enllaç, obtindrem el que s'anomena producte cartesià de les files. És a dir: cada fila d'alumnes la relacionarà amb cada fila de pobles. Per tant, tindrem 3 x 2 = 6 files:

SQL
SELECT alumnes.*, pobles.*
    FROM alumnes, pobles

num	nom	cp	cpostal	nom	comarca
1	Pep	46410	46410	Sueca	Ribera Baixa
1	Pep	46410	46760	Tavernes	La Safor
2	Pepa	46410	46410	Sueca	Ribera Baixa
2	Pepa	46410	46760	Tavernes	La Safor
3	Pepet	46760	46410	Sueca	Ribera Baixa
3	Pepet	46760	46760	Tavernes	La Safor

Però està clar que no és això el que volíem, sinó que d'eixes files només ens interessa aquelles que el cp és igual al cpostal. Per tant:

SQL
SELECT alumnes.*, pobles.*
    FROM alumnes, pobles
    WHERE alumnes.cp = pobles.cpostal

num	nom	cp	cpostal	nom	comarca
1	Pep	46410	46410	Sueca	Ribera Baixa
2	Pepa	46410	46410	Sueca	Ribera Baixa
3	Pepet	46760	46760	Tavernes	La Safor

I ara, si volem, com que el codi postal apareix repetit en dos columnes, en llevarem una qualsevol de les dos (o les dos, si no volguérem mostrar-lo):

SQL
SELECT alumnes.*, pobles.nom, pobles.comarca
    FROM alumnes, pobles
    WHERE alumnes.cp = pobles.cpostal

num	nom	cp	nom	comarca
1	Pep	46410	Sueca	Ribera Baixa
2	Pepa	46410	Sueca	Ribera Baixa
3	Pepet	46760	Tavernes	La Safor

4.2.3. Exemple per a més de dos taules

Suposem que tenim estes 3 taules:

Text Only
1 2 3 4 5 6 7	`ASSIGNATURES = codi + nom ALUMNES = num + nom + grup + cp MATRÍCULES = alu + assig + nota C. Ali: alu → ALUMNES(num) assig → ASSIGNATURES(codi)`

Volem obtindre de cada alumne: el seu nom i el nom de les assignatures on està matriculat.

Solució:

El nom de l'alumne està en la taula ALUMNES i el nom de les assignatures en la taula ASSIGNATURES. Però també necessitem la taula MATRÍCULES, ja que fa d'enllaç entre elles dos (és en eixa taula on està la informació de quines assignatures està matriculat cada alumne).
Dit d'una altra manera: la informació de les matrícules està en la taula MATRÍCULES però els camps a obtindre estan en altres dos taules. Per tant, caldrà fer l'enllaç de la taula MATRÍCULES a les altres dos.

Per tant, tindrem:

SQL
SELECT alumnes.nom, assignatures.nom
    FROM alumnes, assignatures, matricules
    WHERE alumnes.num = matricules.alu
       AND matricules.assig = assignatures.codi

NOM	NOM
Pep Garcia	Matemàtiques
Pep Garcia	Valencià
Pepa Sales	Valencià
Pepa Sales	Anglés
Martí Manyes	Valencià

Nota

Si tenim 3 taules, per a enllaçar-les caldrà posar els 2 enllaços entre elles (claus alienes).

4.2.4. Columnes d'emparellament

El procés de formar parelles de files fent coincidir els continguts de les columnes relacionades es denomina compondre les taules (o bé: "fer un join" de les taules).

Les columnes usades en la composició de les taules es diuen columnes d'emparellament. En la majoria dels casos, les columnes d'emparellament estan formades per la clau aliena d'una taula amb la clau principal de l'altra taula. Caldrà anar en compte si entre les taules hi ha alguna clau aliena composta o bé hi ha distintes claus alienes que relacionen diverses taules:

a) Relacionar taules amb clau composta

Caldrà relacionar cadascuna de les parelles de camps. Per exemple:

Text Only
1 2 3 4	`FACTURES = any + numero + client + data LINIES_FAC = any + numero + linia + article + quantitat + preu C. Ali: (any + numero) → FACTURES(any + numero)`

SQL
SELECT factures.*, linies_fac.*
    FROM factures, linies_fac
    WHERE factures.any = linies_fac.any
        AND factures.numero = linies_fac.numero

b) Relacionar taules amb més d'una clau aliena entre elles

Caldrà "seguir el camí" de claus alienes segons la consulta que volem fer. Per exemple:

b.1) Mostra de cada ciclista, els ports guanyats i l'etapa per on passa cadascun d'eixos ports:
- Relacionarem: ciclistes amb ports, ports amb etapes.
b.2) Mostra de cada ciclista, les etapes guanyades i els ports que té cadascuna d'eixes etapes:
- Relacionarem: ciclistes amb etapes, etapes amb ports.

És a dir, si tenim el següent esquema relacional:

Text Only

CICLISTES = dorsal + nom + edat

ETAPES = numero + kms + ciclista
    C. Ali: ciclista → CICLISTES (dorsal)

PORTS = nom + altura + categoria + etapa + ciclista
    C. Ali: etapa → ETAPES(numero)
    C. Ali: ciclista → CICLISTES(dorsal)

Les consultes anteriors serien:

SQL
-- b.1)
SELECT ciclistes.*, ports.*, etapes.*
    FROM ciclistes, ports, etapes
    WHERE ciclistes.dorsal = ports.ciclista
       AND ports.etapa = etapes.numero

SQL
-- b.2)
SELECT ciclistes.*, ports.*, etapes.*
    FROM ciclistes, ports, etapes
    WHERE ciclistes.dorsal = etapes.ciclista
       AND etapes.numero = ports.etapa

4.2.5. Condicions de recerca en consultes multitaula

En la clàusula WHERE podem combinar emparellament de columnes i altres condicions de recerca. Per exemple:

SQL
SELECT nom_edit, títol_llibre
    FROM llibre, editorial
    WHERE llibre.codi_edit = editorial.codi_edit
       AND preu_llibre > 30

4.2.6. Ús d'àlies en les taules

En la clàusula FROM podem fer ús d'àlies de taules, per diferents motius:

a) Per a simplificar noms qualificats de les taules

SQL
SELECT ven.*, ofi.ciutat, ofi.comarca
    FROM vendes AS ven, oficines AS ofi
    WHERE ven.oficina = ofi.codi

Usem la paraula reservada AS per a indicar l'àlies, però és opcional. També podem usar àlies en consultes amb una única taula.

b) Per a crear una consulta multitaula que relaciona una taula amb ella mateixa

Per exemple, en la taula empleats tenim tots els empleats d'una empresa: tant els treballadors subordinats com els seus superiors.

En el disseny relacional tindríem:

Text Only
1 2	`EMPLEATS = codi + nom + data_naix + superior C. Ali: superior → EMPLEATS (codi)`

En eixa taula, cada empleat té el codi del seu superior, però no el nom.

Volem obtindre una consulta on aparega el nom de l'empleat junt al nom (no el codi) del seu superior. Implementant la consulta que volem en SQL, tindríem:

SQL
SELECT sub.nom, cap.nom
    FROM empleats AS sub, empleats AS cap
    WHERE sub.superior = cap.codi

El truc està en fer com si tinguérem dos taules d'empleats: una on estan els empleats com a subordinats i altra on estan els empleats com a superiors (caps).

4.2.7. JOIN: una altra forma de fer composicions de taules

Hi ha una altra forma de fer les composicions de taules: l'ús de JOIN. Per exemple, estes dos sentències trauen el mateix resultat:

SQL
SELECT alumnes.*, pobles.nom, pobles.comarca
    FROM alumnes, pobles
    WHERE alumnes.cp = pobles.cpostal
       AND alumnes.edat > 17;

SQL
SELECT alumnes.*, pobles.nom, pobles.comarca
    FROM alumnes JOIN pobles
        ON alumnes.cp = pobles.cpostal
    WHERE alumnes.edat > 17;

Esta segona forma és més eficient quan alguna de les dos taules té un índex en alguna de les columnes d'emparellament (per exemple, si una és clau principal).

A més, amb el JOIN, podrem dir més coses sobre com volem enllaçar dos taules. Què passaria si hi ha alumnes sense poble (és a dir, alumnes que tenen NULL en el cp)? Que no eixirien en el llistat (ni amb l'operador coma ni amb el JOIN). Tampoc apareixeran els pobles que no tenen alumnes. Per a solucionar això, ho indicarem d'alguna forma en el JOIN. Ara veurem els distints tipus de JOIN que podem fer entre dos taules.

Tipus de JOIN

join = inner join = cross join: no agafa files d'una taula si no troba correspondència en l'altra. És el mateix funcionament que faríem en l'operador coma (,) del FROM.
Text Only
1
A join B → Només parelles de A i B relacionades
left join = left outer join: agafa files de la taula de l'esquerra (alumnes) encara que no tinga correspondència amb la taula de la dreta (ordinadors).
Text Only
1
A left join B → Tots els A (amb els B relacionats)
right join = right outer join: agafa files de la taula de la dreta (ordinadors) encara que no tinga correspondència amb la taula de l'esquerra (alumnes).
Text Only
1
A right join B → Tots els B (amb els A relacionats)
outer join = full outer join: agafa files de les dos taules encara que no tinguen correspondència amb l'altra taula.
Text Only
1
A outer join B → Tots els A i tots els B (amb els corresponents relacionats).
Problema

El outer join (o full outer join) no funciona en MySQL (almenys en algunes versions). Veiem la solució.

Solució: fer un UNION de la left i de la right:

SQL
SELECT alumnes.*, pobles.*
    FROM alumnes LEFT JOIN pobles
        ON alumnes.cp = pobles.cpostal
    WHERE edat > 17

UNION

SELECT alumnes.*, pobles.*
    FROM alumnes RIGHT JOIN pobles
        ON alumnes.cp = pobles.cpostal
    WHERE edat > 17

Nota

Esta forma de fer les composicions (amb JOIN) és millor que l'altra, ja que l'altra primer fa el producte cartesià i després elimina files el WHERE, mentre que esta és més ràpida ja que simplement relaciona les files amb el mateix valor en la clau aliena. Ara bé: és més ràpida si una de les columnes d'emparellament és una clau primària o té un índex creat sobre ella.

Exercicis multitaula (BD lliga1213)

De cada partit volem mostrar els codis dels equips i el nom de la ciutat on juguen.
De cada partit que falta per jugar volem mostrar en quina data es disputarà, els noms curts dels equips, els noms de les ciutats respectives i el total d'habitants de les dos ciutats.
De cada equip: el pressupost, el que es gasta amb els jugadors i el percentatge que representa.
Llista de jugadors on conste: nom del jugador i nom de la ciutat on juga.
Quantitat total de gols de penal marcats per equips de ciutats de menys de 200000 habitants.
En quines dates s'han enfrontat el València i el Barça (sabent que els codis són val i bar). Mostra quin jugava a casa i qui fora i el resultat de gols.
En quines dates s'han enfrontat el València i el Barça (sabent que els noms curts són "València" i "Barça"). Mostra quin jugava a casa i qui fora (noms llargs) i el resultat de gols.
Mostra parelles de jugadors on un d'ells cobra més de 10 voltes que l'altre. Mostra també els seus sous.
Modifica l'exercici anterior perquè també apareguen els respectius noms (llargs) dels equips.
Migcampistes que cobren més que algun davanter del seu equip. Cal mostrar els 2 noms i els 2 sous.
Parelles de porter i golejador d'un mateix equip on el golejador haja marcat més gols que els gols que ha encaixat el porter. Cal mostrar l'equip i els noms del porter i golejador amb els gols respectius. Ordenat per l'equip i el nom del porter.
Volem comparar els gols de Messi i Ronaldo (no sabem el nom complet d'ells). Mostra el nom del jugador i tota l'estadística dels gols com a jugadors però només d'ells dos.
Mitjana de gols marcats en cada jornada i la data de la jornada (un decimal).
Quants partits ha guanyat/empatat/perdut cada equip, però sense diferenciar si és a casa o fora (només els totals).

EQUIP PARTITS RESULTAT

ath 11 guanyats

ath 8 empatats

ath 16 perduts

atm 22 guanyats

atm 6 empatats

atm 8 perduts

bar 29 guanyats

... ... ...
Quants partits li queda per jugar en total a cada equip?

4.2.8. Regles per a processament de consultes multitaula

Veiem què fa SQL per a generar els resultats d'una consulta amb una sentència SELECT:

1) Si la sentència és una UNION de sentències SELECT, aplica els passos 2 fins al 7 a cada una de les sentències per a generar els resultats individuals de cada SELECT.

2) Forma el producte cartesià de les taules indicades en la clàusula FROM. Si la clàusula FROM designa una sola taula, el producte és eixa taula.

3) Si hi ha una clàusula WHERE, aplica la seua condició de recerca a cada fila de la taula producte, retenint aquelles files per a les quals la condició de recerca és TRUE (i descartant aquelles per a les quals és FALSE o NULL).

4) Si hi ha clàusula GROUP BY, les files retingudes anteriorment són agrupades pels camps d'esta clàusula.

5) Si hi ha HAVING, es retenen aquells grups que compleixen la condició d'esta clàusula.

6) Es calcula el valor de cada element de la clàusula SELECT per a cada fila retinguda (o bé, per a cada grup si hi ha el GROUP BY).

7) Si s'especifica SELECT DISTINCT, elimina les files duplicades dels resultats que s'hagueren produït.

8) Si la sentència és una UNION de sentències SELECT, mescla les files retingudes en cada SELECT, en una única taula de resultats. Elimina les files duplicades a no ser que s'haja especificat UNION ALL.

9) Si hi ha una clàusula ORDER BY, ordenar els resultats de la consulta segons s'haja especificat.

4.2.9. Resum de les consultes multitaula

En una consulta multitaula, les taules que contenen les dades són designades en la clàusula FROM.
Si no posem el join de les taules del FROM, es generen tantes files com combinacions diferents de les files de les taules (producte cartesià).
Les consultes multitaula més habituals utilitzen les relacions creades per les claus primàries i claus alienes.
Una taula pot compondre's amb ella mateixa; per a això es requereix l'ús d'àlies.
Amb l'operador JOIN podem fer més variants de les composicions.
Per a processar una consulta multitaula, els SGBD fan els següents passos:
- Per a tota sentència SELECT de la UNION:
  - Si el FROM té més d'una taula, forma el producte cartesià.
  - Si WHERE, selecciona les files.
  - Calcula el valor de cada columna de la SELECT per a cada fila.
  - Si DISTINCT, elimina files repetides.
- Si UNION, junta files obteses. Si no té ALL, elimina files repetides.
- Si ORDER BY, ordena files.

4.3. Subconsultes

4.3.1. Introducció

Les subconsultes són sentències SELECT que s'utilitzen dins d'una altra sentència SELECT, a la qual anomenarem consulta principal.

Exemple introductori

Si volem saber l'edat màxima de la taula alumnes faríem:

SQL
SELECT max(edat) FROM alumnes

Però... i si volem saber el nom d'eixe alumne (el que té la màxima edat)?

Cal anar en compte perquè açò estaria mal:

SQL
SELECT nom, max(edat) FROM alumnes

Està mal ja que mostraria l'edat màxima... i un nom qualsevol de la taula alumnes. Per què qualsevol? MySQL no és prou intel·ligent per a saber-ho? La resposta és que no té per què saber-ho, ja que si férem:

SQL
SELECT nom, max(edat), min(edat), avg(edat), min(nota) FROM alumnes

... quin nom d'alumne hauria d'agafar? El de la màxima edat? El de la mínima nota? ...

Per tant, el que volem és mostrar el nom d'aquell alumne que té la condició que la seua edat és... la màxima edat de tots els alumnes:

SQL
SELECT nom                              -- Consulta principal
    FROM alumnes
    WHERE edat = (SELECT max(edat) FROM alumnes)   -- Subconsulta

La select anterior mostrarà el nom de l'alumne (o alumnes) que tenen la màxima edat. Si, a més, volguérem saber quina és eixa edat, la posarem també a la clàusula select:

SQL
SELECT nom, edat
    FROM alumnes
    WHERE edat = (SELECT max(edat) FROM alumnes)

SQL permet formar sentències SELECT combinant qualsevol de les formes que hem vist fins ara. És a dir: tant la consulta principal com la subconsulta poden ser multitaula, amb union, group by, etc. Una consulta pot tindre diferents subconsultes. Fins i tot, una subconsulta pot tindre altres subconsultes. Les sentències UPDATE i DELETE també poden tindre subconsultes en el WHERE. També poden anar subconsultes en la clàusula FROM i en la SELECT, però no són tan freqüents i no ho vorem. A més, en la condició on posem la subselect, en compte de l'operador relacional =, també en podem posar altres: <, >, !=, ...

Exemple on la WHERE té altres condicions, a més de la subconsulta

Nom d'alumnes de 1r majors que tots els alumnes de 2n:

SQL
SELECT nom
    FROM alumnes
    WHERE curs = 1
        AND edat > (SELECT max(edat) FROM alumnes WHERE curs = 2);

Exemple on el FROM de la subconsulta no és el de la consulta principal

Nom d'alumnes majors que tots els professors:

SQL
SELECT nom
    FROM alumnes
    WHERE edat > (SELECT max(edat) FROM professors)

Exemple amb moltes subconsultes

Noms de l'alumne més major i el del més menor:

SQL
SELECT nom
    FROM alumnes
    WHERE edat = (SELECT max(edat) FROM alumnes)
          OR edat = (SELECT min(edat) FROM alumnes)

Nota

Si hi ha 2 o més persones de major edat, eixiran tots ells. Igual amb els menors.

Exemple amb subconsulta que enllaça amb la consulta principal

Nom, curs i edat de l'alumne més major de cada curs.

Amb la solució de l'exercici anterior, per a obtindre l'alumne de major edat, teníem:

SQL
SELECT nom
    FROM alumnes
    WHERE edat = (SELECT max(edat) FROM alumnes)

Però ara no volem comparar l'edat de cada alumne amb la màxima edat de tots els alumnes, sinó que, de cada alumne voldrem comparar la seua edat amb tots els alumnes DEL MATEIX CURS DE L'ALUMNE QUE ESTEM INTENTANT MOSTRAR (en la consulta principal):

SQL
SELECT nom, curs, edat
    FROM alumnes
    WHERE edat =
        (SELECT max(edat)
            FROM alumnes AS alumnes2
            WHERE alumnes2.curs = alumnes.curs);

És a dir: en la subconsulta hem d'enllaçar la taula de la subconsulta amb la taula de la consulta principal. I, com en este cas és la mateixa taula, necessitem fer ús de l'àlies de les taules.

Les subconsultes que havíem vist fins ara treien el mateix resultat per a cada fila seleccionada en la consulta principal. Però en este cas, la subconsulta s'executa per a cada fila de la consulta principal, de forma que:

L'alumne A serà mostrat si la seua edat és la màxima dels alumnes del curs de l'alumne A.
L'alumne B serà mostrat si la seua edat és la màxima dels alumnes del curs de l'alumne B.
...

És a dir: ara hem vist que la subconsulta pot agafar el valor de la consulta principal. Per tant, la subconsulta pot traure un resultat diferent per a cada fila de la consulta principal.

Exercicis introductoris de subconsultes (BD lliga1213)

Nota

S'ha intentat classificar els exercicis de subconsultes pels tipus de solucions de cadascun d'ells (conforme avancem en els apunts, hi haurà més tipus de subconsultes). Ara bé, com que un exercici pot tindre moltes solucions, potser no estiguen ben classificats segons la solució aportada per l'alumne.

En este apartat són consultes on la subconsulta només retorna un únic valor (una sola fila).

Dorsal, equip i gols del pitxitxi (el que ha marcat més gols).
Nom del pitxitxi.
Mostra el nom i sou del jugador millor pagat de tota la lliga.
Mostra el nom i sou del jugador millor pagat de cada equip.
Jugador que més cobra en cada equip dins la seua categoria (lloc). Cal mostrar el nom de l'equip, el nom del jugador, el lloc i el sou (expressat en milions d'euros, amb 1 decimal). Ordenat per equip i lloc.
Mostra totes les dades dels partits on més gols es marcaren de tot el campionat.
Mostra totes les dades dels partits on més gols es marcaren de cada jornada. Ordenat per la jornada.
Noms dels jugadors dels equips del partit on més gols es marcaren. Mostra també el codi dels seus equips. Ordenat per equip i nom de jugador.
Jornades en les quals s'han marcat més gols que la jornada anterior.
Nom llarg d'equips que tenen més de 2 porters, més de 2 defenses, més de 2 mitjos i més de 2 davanters.

4.3.2. Conjunt de resultats d'una subconsulta. Ús de ALL, ANY i IN

En les subselects que hem vist només retornaven un únic valor. Però sabem que una select i, per tant, una subselect, poden donar com a resultat un conjunt de files i de columnes.

Condicions que han de complir les columnes de la subconsulta

Només podrem posar una única columna. És a dir: només pot aparéixer un camp en la clàusula SELECT. No podem fer: ... WHERE a = (SELECT b, c, d FROM ...)
- (Com a excepció està l'operador EXISTS, que ho vorem més avant).
Hem d'anar en compte que el que posem en la condició que enllaça la consulta principal amb la subconsulta, ha de poder-se comparar:
- Han de tindre un tipus "semblant" (els dos varchar encara que siguen de distinta capacitat; o bé un enter amb un float, etc).
- Han de tindre el mateix contingut semàntic: no podem comparar un codi de client amb l'edat d'una persona, per exemple.

Condicions que han de complir les files de la subconsulta

Si, com en els exemples anteriors, la subselect només va a retornar una fila, no hi ha problema. Podem usar d'enllaç (entre la consulta principal i la subselect) qualsevol operador relacional: =, !=, <, > ...
Però si la subselect pot retornar més d'una fila, estarem obligats a usar, junt amb els operadors relacionals (=, >=, >, <=, <, <>), els operadors ANY (algun) o ALL (tots), ja que voldrem comparar un valor de la consulta principal amb ALGÚN valor retornat per la subselect o amb TOTS els valors retornats per la subselect. Com vorem després, en compte de = ANY també podem usar IN; i, en compte de != ALL també podem usar NOT IN.

Exemple ANY

Volem obtindre els noms dels alumnes que són majors que algun professor:

SQL
SELECT nom
    FROM alumnes
    WHERE edat > ANY (SELECT edat FROM professors)

Exemple ALL

Volem obtindre noms d'alumnes que són majors que tots els professors:

SQL
SELECT nom
    FROM alumnes
    WHERE edat > ALL (SELECT edat FROM professors)

Equivalències a expressions amb ANY i ALL

Comparació	Subconsultes amb ANY o ALL	Subconsultes equivalents amb max, min; IN, NOT IN
Major que tots els d'altra taula	`... WHERE edat > ALL (SELECT edat ...)`	`... WHERE edat > (SELECT max(edat) ...)`
El major d'una taula	`... WHERE edat >= ALL (SELECT edat ...)`	`... WHERE edat = (SELECT max(edat) ...)`
No major	`... WHERE edat < ANY (SELECT edat ...)`	`... WHERE edat < (SELECT max(edat) ...)`
No menor	`... WHERE edat > ANY (SELECT edat ...)`	`... WHERE edat > (SELECT min(edat) ...)`
El menor d'una taula	`... WHERE edat <= ALL (SELECT edat ...)`	`... WHERE edat = (SELECT min(edat) ...)`
Menor que tots els d'altra taula	`... WHERE edat < ALL (SELECT edat ...)`	`... WHERE edat < (SELECT min(edat) ...)`
Igual a algun	`... WHERE edat = ANY (SELECT edat ...)`	`... WHERE edat IN (SELECT edat ...)`
Distint de tots	`... WHERE edat != ALL (SELECT edat ...)`	`... WHERE edat NOT IN (SELECT edat ...)`

Cal recordar que açò estaria mal

SQL
SELECT nom
    FROM alumnes
    WHERE edat > (SELECT edat FROM professors)

Mal, perquè estem comparant l'edat d'1 alumne amb MOLTES edats. Caldria posar ALL o ANY just davant de la subconsulta (segons què pretenem consultar).

Equivalències de subconsultes amb multitaula

Vegem un exemple de consulta equivalent a partir de les següents taules:

Text Only
1 2 3 4	`ALUMNES = codi + nom MATRÍCULES = alumne + assig + nota C. Ali: alumne → ALUMNES(codi)`

Amb subconsulta (tipus IN)Amb multitaula

SQL
SELECT nom
    FROM alumnes
    WHERE codi IN (SELECT alumne
                       FROM matricules
                       WHERE assig = 'BD');

SQL
SELECT nom
    FROM alumnes, matricules
    WHERE alumnes.codi = matricules.alumne
      AND assig = 'BD';

Per als alumnes NO matriculats de BD:

SQL
SELECT nom
    FROM alumnes
    WHERE codi NOT IN (SELECT alumne
                           FROM matricules
                           WHERE assig = 'BD');

Este cas no té una consulta multitaula equivalent.

La següent consulta NO seria correcta

SQL
SELECT nom
    FROM alumnes, matricules
    WHERE alumnes.codi = matricules.alumne
       AND assig != 'BD';

No donarà error però no és el que volem mostrar. Esta consulta mostrarà els alumnes matriculats d'alguna assignatura que no siga BD.

És a dir: les consultes amb subconsultes tipus IN (o bé = ANY) podrien resoldre's també com una consulta multitaula. Però no les de NOT IN.

Exercicis de subconsultes que retornen més d'una fila: ús d'ANY, ALL, IN, NOT IN (BD lliga1213)

Senzills:

Nom de l'equip amb més pressupost (de 2 formes: usant max i usant all).
Nom dels equips que no tenen el major pressupost (de 2 formes: usant max i usant any).
Noms de ciutats que no tenen equip (de 2 formes: usant all i usant in).

Més complicats:

Mostra els noms dels jugadors que cobren més que tot un altre equip sencer.
Nom dels jugadors que han marcat més gols que tot un altre equip sencer.
Nom dels jugadors que han marcat més gols que tot un altre equip sencer. També ha d'aparéixer el codi de l'equip del golejador i el codi de l'equip a qui supera. Ordenat per l'equip del golejador, nom del golejador i equip a qui supera.
Igual que l'exercici anterior però també ha d'aparéixer els gols del golejador i els gols de l'equip amb qui es compara.
Jugadors (equip i nom) que encara no han marcat cap gol. Ordenat per equip i nom.
Equip amb més jugadors.
Equip amb més jugadors i la quantitat de jugadors.
Equip amb més jugadors i equip amb menys jugadors. També ha d'aparéixer les quantitats i una parauleta al costat que diga "MAX" o "MIN".

4.3.3. L'operador EXISTS

Vegem primer un exemple. Volem mostrar els alumnes majors que algun professor. Diverses formes de fer-ho:

SQL
SELECT nom FROM alumnes WHERE edat > ANY (SELECT edat FROM professors);

SELECT nom FROM alumnes WHERE edat > (SELECT min(edat) FROM professors);

SELECT DISTINCT alumnes.nom          -- Cal posar DISTINCT per a no repetir
    FROM alumnes, professors         -- noms d'alumnes (ja que un alumne el
    WHERE alumnes.edat > professors.edat;   -- traurà tantes voltes com la quantitat
                                            -- de professors majors que ell hi hagen).

Però a vegades és més senzill expressar el requeriment així: "Mostrarem un alumne si EXISTEIX un professor més jove que eixe alumne". La consulta seria:

SQL
SELECT nom
    FROM alumnes
    WHERE EXISTS (SELECT *
                      FROM professors
                      WHERE professors.edat < alumnes.edat)

Veiem que l'enllaç entre la consulta principal i la subconsulta no es fa en el WHERE de la consulta principal sinó en el de la subconsulta. La subconsulta s'executa per a cada fila de la consulta principal, de forma que:

L'alumne A serà mostrat si existeix un professor de menor edat que eixe alumne A.
L'alumne B serà mostrat si existeix un professor de menor edat que eixe alumne B.
...

L'expressió WHERE EXISTS (subconsulta) produïx un resultat vertader sempre que en la subconsulta s'obtinga alguna fila, sense importar els camps seleccionats en la subconsulta (per això he posat * però podria posar qualsevol camp de la taula professors). I produirà un resultat fals en cas contrari: si la subconsulta no obté cap resultat.

Al principi del present apartat hem vist que allò que fem amb l'EXISTS ho podem fer amb altre tipus de sentències. Per tant, podríem viure sense l'EXISTS però ja hem dit que és una altra forma de plantejar la resolució de les consultes. A més, en els apartats següents vorem el NOT EXISTS, que sí que ajuda a resoldre diferents tipus de consultes.

Més exercicis de subconsultes que retornen més d'una fila: ús d'EXISTS, etc (BD lliga1213)

Mostra el nom de les ciutats que tinguen algun equip de futbol. Fes-ho almenys amb 3 solucions possibles: a. exists b. in c. multitaula (composició) d. multitaula (join)
Mostra el nom de les ciutats que no tinguen cap equip de futbol. Este exercici ja l'havíem fet amb "not in" i amb "!=all". Fes-lo ara amb el "not exists".
Noms d'equips que no tenen cap jugador amb el dorsal 2.

Ara vorem dos tipus de consultes per vore de quines formes se solen resoldre. Una forma serà amb el NOT EXISTS. Estes consultes són:

Seleccionar una fila si totes les files que estan relacionades amb ella compleixen una mateixa condició. ("Totes les que estan relacionades, complixen").

Per exemple: "Mostra els alumnes tals que totes les assignatures que s'ha matriculat són de 1r curs".
Seleccionar una fila si totes les files que compleixen una mateixa condició estan relacionades amb ella. ("Totes les que complixen, estan relacionades").

Per exemple: "Mostra els alumnes que s'han matriculat de totes les assignatures de 1r curs".

4.3.4. Seleccionar una fila si totes les files relacionades amb ella complixen una mateixa condició

Per exemple: "Mostra els alumnes que han aprovat TOTES les assignatures que s'han matriculat".

Text Only
1 2 3 4 5 6 7	`ALUMNES = alu + nom + nota_mitja ASSIGNATURES = ass + nom + curs MATRÍCULES = alu + ass + nota C. Ali: alu → ALUMNES(alu) C. Ali: ass → ASSIGNATURES(ass)`

Quan volem mostrar una fila (alumne) si un conjunt de files relacionades (assignatures que s'ha matriculat) complix una mateixa condició (estan aprovades), hi ha diverses possibles solucions, depenent de cada cas. Vegem-les:

a) Amb l'operador ALL
b) Comparant quantitats
c) Doble negació:
1. usant la quantitat 0
2. usant l'operador NOT EXISTS
3. usant l'operador NOT IN

Anem a vore diferents solucions per a una mateixa consulta: "Mostra els alumnes que ho han aprovat tot".

a) Amb l'operador ALL

"Mostra els alumnes on el 5 és menor o igual que totes les seues notes".

SQL
SELECT nom
    FROM alumnes
    WHERE 5 <= ALL (SELECT nota
                        FROM matricules
                        WHERE matricules.alu = alumnes.alu);

Nota

Hem de posar el ALL a la dreta de l'operador relacional. La següent consulta és sintàcticament incorrecta: ... WHERE ALL (SELECT ...) >= 5;

b) Comparant quantitats

"Mostra els alumnes on la quantitat de matrícules seues és igual a la quantitat de matrícules seues que estan aprovades".

Esta solució consisteix en comparar els COUNT de 2 subconsultes:

SQL
SELECT nom
    FROM alumnes
    WHERE (SELECT count(*)
               FROM matricules
               WHERE matricules.alu = alumnes.alu)
        =
          (SELECT count(*)
               FROM matricules
               WHERE matricules.alu = alumnes.alu
                  AND matricules.nota >= 5)

c) Doble negació

Si recordem una de les lleis de De Morgan, deia que: \(A = NOT (NOT A)\)

Per tant, trobarem una consulta equivalent amb la "doble negació". En el nostre exemple seleccionarem aquells alumnes que NO compleixen la condició contrària. És a dir: "Alumnes que no en tenen cap suspesa".

c.1) Doble negació: usant la quantitat 0c.2) Doble negació: usant l'operador NOT EXISTSc.3) Doble negació: usant l'operador NOT IN

"Mostra els alumnes on 0 és la quantitat de matrícules seues suspeses".

SQL
SELECT nom
    FROM alumnes
    WHERE 0 = (SELECT count(*)
                   FROM matricules
                   WHERE matricules.alu = alumnes.alu
                      AND matricules.nota < 5);

"Mostrarem un alumne si NO EXISTEIX una matrícula d'eixe alumne que no estiga aprovada":

SQL
SELECT nom
    FROM alumnes
    WHERE NOT EXISTS (SELECT *
                          FROM matricules
                          WHERE matricules.alu = alumnes.alu
                             AND matricules.nota < 5);

Per a cada fila de la consulta principal el NOT EXISTS produïx un resultat vertader quan no s'obté cap fila resultant en la subconsulta. Fals en cas contrari.

Veiem que les dos últimes solucions són pràcticament iguals. És a dir: són equivalents les següents expressions:

EXISTS / NOT EXISTS	Comparar quantitat amb 0
`... WHERE EXISTS (SELECT * FROM ...)`	`... WHERE 0 < (SELECT count(*) FROM ...)`
`... WHERE NOT EXISTS (SELECT * FROM ...)`	`... WHERE 0 = (SELECT count(*) FROM ...)`

"Mostra els alumnes que no estan entre els alumnes que tenen alguna matrícula suspesa".

SQL
SELECT nom
    FROM alumnes
    WHERE alu NOT IN (SELECT alu
                          FROM matricules
                          WHERE nota < 5);

Un altre exemple

"Mostra els alumnes tals que totes les assignatures que s'ha matriculat són de 1r curs".

Solució a) ALLSolució b) Comparar quantitatsSolució c.1) Doble negació. Quantitat 0Solució c.2) Doble negació. NOT EXISTSSolució c.3) Doble negació. NOT IN

SQL
SELECT nom
    FROM alumnes
    WHERE 1 = ALL (SELECT assignatures.curs
                       FROM matricules, assignatures
                       WHERE matricules.ass = assignatures.ass
                          AND matricules.alu = alumnes.alu);

SQL
SELECT nom
    FROM alumnes
    WHERE (SELECT count(*)
               FROM matricules
               WHERE matricules.alu = alumnes.alu)
        =
          (SELECT count(*)
               FROM matricules, assignatures
               WHERE matricules.ass = assignatures.ass
                  AND matricules.alu = alumnes.alu
                  AND assignatures.curs = 1);

SQL
SELECT nom
    FROM alumnes
    WHERE 0 = (SELECT count(*)
                   FROM matricules, assignatures
                   WHERE matricules.ass = assignatures.ass
                      AND matricules.alu = alumnes.alu
                      AND assignatures.curs != 1);

SQL
SELECT nom
    FROM alumnes
    WHERE NOT EXISTS (SELECT *
                      FROM matricules, assignatures
                      WHERE matricules.ass = assignatures.ass
                         AND matricules.alu = alumnes.alu
                         AND assignatures.curs != 1);

SQL
SELECT nom
    FROM alumnes
    WHERE alu NOT IN (SELECT matricules.alu
                          FROM matricules, assignatures
                          WHERE matricules.ass = assignatures.ass
                             AND assignatures.curs != 1);

Exercicis de subconsultes: els relacionats complixen una condició (BD lliga1213)

Equip (nom llarg) que en tots els partits (jugats, clar) ha superat el 65% de possessió del baló jugant en casa. !!! note "Nota" En les solucions dels exercicis, este està resolt amb les 5 solucions vistes, de forma detallada i esquemàtica.
Equip (nom llarg) que en tots els partits (jugats, clar) ha superat el 55% de possessió del baló jugant fora.
Nom curt dels equips que mai han perdut a casa.
Dades dels equips que hagen jugat en casa contra tots els equips.
Equips que haja jugat tots els partits de la ciutat de Madrid fora de casa.
Equips on els han marcat més de 20 gols a cadascun dels seus porters (a tots).

4.3.5. Seleccionar una fila si totes les files que compleixen una mateixa condició estan relacionades amb ella

Per exemple: "Mostra els alumnes que s'han matriculat de totes les assignatures de 1r curs".

Quan volem mostrar una fila (alumne) si complix la condició d'estar relacionada amb MOLTES altres files (matriculat de totes les assignatures de 1r), hi ha diverses possibles solucions, depenent de cada cas. Vegem-les:

a) Amb l'operador ALL
b) Comparant quantitats
c) Doble negació:
1. usant la quantitat 0
2. usant l'operador NOT EXISTS
3. usant l'operador NOT IN

a) Amb l'operador ALL

En els casos de l'apartat 4.3.4 sí que era possible solucionar-ho amb el ALL però no en els casos del 4.3.5.

b) Comparant quantitats

"Mostra els alumnes on la quantitat de matrícules d'assignatures de 1r seues és igual a la quantitat d'assignatures de 1r".

SQL
SELECT nom
    FROM alumnes
    WHERE (SELECT count(*)
               FROM matricules, assignatures
               WHERE matricules.ass = assignatures.ass
                 AND assignatures.curs = 1
                 AND matricules.alu = alumnes.alu)
        =
          (SELECT count(*)
               FROM assignatures
               WHERE assignatures.curs = 1);

c) Doble negació

"Alumnes tals que no hi ha una assignatura de 1r de la qual no estiguen matriculats".

c.1) Doble negació: usant la quantitat 0c.2) Doble negació: usant l'operador NOT EXISTSc.3) Doble negació: usant l'operador NOT IN

"Mostra els alumnes on 0 és la quantitat d'assignatures de 1r que no està matriculat eixe alumne".

SQL
SELECT nom
    FROM alumnes
    WHERE 0 = (SELECT count(*)
                   FROM assignatures
                   WHERE curs = 1
                      AND ass NOT IN (SELECT ass
                                          FROM matricules
                                          WHERE matricules.alu = alumnes.alu));

"Mostrarem un alumne si NO EXISTEIX una assignatura de 1r de la qual no estiga matriculat eixe alumne".

SQL
SELECT nom
    FROM alumnes
    WHERE NOT EXISTS (SELECT *
                          FROM assignatures
                          WHERE curs = 1
                             AND ass NOT IN (SELECT ass
                                                 FROM matricules
                                                 WHERE matricules.alu = alumnes.alu));

"Mostra els alumnes que no estan entre els alumnes on hi ha alguna assignatura de 1r que no estan matriculats".

Es podria fer però és massa complicat: ixen 3 subconsultes niuades.

Nota

Cal anar en compte amb la solució de comparar quantitats, ja que no sempre podrà servir de la forma com ho hem fet. Per exemple:

"Nom dels ciclistes que han portat tots els mallots".

La següent solució NO serviria:

SQL
SELECT nom
    FROM ciclistes
    WHERE (SELECT count(*)
               FROM mallots)
        =
          (SELECT count(*)
               FROM portar
               WHERE portar.ciclista = ciclistes.dorsal)

El problema és que potser un mallot l'ha dut diverses vegades... i altre mallot cap! El que farem serà comptar els DISTINTS mallots que ha portat el ciclista. Això es pot fer amb count(DISTINCT mallot):

SQL
SELECT nom
    FROM ciclistes
    WHERE (SELECT count(*)
               FROM mallots)
        =
          (SELECT count(DISTINCT mallot)
               FROM portar
               WHERE portar.ciclista = ciclistes.dorsal)

Conclusió

Hi ha moltes solucions per als exercicis sobre "tots...". Cal fer-se uns exemples de registres de les taules, intentar vore quina fila compleix el que es demana i traduir-ho a SQL. En altre fitxer teniu esquemes de com plantejar este tipus d'exercicis i les distintes solucions per a cada exercici. Trieu la que trobeu més fàcil.

Exercicis de subconsultes: totes les que complixen una condició estan relacionades amb ella

Exercicis sobre la següent base de dades:

Text Only
1 2 3 4 5 6 7	`ARTICLES = art + nom + família + preu_venda PROVEIDORS = pro + nom + tipus SUBMINISTRES = art + pro + preu_compra C. Ali: art → ARTICLES(art) pro → PROVEIDORS(pro)`

Mostra les dades dels proveïdors que subministren tots els articles de la família "f1".
Mostra les dades dels articles que són subministrats per tots els proveïdors de tipus "t1".

Exercicis sobre la base de dades lliga1213:

Equips que hagen jugat fora de casa en totes les ciutats on hagen de jugar fora de casa. !!! note "Nota" Este és bastant complicat. Teniu les solucions al fitxer corresponent.

4.3.6. Llocs on poden haver subconsultes

a) En clàusules WHERE i HAVING de sentències SELECT

Ja ho hem vist als apartats anteriors.

b) En la clàusula SELECT de sentències SELECT

Este tipus de subconsultes sol usar-se quan necessitem diferents condicions per a diferents columnes de la select.

Per exemple: De cada alumne, mostra el seu nom, de quantes assignatures està matriculat i quantes assignatures ha aprovat.

SQL
SELECT nom, (SELECT COUNT(*)
                 FROM matricules m
                 WHERE m.alu = alumnes.alu)  AS matricules,

            (SELECT COUNT(*)
                 FROM matricules m
                 WHERE m.alu = alumnes.alu
                   AND m.nota >= 5)          AS aprovades

    FROM alumnes;

c) En la clàusula FROM de sentències SELECT (taula derivada)

Sol usar-se quan volem usar una funció d'agregat respecte d'altres valors calculats amb altra funció d'agregat (per exemple, un MAX d'un SUM).

Per exemple: Mostra la quantitat màxima de gols que ha marcat un equip jugant en casa.

SQL
SELECT max(gols)
    FROM (SELECT sum(golsc) AS gols
              FROM partits
              GROUP BY equipc) AS golsequips;

IMPORTANT

És necessari posar un àlies a la subconsulta que fa de taula derivada.

d) En la clàusula WHERE de sentències DELETE

"Esborra aquells alumnes que no estan matriculats de res":

SQL
DELETE FROM alumnes
    WHERE alu NOT IN (SELECT alu
                          FROM matricules);

e) En les clàusules SET i WHERE de sentències UPDATE

"Puja mig punt en el camp nota_mitja dels alumnes que ho tenen tot aprovat".

SQL
UPDATE alumnes
    SET nota_mitja = nota_mitja + 0.5
    WHERE alu IN (SELECT alu
                      FROM matricules
                      GROUP BY alu
                      HAVING min(nota) >= 5);

"Posa la nota mitja dels alumnes amb la mitja de les matrícules de cadascú":

SQL
UPDATE alumnes
    SET nota_mitja = (SELECT avg(nota)
                          FROM matricules
                          WHERE matricules.alu = alumnes.alu);

En este exemple veiem que en les subconsultes d'un update (o delete) també podem enllaçar amb la taula que s'actualitza.

Compte!

No podem fer UPDATE o DELETE d'una taula que també està en la subconsulta.

SQL
UPDATE matricules
    SET nota = nota + 0.1
    WHERE nota IN (SELECT min(nota)
                       FROM matricules
                       WHERE ...
                       GROUP BY ...);

Text Only
1	`Error Code: 1093. You can't specify target table 'matricules' for update in FROM clause`

SQL
DELETE FROM matricules
    WHERE nota = (SELECT min(nota) FROM matricules);

Solució: usar una taula derivada en la subconsulta:

SQL
-- Solució 1: usant una taula derivada amb totes les files i columnes de la taula
UPDATE matricules
    SET nota = nota + 0.1
    WHERE nota IN (SELECT min(nota)
                       FROM (SELECT * FROM matricules) AS matricules_deriv
                       WHERE ...
                       GROUP BY ...);

SQL
-- Solució 2: usant una taula derivada amb només les files i columnes necessàries
UPDATE matricules
    SET nota = nota + 0.1
    WHERE nota IN (SELECT nota_min
                       FROM (SELECT min(nota) AS nota_min
                                 FROM matricules
                                 WHERE ...
                                 GROUP BY ...) AS matricules_deriv);

Exercicis de subconsultes en DELETE, UPDATE i clàusula SELECT (BD lliga1213)

Esborra les ciutats que no tinguen equip de futbol.
Incrementa 1000 euros a cada jugador que haja guanyat algun partit el seu equip.
Incrementa 1000 euros a cada jugador per cada partit que haja guanyat el seu equip.
Incrementa 1000 euros a cada porter que ha rebut menys de 20 gols.
Incrementa 1000 euros a cada jugador per cada gol marcat.
Mostra de cada equip el total de gols marcats en els partits i el total de gols marcats pels jugadors d'eixe equip. És a dir: el primer valor s'obtindria de la taula de partits i l'altre s'obtindria de la taula de golejadors. No té per què quadrar, ja que potser s'han marcat gols en pròpia porta.

EQUIPC	EQUIPF	RESULTAT
ath	bet	2
bar	rso	1
cel	mga	2
dep	osa	1
lev	atm	x
mal	esp	1
ray	gda	1
rma	val	x
sev	get	1
zar	vad	2

EQUIP	PARTITS	RESULTAT
ath	11	guanyats
ath	8	empatats
ath	16	perduts
atm	22	guanyats
atm	6	empatats
atm	8	perduts
bar	29	guanyats
...	...	...

EQUIPC	EQUIPF	RESULTAT
ath	bet	2
bar	rso	1
cel	mga	2
dep	osa	1
lev	atm	x
mal	esp	1
ray	gda	1
rma	val	x
sev	get	1
zar	vad	2

EQUIPC	EQUIPF	RESULTAT
ath	bet	2
bar	rso	1
cel	mga	2
dep	osa	1
lev	atm	x
mal	esp	1
ray	gda	1
rma	val	x
sev	get	1
zar	vad	2