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.

...

SELECT LSS.rep_gene_symbol AS "Gene Symbol",
       COUNT(*) AS "Number of SNPs"
FROM my_snps MS
INNER JOIN _loc_snp_summary AS LSS ON (MS.snp_id = LSS.snp_id)
GROUP BY LSS.rep_gene_symbol
ORDER BY COUNT(*) DESC

Execute Query

Note that in this example we have used the AS keyword after column specifications in the SELECT statement. This has the effect of naming the columns in the results of the query. This is useful when functions and other complex expressions are used in the query.

...