Tutorial XII:
SPARQL-BI Examples

OpenLink Software

Live Instances

All the queries in this tutorial can be verified against the live instance http://demo.openlinksw.com ,
where you have /sparql (basic SPARQL Endpoint) and /isparql (SPARQL Query By Example UI and Endpoint).

Example 1

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
  ?l+>tpch:returnflag,
  ?l+>tpch:linestatus,
  SUM(?l+>tpch:linequantity) as ?sum_qty,
  SUM(?l+>tpch:lineextendedprice) as ?sum_base_price,
  SUM(?l+>tpch:lineextendedprice*(1 - ?l+>tpch:linediscount)) as ?sum_disc_price,
  SUM(?l+>tpch:lineextendedprice*(1 - ?l+>tpch:linediscount)*(?l+>tpch:linetax)) as ?sum_charge,
  AVG(?l+>tpch:linequantity) as ?avg_qty,
  AVG(?l+>tpch:lineextendedprice) as ?avg_price,
  AVG(?l+>tpch:linediscount) as ?avg_disc,
  COUNT(1) as ?count_order

WHERE
  {
    ?l a tpch:lineitem .
    FILTER (?l+>tpch:shipdate <= bif:dateadd ("day", 90, ''1998-12-01''^^xsd:date))
  }
ORDER BY ?l+>tpch:returnflag ?l+>tpch:linestatus

Example 2

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
SELECT
  ?supp+>tpch:acctbal,
  ?supp+>tpch:name,
  ?supp+>tpch:has_nation+>tpch:name as ?nation_name,
  ?part+>tpch:partkey,
  ?part+>tpch:mfgr,
  ?supp+>tpch:address,
  ?supp+>tpch:phone,
  ?supp+>tpch:comment

WHERE
  {
    ?ps a tpch:partsupp;
    tpch:has_supplier ?supp;
    tpch:has_part ?part .
    ?supp+>tpch:has_nation+>tpch:has_region tpch:name ''EUROPE'' .
    ?part tpch:size 15 .
    ?ps tpch:supplycost ?minsc .
    {
      SELECT ?part min(?ps+>tpch:supplycost) as ?minsc
      WHERE
        {
          ?ps a tpch:partsupp;
          tpch:has_part ?part;
          tpch:has_supplier ?ms .
          ?ms+>tpch:has_nation+>tpch:has_region tpch:name ''EUROPE'' .
        }
    }
    FILTER (?part+>tpch:type like ''%BRASS'')
  }
ORDER BY
desc (?supp+>tpch:acctbal)
?supp+>tpch:has_nation+>tpch:name
?supp+>tpch:name
?part+>tpch:partkey

Example 3

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
  ?ord+>tpch:orderkey,
  SUM(?li+>tpch:lineextendedprice*(1 - ?li+>tpch:linediscount)) as ?revenue,
  ?ord+>tpch:orderdate,
  ?ord+>tpch:shippriority

WHERE
  {
    ?cust a tpch:customer ;
    tpch:mktsegment "BUILDING" ;
    tpch:customer_of ?ord .
    ?li tpch:has_order ?ord .
    FILTER ((?ord+>tpch:orderdate < "1995-03-15"^^xsd:date) &&
      (?li+>tpch:shipdate > "1995-03-15"^^xsd:date) )
  }
ORDER BY
desc (SUM (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))
?ord+>tpch:orderdate

Example 4

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT (?ord+>tpch:orderpriority), COUNT(1) as ?order_count

WHERE
  {
    ?ord a tpch:order .
    {
      SELECT ?ord COUNT(?li) as ?cnt
      WHERE
        {
          ?li tpch:has_order ?ord .
          FILTER ( ?li+>tpch:commitdate < ?li+>tpch:receiptdate )
        }
    }
    FILTER ((?ord+>tpch:orderdate >= "1993-07-01"^^xsd:date) &&
      (?ord+>tpch:orderdate < bif:dateadd ("month", 3, "1993-07-01"^^xsd:date)) &&
      (?cnt > 0) )
  }
ORDER BY
?ord+>tpch:orderpriority

Example 5

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?supp+>tpch:has_nation+>tpch:name as ?nation,
 SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?revenue

WHERE
 {
  ?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_supplier ?supp .
  ?ord tpch:has_customer ?cust .
  ?supp+>tpch:has_nation+>tpch:has_region tpch:name "ASIA" .
  FILTER ((?cust+>tpch:has_nation = ?supp+>tpch:has_nation) &&
   (?ord+>tpch:orderdate >= "1994-01-01"^^xsd:date) &&
   (?ord+>tpch:orderdate < bif:dateadd ("year", 1,"1994-01-01" ^^xsd:date)) )
 }
ORDER BY
desc (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))

Example 6

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 SUM(?li+>tpch:lineextendedprice * ?li+>tpch:linediscount) as ?revenue

WHERE
 {
  ?li a tpch:lineitem .
  FILTER ( (?li+>tpch:shipdate >= "1994-01-01"^^xsd:date) &&
   (?li+>tpch:shipdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) &&
   (?li+>tpch:linediscount >= 0.06 - 0.01) &&
   (?li+>tpch:linediscount <= 0.06 + 0.01) &&
   (?li+>tpch:linequantity < 24) )
 }

Example 7

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?supp_nation ?cust_nation ?li_year
 SUM (?value) as ?revenue
WHERE
 {
  {
   SELECT
    ?suppn+>tpch:name as ?supp_nation,
    ?custn+>tpch:name as ?cust_nation,
    (bif:year (?li+>tpch:shipdate)) as ?li_year,
    (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?value
   WHERE
    {
     ?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_supplier ?supp .
     ?ord tpch:has_customer ?cust .
     ?cust tpch:has_nation ?custn .
     ?supp tpch:has_nation ?suppn .
     FILTER ((
      (?custn+>tpch:name = "FRANCE" and ?suppn+>tpch:name = "GERMANY") ||
      (?custn+>tpch:name = "GERMANY" and ?suppn+>tpch:name = "FRANCE") ) &&
       (?li+>tpch:shipdate >= "1995-01-01"^^xsd:date) &&
       (?li+>tpch:shipdate <= "1996-12-31"^^xsd:date) )
   }
  }
 }
ORDER BY
 ?supp_nation
 ?li_year

Example 8

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?o_year,
 (?sum1 / ?sum2) as ?mkt_share
WHERE
 {
  {
   SELECT
    ?o_year
    SUM (?volume * bif:equ (?nation, "BRAZIL")) as ?sum1
    SUM (?volume) as ?sum2
   WHERE
    {
     {
      SELECT
       (bif:year (?ord+>tpch:orderdate)) as ?o_year,
       (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?volume,
       ?n2+>tpch:name as ?nation
      WHERE
       {
        ?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_part ?part .
        ?li+>tpch:has_supplier tpch:has_nation ?n2 .
        ?order+>tpch:has_customer+>tpch:has_nation+>tpch:has_region tpch:name "AMERICA" .
        ?part tpch:type "ECONOMY ANODIZED STEEL" .
        FILTER ((?ord+>tpch:orderdate >= "1995-01-01"^^xsd:date) &&
         (?ord+>tpch:orderdate <= "1996-12-31"^^xsd:date) )
       }
     }
    }
  }
 }
ORDER BY
 ?o_year

Example 9

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?nation,
 ?o_year,
 SUM(?amount) as ?sum_profit
WHERE
 {
  {
   SELECT
    ?supp+>tpch:has_nation+>tpch:name as ?nation,
    (bif:year (?ord+>tpch:orderdate)) as ?o_year,
    (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount) - ?ps+>tpch:supplycost * ?li+>tpch:linequantity) as ?amount
   WHERE
    {
     ?li a tpch:lineitem ;
     tpch:has_order ?ord ;
     tpch:has_supplier ?supp ;
     tpch:has_part ?part .
     ?ps a tpch:partsupp ;
     tpch:has_part ?part ;
     tpch:has_supplier ?supp .
     FILTER (?part+>tpch:name like "%green%")
    }
  }
 }
ORDER BY ?nation desc (?o_year)

Example 10

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?cust+>tpch:custkey,
 ?cust+>tpch:companyName,
 (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount))) as ?revenue,
 ?cust+>tpch:acctbal,
 ?cust+>tpch:has_nation+>tpch:name as ?nation,
 ?cust+>tpch:address,
 ?cust+>tpch:phone,
 ?cust+>tpch:comment
WHERE
 {
  ?li tpch:returnflag "R" ; tpch:has_order ?ord .
  ?ord tpch:has_customer ?cust .
  FILTER ((?ord+>tpch:orderdate >= "1993-10-01"^^xsd:date) &&
    (?ord+>tpch:orderdate < bif:dateadd ("month", 3, "1993-10-01"^^xsd:date)) )
 }
ORDER BY
 desc (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))

Example 11

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?bigps+>tpch:has_part,
 ?bigpsvalue
WHERE
 {
  {
   SELECT
    (SUM(?thr_ps+>tpch:supplycost * ?thr_ps+>tpch:availqty) * 0.0001) as ?threshold
   WHERE
    {
     ?thr_tps a tpch:partsupp .
     ?thr_ps+>tpch:has_supplier+>tpch:has_nation tpch:name "GERMANY" .
    }
  }
  { SELECT
   ?bigps+>tpch:has_part as ?bpart,
   SUM(?bigps+>tpch:supplycost * ?bigps+>tpch:availqty) as ?bigpsvalue
   WHERE
    {
     ?bigps a tpch:partsupp .
     ?bigps+>tpch:has_supplier+>tpch:has_nation tpch:name "GERMANY" .
    }
  }
  FILTER (?bigpsvalue > ?threshold)
 }
ORDER BY desc (?bigpsvalue)

Example 12

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?li+>tpch:shipmode,
 SUM (
  bif:__or (
  bif:equ (?ord+>tpch:orderpriority, "1-URGENT"),
  bif:equ (?ord+>tpch:orderpriority, "2-HIGH") ) ) as ?high_line_count,
 SUM (1 -
  bif:__or (
  bif:equ (?ord+>tpch:orderpriority, "1-URGENT"),
  bif:equ (?ord+>tpch:orderpriority, "2-HIGH") ) ) as ?low_line_count
WHERE
 {
  ?li tpch:has_order ?ord .
  FILTER (?li+>tpch:shipmode in ("MAIL", "SHIP") &&
   (?li+>tpch:commitdate < ?li+>tpch:receiptdate) &&
   (?li+>tpch:shipdate < ?li+>tpch:commitdate) &&
   (?li+>tpch:receiptdate >= "1994-01-01"^^xsd:date) &&
   (?li+>tpch:receiptdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) )
 }
ORDER BY ?li+>tpch:shipmode

Example 13

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?c_count,
 COUNT(1) as ?custdist
WHERE
 {
  {
   SELECT
    ?cust+>tpch:custkey,
    count (?ord) as ?c_count
   WHERE
    {
     ?cust a tpch:customer .
     OPTIONAL { ?cust tpch:customer_of ?ord
      FILTER (!(?ord+>tpch:comment like "%special%requests%")) }
    }
  }
 }
ORDER BY
 desc (COUNT(1))
 desc (?c_count)

Example 14

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 (100 * SUM (
  bif:equ(bif:LEFT(?part+>tpch:type, 5), "PROMO") *
  ?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount) ) /
   SUM (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) ) as ?promo_revenue
WHERE
 {
  ?li a tpch:lineitem ; tpch:has_part ?part .
  FILTER ((?li+>tpch:shipdate >= "1995-09-01"^^xsd:date) &&
  (?li+>tpch:shipdate < bif:dateadd("month", 1, "1995-09-01"^^xsd:date)) )
 }

Example 15

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?supplier ?supplier+>tpch:name ?supplier+>tpch:address
 ?supplier+>tpch:phone ?total_revenue
WHERE
 {
  ?supplier a tpch:supplier .
  {
   SELECT
    ?supplier
    (SUM(?l_extendedprice * (1 - ?l_discount))) as ?total_revenue
   WHERE
    {
     [ a tpch:lineitem ; tpch:shipdate ?l_shipdate ;
     tpch:lineextendedprice ?l_extendedprice ; tpch:linediscount ?l_discount ;
     tpch:has_supplier ?supplier ] .
     FILTER (
      ?l_shipdate >= "1996-01-01"^^xsd:date and
      ?l_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
    }
  }
  {
   SELECT max (?l2_total_revenue) as ?maxtotal
   WHERE
    {
     {
      SELECT
       ?supplier2
       (SUM(?l2_extendedprice * (1 - ?l2_discount))) as ?l2_total_revenue
      WHERE
       {
        [ a tpch:lineitem ; tpch:shipdate ?l2_shipdate ;
        tpch:lineextendedprice ?l2_extendedprice ; tpch:linediscount ?l2_discount ;
        tpch:has_supplier ?supplier2 ] .
        FILTER ( ?l2_shipdate >= "1996-01-01"^^xsd:date &&
         ?l2_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
       }
     }
    }
  }
  FILTER (?total_revenue = ?maxtotal)
 }
ORDER BY ?supplier

Example 16

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?part+>tpch:brand,
 ?part+>tpch:type,
 ?part+>tpch:size,
 (COUNT(distinct ?ps)) as ?supplier_cnt
WHERE
  {
   ?ps tpch:has_part ?part .
   OPTIONAL {
    ?ps tpch:comment ?badcomment . FILTER (?badcomment like "%Customer%Complaints%") }
   FILTER (
    (?part+>tpch:brand != "Brand#45") &&
    !(?part+>tpch:type like "MEDIUM POLISHED%") &&
    (?part+>tpch:size in (49, 14, 23, 45, 19, 3, 36, 9)) &&
    !bound (?badcomment) )
  }
ORDER BY
 desc ((COUNT(distinct ?ps)))
 ?part+>tpch:brand
 ?part+>tpch:type
 ?part+>tpch:size

Example 17

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 (SUM(?li+>tpch:lineextendedprice) / 7.0) as ?avg_yearly
WHERE
  {
   ?li a tpch:lineitem ; tpch:has_part ?part .
   ?part tpch:container "MED BOX" ; tpch:brand "Brand#23" .
   {
    SELECT ?part, (0.2 * AVG(?li2+>tpch:linequantity)) as ?threshold
    WHERE
     { ?li2 a tpch:lineitem ;
      tpch:has_part ?part
     }
   }
   FILTER (?li+>tpch:linequantity < ?threshold)
  }

Example 18

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?cust+>foaf:name ?cust ?ord ?ord+>tpch:orderdate
 ?ord+>tpch:ordertotalprice SUM(?li+>tpch:linequantity)
WHERE
 {
  ?cust a tpch:customer ;
  foaf:name ?c_name .
  ?ord a tpch:order ;
  tpch:has_customer ?cust .
  ?li a tpch:lineitem ;
  tpch:has_order ?ord .
   {
    SELECT ?sum_order SUM (?li2+>tpch:linequantity) as ?sum_q
    WHERE
     {
      ?li2 a tpch:lineitem ; tpch:has_order ?sum_order .
     }
   } .
  FILTER (?sum_order = ?ord and ?sum_q > 250)
 }
ORDER BY desc (?ord+>tpch:ordertotalprice) ?ord+>tpch:orderdate

Example 19

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount))) as ?revenue
WHERE
 {
  ?li a tpch:lineitem ; tpch:has_part ?part ; tpch:shipinstruct "DELIVER IN PERSON" .
  FILTER (?li+>tpch:shipmode in ("AIR", "AIR REG") &&
   ( ( (?part+>tpch:brand = "Brand#12") &&
   (?part+>tpch:container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
   (?li+>tpch:linequantity >= 1) && (?li+>tpch:linequantity <= 1 + 10) &&
   (?part+>tpch:size >= 1) && (?part+>tpch:size <= 5) ) ||
   ( (?part+>tpch:brand = "Brand#23") &&
   (?part+>tpch:container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
   (?li+>tpch:linequantity >= 10) && (?li+>tpch:linequantity <= 10 + 10) &&
   (?part+>tpch:size >= 1) && (?part+>tpch:size <= 10) ) ||
   ( (?part+>tpch:brand = "Brand#34") &&
   (?part+>tpch:container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
   (?li+>tpch:linequantity >= 20) && (?li+>tpch:linequantity <= 20 + 10) &&
   (?part+>tpch:size >= 1) && (?part+>tpch:size <= 15) ) ) )
 }

Example 20

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?supp+>tpch:name,
 ?supp+>tpch:address
WHERE
 {
  {
   SELECT
    ?supp, count (?big_ps) as ?big_ps_cnt
   WHERE
    {
     ?big_ps a tpch:partsupp ; tpch:has_supplier ?supp .
     ?supp+>tpch:has_nation tpch:name "CANADA" .
     {       SELECT ?forest_part
      WHERE
       {
        ?forest_part a tpch:part .
        FILTER ( ?forest_part+>tpch:name like "forest%" )
       }
     }
     {
      SELECT
        ?big_ps, (0.5 * SUM(?li+>tpch:linequantity)) as ?qty_threshold
      WHERE
       {
        ?li a tpch:lineitem ;
        tpch:has_part ?big_ps+>tpch:has_part ;
        tpch:has_supplier ?bigps+>tpch:has_supplier .
        FILTER ((?li+>tpch:shipdate >= "1994-01-01"^^xsd:date) &&
         (?li+>tpch:shipdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) )
       }
     }
     FILTER (?big_ps+>tpch:availqty > ?qty_threshold)
    }
  }
 }
ORDER BY ?supp+>tpch:name

Example 21

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 ?supp+>tpch:name,
 (COUNT(1)) as ?numwait
WHERE
 {
  {
   SELECT ?l1 ?ord ?supp (COUNT(1)) as ?l2_cnt
   WHERE
    {
     ?supp a tpch:supplier .
     ?supp+>tpch:has_nation tpch:name "SAUDI ARABIA" .
     ?l1 a tpch:lineitem ;
     tpch:has_supplier ?supp ;
     tpch:has_order ?ord .
     ?ord tpch:orderstatus "F" .
     ?l2 a tpch:lineitem ; tpch:has_supplier ?supp2 ; tpch:has_order ?ord .
     OPTIONAL {
      {
       SELECT ?l1 (count (1)) as ?l3_cnt
       WHERE
        {
         ?l1 a tpch:lineitem ;
         tpch:has_supplier ?supp ;
         tpch:has_order ?ord .
         ?l3 a tpch:lineitem ;
         tpch:has_supplier ?supp3 ;
         tpch:has_order ?ord .
         FILTER ((?l3+>tpch:receiptdate > ?l3+>tpch:commitdate) &&
         (?supp3 != ?supp))
        }
      }
     }
    FILTER ((?l1+>tpch:receiptdate > ?l1+>tpch:commitdate) &&
     (?supp2 != ?supp) && !bound (?l3_cnt))
    }
  }
 }
ORDER BY
  desc (COUNT(1))
  ?supp+>tpch:name

Example 22

DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
 (bif:LEFT (?cust+>tpch:phone, 2)) as ?cntrycode,
 (count (1)) as ?numcust,
 SUM (?cust+>tpch:acctbal) as ?totacctbal
WHERE
 {
  {
  SELECT
   (avg (?cust2+>tpch:acctbal)) as ?acctbal_threshold
  WHERE
   {
    ?cust2 a tpch:customer .
    FILTER ((?cust2+>tpch:acctbal > 0.00) &&
     bif:LEFT (?cust2+>tpch:phone, 2) in
     ("13", "35", "31", "23", "29", "30", "17", "18") )
   }
  }
  ?cust a tpch:customer .
  OPTIONAL {
   SELECT ?cust (COUNT(?ord)) as ?ord_cnt
   WHERE
    {
     ?cust a tpch:customer ;
     tpch:customer_of ?ord
    }
  }
  FILTER ((?cust+>tpch:acctbal > ?acctbal_threshold) &&
   bif:LEFT (?cust+>tpch:phone, 2) in
   ("13", "35", "31", "23", "29", "30", "17", "18") &&
    !bound (?ord_cnt) )
 }
ORDER BY (bif:LEFT (?cust+>tpch:phone, 2))

RDF
SPARQL
SQL
OpenLink
SIOC
WG
SPARUL
ARQ
XQuery
XPath
URI
Vocabulary
IFP
RIF
RDFS
OWL
RDBMS
ODBC
JDBC
GET
POST
QL