Question

write an SQL query

A related table to snp151 is named snp151CodingDbSnp, which stores data about changes in protein sequence related to SNPs. For this query, you will extract data from both tables using a join. The query results should have the SNP name, chromosome, strand and start position from the snp151 table, AND the transcript, codons & peptides from the snp151CodingDbSnp table. Q2: Modify Q1 to only include SNPs that are in frame 2. Q3: Modify Q2 to only include SNPs that involve a change to or from a methionine (M). Q4: Modify Q3 by joining another table, snp151OrthoPt5Pa2Rm8, which has data for orthologous alleles in chimp, orangutan and rhesus macaque. To clarify, there will be a total of three tables joined. From this third table add the fields for the chimp chromosome and chimp start coordinate. Q5: Modify Q4 to select SNPs where the chimp ortholog is unknown. To do this, check if the chimp chromosome has a value of “?”.  

Answer 1

Sol:

1.

select A.SNP name, A.chromosome, A.strand, A.start position, B.transcript, B.codons, B.peptides from

snp151 A INNER JOIN snp151CodingDbSnp B on

A.SNP name=B.SNP name

2.

select A.SNP name, A.chromosome, A.strand, A.start position, B.transcript, B.codons, B.peptides from

snp151 A INNER JOIN snp151CodingDbSnp B on

A.SNP name=B.SNP name and A.frame='2'

3.

select A.SNP name, A.chromosome, A.strand, A.start position, B.transcript, B.codons, B.peptides from

snp151 A INNER JOIN snp151CodingDbSnp B on

A.SNP name=B.SNP name and A.frame='2' and (a.codon='AUG' OR b.codon='AUG')

4.

select A.SNP name, A.chromosome, A.strand, A.start position, B.transcript, B.codons, B.peptides, C.chimp_chromosome, C.chimp_start_coordinate from

snp151 A INNER JOIN snp151CodingDbSnp B on

A.SNP name=B.SNP name and A.frame='2' and (a.codon='AUG' OR b.codon='AUG')

INNER JOIN snp151OrthoPt5Pa2Rm8 C on

B.chromosome=C.chimp_chromosome

NOTE: Make sure that the original columns have the same names as mentioned in this query, cause I have assumed names as mentioned in the problem statement.