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!!
- Tongada 1 (amb 2 taules)
- Llistat d'assignatures amb la seva titulació.
- Llistat d'assignatures de la titulació de SMX.
- Llistat d'assignatures de la ESO.
- Llistat d'assignatures amb el nom del professor que les imparteix.
- Llistat de UFs de l'assignatura "Seguretat informàtica".
- Llistat de UFs de l'assignatura d'ofimàtica.
- Llistat de UFs de l'assignatura FOL.
- Nombre total de matrícules del curs 2008-2009
- Nombre total de matrícules del curs 2009-2010
- Tongada 2 (amb 3 taules)
- ...ja arribarà...
- ...
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.
- Llistat de films amb la seva categoria.
- Llistat de films amb el seu gènere.
- Llistat de films de gènere "manga" (ha d'aparèixer també el nom del gènere per comprovar la consulta).
- Llistat de lloguers (IDs,data de sortida) amb els noms dels clients.
- Llistat de lloguers (IDs,data de sortida) amb els noms dels films.
OJU, amb 3 taules:
- Llistat de lloguers amb la ID de lloguer, la data de sortida, el nom del client i el nom del film.
- Llistat de lloguers (igual que l'anterior més la població) però només dels clients de Manresa.
A sako, amb 4 taules:
- Llistat de lloguers amb ID, data, nom de client, nom del film i nom del gènere del film.
- Llistat de lloguers (igual que l'anterior) però només dels que tinguin gènere ACCIÓ.
