MySQL
Esercitazioni
Ripasso
●
Connessione a MySQL.
●
Creazione delle basi di dati e delle tablelle.
●
Inserimento dei dati.
●
Interrogazioni.
Alcuni Esercizi
●
Descrizione della struttura di SDB (Sequence DataBase).
●
Descrizione dei principali campi di SDB.
●
Interrogazioni
Struttura dell'SDB
●
Base di dati Relazionale, costituita da tabelle di tipo myisam .
●
Utilizzato per registrare i dati di:
– Sequenze Nucleotidiche;
– Esecuzioni di Blast;
– Esecuzioni di Clustering.
Tabelle delle Sequenze
●
SAMPLE_PLAN .
●
CONTAINER .
●
REPORTER .
●
DUPLICATES .
●
MAPPING .
●
MAPPING_8KRG .
Tabelle dei Blast
●
BLAST_EST .
●
BLAST_GEN .
●
BLAST_NORM .
Tabelle di Clustering
●
CLUSTERS .
●
CLUSTERS_CONSENSUS.
Alcuni Campi della Base di Dati
●
Testo delle Sequenze.
●
Campi di Blast_gen:
– Chromosome;
– Contig_start;
– STS;
– HomologyPerc.
Esercizio 1
●
Selezionare gli identificatori e la lunghezza delle sequenze più lunghe di 100 paia di basi.
– Selezionare il numero di sequenze più lunghe di 100 paia di basi.
●
Selezionare i risultati di blast ( BLAST_NORM ) che hanno un evalue maggiore di 10
-100.
●
Selezionare i risultati del blast genomico
( BLAST_GEN ) che sono più lunghi di 100 paia di basi e che si trovano sul cromosoma 3.
– E se volessi quelli sul cromosoma X?
Soluzioni Esercizio 1
●
select Reporter_ID, Length from REPORTER where Length
> 100;
– select count(Reporter_ID) from REPORTER where Length > 100;
●
select Reporter_ID, E_Value_Best_Match from BLAST_NORM where E_Value_Best_Match > 1e-100;
●
select BLAST_GEN.Reporter_ID, BLAST_GEN.Sx,
BLAST_GEN.Sy, BLAST_GEN.Chromosome from BLAST_GEN where (BLAST_GEN.Sy-BLAST_GEN.Sx)>100 and
BLAST_GEN.Chromosome = 3;
– select BLAST_GEN.Reporter_ID, BLAST_GEN.Sx, BLAST_GEN.Sy, BLAST_GEN.Chromosome from BLAST_GEN where (BLAST_GEN.Sy- BLAST_GEN.Sx)>100 and BLAST_GEN.Chromosome = 'X';
Esercizio 2
●
Selezionate l'identificatore, la
lunghezza ed il cromosoma di tutte le sequenze il cui nome comincia con
5000.
●
Selezionare tutte le sequenze di
lunghezza maggiore di 100 che sono in relazione con p53 (hanno un risultato di blast correlato con un'annotazione di
p53).
Soluzione – Esercizio 2 (1)
●
Normale
– select REPORTER.Reporter_ID, REPORTER.Length,
BLAST_GEN.Chromosome from REPORTER, BLAST_GEN where REPORTER.Reporter_ID = BLAST_GEN.Reporter_ID and REPORTER.Reporter_ID like '5000%';
●
Inner Join
– select REPORTER.Reporter_ID, REPORTER.Length,
BLAST_GEN.Chromosome from REPORTER inner join BLAST_GEN on REPORTER.Reporter_ID = BLAST_GEN.Reporter_ID where
REPORTER.Reporter_ID like '5000%';
●
Natural Join
– select REPORTER.Reporter_ID, REPORTER.Length,
BLAST_GEN.Chromosome from REPORTER natural join BLAST_GEN where REPORTER.Reporter_ID like '5000%';
Soluzione – Esercizio 2 (2)
●
Right Join
– select REPORTER.Reporter_ID, REPORTER.Length,
BLAST_GEN.Chromosome from REPORTER right join BLAST_GEN on REPORTER.Reporter_ID =BLAST_GEN.Reporter_ID where
REPORTER.Reporter_ID like '5000%';
●
Left Join
– select REPORTER.Reporter_ID, REPORTER.Length,
BLAST_GEN.Chromosome from REPORTER left join BLAST_GEN on REPORTER.Reporter_ID = BLAST_GEN.Reporter_ID where
REPORTER.Reporter_ID like '5000%';
Soluzione – Esercizio 2 (3)
●
Soluzione
– select REPORTER.Reporter_ID from REPORTER, BLAST_NORM where REPORTER.Reporter_ID = BLAST_NORM.Reporter_ID and
REPORTER.Length > 100 and BLAST_NORM.Annotation like '%p53%';
●
Usando left join
– select REPORTER.Reporter_ID from REPORTER left join BLAST_NORM on REPORTER.Reporter_ID = BLAST_NORM.Reporter_ID where
REPORTER.Length > 100 and BLAST_NORM.Annotation like '%p53%';
●
Left Join con Using
– select REPORTER.Reporter_ID from REPORTER left join BLAST_NORM using (Reporter_ID) where REPORTER.Length > 100 and
BLAST_NORM.Annotation like '%p53%';
Esercizio 3
●
Quante sono le sequenze che hanno un match in BLAST_EST con percentuale di
omologia maggiore del 99%?
●
Contare il numero di risultati di blast per ogni sequenza di BLAST_NORM .
– Resituire solo quelli che hanno più di 3 risultati.
●
Le query con order by e limit .
Soluzioni – Esercizio 3
●
Distinct
– select distinct(BLAST_EST.Reporter_ID) from BLAST_EST where BLAST_EST.HomologyPerc > 99 order byReporter_ID;
●
Count
– select Reporter_ID, count(Ordr) from BLAST_NORM group by Reporter_ID;
– select Reporter_ID, count(Ordr) from BLAST_NORM group by Reporter_ID having E_Value_Best_Match > 1e-100;
● Order by e limit
– select Reporter_ID, E_Value_Best_Match, Chromosome from
BLAST_GEN where E_Value_Best_Match=1e-100 order by Chromosome limit 0,30
Esercizio 4
●
Selezionare, dalla tabella BLAST_NORM le sequenze che hanno lo stesso NCBI_Subject_ID della sequenza
8RG3CGA11 .
●
Per casa: selezionate tutte le sequenze che hanno Poly_C nella stessa posizione della
sequenza 5000ABC06 o della sequenza 5000AAE09 .*
– sugg. provate prima con una sola sequenza.
●
Per casa: trovare quante sono le sequenze che hanno la stessa lunghezza purché diversa da zero.***
–
sugg. è una query che coinvolge la tabella
REPORTERcon
se stessa, si devono usare gli alias.
Soluzioni – Esercizio 4
●
Self query
– select BLAST_NORM1.Reporter_ID, BLAST_NORM1.NCBI_Subject_ID, BLAST_NORM2.NCBI_Subject_ID from BLAST_NORM as BLAST_NORM1, BLAST_NORM as BLAST_NORM2 where BLAST_NORM1.NCBI_Subject_ID = BLAST_NORM2.NCBI_Subject_ID and BLAST_NORM2.Reporter_ID =
'8RG3CGA11' order by BLAST_NORM1.NCBI_Subject_ID;
– select Reporter_ID from BLAST_NORM where NCBI_Subject_ID = (select NCBI_Subject_ID from BLAST_NORM where Reporter_ID = '8RG3CGA11');
●
Ho detto per casa o no?
Esercizio 5
Costruite una base di dati che abbia le seguenti caratteristiche:
●
Mantenga i dati su sequenze nucleotidiche.
– Testo della Sequenza.
– Lunghezza delle sequenze.
●
