Informatica 3 Liceo Scientifico Scienze Applicate/Query tramite SELECT

Interrogare il database mediante il comando Select

Per poter eseguire una interrogazione (query) del database utilizziamo il comando SELECT del linguaggio SQL

Con questo comando e' possibile decidere quali colonne visualizzare (proiezione), quali righe visualizzare (selezione) e quali tabelle unire (congiunzione)

Per prima cosa vediamo di capire come si interroga un database partiamo da un database in cui ci sono 2 tabelle collegate da una associazione 1:N

studenti

IDStudente	Nome	Cognome	DataNascita	CittaNascita	Altezza
1	Marco	Rossi	21/1/1980	Rovigo	178
2	Luca	Verdi	25/12/1978	Padova	180
3	Silvia	Blu	13/9/1997	Rovigo	173
4	Michele	Giallo	14/9/1997	Padova	167
5	Anna	Rossi	23/2/1995	Venezia	180

compiti

IDCompito	Materia	Data	Voto	Tipo	IDstudente
1	matematica	13/10/2014	7	scritto	1
2	storia	23/10/2014	8	scritto	3
3	matematica	13/11/2014	6	orale	1
4	matematica	13/11/2014	7	orale	4
5	geografia	15/5/2013	10	orale	4
6	inglese	13/4/2013	2	scritto	2
7	storia	3/10/2016	4	scritto	1

L'istruzione per interrogare ( query) un database e' l'struzione SELECT, la sua struttura e' la seguente

select  listacolonne
from  listatabelle
where condizionesullerighe
group by  listacolonneoggettoraggruppamento
having condizionesuiraggruppamenti
order by listacolonneoggettodiordinamento ;

per partire però partiamo lavorando su una sola tabella .

PROIEZIONE

1) Vogliamo ottenere il nome e cognome di tutti gli studenti, il comando e':

select nome,cognome
from studenti ;

in pratica questo comando isola solo alcune colonne della tabella studenti (l'operazione di selezione di alcune colonne e' detta proiezione) quello che otteniamo da questa query e' una tabella ( con due sole colonne e con tutte le righe della tabella studenti, quindi il grado diminuisce e la cardinalita' si conserva, come capita in tutte le operazioni di proiezione)

Query1

Nome	Cognome
Marco	Rossi
Luca	Verdi
Silvia	Blu
Michele	Giallo
Anna	Rossi

se si preferisce possiamo cambiare l'ordine delle colonne

2) Vogliamo ottenere il cognome e nome di tutti gli studenti, il comando e':

select cognome,nome
from studenti ;

Query2

Cognome	Nome
Rossi	Marco
Verdi	Luca
Blu	Silvia
Giallo	Michele
Rossi	Anna

3) Vogliamo ottenere il cognome e nome di tutti gli studenti,ordinando per cognome il comando e':

select cognome,nome
from studenti
order by cognome ;

Query3

Cognome	Nome
Blu	Silvia
Giallo	Michele
Rossi	Marco
Rossi	Anna
Verdi	Luca

4) Vogliamo ottenere il cognome e nome di tutti gli studenti,ordinando per cognome e nel caso di cognome uguale ordinando anche per nome il comando e':

select cognome,nome
from studenti
order by cognome,nome ;

Query4

Cognome	Nome
Blu	Silvia
Giallo	Michele
Rossi	Anna
Rossi	Marco
Verdi	Luca

il comando query4 poteva essere scritto

select cognome,nome
from studenti
order by cognome ASC,nome ASC ;

dove ASC significa ordine ascendente (dal piu' piccolo al piu' grande) e puo' essere omesso perché e' il valore di default

5) Vogliamo ottenere il cognome e nome di tutti gli studenti,ordinando per cognome e nome in senso discendente (zorro prima di annibale) il comando e':

select cognome,nome
from studenti
order by cognome DESC ,nome DESC ;

Query5

Cognome	Nome
Verdi	Luca
Rossi	Marco
Rossi	Anna
Giallo	Michele
Blu	Silvia

6) dopo aver visto l'ordinamento ritorniamo alla nostra proiezione (selezione delle colonne) possiamo voler ottenere tutte le colonne della tabella studenti si puo' scrivere

select IDStudente,Nome,Cognome,DataNascita,CittaNascita,Altezza
from Studenti ;

o piu' comodamente

select *
from studenti ;

dove * significa tutte le colonne

7) vogliamo ottenere tutte le citta' in cui sono nati gli studenti, il comando e'

select CittaNascita
from studenti ;

otteniamo

Query6

CittaNascita

Rovigo

Padova

Rovigo

Padova

Venezia

per evitare le ripetizioni delle stesse voci si usa DISTINCT

select DISTINCT CittaNascita
from Studenti ;

ottenendo

Query7

CittaNascita

Rovigo

Padova

Venezia

se le volete ordinate

select DISTINCT CittaNascita
from Studenti
order by Cittanascita ;

8) possiamo rinominare le colonne del risultato specificando l'attributo AS

select IDStudente AS Matricola, Cognome, Nome 
from Studenti ;

otteniamo

Query8

Matricola	Nome	Cognome
1	Marco	Rossi
2	Luca	Verdi
3	Silvia	Blu
4	Michele	Giallo
5	Anna	Rossi

pensiamo di avere la tabella rettangoli

IdRettangolo	Base	Altezza
1	10	5
2	15	20

come sapete per evitare problemi di ridondanza i campi che possono essere calcolati da altri campi della tabella non vengono inseriti nella tabella nel nostro caso quindi non e' possibile aggiungere il campo area rettangolo. E' invece possibile visualizzare un campo calcolato mediate una query, non esistendo il nome del campo calcolato nella tabella gli viene fornito un nome di default del tipo campo1, campo2 etc, possiamo usando l'AS decidere noi un nome particolare per la colonna calcolata

select IdRettangolo,Base,Altezza, Base*Altezza AS AreaRettangolo
from rettangoli ;

IdRettangolo	Base	Altezza	AreaRettangolo
1	10	5	50
2	15	20	300

quando si costruisce l'espressione di un campo calcolato si possono usare anche delle funzioni matematiche come sin( ) cos( ) tang( )etc

9) il risultato di una query e' una tabella ( che nei casi piu' semplici puo' diventare una sola colonna oppure una sola riga oppure una sola cella) puo' essere salvata come una particolare tabella di cui possiamo scegliere il nome e riutilizzata per ulteriori interrogazioni

10) nel caso di ambiguita' (piu tabelle con lo stesso nome del campo) per indicare un particolare campo si specifica il nomedellatabella.nomecampo ad esempio

studenti.Cognome

altre volte il nome del campo/tabella contiene degli spazi e allora va racchiuso fra parentesi quadre ad esempio [Nome Studente]

Selezione

Spesso quando si ricerca qualcosa non si vogliono come risultato tutte le righe della tabella, si impone allora una o piu' condizioni che le righe devono soddisfare per ricadere nella soluzione, queste condizioni possono essere composte utilizzando gli operatori di confronto

<
<=
>
>=
<>      diverso
=       uguale 

e gli operatori logici

AND
OR
NOT

11) selezioniamo tutti gli studenti alti piu' di 175 e nati a Rovigo

select *
from studenti
where altezza>175 AND CittaNascita='Rovigo' ;

il where permette di decidere quali righe filtrare (selezionare, questa operazione di selezione diminusce la cardinalita' e mantiene il grado), quindi il select isola le colonne (ne riduce il grado) mentre il where ne riduce la cardinalita' (numero di righe)

12) selezioniamo tutti gli studenti nati a Rovigo o a Padova

select *
from studenti
where CittaNascita='Rovigo' OR CittaNascita='Padova' ;

questo comando puo' essere scritto in modo diverso, dicendo che il valore del campo CittaNascita deve assumere uno dei valori specificati in un elenco tramite il comando IN

select *
from studenti
where CittaNascita IN ('Rovigo','Padova') ;

per specificare un particolare gruppo di valori che un campo deve avere, posso usare il comando LIKE e i simboli _ e % . Il simbolo _ significa un carattere qualsiasi (un solo carattere); il simbolo % significa una sequenza di caratteri qualsiasi di lunghezza qualsiasi (anche nulla).

Nel programma Microsoft Access si usano i simboli ? e * . Il simbolo ? sta per _, invece il simbolo * sta per % .

se voglio tutti gli studenti il cui conome inizia con la lettera R posso allora scrivere

select *
from studenti
where cognome LIKE 'R%' ;

se voglio tutti gli studenti che hanno un cognome che termina in so scrivo allora

select *
from studenti
where cognome LIKE '%so' ;

se voglio tutti gli studenti che hanno un cognome che contiene la sequenza os scrivo allora

select *
from studenti
where cognome LIKE '%os%' ;

se voglio tutti gli studenti con un cognome di 5 caratteri di cui il primo carattere e' R

select *
from studenti
where cognome LIKE 'R____' ;

____ sono 4 underscore consecutivi

e cosi' via

13) se si vogliono ottenere tutti gli studenti la cui data di nascita' e' compresa fra due particolari date si può utilizzare l'operatore BETWEEN ( che serve per specificare un intervallo di valori, da un valore minimo a un valore max)

selezionare tutti gli studenti nati fra il 1/1/1985 e il 25/12/1985

select *
from studenti
where DataNascita BETWEEN '01/01/1985' AND '25/12/1985' ;

oppure per selezionare tutti gli studenti con altezza compresa fra 178 cm e 185 cm possiamo scrivere

select *
from studenti
where Altezza BETWEEN 178 AND 185 ;

per lavorare con il tipo di dato date ci sono diverse funzioni per isolare anno, mese, giorno e per fare calcoli con le date ( per poi poter scrivere condizioni visualizzare tutte le fatture con data di pagamento che scadono entro 30 giorni, visualizzare le buste paga del mese di maggio etc)

funzione	descrizione
now()	restituisce la data e ora corrente
curdate()	restituisce la data corrente
dateadd()	aggiuge a una data un certo numero di giorni
datediff()	calcola il numero di giorni che intercorrono fra due date
extract( )	estrae da una data o dataora una specifica parte : anno mese giorno ora secondi
datesub()	sottrae a una data un certo numero di giorni
dateformat()	visualizza la data in un formato specifico

il formato della data e' spesso il seguente ( ed e' fonte di errori se non lo si sa), ad esempio

  tipo dato       formato
   DATE           YYYY-MM-DD
   DATETIME       YYYY-MM-DD HH:MI:SS
   TIMESTAMP      YYYY-MM-DD HH:MI:SS
   YEAR           YYYY or YY

14 nelle condizioni di ricerca qualche volta si vuole trovare un campo che e' stato lasciato vuoto (lasciato vuoto non vuol dire con dei caratteri spazio o invio" per individuare questi campi si usa is null oppure nel caso opposto is not null

select *
from studenti
where cognome is null ;

15) certe volte si vuole scrivere una condizione legata a un parametro scelto dall'utente nel momento dell'esecuzione della query ad esempio trovare tutti gli studenti con altezza maggiore di un valore specificato dall'utente

select *
from studenti
where altezza > [specificare altezza] ;

quando eseguita . all'utente compare una finestra popup che gli domanda il valore della'altezza, il valore digitato dall'utente sara' utilizzato per eseguire la query al posto del parametro [specificare altezza]

16) se vogliamo estrarre solo un certo numero di righe ad esempio visualizzare i primi 5 studenti in ordine alfabetico utilizziamo la clausola TOP

select TOP 5 *
from studenti
order by Cognome

oppure selezionare (nome cognome eta) lo studente piu' alto

select TOP 1 nome,cognome,altezza
from studenti
order by Altezza DESC

Funzioni di aggregazione

17) qualche volta si vuole calcolare il valor medio, max, minimo, o la somma dei valori numerici di una particolare colonna o contare il numero di righe estratte, per farlo si usano le funzioni di aggregazione ;

COUNT(nomecolonna)      conta il numero di righe della tabella ottenuta
AVG (nomecolonna)       calcola il valor medio 
MAX (nomecolonna)       calcola il valore massimo
MIN (nomecolonna)       calcola il valore minimo
SUM (nomecolonna)       calcola la somma dei valori di una certa colonna

Quando si vuole utilizzare una o piu' funzioni di aggregazione tutti gli elementi posti dopo il select devono essere funzioni di aggregazione

calcolare l'altezza massima degli studenti

select max(altezza)
from studenti;

non si può scrivere

select nome, max(altezza)
from studenti;

( per ottenere questo risultato bisogna procedere con una subquery spiegata successivamente o con un TOP 1 applicato su un elenco ordinato)

per calcolare l'altezza min media e max

select min(altezza) as altezzaminima ,avg(altezza) as altezzamedia, max(altezza) as altezzamassima
from studenti;

per calcolare la somma di tutte le aree dei diversi rettangoli

select sum( base*altezza) as areatotale
from rettangoli;

per contare quanti studenti sono nati a rovigo posso scrivere

select count(*)
from studenti
where CittaNascita = 'Rovigo';

in questo caso quando faccio il conteggio delle righe non ha importanza la particolare colonna a cui si riferisce e quindi si può utilizzare il simbolo * invece di specificare una particolare colonna, comunque

select count(cognome)
from studenti
where CittaNascita = 'Rovigo';

avrebbe restituito lo stesso risultato

se voglio calcolare quante sono le città di nascita (distinte, quindi conto Rovigo una sola volta ...)

select count(distinct(CittaNascita))
from studenti;

Congiunzione

18) Diversamente da tutte le precedenti query spesso l'interrogazione coinvolge diverse tabelle ( spesso quelle di un legame 1:N) pensiamo di avere le seguenti tabelle

Tabella1

IDStudente	Nome
1	Marco
2	Luca
3	Anna
4	Eva

e la tabella

Tabella2

CodiceStudente	Voto
1	6
3	8
3	10
7	7

le due tabelle non sono 2 tabelle del tipo 1:N per evidenziare meglio le possibili congiunzioni.

Ora se scriviamo

select *
from Tabella1,tabella2

si ottengono tutte le colonne e righe del prodotto fra le 2 tabelle ( il prodotto fra le 2 tabelle e' dato da tutte le possibili combinazioni delle righe della prima tabella con le righe della seconda) quindi si ottiene

IDStudente	Nome	CodiceStudente	Voto
1	Marco	1	6
1	Marco	3	8
1	Marco	3	10
1	Marco	7	7
2	Luca	1	6
2	Luca	3	8
2	Luca	3	10
2	Luca	7	7
3	Anna	1	6
3	Anna	3	8
3	Anna	3	10
3	Anna	7	7
4	Eva	1	6
4	Eva	3	8
4	Eva	3	10
4	Eva	7	7

si vede che il prodotto non esprime nessun legame logico fra i campi della prima e della seconda tabella

se si vuole specificare un legame logico fra le due tabelle (di solito la corrispondenza fra chiave primaria e chiave esterna corrispondenti di due tabelle legate da un legame 1:N) si deve scrivere

select *
from Tabella1,Tabella2
where Tabella1.IDStudente= Tabella2.CodiceStudente;

oppure

select *
from tabella1 INNER JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente;

oppure

select *
from tabella1 JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente;

in questo caso l'unione delle tabelle unisce fra loro solo le righe della prima e seconda tabella in cui i campi IDStudente e Codicestudente si corrispondono si ga allora un INNER-JOIN o semplice JOIN

IDStudente	Nome	CodiceStudente	Voto
1	Marco	1	6
3	Anna	3	8
3	Anna	3	10

nella congiunzione della tabella abbiamo perso qualche studente perché non c'era un corrispondente elemento nella 2 tabella

sono possibili anche altri tipi di congiunzione nel caso del LEFT-JOIN si collegano tutte le righe della prima tabella (left) con quelle corrispondenti della seconda e nel caso non ci fosse una riga corrispondente nella seconda tabella con una riga vuota, in questo modo siamo sicuri che tutte le righe della prima tabella compaiono

select *
from tabella1 LEFT JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente

IDStudente	Nome	CodiceStudente	Voto
1	Marco	1	6
2	Luca
3	Anna	3	8
3	Anna	3	10
4	Eva

analogamente nel caso di RIGHT-JOIN tutte le righe della seconda tabella si uniscono con quelle della prima e se non c'e' una corrispondente riga nella prima con una riga vuota

select *
from tabella1 RIGHT JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente

IDStudente	Nome	CodiceStudente	Voto
1	Marco	1	6
3	Anna	3	8
3	Anna	3	10

esiste anche la possibilita' di congiungere una tabella con se stessa collegando lo stesso campo o campi diversi in questo caso si parla di SELF-JOIN

esiste infine anche la possibilita' di congiungere la tabella1 con la tabella2 facendo in modo che compaiano tutte le righe della prima e della seconda (se non c'e' una riga corrispondente viene aggiunta una riga vuota) in questo caso si parla di FULL-JOIN

select *
from tabella1 FULL JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente