MySQL

1. Esercitazioni MySQL

2. Ripasso • Connessione a MySQL. • Creazione delle basi di dati e delle tablelle. • Inserimento dei dati. • Interrogazioni.

3. Alcuni Esercizi • Descrizione della struttura di SDB (Sequence DataBase). • Descrizione dei principali campi di SDB. • Interrogazioni

4. 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.

5. Tabelle delle Sequenze • SAMPLE_PLAN. • CONTAINER. • REPORTER. • DUPLICATES. • MAPPING. • MAPPING_8KRG.

6. Tabelle dei Blast • BLAST_EST. • BLAST_GEN. • BLAST_NORM.

7. Tabelle di Clustering • CLUSTERS. • CLUSTERS_CONSENSUS.

8. Alcuni Campi della Base di Dati • Testo delle Sequenze. • Campi di Blast_gen: • Chromosome; • Contig_start; • STS; • HomologyPerc.

9. 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?

10. 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';

11. 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).

12. 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%';

13. 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%';

14. 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%';

15. 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.

16. 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

17. 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.

18. 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?

19. Esercizio 5 Costruite 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.