Page Comparison

In the Advanced Query section of the BioQ::Query page you may enter a precise SQL query that will be passed to our MySQL database server. A tutorial on writing SQL queries can be found here, and general documentation on MySQL can be found here.

e

You can specify any table from the current database, which in thise case is 1000 Genomes IP1 Release. You can also access other databases using database.table notation, such as bioq_dbsnp_human_134.snp_summary.

...

SELECT LSS.snp_id,
       LSS.ancestral_alleles
FROM _loc_snp_summary AS LSS
INNER JOIN my_snps AS MS ON (LSS.snp_id = MS.snp_id) 

Execute Query

Joining several tables

More than two tables may joined together as illustrated by the example below. Note that the column snp_id, which is sometimes a unique identifier for a table, is not unique in the dbSNP table b134_SNPContigLocusId_37_2. This is because the variant may map to multiple contigs and gene transcripts and functional data may exist for separate alleles. It may therefore be necessary to include columns such as ctg_id, contig_acc and allele in order to explain the multiplicity.

SELECT LSS.snp_id,
       LSS.ancestral_alleles,
       SNP.avg_heterozygosity,
       SCLI.locus_symbol,
       SCLI.ctg_id,
       SCLI.contig_acc,
       SCLI.allele
FROM _loc_snp_summary AS LSS
INNER JOIN my_snps AS MS ON (LSS.snp_id = MS.snp_id)
INNER JOIN SNP ON (LSS.snp_id = SNP.snp_id)
INNER JOIN b134_SNPContigLocusId_37_2 AS SCLI ON (LSS.snp_id = SCLI.snp_id) 

Execute Query

Counting rows

The MySQL COUNT function can be used to compute the number of rows in a table. The following query counts the number of SNPs in the table SNP - a dbSNP table with summary information on each unique reference SNP or rs ID. The query results in a table with a single column named COUNT(*) and a single row showing the number of SNPs.

...