SQL consultes II

En aquest apartat realitzarem consultes una mica més elaborades i que implicaran més d'una taula. Per dur això a terme, cal que ampliem una mica més la sintaxi del SELECT.

Per afegir diverses taules necessitarem la sentència INNER JOIN. Però amb això no n'hi ha prou, i ens caldrà la partícula ON per relacionar les taules entre sí. Si no les relacionem correctament amb ON la consulta sortirà errònia.

MOLT IMPORTANT: a partir d'ara les columnes no es podran posar directament el nom i prou, sinó que caldrà especificar a quina taula pertanyen amb la sintaxi <taula>.<columna>

La sintaxi més complerta ens quedarà:

SELECT <nom_taula1>.<nom_columna1>, <nom_taula2>.<nom_columna2>, ...
FROM <nom_taula1> INNER JOIN <nom_taula2>, <nom_taula3>, ...
ON <taula1>.<columna1>=<taula2>.<columna2> , etc.
WHERE ... ;

A l'exemple de la base de dades de UFs podem realitzar algunes consultes. Aquesta ens permet fer un llistat d'assignatures amb la seva titulació.

Podem saber directament la correspondència entre titulacions i assignatures amb la consulta:

mysql> SELECT titulacio.titulacio, assignatures.nom, assignatures.id_assignatura
    -> FROM titulacio INNER JOIN assignatures
    -> ON titulacio.id_titulacio=assignatures.id_titulacio;

Fixeu-vos molt bé amb els INNER JOIN i les condicions del ON per relacionar les taules entre sí.

I si volem saber, a més, filtrar les que corresponen a "SMX" podem fer:

mysql> SELECT titulacio.titulacio, assignatures.nom, assignatures.id_assignatura
    -> FROM titulacio INNER JOIN assignatures
    -> ON titulacio.id_titulacio=assignatures.id_titulacio
    -> WHERE titulacio.titulacio LIKE "%SMX%";

Exercici

Realitzeu les següents consultes. Enregistreu-les com a views a la vostra base de dades amb el nom "consulta_1_1", "consulta_2_3", etc. (els 2 números són la tongada i el número de consulta).

IMPORTANT: en aquestes consulets no ha d'aparèixer cap ID. Són consultes de veritat, és a dir, que donen informació a una persona externa que no coneix la estructura interna de la base de dades. Els ID que acostumem a utilitzar és un número intern i, per tant, a ningú l'interessa (a part del programador).

Tampoc es podran utilitzar els IDs en les clàusules WHERE, és a dir, que heu de fer els filtres amb LIKE i posant cadenes de caràcters. Per exemple, si vull mostrar les assignatures de SMX, no s'ha de posar:

SELECT ... WHERE id_titulacio=3;

sinó:

SELECT ... WHERE titulacio.titulacio LIKE "%SMX%";

Avanti!!

• Tongada 1 (amb 2 taules)

1. 1. Llistat d'assignatures amb la seva titulació.
   2. Llistat d'assignatures de la titulació de SMX.
   3. Llistat d'assignatures de la ESO.
   4. Llistat d'assignatures amb el nom del professor que les imparteix.
   5. Llistat de UFs de l'assignatura "Seguretat informàtica".
   6. Llistat de UFs de l'assignatura d'ofimàtica.
   7. Llistat de UFs de l'assignatura FOL.
   8. Nombre total de matrícules del curs 2008-2009
   9. Nombre total de matrícules del curs 2009-2010

• Tongada 2

1. 1. ...ja arribarà...
   2. ...