10 – 11 join molti a molti ed a N tabelle, group by , having, select annidate (subquery)

Tenendo presente l’esempio della lezione precedete, vogliamo ora collegare gli atleti con le discipline praticate.

select cognome, nome, nomeDisciplina from atleti, discipline;

In questo caso non abbiamo nessuna unione tra i campi della tabella atleti e quella discipline. Un atleta potrebbe praticare più discipline e una disciplina potrebbe avere più atleti che la praticano. Per risolvere il problema, abbiamo bisogno di una tabella che raccolga l’id dell’atleta e l’id della disciplina. Creaiamo quindi la tabella atletidiscipline  e inseriamola nel from della select

select cognome, nome, nomeDisciplina from atleti, atletidiscipline, discipline;

ora creiamo la condizione di join che lega idAtleta (tabella atleti) con atleta (tabella atletidiscipline)

where idAtleta=atleta

e idDisciplina (tabella atletidiscipline) con disciplina (tabella discipline)

and idDisciplina=disciplina;

unisco le join

where idAtleta=atleta and idDisciplina=disciplina;

unisco la join con la select e ordino per cognome e nome

select cognome, nome, nomeDisciplina 
from atleti, atletidiscipline, discipline 
where idAtleta=atleta and idDisciplina=disciplina 
order by cognome, nome;

Questo è il codice che lega le tre tabella con una relazione MOLTI A MOLTI, perché a più discipline corrispondono più atleti.

Volendo aggiungere anche la nazione ecco il codice:

and idNazione=nazione;

Questa è una congiunzione join N A MOLTI, in quanto per una nazione possono corrispondere più atleti. Aggiungo la seconda JOIN

select cognome, nome, nomeDisciplina, nomeNazione 
from atleti, atletidiscipline, discipline, nazioni 
where idAtleta=atleta and idDisciplina=disciplina and idNazione=nazione
order by cognome, nome;

Riassumendo la select estrae i campi che sono indipendenti dalle tabelle specificate nella from. Basta esaminarle 1 ad 1, man mano che si scrivono, e verificare se ci sono dei collegamenti tra una tabella ed un altra e in quel caso esplicitarlo con il where e la chiave primaria dell’una con quella esterna dell’altra.

GRUPPI

Facciamo l’esempio che sapere quanti atleti abbiamo in base ad una età. In questo caso dobbiamo estrarre le età dalla tabella atleti, raggrupparle GROUP BY e chiedere un conteggio del numero delle righe e stamparle in una colonna che chiamaremo numero atleti:

select eta, count(*) as 'numero atleti' 
from atleti group by eta;

I campi che compaiono dopo la select DEVONO comparire obbligatoriamente anche dopo il group by. Sono esclusi i count, <, >, ace e sum.

Con l’esempio sopra, ho un raggruppamento in base alle età, per esempio vengono raggruppati tutti quelli che hanno 18 anni e messi vicino uno all’altro. In questi raggruppamenti, potrei escludere quelli di una certa nazione:

select eta, count(*) as 'numero atleti' 
from atleti where nazione != 3 
group by eta;

dal raggruppamento età escludo quelli della nazione con id 3, il where nazione != 3 va messo dopo il from .

Per esempio voglio escludere dal raggruppamento i risultati di età dove gli atleti sono inferiore a 2, cioè quelli con 1 atleta :

select eta, count(*) as 'numero atleti' 
from atleti where nazione != 3 
group by eta having count(*)>1 ;

Il count(*) è maggiore di 1. Escludo ora i diciottenni:

select eta, count(*) as 'numero atleti' 
from atleti where nazione != 3 
group by eta having eta != 18 ;

Voglio ora sapere quante discipline praticano ogni atleta:

select cognome, count(*)
from atleti, atletidiscipline 
where idAtleta=atleta
group by idAtleta, cognome;

da notare che la regola che il campo incluso nella select deve essere specificato anche ne group by è rispettata, perché idAtleta è contenuto in group by e non viceversa (non poteva essere incluso solo nella select). Vogliamo escludere la disciplina 100 metri, ma dalla sua descrizione, non dal suo id

select cognome, count(*)
from atleti, atletidiscipline 
where idAtleta=atleta and nomeDisciplina != '100m'
group by idAtleta, cognome;

dobbiamo aggiungere la tabella discipline  sul quale il campo nomeDisciplina si trova e aggiungere la condizione di join tra discipline e le tabelle ad essa collegate (soltanto aletidiscipline in questo caso)

select cognome, count(*)
from atleti, atletidiscipline, discipline 
where idAtleta=atleta and idDisciplina=disciplina and nomeDisciplina != '100m'
group by idAtleta, cognome;

In questo caso vengono tolti dai gruppi tutte le righe che nella descrizione delle discipline si trova 100m. Ordiniamo i risultati in modo decrescente

select cognome, count(*)
from atleti, atletidiscipline, discipline 
where idAtleta=atleta and idDisciplina=disciplina and nomeDisciplina != '100m'
group by idAtleta, cognome order by cognome desc;

SELECT ANNIDATE

Prendiamo l’età media degli atleti

select avg(eta) from atleti;

Ora voglio selezionare gli atleti più giovani, cioè quelli che hanno un età inferiore alla media appena estratta. Dobbiamo fare il conteggio su un’ulteriore select

select cognome, nome from atleti where eta < (select avg(eta) from atleti);

Questa è una condizione dove ho una select nidificata all’interno di un’altra.

Voglio ora ottenere gli atleti con età massima

select cognome, nome from atleti where eta = (select max(eta) from atleti);

ora voglio: elenco delle nazioni con atleti con più di 14 anni

select nomenazione from nazioni;

ho estratto l’elenco di tutte le nazioni ora cerco tutti i codici nazione che appaiono nell’elenco unendo le nazioni con gli atleti imponendo che l’età superi i 14 anni

where idnazione = any ()

dove il cod nazione appare in . Appare in si traduce in SQL in = any .

where idnazione = any (select idnazione from nazioni)

la seconda select dice: produci l’elenco dei codici  delle nazioni

select idnazione from nazioni, atleti where idnazione=nazione and eta>14

che combinate con la tabella atleti (join idnazione e atleti) e età superiore a 14. Ecco il codice completo

select nomenazione from nazioni
where idnazione = any (select idnazione from nazioni, atleti where idnazione=nazione and eta>14);