Tutorial IV:
Business Intelligence & Enterprise
oriented SPARQL Examples
using GoodRelations
and eCRM Ontologies

Virtuoso GoodRelation
SPARQL Examples

Filter All Amounts Greater Than Given Value Query

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT ?amount
WHERE
  {
    _:n rdf:type gr:TypeAndQuantityNode.
    _:n gr:amountOfThisGood ?amount.
    FILTER(?amount > 10)
  }
LIMIT 10

Filter All Amounts Greater Than Given Value: Query Result

Sum All Amounts Query

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT SUM (?amount)
WHERE
  {
    _:n rdf:type gr:TypeAndQuantityNode .
    _:n gr:amountOfThisGood ?amount .
  }
LIMIT 10

Sum All Amounts: Query Result

Get The Minimum Amount Of Good Query

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT MIN (?amount)
WHERE
  {
    _:n rdf:type gr:TypeAndQuantityNode .
    _:n gr:amountOfThisGood ?amount .
  }
LIMIT 10

Get The Minimum Amount Of Good: Query Result

Calculate Average Amounts Of Good Query

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT AVG (?amount)
WHERE
  {
    _:n rdf:type gr:TypeAndQuantityNode.
    _:n gr:amountOfThisGood ?amount.
  }
LIMIT 10

Calculate Average Amounts Of Good: Query Result

Get All Products With Their Properties And Models Query

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
CONSTRUCT { ?product ?property ?valueModel }
WHERE
  {
    {
      { ?product a gr:ActualProductOrServiceInstance }
      UNION
      { ?product a gr:ProductOrServicesSomeInstancesPlaceholder }
    }
    ?model a gr:ProductOrServiceModel .
    ?product gr:hasMakeAndModel ?model .
    ?model ?property ?valueModel .
    {
      { ?property rdfs:subPropertyOf gr:qualitativeProductOrServiceProperty . }
      UNION
      { ?property rdfs:subPropertyOf gr:quantitativeProductOrServiceProperty . }
      UNION
      { ?property rdfs:subPropertyOf gr:datatypeProductOrServiceProperty . }
    }
    OPTIONAL { ?product ?property ?valueProduct . }
  }

Get All Products With Their Properties And Models: Query Result

Find How Many Products Each Manufacturer Has And The One With Most Products Query

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT ?a COUNT(?c) AS ?total
WHERE
  {
    ?a rdf:type gr:BusinessEntity .
    ?b gr:hasManufacturer ?a .
    ?b rdf:type gr:ProductOrServiceModel .
    ?c gr:hasMakeAndModel ?b .
    ?c rdf:type gr:ProductOrServicesSomeInstancesPlaceholder .
  }
ORDER BY DESC (?total)
LIMIT 1000

Find How Many Products Each Manufacturer Has And The One With Most Products: Query Result

Find All Products For Given Manufacturer Query

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT COUNT(?prd) AS ?total
WHERE
  {
    ?prd gr:hasManufacturer ?mnf .
    ?mnf rdfs:isDefinedBy ?name .
    FILTER(?name = <http://openean.kaufkauf.net/id/>)
  }

Find All Products For Given Manufacturer: Query Result

Find All Vendors With All Offers And Their Prices

## Find All Vendors With All Offers And Their Prices
## greater than given value

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?lname AS ?Legal_Name
       ?xx AS ?BizEntity
       ?ab AS ?Offerring
       ?c AS ?TypeOfGoods
       ( bif:round(?cv) ) AS ?CurrencyValue
       ?bp as ?BizFunction
WHERE
  {
    ?xx a gr:BusinessEntity .
    OPTIONAL { ?xx gr:legalName ?lname } .
    ?xx gr:offers ?ab .
    ?ab rdf:type gr:Offering .
    ?ab gr:hasBusinessFunction ?bp .
    ?ab gr:eligibleCustomerTypes ?el .
    ?ab gr:includesObject ?b .
    ?b rdf:type gr:TypeAndQuantityNode .
    ?b gr:typeOfGood ?c .
    ?c rdf:type gr:ProductOrServicesSomeInstancesPlaceholder .
    ?ab gr:hasPriceSpecification ?p .
    ?p gr:hasCurrencyValue ?cv .
    FILTER (?cv > 6).
  }
GROUP BY ?c
HAVING ?cv > 100
ORDER BY DESC 5
LIMIT 100

Find All Vendors With All Offers And Their Prices: Query Result

Show All Manufacturers With Their Products And Models With Prices Query

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT *
WHERE
  {
    ?a rdf:type gr:BusinessEntity .
    ?b gr:hasManufacturer ?a .
    ?b rdf:type gr:ProductOrServiceModel .
    ?c gr:hasMakeAndModel ?b .
    ?c rdf:type gr:ProductOrServicesSomeInstancesPlaceholder .
    ?c1 gr:typeOfGood ?c .
    ?c1 rdf:type gr:TypeAndQuantityNode .
    ?d gr:includesObject ?c1 .
    ?d gr:hasPriceSpecification ?p .
    ?p gr:hasCurrencyValue ?cv .
    ?p gr:hasCurrency ?cur .
  }
LIMIT 1000

Show All Manufacturers With Their Products And Models With Prices: Query Result

Find The Most Lightweight Camcorders Query

PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX ceo: <http://www.ebusiness-unibw.org/ontologies/consumerelectronics/v1#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?label MIN(?weight) as ?wgt ?ean
WHERE
  {
    ?m a ceo:Camcorder .
    ?m rdfs:label ?label .
    ?m ceo:hasWeight ?v .
    ?v gr:hasValueFloat ?weight .
    ?v gr:hasUnitOfMeasurement "GRM"^^xsd:string .
    OPTIONAL { ?m gr:hasEAN_UCC-13 ?ean }
  }
ORDER BY ?wgt
LIMIT 10

Find The Most Lightweight Camcorders: Query Result

Describe Camera Ascending Query

## Describes camera and order results
## in ASC weight/zoom_factor ratio

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX ceo: <http://www.ebusiness-unibw.org/ontologies/consumerelectronics/v1#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?m ?label ?weight ?ean ?zoomfactor
       (( ?weight / ?zoomfactor )) as ?weight_zoom_factor_ratio
WHERE
  {
    ?m a ceo:Camcorder .
    ?m rdfs:label ?label .
    ?m ceo:hasWeight ?v .
    ?m ceo:hasDigitalZoomFactor ?z .
    ?z gr:hasValueInteger ?zoomfactor .
    filter ( ?zoomfactor > 0 ) .
    ?v gr:hasValueFloat ?weight .
    ?v gr:hasUnitOfMeasurement "GRM"^^xsd:string .
    OPTIONAL { ?m gr:hasEAN_UCC-13 ?ean }
  }
ORDER BY ASC ( ?weight / ?zoomfactor )

Describe Camera Ascending: Query Result

Describe Camera Descending Query

## Describes camera and order results
## in DESC weight/zoom_factor ratio

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX ceo: <http://www.ebusiness-unibw.org/ontologies/consumerelectronics/v1#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT ?m ?label ?weight ?ean ?zoomfactor
       (( ?weight / ?zoomfactor )) as ?weight_zoom_factor_ratio
WHERE
  {
    ?m a ceo:Camcorder .
    ?m rdfs:label ?label .
    ?m ceo:hasWeight ?v .
    ?m ceo:hasDigitalZoomFactor ?z .
    ?z gr:hasValueInteger ?zoomfactor .
    filter ( ?zoomfactor > 0 ) .
    ?v gr:hasValueFloat ?weight .
    ?v gr:hasUnitOfMeasurement "GRM"^^xsd:string .
    OPTIONAL { ?m gr:hasEAN_UCC-13 ?ean }
  }
ORDER BY DESC ( ?weight / ?zoomfactor )

Describe Camera Descending: Query Result

Get All Offerings In LOC Space By Currency Query

## Example to get first 50 items in the
## space that cost between 5 and 10 EUR
## or 7 and 14 USD

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX gr:<http://purl.org/goodrelations/v1#>
SELECT ?s ?p ?c
WHERE
  {
    ?s a gr:Offering .
    ?s gr:hasPriceSpecification ?ps .
    ?ps gr:hasCurrencyValue ?p .
    ?ps gr:hasCurrency ?c .
    FILTER ( ( regex(?c, "EUR")
      && ?p >"5"^^xsd:float && ?p <"10"^^xsd:float )
    || ( regex(?c, "USD")
      && ?p >"7"^^xsd:float && ?p <"14"^^xsd:float ) )
  }
LIMIT 50

Get All Offerings In LOC Space By Currency: Query Result

List Of Class Frequency Query

SELECT ?class, COUNT(?instance) as ?freq
WHERE
  {
    ?instance a ?class .
    {
      SELECT ?class
      FROM <http://purl.org/goodrelations/v1#>
      WHERE
        {
          ?class a owl:Class .
        }
    }
  }

List Of Class Frequency: Query Result

List of Class Frequency Order By Descending Query

SELECT ?class, ?freq
WHERE
  {
    {
      SELECT ?class, COUNT(?instance) as ?freq
      WHERE
        {
          ?instance a ?class .
          {
            SELECT ?class
            FROM <http://purl.org/goodrelations/v1#>
            WHERE
              {
                ?class a owl:Class .
              }
          }
       }
    }
}
ORDER BY DESC (?freq)

List of Class Frequency Order By Descending: Query Result

List Of Object Property Frequency Query

SELECT ?prop, ?label, COUNT(?instance) as ?freq
WHERE
  {
    ?instance ?prop ?object .
      {
        SELECT ?prop, ?label
        FROM <http://purl.org/goodrelations/v1#>
        WHERE
          {
            ?prop a owl:ObjectProperty .
            ?prop rdfs:label ?label .
          }
      }
  }

List Of Object Property Frequency: Query Result

List Of Datatype Property Frequency Query

SELECT ?prop, ?label, COUNT(?instance) as ?freq
WHERE
  {
    ?instance ?prop ?object .
    {
      SELECT ?prop, ?label
      FROM <http://purl.org/goodrelations/v1#>
      WHERE
        {
          ?prop a owl:DatatypeProperty .
          ?prop rdfs:label ?label .
        }
    }
  }

List Of Datatype Property Frequency: Query Result

Consolidate Business Entities Query

PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
CONSTRUCT { ?u2 owl:sameAs ?u1 }
WHERE
  {
    ?u1 a gr:BusinessEntity .
    ?u2 a gr:BusinessEntity .
    ?u1 gr:legalName ?name1 .
    ?u2 gr:legalName ?name2 .
    FILTER ( ?u1 != ?u2 && ?name1 = ?name2 )
  }

Consolidate Business Entities: Query Result

Consolidate Product Models Query

PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
CONSTRUCT { ?u2 owl:sameAs ?u1 }
WHERE
  {
    ?u1 a gr:ProductOrServiceModel .
    ?u2 a gr:ProductOrServiceModel .
    ?u1 gr:hasEAN_UCC-13 ?ean1 .
    ?u2 gr:hasEAN_UCC-13 ?ean2 .
    FILTER ( ?u1 != ?u2
      && ?ean1 = ?ean2 && ! bif:isnull (?ean1) )
  }

Consolidate Product Models: Query Result

Get The Maximum Amount Of Good Query

PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT MAX (?amount)
WHERE
  {
    _:n rdf:type gr:TypeAndQuantityNode.
    _:n gr:amountOfThisGood ?amount.
  }
LIMIT 10

Get The Maximum Amount Of Good: Query Result

ODS eCRM Data Space GoodRelation
SPARQL Examples

Companies With Support Cases That Have Not Placed At Least One Order Query

PREFIX ecrm: <http://demo.openlinksw.com/schemas/ecrm#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?name ?case
FROM <http://demo.openlinksw.com/ecrm>
WHERE
  {
    ?comp a gr:BusinessEntity .
    ?comp foaf:name ?name .
    ?comp ecrm:is_company_of ?case .
    ?case a ecrm:Case.
    OPTIONAL{ ?comp ecrm:has_order ?order .
              ?order a ecrm:Order } .
    FILTER ( !bound (?order) ) .
  }

Companies With Support Cases That Have Not Placed At Least One Order: Query Result

Evaluators Whose Company Has Not Placed At Least One Order Query

PREFIX ecrm: <http://demo.openlinksw.com/schemas/ecrm#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?comp ?name
FROM <http://demo.openlinksw.com/ecrm>
WHERE
  {
    ?comp a gr:BusinessEntity .
    ?comp foaf:name ?name .
    OPTIONAL { ?comp ecrm:has_order ?order .
               ?order a ecrm:Order } .
    FILTER ( !bound (?order) ) .
  }

Evaluators Whose Company Has Not Placed At Least One Order: Query Result

Evaluators Who's Company Has Not Placed At Least One Order

Orders By Country

PREFIX ecrm: <http://demo.openlinksw.com/schemas/ecrm#>
PREFIX gr: <http://purl.org/goodrelations/v1#>
SELECT *
FROM <http://demo.openlinksw.com/ecrm>
WHERE
  {
    ?order a gr:Offering .
    ?order ecrm:shipCountry ?country .
    FILTER ( ?country LIKE '%Russia%' ) .
  }

Orders By Country: Query Result

Tutorial IV:Business Intelligence & Enterpriseoriented SPARQL Examples using GoodRelationsand eCRM Ontologies

Virtuoso GoodRelationSPARQL Examples