Guida SQLITE

Viste le numerose richieste da parte dei fanz ecco una guida a sqlite, programma per creare e gestire database.
Un database non e' altro che una tabella farcita di dati e ogni operazione andra' conclusa con il punto e virgola (";"). Apri sqlite dal terminale con:

sqlite3 nome_file.sqlite

(se il file non esiste verra' creato nuovo).
Crea una tabella con la seguente stringa:

create table nome_tabella(attributo_1, attributo_2, ecc..., primary key(attributo_1/2/ecc...));

Ok, abbiamo creato una tabella. Come inseriamo valori? Cosi':

insert into nome_tabella values(valore_attributo_1, valore_attributo_2, ecc...);

Ricordate di mettere le virgolette se inserite valori diversi da numeri!
Ora vogliamo visualizzare i dati inseriti dunque:

select * from nome_tabella;

In questo modo vedremo tutti gli attributi dei dati inseriti (lo abbiamo richiesto con "*"), per vedere solo un certo attributo basta sostituire l'asterisco con il nome dell'attributo che ci interessa, ad esempio:

select attributo_2 from nome_tabella;

Bene, per visualizzarlo meglio digitiamo le stringhe seguenti:

.mode column

.header on

Ottimo! Riproviamo con il

select * from nome_tabella;

e vedremo una tabella meglio organizzata.
______________________________________________________________

Come colleghiamo un'altra tabella? Bisogna crearla e fare in modo che contenga quella creata precedentemente quindi:

CREATE TABLE nome_tabella_2(attributo_1,attributo_2,attributo_ecc...,primary key(attributo_principale), foreign key (prima_tabella) references prima_tabella(attributo_prima_tabella));

Nel caso di un collegamento molti a molti procediamo cosi: prendiamo come esempio queste 2 tabelle:

CREATE TABLE docente(cognome, materia, primary key(cognome));
CREATE TABLE classe(sezione, anno, primary key(sezione,anno));

Per completare il collegamento collegamento di tipo molti a molti dobbiamo creare ora una terza table che includa le due precedenti:

CREATE TABLE insegnamento(cognome, sezione, anno, primary key(cognome,sezione,anno), foreign key (cognome) references docente(cognome), foreign key (sezione,anno) references classe(sezione,anno));

Ecco, cosi' dovreste risolvere la maggior parte dei problemi. Ciao!

______________________________________________________________

Viste le numerose richieste da parte dei fanz ecco un'estensione della guida per INTERROGARE un database
Una volta creato un database e inseriti dei valori possiamo interrogarla.
Scarichiamo questo database: http://server/~ferri-emanuele/database/veterinario/db.sqlite
Questi sono i quesiti richiesti: http://server/~ferri-emanuele/database/veterinario/quesiti.txt

Aprite il database scaricato.
Come mostriamo l'elenco delle patologie? Il comando e' nella forma

select colonna_tabella from nome_tabella;

Dunque nel nostro caso select * from patologia;
L'* serve per mostrare tutte le colonne (in questo caso la tabella patologia ne ha solo una). Se volessimo mostrare due colonne scriviamo i nomi di entrambe separate da una virgola.
Ora selezioniamo tutti i padroni con il telefono aggiungendo una specificazione mediante comando where:

select * from padrone where telefono not null;

Ok, ora tutti i cani meticci (codice, nome):

select nome,codice from cane where razza="meticcio";

Quindi il comando where e' molto utile per aggiungere dei limiti alla ricerca e selezionare piu' specificamente i campi da cercare.
Adesso tutti i cani vaccinati nel 2008 contro la rabbia:

select * from vaccinazione where data>"2008-01-01" and data<"2009-01-01" and patologia="rabbia";

In questo modo abbiamo aggiunto piu' di una limitazione mediante il comando where utilizzando la congiunzione and.
Abbiamo appena visto la base di ricerca nei database.


______________________________________________________________

Passiamo alla join, un procedimento che consente di ricercare parametri collegando due tabelle. Per farlo basta apporre una virgola tra i nomi di due tabelle, ad esempio nella forma:

select nome_tabella1.colonna,ecc... from nome_tabella1,nome_tabella2;

Ricorda di specificare il nome della tabella di cui vuoi mostrare la colonna per evitare casi di omonimia (anche dopo il where).
Proviamo a fare questo: nominativo e numero di telefono dei padroni dei cani vaccinati contro la rabbia.

select padrone.nominativo,padrone.telefono from padrone,vaccinazione where vaccinazione.patologia="rabbia";

Ottimo! Se vogliamo togliere pino scotto che vive nella giungla (ridere fa bene) ed e' di conseguenza senza numero modifichiamo cosi':

select padrone.nominativo,padrone.telefono from padrone,vaccinazione where vaccinazione.patologia="rabbia" and padrone.telefono not null;

Dato che ci ripete le info più volte inseriamo il comando "distinct" subito dopo il "select".
Con questo comando possiamo risolvere i quesiti successivi.
Occupiamoci delle operazioni insiemistiche: tutti i cani mai vaccinati:

select nome from cane except select cane from vaccinazione;

Il comando except fa la differenza tra due tabelle.
Vi sono poi i comandi "intersect" per intersecare i valori di due tabelle (prendere solo i valori in comune) e "union" per prendere i valori non in comune.

______________________________________________________________

Per salvare le ricerche possiamo creare delle view. Per esempio usando il database della lezione precedente e riconsiderando tutti i cani mai vaccinati:

select nome from cane except select cane from vaccinazione;

Possiamo salvare questa query con il semplice comando:

CREATE VIEW nome_view as ricerca;

Nel nostro caso con la ricerca che abbiamo deciso di salvare verra':

CREATE VIEW Bonny as select nome from cane except select cane from vaccinazione;

E per richiamare la ricerca salvata:

select * from Bonny;

Ecco. Ricorda che la view viene ricreata ogni volta quindi aggiornando la tabella si aggiornera' di conseguenza.

______________________________________________________________

Adesso ci occupiamo di comandi speciali:
count: conta gli elementi di una colonna
avg: fa la media dei valori di una colonna
sum: fa la somma dei valori di una colonna
max: riporta il valore massimo di una colonna
min: riporta il valore minimo di una colonna
La cui sintassi e' la seguente nel caso del count:

select count(*) from nome_tabella;

Al posto dell'asterisco possiamo inserire una colonna specifica della tabella.

Iniziamo con il database sulle donazioni di sangue reperibile qui: http://copernico.duckdns.org/~ferri-emanuele/database/donazioni/db.sqlite
E i quesiti che stanno qua: http://copernico.duckdns.org/~ferri-emanuele/database/donazioni/questiti.txt
Proviamo a outputtare il numero dei donatori:

select count(*) as NumeroDonatori from donatore;

dove as NumeroDonatori da' un nome piu' decente alla colonna.
Proviamo a contare il numero dei gruppi sanguigni dei donatori:

select count(ab0) as NumeroDonatori from donatore;

Cio' equivale a contare come abbiamo fatto prima.
Se vogliamo distinguere i gruppi e contare solo quanti gruppi diversi ci sono:

select count(distinct ab0) as NumeroDonatori from donatore;

Adesso contiamo quante persone hanno un determinato gruppo sanguigno. Se scriviamo:

select ab0,rh,count(*) as NumeroGruppiDisponibili from donatore;

ci fa collassare tutti i gruppi come se fossero B+. Per distinguere il numero di donatori in base al gruppo dobbiamo usare il comando 'group by':

select ab0,rh,count(*) as NumeroGruppiDisponibili from donatore group by ab0,rh;

Ecco, cosi' e' il top.
Per ultimo calcoliamo il numero di donazioni per ogni donatore:

select count(*) from donazione,donatore where donazione.donatore=donatore.cf group by donatore;

Cosi' pero' non capiamo chi ha donato. Modifichiaml:

select cf,count(*) as NumeroDonazioni from donazione,donatore where donazione.donatore=donatore.cf group by donatore;

Brr brr

______________________________________________________________

IN e NOT IN
Grazie a questi comandi possiamo selezionare elementi in maniera che può tornare utile. Continuiamo l'indagine del database precedente e andiamo a vedere i quesiti della sezione IN e NOT IN.
Come selezioniamo i donatori residenti a topolinia e paperopoli?

select * from donatore where residenza in ("topolinia",'paperopoli');

Come vedete con l'IN possiamo scrivere una lista (usando le parentesi e separando gli elementi con la virgola) con cui verificare l'uguaglianza.
Per trovare quelli che non abitano in nessuna delle due basta mettere il NOT davanti all'IN.

______________________________________________________________

Query annidate
Le query annidate permettono di fare ricerche incastrando piu' selezionamenti insieme.
Per farlo dopo il from possiamo inserire una select da cui selezionare ulteriormente elementi piu' specifici.
Come possiamo svolgere il primo quesito, cioe' contare i diversi gruppi sanguigni disponibili?
Intanto outputtiamo i diversi gruppi sanguigni:

select distinct ab0,rh from donatore group by ab0,rh;

Ottimo, ora contiamo gli elementi includendo il select appena creato:

select count(*) as NumeroGruppi from (select ab0,rh from donatore group by ab0,rh);

Ecco!
Ora contiamo quelli non disponibili:

select count(*) as NumeroGruppi from (select distinct * from gruppo except select ab0,rh from donatore group by ab0,rh);

In questo caso davanti alle colonne ab0 e rh del donatore possiamo mettere il distinct togliendo il group by.
Per ultimo cerchiamo le ultime (plurale) donazioni effettuate con anche il nominativo del donatore:

select donatore,data from donazione where data=(select max(data) from donazione);