Transcript MySQL - uniud.it

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.
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.SyBLAST_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.SyBLAST_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
–
●
Inner Join
–
●
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%';
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
–
●
Usando left join
–
●
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%';
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.***
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.
Mantenga i dati sui ricercatori cui
appartengono le sequenze.
–
●
Dati Anagrafici.
– Sequenze possedute.
–