SPARQL Examples

Lab 3: SPARQL

List all triples

SELECT ?s ?p ?o
WHERE {?s ?p ?o .}

List the first 100 triples

SELECT ?s ?p ?o
WHERE {?s ?p ?o .} 
LIMIT 100

Count the number of triples

SELECT (COUNT(*) as ?count)
WHERE {?s ?p ?o .}

Count the number of indictments

PREFIX ns1: <http://example.org#> 

SELECT (COUNT(?ind) as ?amount) 
WHERE {
  ?s ns1:outcome ?ind;
     ns1:outcome ns1:indictment.
}

List the names of everyone who pleaded guilty, along with the name of the investigation

PREFIX ns1: <http://example.org#> 

SELECT ?name ?invname 
WHERE {
  ?s ns1:name ?name;
     ns1:investigation ?invname;
     ns1:outcome ns1:guilty-plea .
}

List the names of everyone who were convicted, but who had their conviction overturned by which president

PREFIX ns1: <http://example.org#> 

SELECT ?name ?president
WHERE {
  ?s ns1:name ?name;
     ns1:president ?president;
     ns1:outcome ns1:conviction;
     ns1:overturned ns1:true.
}

For each investigation, list the number of indictments made

PREFIX ns1: <http://example.org#> 

SELECT ?invs (COUNT(?invs) as ?count)
WHERE {
  ?s ns1:investigation ?invs;
     ns1:outcome ns1:indictment .
} 
GROUP BY ?invs

For each investigation with multiple indictments, list the number of indictments made

PREFIX ns1: <http://example.org#> 

SELECT ?invs (COUNT(?invs) as ?count)
WHERE {
  ?s ns1:investigation ?invs;
     ns1:outcome ns1:indictment .
} 
GROUP BY ?invs
HAVING(?count > 1)

For each investigation with multiple indictments, list the number of indictments made, sorted with the most indictments first

PREFIX ns1: <http://example.org#> 

SELECT ?invs (COUNT(?invs) as ?count)
WHERE {
  ?s ns1:investigation ?invs;
     ns1:outcome ns1:indictment .
} 
GROUP BY ?invs
HAVING(?count > 1)
ORDER BY DESC(?count)

For each president, list the numbers of convictions and of pardons made

PREFIX ns1: <http://example.org#> 

SELECT ?president (COUNT(?outcome) as ?conviction) (COUNT(?pardon) as ?pardons)
WHERE {
  ?s ns1:president ?president;
     ns1:outcome ?outcome ;
     ns1:outcome ns1:conviction.
     OPTIONAL{
        ?s ns1:pardoned ?pardon .
        FILTER (?pardon = ns1:true)
     }
}
GROUP BY ?president

Rename mullerkg:name to something like muellerkg:person

PREFIX ns1: <http://example.org#> 

DELETE{?s ns1:name ?o}
INSERT{?s ns1:person ?o}
WHERE {?s ns1:name ?o}

Update the graph so all the investigated person and president nodes become the subjects in foaf:name triples with the corresponding strings

PREFIX ns1: <http://example.org#> 
PREFIX foaf: <http://xmlns.com/foaf/0.1/>

#Persons
INSERT {?person foaf:name ?name}
WHERE {
     ?investigation ns1:person ?person .
     BIND(REPLACE(STR(?person), STR(ns1:), "") AS ?name)
}

#Presidents
INSERT {?president foaf:name ?name}
WHERE {
     ?investigation ns1:president ?president .
     BIND(REPLACE(STR(?president), STR(ns1:), "") AS ?name)
}

Use INSERT DATA updates to add these triples

PREFIX ns1: <http://example.org#> 

INSERT DATA {
    ns1:George_Papadopoulos ns1:adviserTo ns1:Donald_Trump;
        ns1:pleadGuiltyTo ns1:LyingToFBI;
        ns1:sentencedTo ns1:Prison.
        
    ns1:Roger_Stone a ns1:Republican;
        ns1:adviserTo ns1:Donald_Trump;
        ns1:officialTo ns1:Trump_Campaign;
        ns1:interactedWith ns1:Wikileaks;
        ns1:providedTestimony ns1:House_Intelligence_Committee;
        ns1:clearedOf ns1:AllCharges.
}

#To test if added
SELECT ?p ?o
WHERE {ns1:Roger_Stone ?p ?o .}

Use DELETE DATA and then INSERT DATA updates to correct that Roger Stone was cleared of all charges

PREFIX ns1: <http://example.org#> 

DELETE DATA {
     ns1:Roger_Stone ns1:clearedOf ns1:AllCharges .
}

INSERT DATA {
     ns1:Roger_Stone ns1:indictedFor ns1:ObstructionOfJustice,
                                     ns1:WitnessTampering, 
                                     ns1:FalseStatements.
}

#The task specifically requested DELETE DATA & INSERT DATA, put below is a more efficient solution

DELETE{ns1:Roger_Stone ns1:clearedOf ns1:AllCharges.}
INSERT{
  ns1:Roger_Stone ns1:indictedFor ns1:ObstructionOfJustice,
                                  ns1:WitnessTampering,
                                  ns1:FalseStatements.
}
WHERE{ns1:Roger_Stone ns1:clearedOf ns1:AllCharges.}

Use a DESCRIBE query to show the updated information about Roger Stone

PREFIX ns1: <http://example.org#> 

DESCRIBE ?o
WHERE {ns1:Roger_Stone ns1:indictedFor ?o .}

Use a CONSTRUCT query to create a new RDF group with triples only about Roger Stone

PREFIX ns1: <http://example.org#> 

CONSTRUCT {
  ns1:Roger_Stone ?p ?o.
  ?s ?p2 ns1:Roger_Stone.
}
WHERE {
  ns1:Roger_Stone ?p ?o .
  ?s ?p2 ns1:Roger_Stone
}

Write a DELETE/INSERT statement to change one of the prefixes in your graph

PREFIX ns1: <http://example.org#> 
PREFIX dbp: <https://dbpedia.org/page/>

DELETE {?s ns1:person ?o1} 
INSERT {?s ns1:person ?o2} 
WHERE{
  ?s ns1:person ?o1 .
  BIND (IRI(replace(str(?o1), str(ns1:), str(dbp:)))  AS ?o2)
}

#This update changes the object in triples with ns1:person as the predicate. It changes it's prefix of ns1 (which is the "shortcut/shorthand" for example.org) to the prefix dbp (dbpedia.org)

Write an INSERT statement to add at least one significant date to the Mueller investigation, with literal type xsd:date. Write a DELETE/INSERT statement to change the date to a string, and a new DELETE/INSERT statement to change it back to xsd:date.

#Whilst this solution is not exactly what the task asks for, I feel like this is more appropiate given the dataset. The following update
changes the objects that uses the cp_date as predicate from a URI, to a literal with date as it's datatype 

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX ns1: <http://example.org#> 

DELETE {?s ns1:cp_date ?o}
INSERT{?s ns1:cp_date ?o3}
WHERE{
  ?s ns1:cp_date ?o .
  BIND (replace(str(?o), str(ns1:), "")  AS ?o2)
  BIND (STRDT(STR(?o2), xsd:date) AS ?o3)
}

#To test:

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX ns1: <http://example.org#> 

SELECT ?s ?o 
WHERE{
  ?s ns1:cp_date ?o.
  FILTER(datatype(?o) = xsd:date)
}

#To change it to an integer, use the following code, and to change it back to date, swap "xsd:integer" to "xsd:date" 

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX ns1: <http://example.org#> 

DELETE {?s ns1:cp_date ?o}
INSERT{?s ns1:cp_date ?o2}
WHERE{
  ?s ns1:cp_date ?o .
  BIND (STRDT(STR(?o), xsd:integer) AS ?o2)
}