# Query 1 書き直し

DEFINE sql:select-option "order"
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX icgc: <http://icgc.link/vocab/>
PREFIX hgnc_vocabulary: <http://bio2rdf.org/hgnc_vocabulary:>
PREFIX bio2rdf_vocabulary: <http://bio2rdf.org/bio2rdf_vocabulary:>
PREFIX bio2rdf-ensembl: <http://bio2rdf.org/ensembl_vocabulary:>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX dct: <http://purl.org/dc/terms/>

SELECT 
 (?project_code AS ?item)
 (COUNT(DISTINCT ?donor) AS ?value)
FROM <http://icgc.link/release_20>
WHERE
{
  ?project icgc:primary_site "Blood" ;
    a <http://icgc.link/Project> ;
    icgc:project_code ?project_code .
  ?donor icgc:project ?project ;
    a <http://icgc.link/Donor> .
  ?detection icgc:donor ?donor ;
    a <http://icgc.link/Detection> ;
    icgc:mutation ?mutation .
  ?mutation a <http://icgc.link/Mutation> .
  ?effect icgc:mutation ?mutation ;
    a <http://icgc.link/Effect> ;
    icgc:gene_affected ?gene .
  ?gene a <http://icgc.link/Gene>
}
GROUP BY ?project_code
ORDER BY DESC(?value)


# Query 2 書き直し
# Query 2
#
# イギリスの骨肉腫のプロジェクト「BOCA-UK」で多くの症例で検出された変異上位20個

DEFINE sql:select-option "order"
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX icgc: <http://icgc.link/vocab/>
PREFIX hgnc_vocabulary: <http://bio2rdf.org/hgnc_vocabulary:>
PREFIX bio2rdf_vocabulary: <http://bio2rdf.org/bio2rdf_vocabulary:>
PREFIX bio2rdf-ensembl: <http://bio2rdf.org/ensembl_vocabulary:>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX dct: <http://purl.org/dc/terms/>

SELECT 
 (?mutation_id AS ?ID)
 (CONCAT("chr", ?chromosome, ":g.", STR(?chromosome_start), ":g.",
  ?mutated_from_allele, ">", ?mutated_to_allele) AS ?DNA_Change)
 (?mutation_type AS ?Type)
 (COUNT(DISTINCT ?donor) AS ?donors)
FROM <http://icgc.link/release_20>
{
  ?project icgc:project_code "BOCA-UK" ;
    a <http://icgc.link/Project> ;
    icgc:primary_site ?primary_site .
  ?donor icgc:project ?project .
  ?detection icgc:donor ?donor ;
    a <http://icgc.link/Detection> ;
    icgc:mutation ?mutation .
  ?mutation a <http://icgc.link/Mutation> ;
    icgc:mutation_id ?mutation_id ;
    icgc:mutation_type ?mutation_type ;
    icgc:chromosome ?chromosome ;
    icgc:chromosome_start ?chromosome_start ;
    icgc:mutated_from_allele ?mutated_from_allele ;
    icgc:mutated_to_allele ?mutated_to_allele .
  ?effect icgc:mutation ?mutation ;
    a <http://icgc.link/Effect> ;
    icgc:gene_affected ?gene .
  ?gene a <http://icgc.link/Gene>
}
GROUP BY ?mutation_id ?chromosome ?chromosome_start ?mutated_from_allele ?mutated_to_allele ?mutation_type
ORDER BY DESC(?donors)
LIMIT 20


# Query 3 書き直し
DEFINE sql:select-option "order"
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX icgc: <http://icgc.link/vocab/>
PREFIX hgnc_vocabulary: <http://bio2rdf.org/hgnc_vocabulary:>
PREFIX bio2rdf_vocabulary: <http://bio2rdf.org/bio2rdf_vocabulary:>
PREFIX bio2rdf-ensembl: <http://bio2rdf.org/ensembl_vocabulary:>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX dct: <http://purl.org/dc/terms/>

SELECT DISTINCT ?gene (COUNT(DISTINCT ?donor) AS ?count)
FROM <http://icgc.link/release_20>
{
  ?project icgc:project_code "BOCA-UK" ;
    a <http://icgc.link/Project> ;
    icgc:primary_site ?primary_site .
  ?donor icgc:project ?project .
  ?detection icgc:donor ?donor ;
    a <http://icgc.link/Detection> ;
    icgc:mutation ?mutation .
  ?mutation a <http://icgc.link/Mutation> .
  ?effect icgc:mutation ?mutation ;
    a <http://icgc.link/Effect> ;
    icgc:gene_affected ?gene .
  ?gene a <http://icgc.link/Gene>
}
GROUP BY ?gene
ORDER BY DESC (?count) LIMIT 20