Wikidata:SPARQL query service/queries/examples/maintenance

From Wikidata
Jump to navigation Jump to search

Queries for maintenance[edit]

These queries help to maintain the data quality of Wikidata. Note that some of these queries may return no results, it's OK - this means the data is correct, as such queries are meant to detect errors.

Find labels starting with double quotes (")[edit]

SELECT *
WHERE
{
  ?item rdfs:label ?label.
  FILTER(
    lang(?label) = "en" 
    && REGEX(str(?label),"^[\"].*") 
        )
}
LIMIT 1

Try it!


Queries for monitoring Wikidata entities sourced from the Disease Ontology[edit]

Disease items with redirected values[edit]

This query checks for existing redirects in disease statements. Regularly, the ProteinBoxBot synchronizes Disease items in Wikidata with the disease ontology. This query checks for statement value that got redirected to another Wikidata item.

#added before 2016-10

SELECT *
WHERE
{
   ?disease ?p ?o ;
            wdt:P699 ?doid.
   ?o owl:sameAs ?sa .
}

Try it!

Disease items with statements capturing Disease Ontology IDs that haven't been update for more then a month[edit]

ProteinBoxBot regularly synchronizes disease related items in Wikidata with the disease ontology. This query returns all modification dates ?date_update to less then a month.

  • Expected results: All update dates older then one month require manual inspection by a curator.
SELECT DISTINCT ?date_range ?date_update (count(?disease) as ?counts)
                (GROUP_CONCAT(?disease ;separator = "; ") as ?diseases)
                (GROUP_CONCAT(?do_id ;separator = "; ") as ?doids)
{
  ?disease p:P699/prov:wasDerivedFrom/pr:P813 ?date_update ;
           wdt:P699 ?do_id .
  BIND (now() - ?date_update as ?date_range)
  FILTER (?date_range > 31)
}
GROUP BY ?date_range ?date_update
ORDER BY DESC(?counts)

Try it!

Items on human genes that haven't been updated for more then a month[edit]

  • Expected results: All update dates older then one month require manual inspection by a curator.
#added before 2016-10

SELECT DISTINCT ?date_range ?date_update (count(?gene) as ?counts) (GROUP_CONCAT(?gene ;separator = "; ") as ?genes)  (GROUP_CONCAT(?ncbi_gene ;separator = "; ") as ?gene_ids) WHERE {
  ?gene p:P351 ?ncbigene ;
           wdt:P351 ?ncbi_gene .
  ?ncbigene prov:wasDerivedFrom ?derivedFrom  .
  ?derivedFrom pr:P813 ?date_update .
  BIND (now() - ?date_update as ?date_range)
  FILTER (?date_range > 31)
  }
GROUP BY ?date_range ?date_update
ORDER BY DESC(?counts)

Try it!

Translation of labels and descriptions[edit]

Missing labels for a target language[edit]

#Search for labels in other languages that do not exist in a target language
#Can be input for Q29032512
SELECT ?item ?itemLabel ?itemDescription WHERE {
 ?item wdt:P31 wd:Q5.  #instance of human
 ?item wdt:P27 wd:Q31. #country of citizenship Belgium

 SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,fr,en,de,it,es,no,pt". }
 FILTER(NOT EXISTS {
   ?item rdfs:label ?lang_label.
   FILTER(LANG(?lang_label) = "it") #with missing Italian label
 })
}
ORDER BY ?itemLabel

Try it!

Missing description for a target language[edit]

#Search for missing descriptions in a target language.
#Can be input for Q29032512
SELECT ?item ?itemLabel ?genderLabel ?occupationLabel ?domainLabel ?positionLabel ?itemDescription WHERE {
  ?item wdt:P31 wd:Q5.  # instance of human
  ?item wdt:P27 wd:Q31. # country of citizenship Belgium
optional{ ?item wdt:P21 ?gender.}
optional{ ?item wdt:P106 ?occupation.}
optional{ ?item wdt:P101 ?domain.}
optional{ ?item wdt:P39 ?position.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,fr,en,de,it,es,pt,no". }
  FILTER(NOT EXISTS {
    ?item schema:description ?itemdesc.
    FILTER(LANG(?itemdesc) = "nl") # with missing Dutch description
  })
}
order by ?itemLabel

Try it!

Identifying redirects and their targets in a list of QIDs[edit]

# Identify redirects using item property owl:sameAs

SELECT (REPLACE(STR(?item),".*Q","Q") AS ?qid) (REPLACE(STR(?tgt),".*Q","Q") AS ?tgtQid) ?tgtLabel 
WHERE {
   VALUES (?item) {
      (wd:Q6636058)   (wd:Q8989284)  (wd:Q243634)   (wd:Q13383295)
      (wd:Q659516)    (wd:Q1020776)  (wd:Q10124662) (wd:Q9066399)
      (wd:Q12309669)  (wd:Q16033883) (wd:Q733744)   (wd:Q226569)
      (wd:Q14210256)  (wd:Q408041)   (wd:Q750594)   (wd:Q11075312)
     }
  ?item owl:sameAs ?tgt .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY ?qid

Try it!

Find duplicate IMDB identifiers[edit]

#added before 2016-10

SELECT * WHERE {
  {
    SELECT ?id (COUNT(?obj) AS ?count) (GROUP_CONCAT(?obj; SEPARATOR = " , ") AS ?items) WHERE { ?obj wdt:P345 ?id. }
    GROUP BY ?id
  }
  FILTER(?count > 1)
}

Try it!

Administrative entities located within themselves[edit]

#added before 2016-10

SELECT ?x WHERE {
  ?x wdt:P131 ?x .
}

Try it!

People born after year 1601 and before year 1880 with no death date[edit]

This was originally before year 1880 but was returning primarily people born thousands of years BCE, which is not a helpful query for people who want to remediate the data. Updated the query to add an earliest birth date of 1602. Users may wish to edit the date to make it even later, e.g. 1800, to find people about whom there's more information. Note: the date 1601 will return people whose birth dates are "17th century" which may not be very useful, hence the choice of people born after 1601.

#updated 2019-09-27
SELECT ?h ?date
WHERE
{
	?h wdt:P31 wd:Q5 .
	?h wdt:P569 ?date .
	OPTIONAL {?h wdt:P570 ?d }
	FILTER (?date > "1601-01-01T00:00:00Z"^^xsd:dateTime)
	FILTER (?date < "1880-01-01T00:00:00Z"^^xsd:dateTime)
	FILTER (!bound(?d))
}
LIMIT 100

Try it!

Properties that are defined as "qualifier only" but are used in references[edit]

#added before 2016-10
SELECT ?entity ?claim ?prop
WHERE
{
	?prop wdt:P31 wd:Q15720608 .
	?prop wikibase:reference ?propClaim .
	?ref ?propClaim ?o .
	?statement prov:wasDerivedFrom ?ref .
	?entity ?claim ?statement .
}
LIMIT 100

Try it!

Objects that have globe which does not match their coordinates[edit]

#added before 2016-10

SELECT ?item ?body ?globe
WHERE {
?item wdt:P376 ?body .
?item p:P625/psv:P625/wikibase:geoGlobe ?globe .
filter (?globe != ?body) .
} LIMIT 30

Try it!

Aliases of properties which are used more than once[edit]

  • Items used: none
  • Properties used: all
#added before 2016-10
SELECT ?property ?alias ?occurences
WHERE
{
  hint:Query hint:optimizer "None" .

  {
    SELECT ?alias (COUNT (?alias) as ?occurences) WHERE {	
      ?tmp rdf:type wikibase:Property .
      ?tmp skos:altLabel ?alias FILTER (LANG (?alias) = "en")
    } GROUP BY ?alias
  }

  ?property rdf:type wikibase:Property .
  ?property skos:altLabel ?alias FILTER (?occurences > 1) .
}
ORDER BY ?alias

Try it!

Actresses without a description in Spanish[edit]

#added before 2016-10
SELECT DISTINCT ?item ?itemLabel
WHERE
{
	?item wdt:P106 wd:Q33999 .
	?item wdt:P21 wd:Q6581072 .
	OPTIONAL { ?item schema:description ?itemDescription  }
	FILTER (!BOUND(?itemDescription))
	?item wdt:P31 wd:Q5 .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "es"}
}
LIMIT 111

Try it!

French people with an article on the English Wikipedia but not on the French one[edit]

#added before 2016-10
SELECT ?item ?itemLabel (COUNT(distinct ?sitelink) as ?count)
WHERE
{
	?item wdt:P31 wd:Q5 .
	?item wdt:P27 wd:Q142 .
	?sitelink schema:about ?item .
	FILTER EXISTS { ?wen schema:about ?item . ?wen schema:inLanguage "en" }
	FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "fr" }
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "fr,en" .
	}
}
GROUP BY ?item ?itemLabel
ORDER BY DESC(?count)

Try it!

Deprecated rank statements[edit]

#added before 2016-10
SELECT ?wdLabel ?wdPLabel ?valueLabel ?wd ?wdP ?wds ?value
WHERE
{
	hint:Query hint:optimizer "None" .
	?wds wikibase:rank wikibase:DeprecatedRank .
	?wd ?p ?wds .
	?wds ?v ?value .
	?wdP wikibase:statementProperty ?v .
	?wdP wikibase:claim ?p .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
LIMIT 100

Try it!

VIAF identifier statements with deprecated rank[edit]

SELECT ?item ?value WHERE {
  ?item p:P214 ?statement.
  ?statement wikibase:rank wikibase:DeprecatedRank;
    ps:P214 ?value.
}

Try it!

Actors whose Spanish label ends with “ (actor)”[edit]

#added before 2016-10

# Actors whose Spanish label ends with “ (actor)”.
# This is almost certainly imported from the Wikipedia label, which needs to be unique (distinguish between John Doe the actor, John Doe the author, etc.).
# Wikidata labels have no such restrictions, and therefore should contain no such disambiguatory additions.
# Most likely, all these “ (actor)” suffixes can be removed.
# (This concept obviously applies equally to other professions and languages.)
SELECT ?person ?label (LANG(?label) AS ?lang)
WHERE
{
  ?person wdt:P31 wd:Q5;
          wdt:P106 wd:Q33999;
          rdfs:label ?label.
  FILTER(LANG(?label) = "es").
  FILTER(STRENDS(?label, " (actor)")).
}

Try it!

Labels containing HTML escape sequences[edit]

#added before 2016-10

SELECT ?item ?label
WHERE
{
  ?item rdfs:label ?label.
  FILTER CONTAINS(?label, "&quot;").
}
LIMIT 1

Try it!

Person labels containing parentheses[edit]

#added before 2016-10

SELECT ?person ?label (LANG(?label) AS ?lang)
WHERE
{
  ?person wdt:P31 wd:Q5;
          rdfs:label ?label.
  FILTER(LANG(?label) IN ("en", "es", "fr", "de")). # tweak to taste
  FILTER(CONTAINS(?label, "(")).
}
LIMIT 10

Try it!

Non-integer populations[edit]

#added before 2016-10

SELECT ?country ?countryLabel ?population
WHERE
{
  ?country wdt:P1082 ?population.
  FILTER(?population - ROUND(?population) != 0).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?population

Try it!

Odd countries[edit]

#added before 2016-10

SELECT ?item ?country ?itemLabel ?countryLabel
WHERE
{
  ?item wdt:P17 ?country.
  MINUS { ?country wdt:P31/wdt:P279* wd:Q7275. }     # excludes normal countries
  MINUS { ?country wdt:P31/wdt:P279* wd:Q183366. }   # excludes Scotland
  MINUS { ?country wdt:P31/wdt:P279* wd:Q15893266. } # excludes Brittany
  MINUS { ?country wdt:P31/wdt:P279* wd:Q1048835. }  # excludes the European Union
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 1000

Try it!

Paintings on taxons[edit]

#added before 2016-10

SELECT ?material ?painting ?materialLabel ?paintingLabel
WHERE
{
  ?painting wdt:P31/wdt:P279* wd:Q3305213;
            p:P186 [ ps:P186 ?material; pq:P518 wd:Q861259 ].
  ?material wdt:P31 wd:Q16521.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Descriptions that are just the default Portuguese description[edit]

#added before 2016-10

SELECT ?item ?label
WHERE
{
  ?item schema:description "introduza uma descrição"@pt.
  OPTIONAL {
    ?item rdfs:label ?label.
    FILTER(LANG(?label) = "pt").
  }
}

Try it!

Items that are simultaneously instance and subclass of the same class[edit]

#added before 2016-10

SELECT ?thingLabel ?classLabel ?thing ?class
WHERE
{
	?thing wdt:P31 ?class.
	?thing wdt:P279 ?class.
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Items with specific picture[edit]

Note that URL of the filename must be URL encoded.

#added before 2016-10

SELECT ?item ?itemLabel
WHERE
{
?item wdt:P18 <http://commons.wikimedia.org/wiki/Special:FilePath/Flag%20of%20the%20Netherlands.svg>
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
LIMIT 100

Try it!

Language statements that point to a country[edit]

These language statements point to a country instead of a language, which is almost certainly a mistake. In certain languages, a country and its language may have the same label.

#added before 2016-10

SELECT DISTINCT ?work ?workLabel ?languageLabel
WHERE
{
  ?work wdt:P37|wdt:P103|wdt:P364|wdt:P407|wdt:P1412 ?language.
  ?language wdt:P31/wdt:P279* wd:Q7275.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?languageLabel

Try it!

People with statements where start and end time are over 100 years apart[edit]

It is rare that a statement for a person is actually valid for over 100 years; most of the results are errors (often simple typos in the year).

#added before 2016-10

SELECT ?person ?personLabel ?duration
WHERE
{
  ?person wdt:P31 wd:Q5;
          ?p [ pq:P580 ?start; pq:P582 ?end ].
  BIND(ROUND((?end - ?start)/365.2425) AS ?duration).
  FILTER(?duration > 100).
  FILTER(?p != p:P119) . # "place of burial" can be an exception
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY DESC(?duration)

Try it!

Human gene items with unreferenced statements[edit]

This query returns the number of times a given property exists with in a human gene item without references backing that statement.

#added before 2016-10

SELECT ?prop (count(?prop) as ?counts) WHERE
{SELECT DISTINCT ?item ?prop WHERE {
   ?item wdt:P279 wd:Q7187 ;
         wdt:P703 wd:Q5 ;
         ?prop ?statement .
  FILTER(!(regex(str(?prop), "http://www.wikidata.org/prop/direct/" ) ))
  FILTER(regex(str(?prop), "http://www.wikidata.org/prop/" ) )
  FILTER NOT EXISTS {
               ?item ?prop ?statement .
               ?statement prov:wasDerivedFrom ?derivedFrom  .}
}
}
GROUP BY ?prop
ORDER BY desc(?counts)

Try it!

Capitals that aren’t capitals[edit]

This query returns items that are instances of capital but not the capital of any item.

#added before 2016-10

SELECT ?capital ?capitalLabel ?ofLabel ?start ?end
WHERE
{
  ?capital p:P31 ?capitalStat.
  ?capitalStat ps:P31 wd:Q5119.
  OPTIONAL { ?capitalStat pq:P642 ?of. }
  OPTIONAL { ?capitalStat pq:P580 ?start. }
  OPTIONAL { ?capitalStat pq:P582 ?end. }
  MINUS { [] wdt:P36 ?capital. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Chemical compounds with no CAS registry number[edit]

#added before 2016-10

#All CAS registry numbers in Wikidata
SELECT DISTINCT ?compound ?compoundLabel ?formula ?inchikey ?chemspider ?pubchem WHERE {
  ?compound wdt:P31 wd:Q11173 .
  OPTIONAL { ?compound wdt:P231 ?cas . }
  OPTIONAL { ?compound wdt:P274 ?formula . }
  OPTIONAL { ?compound wdt:P235 ?inchikey . }
  OPTIONAL { ?compound wdt:P661 ?chemspider . }
  OPTIONAL { ?compound wdt:P662 ?pubchem . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  FILTER(!BOUND(?cas))
}

Try it!

Human settlements without an article in the English Wikipedia[edit]

# Human settlements without an article in the English Wikipedia
#defaultView:Map
SELECT DISTINCT ?settlement ?settlementLabel ?coor WHERE {
  VALUES ?type {wd:Q3957 wd:Q515 wd:Q532 wd:Q486972}
  ?settlement wdt:P31 wd:Q3957 ;
              wdt:P625 ?coor .   
  ?article schema:about ?settlement .
  FILTER NOT EXISTS { ?wen schema:about ?settlement ; schema:inLanguage "en" }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Human settlements without an article in any language version of Wikipedia[edit]

#added before 2016-10

# Human settlements without an article in any language version of Wikipedia
#defaultView:Map
SELECT DISTINCT ?city ?cityLabel ?coor WHERE {
    VALUES ?type { wd:Q3957 wd:Q515 wd:Q532 wd:Q486972 } .
    ?city wdt:P31 wd:Q3957 ;
          wdt:P625 ?coor .

    FILTER NOT EXISTS {?article schema:about ?city } .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

Files used as “image” in more than 10 items[edit]

#added before 2016-10

SELECT ?image (count(?image) AS ?cnt) (group_concat(?item) AS ?items) {
  ?item wdt:P18 ?image
  } group by ?image having (?cnt>10)

Try it!

Properties, their labels, descriptions and if they exist of which they are a subproperty[edit]

Description: This query returns a table with all properties and if they exist their subproperties

#added before 2016-10

SELECT DISTINCT ?property ?propertyLabel ?propertyDescription ?subpropertyOf ?subpropertyOfLabel 
WHERE
{
    ?property rdf:type wikibase:Property .
    OPTIONAL {?property wdt:P1647  ?subpropertyOf .}
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }	
}

Try it!

Most frequent occupations without a label in a given language[edit]

#added before 2016-10

#Most frequent occupations without a label in Latvian (lv)
#by Edgars2007/Jura1, 2016-05-09
SELECT ?occup ?occupLabel ?count
WHERE
{
	{
		SELECT ?occup (COUNT(?person) as ?count)
		WHERE
		{
			?person wdt:P106 ?occup
		}
		GROUP BY ?occup
		ORDER BY DESC(?count)
		LIMIT 1000
	}
  	OPTIONAL {?occup rdfs:label ?label1 	filter(lang(?label1) = "lv")}
	FILTER(!BOUND(?label1))
 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es,pl,ja,zh,ru" }
}
ORDER BY DESC(?count)
LIMIT 50

Try it!

Items and properties where the description is the same as the label[edit]

#Items and properties where the description is the same as the label
#by YMS, 2016-09-26
select ?item ?label (lang(?label) as ?lang) where {
  ?item rdfs:label ?label .
  ?item schema:description ?label .
} limit 50

Try it!

A batch of entities that were imported from Commons that would need to be merged with pre-existing entities[edit]

SELECT ?subject ?subjectLabel ?gallery
WHERE {
    ?subject wdt:P31 wd:Q5 .
    ?subject wdt:P935 ?gallery.
    FILTER NOT EXISTS { ?subject wdt:P18 ?x }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" }
    FILTER regex(str(?subject), "Q211", "i")
}
LIMIT 100

Try it!

How many people are there whose "number of children (P1971)" matches with "child (P40)"?[edit]

PS: Including the corner case where the number of children equals 0. P1971 is mainly used when full listings of children can't or shouldn't be included.

SELECT (COUNT(DISTINCT(?person)) AS ?countPerson)
WHERE {
  { # non-zero number of children matches
    ?person wdt:P1971 ?numOfChildren .
    {SELECT ?person (COUNT(?child) AS ?numOfChildrenComputed)
    WHERE {
      ?person wdt:P40 ?child 
    }
    GROUP BY ?person}
    FILTER (?numOfChildren = ?numOfChildrenComputed)
  }
  UNION
  { # zero number of children matches
    ?person wdt:P1971 "0"^^xsd:decimal .
    FILTER NOT EXISTS { ?person wdt:P40 ?child }
  }
}

Try it!

Class distribution of a property[edit]

PS: In the example, the property used is "child (P40)". This can be changed to any other property.

SELECT ?class (COUNT(?subject) AS ?countSubject) WHERE {
  { SELECT DISTINCT ?subject ?class
    WHERE {
      ?subject wdt:P40 ?object .
      ?subject wdt:P31 ?class
    } }
} GROUP BY ?class ORDER BY DESC(?countSubject)

Try it!

Disambiguation pages entities used as publishers[edit]

SELECT ?publisher ?publisherLabel ?work WHERE {
  # entities used as publisher
  ?work wdt:P123 ?publisher .
  # but that are actually a disambiguation page
  ?publisher wdt:P31 wd:Q4167410 .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en,fr,de" .
  }
}

Try it!

Instances of actual languages[edit]

#Instances of actual languages (e.g. "instance of Hindi", often incorrectly set to indicate the language of something or to request a sitelink)
SELECT ?item ?itemLabel
WHERE {
  ?item wdt:P31/wdt:P31* wd:Q34770 .
  FILTER NOT EXISTS {?item wdt:P31 wd:Q34770}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Look up q-codes based on English label[edit]

SELECT ?lab ?item WHERE {
  ?item rdfs:label ?lab .
  MINUS {?item wdt:P31 wd:Q4167836 } . # no category items
  VALUES ?lab {"Agelenopsis pennsylvanica"@en
    "Agelenopsis potteri"@en
    "Anelosimus studiosus"@en
  } .
}

Try it!

CONSTRUCT query to get the RDF graph for a Wikidata item (e.g. asthma)[edit]

CONSTRUCT {
  wd:Q35869 ?p ?o .
  ?o ?qualifier ?f .
  ?o prov:wasDerivedFrom ?u .
  ?u ?a ?b .
  }
WHERE {
  wd:Q35869 ?p ?o .
  optional {?o ?qualifier ?f .}
  OPTIONAL {?o prov:wasDerivedFrom ?u .
  ?u ?a ?b .}
  }

Try it!

Personendaten template equivalent (dewiki)[edit]

SELECT
	?item
	?NAME
	(GROUP_CONCAT( DISTINCT ?ALTERNATIVNAMEN; separator=";") 	as ?ALTERNATIVNAMEN)
	(SAMPLE ( ?KURZBESCHREIBUNG) 						as ?KURZBESCHREIBUNG)
	(GROUP_CONCAT( DISTINCT ?GEBURTSDATUM; separator=";") 		as ?GEBURTSDATUM)
	(GROUP_CONCAT( DISTINCT ?GEBURTSORT; separator=";") 			as ?GEBURTSORT)
	(GROUP_CONCAT( DISTINCT ?STERBEDATUM; separator=";") 		as ?STERBEDATUM)
	(GROUP_CONCAT( DISTINCT ?STERBEORT; separator=";") 			as ?STERBEORT)
	?article
WHERE
{
	{ SELECT ?item ?article { ?article 	schema:about ?item ; schema:isPartOf <https://de.wikipedia.org/> . ?item wdt:P31 wd:Q5 . } LIMIT 200 }
  	OPTIONAL { ?item skos:altLabel ?ALTERNATIVNAMEN  		. FILTER(lang(?ALTERNATIVNAMEN)="de")  }.
  	OPTIONAL { ?item schema:description ?KURZBESCHREIBUNG 	. FILTER(lang(?KURZBESCHREIBUNG)="de") } . 
  	OPTIONAL { ?item wdt:P569 	?GEBURTSDATUM }	.
  	OPTIONAL { ?item wdt:P19 	?pob }	.
  	OPTIONAL { ?item wdt:P570 	?STERBEDATUM }	.
  	OPTIONAL { ?item wdt:P20 	?pod }	.
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "de" .
							?item rdfs:label ?NAME .
                            ?pob rdfs:label ?GEBURTSORT .
                            ?pod rdfs:label ?STERBEORT  }
}
GROUP BY ?item ?NAME ?article

Try it!

People that have a label in Hebrew, but their given name doesn't have a label in Hebrew[edit]

SELECT ?item ?itemLabelHE ?name WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P735 ?name.
  FILTER NOT EXISTS {?name rdfs:label ?nameLabelHE filter (lang(?nameLabelHE) = "he")} .
  ?item rdfs:label ?itemLabelHE filter (lang(?itemLabelHE) = "he") .
  } LIMIT 50

Try it!

Fathers with non-existent or unusual gender statements[edit]

# Fathers with non-existent or unusual gender statements

SELECT
  (?father as ?father_id) ?father_name ?father_is_a ?father_gender
  (?child  as ?child_id)  ?child_name  ?child_is_a
WHERE {
  ?child  wdt:P22 ?father .
  ?father wdt:P31 ?f_is_a . #?father wdt:P31 wd:Q5 . 
  ?child  wdt:P31 ?c_is_a . #?child wdt:P31 wd:Q5 .
  OPTIONAL { ?father wdt:P21 ?f_gender . }
  
  # Aliases for the headings in the results table. Must be done this way because
  # SELECT (?var as ?varAlias) works, but (?varLabel as ?varLabelAlias) doesn't.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?father   rdfs:label ?father_name .
    ?f_is_a   rdfs:label ?father_is_a .
    ?f_gender rdfs:label ?father_gender .
    ?child    rdfs:label ?child_name .
    ?c_is_a   rdfs:label ?child_is_a .
  }
  
  # Show only results where the father's gender:
  FILTER( 
    # ...is not set
    NOT EXISTS { ?father wdt:P21 [] }
    # ...or is set to something other than "male" or "male organism", or "unknown value"
    || ?f_gender NOT IN(wd:Q6581097, wd:Q44148) && !isBLANK(?f_gender)
  )
}
ORDER BY DESC(?f_gender)

Try it!

Items created last[edit]

Since the item creation date seems not to be available for SPARQL queries, here a query for the 10 latest items of a certain class (lighthouse (Q39715) as an example).

select ?item ?itemLabel
where {
  # restrict to some class, or select otherwise
  # (does not work with results too large!)
  ?item wdt:P31 wd:Q39715 .
  bind(xsd:integer(strafter(str(?item), 'Q')) as ?id)
  service wikibase:label { bd:serviceParam wikibase:language "en" .}
}
order by desc(?id )
limit 10

Try it!

String search : All items having the string "airport" without having instance of (P31) nor subclass of (P279)[edit]

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "airport -haswbstatement:P31 -haswbstatement:P279" .
    ?title wikibase:apiOutput mwapi:title. 
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  #add any other requirements here
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Performant way to list 100 scholary articles sorted by linkcount[edit]

select ?item ?itemLabel ?itemDescription ?linkCount with {
  select ?item where {
    ?item wdt:P31 wd:Q13442814.
  } limit 100
} as %result where {
  include %result
  ?item wikibase:sitelinks ?linkCount.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?linkCount)

Try it!

Display the class tree under a known class (subclass of)[edit]

# Tree of classes under a specified root, up to 7 levels deep
SELECT ?level1Label ?level2Label ?level3Label ?level4Label ?level5Label ?level6Label ?level7Label ?level1 ?level2 ?level3 ?level4 ?level5 ?level6 ?level7
WHERE 
{
  # Adjust the class value to display a different branch of the class tree (here the class 'database')
  VALUES ?class { wd:Q8513 }
  {
    ?level1 wdt:P279 ?class .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
    ?level3 wdt:P279 ?level2 .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
    ?level3 wdt:P279 ?level2 .
    ?level4 wdt:P279 ?level3 .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
    ?level3 wdt:P279 ?level2 .
    ?level4 wdt:P279 ?level3 .
    ?level5 wdt:P279 ?level4 .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
    ?level3 wdt:P279 ?level2 .
    ?level4 wdt:P279 ?level3 .
    ?level5 wdt:P279 ?level4 .
    ?level6 wdt:P279 ?level5 .
  }
  UNION
  {
    ?level1 wdt:P279 ?class .
    ?level2 wdt:P279 ?level1 .
    ?level3 wdt:P279 ?level2 .
    ?level4 wdt:P279 ?level3 .
    ?level5 wdt:P279 ?level4 .
    ?level6 wdt:P279 ?level5 .
    ?level7 wdt:P279 ?level6 .
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}
ORDER BY ?level1Label ?level2Label ?level3Label ?level4Label ?level5Label ?level6Label ?level7Label

Try it!

Europeana[edit]

Examples from workshop 2018[edit]

Mayors that are either a dog, a cat or a chicken (and that contains an image)

SELECT ?image ?speciesLabel ?mayorLabel ?placeLabel WHERE {
 
 VALUES ?species {wd:Q144 wd:Q146 wd:Q780}
 ?mayor wdt:P31 ?species .
 ?mayor p:P39 ?node .
 ?node ps:P39 wd:Q30185 .
 ?node pq:P642 ?place .
 ?mayor wdt:P18 ?image
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Mayors that are any kind of domesticated animal:

SELECT ?image ?speciesLabel ?mayorLabel ?placeLabel WHERE {
 ?species wdt:P279* wd:Q622852 .
 ?mayor wdt:P31 ?species . 
 
 ?mayor p:P39 ?node .
 ?node ps:P39 wd:Q30185 .
 ?node pq:P642 ?place .
 optional{?mayor wdt:P18 ?image}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

All impressionist painters that have been in an exhibition, together with the amount of exhibitions they have been in.

SELECT DISTINCT ?painter ?painterLabel (count (DISTINCT ?exhibition) as ?exhibition_count) 
(group_concat(DISTINCT ?exhibitionLabel; separator=", ") as ?exhibitions)
WHERE {
  ?painter wdt:P106 wd:Q1028181 . #give me all people with occupation (P106) painter (Q1028181)
  ?painter wdt:P135 wd:Q40415 . #who belonged to the impressionist (Q40415) movement (P135)  
  ?painting wdt:P170  ?painter . #the paintings created by (P170) the painter
  ?painting wdt:P608 ?exhibition . #have an exhibition history (P608) at an exhibition
  ?exhibition rdfs:label ?exhibitionLabel . #give me the english Labels of these exhibitions, if possible
  FILTER (lang(?exhibitionLabel) = "en") 

  SERVICE wikibase:label {bd:serviceParam wikibase:language "en".}
} GROUP BY ?painter ?painterLabel

Try it!

Unsorted[edit]

Occupations with a German label[edit]

SELECT DISTINCT ?occ ?occLabelEN ?occLabelDE {
  ?occ (wdt:P31/wdt:P31* | wdt:P279/wdt:P279*) wd:Q12737077 .
  FILTER NOT EXISTS { ?occ wdt:P31 wd:Q5 }
  FILTER NOT EXISTS { ?occ wdt:P31 wd:Q13406463 }
  FILTER ( ?occ != wd:Q28640 )
  ?occ rdfs:label ?occLabelDE filter (lang(?occLabelDE) = "de") .
  OPTIONAL {
    ?occ rdfs:label ?occLabelEN filter (lang(?occLabelEN) = "en")
  }
}

Try it!

Animals owned by people holding any position[edit]

SELECT ?animal ?animalLabel ?itemLabel (SAMPLE(?image) as ?image) 
(GROUP_CONCAT(DISTINCT(?personLabel); separator=", ") as ?owners) {
  ?item wdt:P31 wd:Q16521.
  ?animal wdt:P31 ?item .
  ?person wdt:P39 ?x .
  ?animal wdt:P127 ?person .
  OPTIONAL { ?animal wdt:P18 ?image }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
    ?animal rdfs:label ?animalLabel .
    ?item rdfs:label ?itemLabel .
    ?person rdfs:label ?personLabel
  }
} GROUP BY ?animal ?animalLabel ?itemLabel

Try it!

Properties of type external-id[edit]

# Make a list of properties of the type external-id
SELECT ?property ?propertyLabel ?propertyDescription WHERE {
  ?property wikibase:propertyType wikibase:ExternalId .
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "en" .
	}          
} ORDER BY ?propertyLabel

Try it!

Items with a Biografisch Portaal number and fewer than 6 statements[edit]

# List items that have a Biografisch Portaal number (P651), but have fewer than 6 statements
# The minimum set of statements expected are:
# * Biografisch Portaal number (P651)
# * instance of (P31) -> human (Q5)
# * sex or gender (P21)
# * place of birth (P19)
# * date of birth (P569)
# The SPARQL database isn't completely up to date for the number of statements, so this query will return fewer or incorrect items.

SELECT ?item ?itemLabel ?itemDescription ?statementcount WHERE {
  ?item wdt:P651 [] .
  ?item wikibase:statements ?statementcount .
  FILTER (?statementcount < 6 )
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "en,nl" .
	}          
} ORDER BY ?itemLabel

Try it!

Most specific parent classes of Wikidata classes[edit]

# What is or are the lowest common ancestor(s) of a 
# church, a mosque, a synagogue and a palace in the 
# Wikidata ontology ? Answer : an architectural structure

SELECT ?lcs ?lcsLabel WHERE {
    ?lcs ^wdt:P279* wd:Q32815, wd:Q34627, wd:Q16970, wd:Q16560 .
    filter not exists {
    ?sublcs ^wdt:P279* wd:Q32815, wd:Q34627, wd:Q16970, wd:Q16560 ;
          wdt:P279 ?lcs . }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en" . }
  }

Try it!