SQL Examples


Basic Queries

Show the first 10 items in a table (e.g. drugs):
SELECT * FROM drugs LIMIT 10;
Get the total number of items in a table (e.g. drugs):
SELECT COUNT(*) FROM drugs;
Get the number of each record type (Drug, Category, Metabolite, etc.) in the database:
SELECT record_type, COUNT(*) FROM accession_numbers GROUP BY record_type;

Drug Queries

Drugs are linked to categories in a many-to-many relationship via the drug_categorizations table. To get the categories for each drug:
SELECT d.name, d.drugbank_id, c.title AS category FROM drugs d
  JOIN drug_categorizations dc ON dc.drug_id = d.id
  JOIN categories c            ON dc.category_id = c.id
  ORDER BY d.drugbank_id;
Get drugs and their pharmacology information:
SELECT d.drugbank_id, d.name, p.* FROM drugs d
  JOIN pharmacologies p ON d.id = p.drug_id
  ORDER BY d.drugbank_id;
Get drugs and their reactions:
SELECT d.drugbank_id, d.name, r.* FROM drugs d
  JOIN reactions r ON r.drug_id = d.id
  ORDER BY d.drugbank_id;
Drug interactions are found in the structured_drug_interactions table. This is linked to the drugs table via the subject_drug_id and affected_drug_id columns. To get drugs and their interactions with other drugs:
SELECT d.drugbank_id, d.name, sdi.* FROM drugs d
  JOIN structured_drug_interactions sdi ON sdi.subject_drug_id = d.id OR sdi.affected_drug_id = d.id
  ORDER BY d.drugbank_id;
Drug categories are linked to different vocabularies in the category_mappings table. To get drugs with their categories and category ATC codes:
SELECT DISTINCT drg.name, cat.title AS category_title, map.code, map.vocabulary_level FROM drugs drg
  JOIN drug_categorizations dcs ON drg.id = dcs.drug_id
  JOIN categories cat           ON cat.id = dcs.category_id
  JOIN category_mappings map    ON map.category_id = cat.id and map.vocabulary = 'ATC'
  ORDER BY drg.name;
Drugs are linked to different vocabularies in the drug_mappings table. To get drugs with their ATC codes:
SELECT DISTINCT drg.name, map.code, map.vocabulary_level FROM drugs drg
  JOIN drug_mappings map ON map.drug_id = drg.id and map.vocabulary = 'ATC'
  ORDER BY drg.name;
Drugs are linked to clinical trials via the clinical_trial_interventions_drugs and clinical_trial_interventions tables. To get drugs with their clinical trial identifiers:
SELECT d.name, d.drugbank_id, t.identifier AS trial_identifier FROM clinical_trials t
  JOIN clinical_trial_interventions i        ON i.trial_id = t.identifier
  JOIN clinical_trial_interventions_drugs id ON id.intervention_id = i.id
  JOIN drugs d                               ON id.drug_id = d.id
  ORDER BY d.drugbank_id;
Drugs are linked to targets, enzymes, carriers, and transporters via the bonds table. To get drugs and their bonded entities:
SELECT d.name, b.type, be.name AS bond_name FROM drugs d
  JOIN bonds b         ON b.drug_id = d.id
  JOIN bio_entities be ON be.biodb_id = b.biodb_id
  ORDER BY d.name;

Product Queries

When querying products, there are 4 boolean columns in particular that can be used to filter various kind of products:

approved:'1' = approved product,'0' = non-approved product
otc:'1' = over-the-counter product,'0' = prescription product
generic:'1' = generic product,'0' = branded/non-generic
mixture:'1' = compound/mixture product,'0' = single-drug product

The ingredients table creates a many-to-many relationship between products and drugs. Get all products and their drug ingredients:
SELECT * FROM products p
  JOIN ingredients i ON i.product_id = p.id
  JOIN drugs d       ON d.id = i.drug_id
  ORDER BY p.id;
Get all approved prescription products:
SELECT p.id, p.name FROM products p
  WHERE p.approved = '1' AND p.otc = '0' AND p.generic = '0' AND p.mixture = '0'
  ORDER BY p.name;
Get all approved generic prescription products:
SELECT p.id, p.name FROM products p
  WHERE p.approved = '1' AND p.otc = '0' AND p.generic = '1' AND p.mixture = '0'
  ORDER BY p.name;
Get all approved generic prescription products containing a given drug ingredient (e.g. Abacavir, drug_id = 1048):
SELECT p.id, p.name FROM products p
  JOIN ingredients drug_filter ON drug_filter.product_id = p.id
  WHERE drug_filter.drug_id = 1048 AND p.approved = '1' AND p.otc = '0' AND p.generic = '1' AND p.mixture = '0'
  ORDER BY p.name;
Get all brand mixtures and their ingredient drugs:
SELECT p.id, p.name, d.drugbank_id, d.name AS drug_name FROM products p
  JOIN ingredients i ON i.product_id = p.id
  JOIN drugs d       ON i.drug_id = d.id
  WHERE p.mixture = '1'
  ORDER BY p.name
Get all brand mixtures containing a given drug ingredient (e.g. Abacavir, drug_id = 1048):
SELECT p.id, p.name, d.drugbank_id, d.name AS drug_name FROM products p
  JOIN ingredients i           ON i.product_id = p.id
  JOIN drugs d                 ON i.drug_id = d.id
  JOIN ingredients drug_filter ON drug_filter.product_id = p.id
  WHERE p.mixture = '1' AND drug_filter.drug_id = 1048
  ORDER BY p.name;
Get a list of products with their dosage forms:
SELECT p.name, df.name AS form FROM products p
  JOIN dosage_forms_products dfp ON dfp.product_id = p.id
  JOIN dosage_forms df           ON dfp.dosage_form_id = df.id
  ORDER BY p.name;
Get a list of products with their dosage routes:
SELECT p.name, dr.name AS route FROM products p
  JOIN dosage_routes_products drp ON drp.product_id = p.id
  JOIN dosage_routes dr           ON drp.dosage_route_id = dr.id
  ORDER BY p.name;
Get a list of parent and child product concept pairs:
SELECT pc.title as parent_title, child.title AS child_title FROM product_concepts pc
  JOIN product_concept_children is_a ON is_a.parent_id = pc.id
  JOIN product_concepts child        ON child.id = is_a.child_id
  ORDER BY pc.title;

Other Queries

Get all conditions and their synonyms:
SELECT c.*, s.* FROM conditions c
  JOIN condition_synonyms cs ON cs.preferred_term_id = c.id
  JOIN conditions s          ON cs.synonym_term_id = s.id
  ORDER BY c.drugbank_id;