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 REPORTER con 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 5Costruite una base di dati che abbia le
seguenti caratteristiche:● Mantenga i dati su sequenze nucleotidiche.
– Testo della Sequenza.– Lunghezza delle sequenze.
● Mantenga i dati sui ricercatori cui appartengono le sequenze.– Dati Anagrafici.– Sequenze possedute.
Top Related