MySQL

1
MySQL

• Esercitazioni

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 gt 100
• select count(Reporter_ID) from REPORTER where
  Length gt 100
• select Reporter_ID, E_Value_Best_Match from
  BLAST_NORM where E_Value_Best_Match gt 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)gt100 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)gt100 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 gt 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 gt
  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 gt 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 gt 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 gt
  1e-100
• Order by e limit
• select Reporter_ID, E_Value_Best_Match,
  Chromosome from BLAST_GEN where
  E_Value_Best_Match1e-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_Subj
  ect_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.