Wiki Administrator: 1 revision

2012-03-13T00:04:01Z

1 revision

← Older revision	Revision as of 00:04, 13 March 2012
(No difference)

MediaWiki default at 16:38, 14 March 2011

2011-03-14T16:38:58Z

New page

Below are some illustrative examples of [[:Wikipedia:SQL|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.

[[Category:Bioscreen]]

SQL Examples - Revision history

Wiki Administrator: 1 revision

MediaWiki default at 16:38, 14 March 2011