Queries

Queries used for Biofed experiments

Simple Queries

SQL1:

Find all the drugs along with their indications.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX dailymed: <http://www4.wiwiss.fu-berlin.de/dailymed/resource/dailymed/>
SELECT ?genericName ?indication
WHERE 
{
{
?dn drugbank:genericName ?genericName ;
drugbank:indication ?indication.
}
UNION 
{
?dn dailymed:name ?genericName ;
dailymed:indication ?indication .
}
} 

SQL2:

Find all the drug description and chemical equations of reactions related to drugs from category Cathartics.


PREFIX drugcategory: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugcategory/>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX kegg: <http://bio2rdf.org/ns/kegg#>
SELECT ?drugDesc ?cpd ?equation WHERE 
{
?drug drugbank:drugCategory drugcategory:cathartics .
?drug drugbank:keggCompoundId ?cpd .
?drug drugbank:description ?drugDesc .
?enzyme kegg:xSubstrate ?cpd .
?enzyme	rdf:type kegg:Enzyme .
?reaction kegg:xEnzyme ?enzyme .
?reaction kegg:equation ?equation . 
}

SQL3:

Find all drugs, together with the URL of the corresponding web-pages as well as images if available.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bio2RDF: <http://bio2rdf.org/ns/bio2rdf#>
PREFIX purl: <http://purl.org/dc/elements/1.1/>
SELECT * 
WHERE 
{
?drug rdf:type drugbank:drugs .
?drug drugbank:keggCompoundId ?keggDrug .
?keggDrug bio2RDF:url ?keggUrl.
?drug drugbank:genericName ?drugBankName .
?chebiDrug purl:title ?drugBankName .
?chebiDrug bio2RDF:image ?chebiImage .
}

SQL4:

Find KEGG drug names of all drugs in Drugbank belonging to category Micronutrient.


PREFIX drugcategory: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugcategory/>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX bio2RDF: <http://bio2rdf.org/ns/bio2rdf#>
PREFIX purl: <http://purl.org/dc/elements/1.1/>
PREFIX kegg: <http://bio2RDF.org/ns/kegg#>
SELECT *  WHERE { 
?drug drugbank:drugCategory drugcategory:micronutrient .
?drug drugbank:casRegistryNumber ?id .
?keggDrug rdf:type kegg:Drug .
?keggDrug bio2RDF:xRef ?id .
?keggDrug purl:title ?title .
}

SQL5:

Find all drugs and their mass that affect humans and mammals. For those having a description of their biotransformation, also return this description.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX bio2RDF: <http://bio2rdf.org/ns/bio2rdf#>
SELECT * 
WHERE 
{  
?drug drugbank:affectedOrganism  'Humans and other mammals'.
?drug drugbank:casRegistryNumber ?cas .
?keggDrug bio2RDF:xRef ?cas .
?keggDrug bio2RDF:mass ?mass .
OPTIONAL 
{ ?drug drugbank:biotransformation ?transform } 
}

SQL6:

Find diseases and corresponding drugs that target those diseases.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX diseasome:<http://www4.wiwiss.fu-berlin.de/diseasome/resource/diseasome/>
SELECT ?drug ?disease ?name
WHERE
{
?drug drugbank:molecularWeightAverage ?weight.
?drug drugbank:possibleDiseaseTarget ?disease.
?disease diseasome:name ?name.
} 

SQL7:

Find Drugs and their Side Effects with Labels for the drug name “Sodium Phosphate” in dailymed.


PREFIX dailymed: <http://www4.wiwiss.fu-berlin.de/dailymed/resource/dailymed/>
PREFIX sider: <http://www4.wiwiss.fu-berlin.de/sider/resource/sider/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
Select ?drug ?sidereffect ?label
WHERE
{
?drug dailymed:name 'Sodium Phosphates'.
?drug owl:sameAs ?drugAlt.
?drugAlt sider:sideEffect ?sidereffect.
?sidereffect rdfs:label ?label
} 

SQL8:

Find diseases and corresponding drugs that target those diseases along with their lables.


PREFIX diseasome:<http://www4.wiwiss.fu-berlin.de/diseasome/resource/diseasome/>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?drug ?disease ?lable
WHERE
{
?disease diseasome:name ?diseasename.
?disease drugbank:possibleDiseaseTarget ?drug.
?drug rdfs:lable ?lable.
}
 

SQL9:

Find Intervention names and ids for the drugs in dailymed with drug dose, description, inactive ingredients as well as possible disease target.


PREFIX dailymed: <http://www4.wiwiss.fu-berlin.de/dailymed/resource/dailymed/>
PREFIX linkedCT: <http://data.linkedct.org/resource/linkedct/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT distinct *
WHERE
{
?intervention a linkedCT:intervention;
linkedCT:intervention_name ?intervention_name;
linkedCT:intervention_id ?intervention_id;
rdfs:seeAlso ?dailymedDrug.
?dailymedDrug dailymed:dosage ?dosage;
dailymed:description ?description;
dailymed:inactiveIngredient ?inactiveIngredient;
dailymed:possibleDiseaseTarget ?possibleDiseaseTarget .
} 

SQL10:

Find Intervention names and types for the drugs in drugbank with drug chemical structure, drug state, its protein binding and smiles String Canonical.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX linkedCT: <http://data.linkedct.org/resource/linkedct/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT distinct *
WHERE
{
?intervention a linkedCT:intervention;
linkedCT:intervention_name ?intervention_name;
linkedCT:intervention_type ?intervention_type;
rdfs:seeAlso ?drugbankDrug.
?drugbankDrug drugbank:structure ?structure;
drugbank:state ?state;
drugbank:proteinBinding	?proteinBinding;
drugbank:smilesStringCanonical ?smilesStringCanonical .
}

Complex Quereis

CQ1:

Find the equations of chemical reactions and reaction title related to drugs with drug description and drug type 'smallMolecule'. Show only those whose molecular weight average larger than 114.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/> 
PREFIX drugtype: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugtype/>
PREFIX kegg: <http://bio2rdf.org/ns/kegg#>
PREFIX purl: <http://purl.org/dc/elements/1.1/>
SELECT * 
WHERE
{
?drug drugbank:description ?drugDesc .
?drug drugbank:drugType drugtype:smallMolecule .
?drug drugbank:keggCompoundId ?compound. 
?enzyme kegg:xSubstrate ?compound .
?Chemicalreaction kegg:xEnzyme ?enzyme .
?Chemicalreaction kegg:equation ?ChemicalEquation .
?Chemicalreaction purl:title ?ReactionTitle
OPTIONAL 
{ 
?drug drugbank:molecularWeightAverage ?molecularWeightAverage.
FILTER (?molecularWeightAverage > 114) 
}
}

CQ2:

Find all the drugs with their mass and chebiIupacName optionally the Inchi values retrieving from two sources are equal.


PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX chebi: <http://bio2rdf.org/ns/chebi#>
PREFIX purl: <http://purl.org/dc/elements/1.1/>
PREFIX bio2RDF: <http://bio2rdf.org/ns/bio2rdf#>
SELECT * 
WHERE 
{
?drug rdf:type drugbank:drugs .
?drug drugbank:keggCompoundId ?keggDrug .
?keggDrug bio2RDF:mass ?keggmass .
?drug drugbank:genericName ?drugBankName .
?chebiDrug purl:title ?drugBankName .
?chebiDrug chebi:iupacName ?chebiIupacName .
OPTIONAL { 
?drug drugbank:inchiIdentifier ?drugbankInchi .
?chebiDrug bio2RDF:inchi ?chebiInchi.
FILTER (?drugbankInchi = ?chebiInchi) 
}
}

CQ3:

Find drugs, with molecular weight below 1000, and supplementary information, from DrugBank, which are used to treat diseases reported in Diseasome datasets entitled ‘Colon Cancer’


PREFIX drugbank:<http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX diseasome:<http://www4.wiwiss.fu-berlin.de/diseasome/resource/diseasome/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT DISTINCT *  
WHERE 
{
?x1_drugs a drugbank:drugs   .
?x0_disease a diseasome:diseases;
diseasome:possibleDrug ?x1_drugs;
rdfs:label ?x2_label.
?x1_drugs rdfs:label ?x4_label ;
drugbank:chemicalFormula ?x5_chemicalFormula ;
drugbank:chemicalIupacName ?x7_chemicalIupacName ;
drugbank:predictedLogpHydrophobicity ?x8_predictedLogpHydrophobicity ;
drugbank:state ?x9_state ;
drugbank:predictedWaterSolubility ?x6_predictedWaterSolubility
Filter regex (xsd:string(?x2_label), "colon cancer", "is")
}

CQ4:

Find name of active pharmacological agents for some drugs in the dailymed dataset. 1) the owl:sameAs links with sider, in order to get the side effects for each drug, and 2) the links dailymed:genericDrug with drugbank to retrieves chemical formulas of drugs.


PREFIX dm: <http://www4.wiwiss.fu-berlin.de/dailymed/resource/dailymed/>
PREFIX db: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX sider: <http://www4.wiwiss.fu-berlin.de/sider/resource/sider/>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
SELECT *
WHERE 
{
?dg dm:activeIngredient ?dgai .
?dg dm:genericDrug ?gdg .
?dg owl:sameAs ?sa .
?sa sider:sideEffect ?se .
?se sider:sideEffectName ?sen .
?gdg db:chemicalFormula ?dgcf .
}

CQ5:

Find Drug Information including drug type, drug number, inchi identifier and inchi key


PREFIX medicare: <http://www4.wiwiss.fu-berlin.de/medicare/resource/medicare/>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
SELECT *
WHERE
{
?medicareDrug a medicare:drugs;
medicare:drugType ?drugType;
owl:sameAs ?drugbankDrug.
?drugbankDrug drugbank:dpdDrugIdNumber ?drugNumber;
drugbank:inchiIdentifier ?inchiIdentifier;
drugbank:inchiKey ?inchiIkey.
OPTIONAL 
{ 
?drugbankDrug drugbank:foodInteraction ?foodInteraction;
drugbank:biotransformation ?biotransformation;
drugbank:drugCategory ?drugCategory;
drugbank:dosageForm ?dosageForm.
}
}

CQ6:

Find Drug Information including drug type, drug description, chemical formula and molecular mass.


PREFIX medicare: <http://www4.wiwiss.fu-berlin.de/medicare/resource/medicare/>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX bio2RDF: <http://bio2rdf.org/ns/bio2rdf#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
SELECT  *
WHERE
{
 ?medicareDrug a medicare:drugs;
 medicare:drugType ?drugType;
 owl:sameAs ?drugbankDrug.
 ?drugbankDrug drugbank:description ?description;
 drugbank:chemicalFormula ?chemicalFormula;
 drugbank:keggCompoundId ?keggCompoundId.
 ?keggCompoundId bio2RDF:mass ?keggmass .
OPTIONAL 
{ 
 ?drugbankDrug drugbank:brandName ?brandName;
 drugbank:chemicalIupacName ?chemicalIupacName;
 drugbank:genericName ?genericName;
 drugbank:brandName ?brandName;
 drugbank:creationDate ?creationDate;
}
}

CQ7:

Get clinical information about TCGA patient along with drug information


PREFIX tcga: <http://tcga.deri.ie/schema/>
PREFIX kegg: <http://bio2rdf.org/ns/kegg#>
PREFIX drugbank: <http://www4.wiwiss.fu-berlin.de/drugbank/resource/drugbank/>
PREFIX purl: <http://purl.org/dc/terms/>
SELECT  *
WHERE
{
 ?uri tcga:bcr_patient_barcode ?patient .
 ?patient tcga:gender ?gender.
 ?patient tcga:bcr_drug_barcode ?drugbcr.
 ?drugbcr tcga:drug_name ?drugName. 
 ?drgBnkDrg drugbank:genericName ?drugName.
 ?drgBnkDrg drugbank:indication ?indication.
 ?drgBnkDrg drugbank:chemicalFormula ?formula.
 ?drgBnkDrg drugbank:keggCompoundId ?compound .
}

CQ8:

Get the methylation values for CNTNAP2 gene of all the cancer patients.


PREFIX tcga: <http://tcga.deri.ie/schema/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX affymetrix: <http://bio2rdf.org/affymetrix_vocabulary:>
SELECT  *
WHERE 
{
 ?s affymetrix:x-symbol <http://bio2rdf.org/symbol:CNTNAP2>.
 ?s affymetrix:x-geneid ?geneId.
 ?geneId rdf:type tcga:expression_gene_lookup.
 ?geneId tcga:chromosome ?lookupChromosome. 
 ?geneId tcga:start ?start.
 ?geneId tcga:stop  ?stop. 
}

CQ9:

For all cancer patients, get the genomic locations and corresponding gene expression values for chromosome associated with KRAS gene.


PREFIX tcga: <http://tcga.deri.ie/schema/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX affymetrix: <http://bio2rdf.org/affymetrix_vocabulary:>
SELECT   *
WHERE 
{
 ?s affymetrix:x-symbol <http://bio2rdf.org/symbol:KRAS>.
 ?s affymetrix:x-geneid ?geneId.
 ?geneId tcga:chromosome ?lookupChromosome.
 ?uri tcga:bcr_patient_barcode ?patient .
 ?patient tcga:result ?recordNo .
 ?recordNo tcga:chromosome   ?chromosome.
 ?recordNo tcga:start ?start.
 ?recordNo tcga:stop ?stop.
 ?recordNo tcga:scaled_estimate ?geneExpVal
 FILTER (str(?lookupChromosome)= str(?chromosome))
}

CQ10:

Find Drug Information including drug name, drug dosage, its active ingredient, its in active ingredient and possible disease target.


PREFIX dailymed: <http://www4.wiwiss.fu-berlin.de/dailymed/resource/dailymed/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX owl: <http://www.w3.org/2002/07/owl#>

SELECT *
WHERE
{
 ?medicareDrug foaf:name ?name;
 owl:sameAs ?dailymedDrug.
 ?dailymedDrug dailymed:dosage ?dosage;
 dailymed:activeIngredient ?activeIngredient;
 dailymed:inactiveIngredient ?inactiveIngredient;
 dailymed:possibleDiseaseTarget ?possibleDiseaseTarget .
 OPTIONAL 
{ 
 ?dailymedDrug dailymed:representedOrganization ?representedOrganization;
 dailymed:routeOfAdministration ?routeOfAdministrationt;
 dailymed:genericMedicine ?genericMedicine.
}
}

BioFed Team