Wikibase/Indexing/SPARQL Query Examples

Syntax according to RDF Dump format. See also https://phabricator.wikimedia.org/T86786

Standard Prefixes
These should be used every time before the query:

PREFIX wd:  PREFIX wdt:  PREFIX wikibase:  PREFIX p:  PREFIX v:  PREFIX q:  PREFIX rdfs: 

People born before year 1880 with no death date
PREFIX wd:  PREFIX wdt:  PREFIX xsd:  SELECT ?h ?date WHERE { ?h wdt:P31 wd:Q5. ?h wdt:P569 ?date. FILTER NOT EXISTS {?h wdt:P570 ?d } FILTER (?date < "1880-01-01T00:00:00Z"^^xsd:dateTime) } LIMIT 100

Largest cities with female mayor
PREFIX wd:  PREFIX wdt:  PREFIX p:  PREFIX v:  PREFIX q:  PREFIX rdfs:  SELECT DISTINCT ?city ?citylabel ?mayorlabel WHERE { ?city wdt:P31/wdt:P279* wd:Q515. # find instances of subclasses of city ?city p:P6 ?statement. # with a P6 (head of goverment) statement ?statement v:P6 ?mayor. # ... that has the value ?mayor ?mayor wdt:P21 wd:Q6581072. # ... where the ?mayor has P21 (sex or gender) female FILTER NOT EXISTS { ?statement q:P582 ?x } # ... but the statement has no P582 (end date) qualifier # Now select the population value of the ?city # (the number is reached through a chain of three properties) ?city wdt:P1082 ?population. # Optionally, find English labels for city and mayor: OPTIONAL { ?city rdfs:label ?citylabel. FILTER ( LANG(?citylabel) = "en" ) } OPTIONAL { ?mayor rdfs:label ?mayorlabel. FILTER ( LANG(?mayorlabel) = "en" ) } } ORDER BY DESC(?population) LIMIT 10

List of countries ordered by the number of their cities with female mayor
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX wikibase: <http://wikiba.se/ontology#> PREFIX p: <http://www.wikidata.org/prop/> PREFIX v: <http://www.wikidata.org/prop/statement/> PREFIX q: <http://www.wikidata.org/prop/qualifier/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?country ?label (count(*) as ?count) WHERE { ?city wdt:P31/wdt:P279* wd:Q515. # find instances of subclasses of city ?city p:P6 ?statement. # with a P6 (head of goverment) statement ?statement v:P6 ?mayor. # ... that has the value ?mayor ?mayor wdt:P21 wd:Q6581072. # ... where the ?mayor has P21 (sex or gender) female FILTER NOT EXISTS { ?statement q:P582 ?x } # ... but the statement has no P582 (end date) qualifier ?city wdt:P17 ?country  # Also find the country of the city # If available, get the en label of the country: OPTIONAL { ?country rdfs:label ?label. FILTER ( LANG(?label) = "en" ) } } GROUP BY ?country ?label ORDER BY DESC(?count) LIMIT 100

How many states this US state borders
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?state ?stateL ?borders WHERE { { SELECT ?state (COUNT(?otherState) as ?borders) WHERE { ?state wdt:P31 wd:Q35657. ?otherState wdt:P47 ?state. ?otherState wdt:P31 wd:Q35657. }  GROUP BY ?state } OPTIONAL { ?state rdfs:label ?stateL. FILTER (lang(?stateL) = "en") } }         ORDER BY DESC(?borders)

Whose birthday is today?
PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?entity (year(?date) as ?year) WHERE { ?entityS wdt:P569 ?date. ?entityS rdfs:label ?entity. FILTER (datatype(?date) = xsd:dateTime) FILTER (month(?date) = month(now)) FILTER (day(?date) = day(now)) } LIMIT 10

Who discovered the most asteroids?
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?discoverer ?name (COUNT(?asteroid) AS ?count) WHERE { ?asteroid wdt:P31 wd:Q3863. ?asteroid wdt:P61 ?discoverer. OPTIONAL { ?discoverer rdfs:label ?name FILTER (lang(?name) = "en") } } GROUP BY ?discoverer ?name ORDER BY DESC(?count) LIMIT 10

Who discovered the most planets?
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?discoverer ?name (COUNT(?planet) as ?count) WHERE { ?ppart wdt:P279* wd:Q634. ?planet wdt:P31 ?ppart. ?planet wdt:P61 ?discoverer. OPTIONAL { ?discoverer rdfs:label ?name FILTER (lang(?name) = "en") } } GROUP BY ?discoverer ?name ORDER BY DESC(?count) LIMIT 10

American universities founded before the states they reside in were created
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> SELECT ?uniName ?founded ?stateName ?stateStart WHERE { ?u wdt:P31|wdt:P279/wdt:P31 wd:Q3918. ?u wdt:P131+ ?state. ?state wdt:P31 wd:Q35657. ?state wdt:P571 ?stateStart. ?u wdt:P571 ?founded. FILTER (?founded < ?stateStart). OPTIONAL { ?state rdfs:label ?stateName FILTER (lang(?stateName) = "en") ?u rdfs:label ?uniName FILTER (lang(?uniName) = "en") } } LIMIT 10