SQL Examples

Below are some illustrative examples of SQL queries that may be run from the Bioscreen reports pages.

Basic SQL queries

Query timestamps from a table

ts from ligands

This query will pull all timestamps (last-modified times) from the ligands table, and shows the basic entry1, entry2, ... from table1, table2 ... form of all SQL queries.

Query a limited amount of entries from a table

name, uniprotID, annotation from proteins limit 20

This query highlights the use of the limit clause to limit the amount of results. This clause is most useful when paired with some order clause.

Query entries with specific values from a table

molID, inchiKey, commonName from ligands where nmrPlate = '1' and nmrWell like 'a%'

This query shows the basic form of a query where only certain results are returned based on their field values. When an exact match is desired, an equals sign is required. Inexact matches require the use of like, and percent signs may be used as wildcards. It's always best to use single-quotes around field values, even though they're only required for strings.

Query a sorted list of entries from a table

molID, inchiKey, commonName from ligands where nmrPlate='1' and nmrWell like 'a%' order by estimatedPrice desc

This query builds on the previous query by sorting the results according to price, in descending order (hence the desc qualifier). Simply omit the qualifier to sort in ascending order.

Query a numeric value from a table

molID, mass from ligands where mass < 100

This query shows the numerical less-than operator being used in a query. Also worthy of note is the <> operator, which denotes inequality for numeric and non-numeric fields.

Query multiple values of a given field using subqueries

molID, inchiKey, commonName, catalogNumber, mass from ligands where nmrPlate in (1, 3, 5)

This query will return the relevant fields of all ligands table entries with nmrPlate values of 1, 3 or 5.

More advanced SQL queries

Query using calculated fields

molID, concat('P', nmrPlate, nmrWell) as nmrPlateWell from ligands where nmrPlateWell like 'P2A%'

Multiple fields may be concatenated together and treated as one field in queries like so. In the above example, the nmrPlateWell field does not actually exist as a field in the ligands table, but is created on the fly using the values of other tables. the nmrActive, nmrMix, msActive and msMix ligands fields are more complex examples of this behavior.

Query fields involving multiple tables

ligands.molID from ligands, mixtures, mixture_ligands
  where mixtures.id = mixture_ligands.mixtureID
  and ligands.id = mixture_ligands.ligandID
  and mixtures.creator like '%brad%'

This query highlights the use of an implicit inner join (comma) to link two tables together. The above query links the ligands and mixtures tables together through the mixture_ligands table based on primary keys in the first two tables.