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 clàusula JOIN. Però amb això no n'hi ha prou, i ens caldrà la clàusula 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> 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 JOIN assignatures
    -> ON titulacio.id_titulacio=assignatures.id_titulacio;

Fixeu-vos molt bé amb els 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 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!!

1. Tongada 1 (amb 2 taules)
   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
2. Tongada 2 (amb 3 taules)
   1. Recompte de les UFs de l'assignatura "Muntatge i manteniment d'equips".
   2. Recompte de UFs per assignatura.
   3. Llistat de UFs de les assignatures que NO siguin de SMX.
   4. Llistat de UFs en què estigui matriculat l'alumne Pere Escribano.
   5. Llistat de qualificacions (assignatura i UF) de l'alumne Òscar Tabuada.
   6. Mitjana de les qualificacions dels alumnes matriculats el curs 2008-2009.
   7. Recompte de qualificacions aprovades dels alumnes del curs 2009-2010.
   8. Mitjana de les qualificacions de l'alumne David Dalmau.
3. Tongada 3 (amb 4 taules)
   1. Llistat d'alumnes aprovats de les UFs de l'assignatura "Sistemes Operatius Monolloc".
   2. Llistat d'alumnes aprovats de la UF "Components d'un sistema informàtic".
4. Tongada 4 (amb 5 taules)
   1. Llistat d'alumnes aprovats de les assignatures impartides pel professor Enric Mieza.

Exercici 2

Amb la base de dades de pel·lícules Fitxer:Videoclub.sql realitza les següents consultes.

NOTA: en aquest exercici NO ES PERMET UTILITZAR CAP ID. Totes les condicions de consultes s'han de fer a través de la columna que figura en l'enunciat. Per exemple, si volem les pel·lícules de gènere "MANGA" cal que la condició sigui genere.nom="MANGA", i no pas utilitzant l'ID de gènere.

1. Llistat de films amb la seva categoria.
2. Llistat de films amb el seu gènere.
3. Llistat de films de gènere "manga" (ha d'aparèixer també el nom del gènere per comprovar la consulta).
4. Llistat de lloguers (IDs,data de sortida) amb els noms dels clients.
5. Llistat de lloguers (IDs,data de sortida) amb els noms dels films.

OJU, amb 3 taules:

6. Llistat de lloguers amb la ID de lloguer, la data de sortida, el nom del client i el nom del film.
7. Llistat de lloguers (igual que l'anterior més la població) però només dels clients de Manresa.

A sako, amb 4 taules:

8. Llistat de lloguers amb ID, data, nom de client, nom del film i nom del gènere del film.
9. Llistat de lloguers (igual que l'anterior) però només dels que tinguin gènere ACCIÓ.