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.