User:Pigsonthewing/Queries

Voice recordings

Wikimedia users and their voice recording, if any

#Wikimedia users and their voice recording, if any
SELECT ?user ?userLabel ?Wikimedia_user_name ?userVoice WHERE 
{
  ?user wdt:P4174 ?Wikimedia_user_name.
  OPTIONAL { ?user wdt:P990 ?userVoice. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

People with more than one voice recording

SELECT ?item ?itemLabel (COUNT(*) AS ?count)
WHERE { ?item p:P990 [] 
        SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      } 
GROUP BY ?item ?itemLabel HAVING(?count > 1) ORDER BY DESC(?count)

Try it!

Query OSM for a QID

• https://overpass-turbo.eu/s/toj

People by surname and initial

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P734  wd:Q16479693  . # Family name Badger
  ?item wdt:P735 ?name .
  ?name rdfs:label ?nameLabel .
  FILTER (LANG(?nameLabel) = "en")
  FILTER (STRSTARTS(?nameLabel, "E")) # First initial
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Author name strings

#Author name strings 
SELECT ?item ?itemLabel
{
  ?item wdt:P2093 "Bill Thompson" .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it! (https://w.wiki/37V)

Labels in every language

...for a given item:

#Labels in every language for a given item
select ?lang ?label {
  wd:Q171497 rdfs:label ?label .
  bind (lang(?label) as ?lang) .
} order by ?lang

Try it!

https://query.wikidata.org/embed.html#select%20%3Flang%20%3Flabel%20%7B%0A%20%20wd%3AQ171497%20rdfs%3Alabel%20%3Flabel%20.%0A%20%20bind%20(lang(%3Flabel)%20as%20%3Flang)%20.%0A%7D%20order%20by%20%3Flang

Find item by website

SELECT ?item ?itemLabel
{
  ?item wdt:P856 ?url.
  filter(contains(str(?url),"twitter.com")) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Biologists with Twitter accounts

#Biologists with Twitter accounts
SELECT DISTINCT ?person ?personLabel ?twitterName
WHERE {
  ?person wdt:P2002 ?twitterName ;
    wdt:P106 ?occupation .
  ?occupation wdt:P279* wd:Q864503 . # all subclasses of biologists
    FILTER NOT EXISTS { ?person  wdt:P496 ?orcid }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

ORCID

FRS appointed in [year], with no ORCID iD

#FRS appointed in 2019, with no ORCID iD
SELECT ?frs ?frsLabel
WHERE
{
	?frs wdt:P31 wd:Q5.
    ?frs p:P166 ?award.
    ?award ps:P166 wd:Q15631401.
    ?award pq:P585 ?year.
    FILTER (?year = "2019-01-01T00:00:00Z"^^xsd:dateTime) .
    MINUS { ?frs wdt:P496 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

People with an ORCID iD, and an article in a given Wikipedia

#People with an ORCID iD, and an article in a given Wikipedia
SELECT ?person ?orcid ?personLabel ?sitelink
{
  ?person wdt:P496 ?orcid.
  ?sitelink schema:about ?person.
  ?sitelink schema:isPartOf <https://he.wikipedia.org/>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
}

Try it!

ORCID iD holders that have an article on a given Wikipedia

#ORCID iD holders that have an article on a given Wikipedia
SELECT ?sitelink ?itemLabel ?orcid 
{
  ?item wdt:P31 wd:Q5.
  ?item wdt:P496 ?orcid.
  ?sitelink schema:isPartOf <https://de.wikipedia.org/>;
  schema:about ?item;
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}  ORDER BY ?itemLabel

Try it!

People alive after 2012, who received a Nobel prize, with ORCID iD, if any

#People alive after 2012, who received a Nobel prize, with ORCID iD, if any
SELECT ?person ?personLabel ?ORCID ?award ?awardLabel ?ddate WHERE 
{
  ?person wdt:P166 ?award.
  ?person wdt:P31 wd:Q5.
  ?award wdt:P279* ?type.
  ?type wdt:P31 wd:Q7191.
  OPTIONAL { ?person wdt:P496 ?ORCID. }
  OPTIONAL { ?person wdt:P570 ?ddate. 
  FILTER(?ddate > "2012-01-01T00:00:00Z"^^xsd:dateTime) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Works of an author, from their ORCID iD

#Works of an author, from their ORCID iD 
SELECT distinct ?work ?workLabel ?typeLabel
WHERE {
 ?person wdt:P496 ?orcid .                  # person has an ORCID
 Values ?orcid { "0000-0001-5882-6823" }.   # ...and the ORCID is...
 ?work wdt:P50 ?person .                    # a work has this person as author
 ?work wdt:P31 ?type .                      # what type is the work?
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

External-id properties, with no formatter URL

SELECT ?property ?propertyLabel WHERE 
{
  ?property wikibase:propertyType wikibase:ExternalId .
  MINUS { ?property wdt:P1630 [] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Birds with different eBird & ABA IDs

SELECT ?item ?itemLabel ?ebird ?ababird WHERE {
  ?item wdt:P3444 ?ebird.
  ?item wdt:P4526 ?ababird .
  BIND(IF(?ebird != ?ababird, 'different', 'same') AS ?match) .
  FILTER(?match!='same').
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
  }
}

Try it!

Taxon authors with no death date

# Taxon authors with no death date
  SELECT DISTINCT ?author ?authorLabel WHERE {
  ?taxon p:P225/pq:P405 ?author.
  ?author wdt:P31 wd:Q5.
  MINUS { ?author wdt:P570 ?dateOfDeath. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Museums in Country

SELECT ?museum ?museumLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?museum wdt:P31/wdt:P279* wd:Q33506.
  ?museum wdt:P17 wd:Q252.
}

Try it!

Five-letter words in English

SELECT ?lexeme ?word WHERE {
  ?lexeme a ontolex:LexicalEntry ; dct:language ?language ; ontolex:lexicalForm ?form .
  ?form ontolex:representation ?word .
  ?language wdt:P218 'en' .
  filter(strlen(?word)=5)
  filter(strstarts(ucase(?word),"F"))  
} order by ?word

Try it!

Flourit

select (min(year(?date)) as ?min_year) (max(year(?date)) as ?max_year ) {
  values ?author { wd:Q937 }
  ?author ^wdt:P50/wdt:P577 ?date .
}

Try it!

Duplicate BHL IDs

SELECT ?item ?itemLabel (COUNT(*) AS ?count)
WHERE { ?item p:P4081 [ wikibase:rank ?rank ] . FILTER(!(?rank=wikibase:DeprecatedRank)) 
        SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      } 
GROUP BY ?item ?itemLabel HAVING(?count > 1) ORDER BY DESC(?count)

Try it!

England & Wales council websites

Show type of council and dissolution date if any

SELECT ?item ?itemLabel ?website ?P31Label ?dissolved WHERE {
  VALUES ?P31 {
    wd:Q21561306
    wd:Q110418000
    wd:Q110417190
    wd:Q21561350
    wd:Q3154693
    wd:Q21561328
    wd:Q19414242
    wd:Q16690653
    wd:Q110416322
    wd:Q21451686
  }
  ?item p:P31 ?stat.
  ?stat ps:P31 ?P31.
  FILTER(NOT EXISTS { ?stat pq:P582 _:b3. })
  ?item wdt:P17 wd:Q145.
  OPTIONAL { ?item wdt:P856 ?website. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P576 ?dissolved. }
}

Try it!

References by web domain

Note: domain is entered twice.

SELECT DISTINCT ?item ?itemLabel ?stmt ?refURL
WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "birmingham.gov.uk".
    ?item wikibase:apiOutputItem mwapi:title . 
  }
  ?item ?p ?stmt .
  ?stmt prov:wasDerivedFrom/pr:P854 ?refURL .
  FILTER(CONTAINS(str(?refURL), 'birmingham.gov.uk')) .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} LIMIT 1000

Try it!

All values for a property, on a single item

For a given item (say Q116738730) return each of the values for a given property (say P921).

SELECT ?item ?itemLabel ?value ?valueLabel
WHERE 
{
  VALUES ?item {wd:Q116738730}
  ?item p:P921/ps:P921 ?value. 
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

Problems to solve

Papers on topic, without 'main subject'

e.g. OpenStreetMap (Q936)

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 "OpenStreetMap haswbstatement:P31=Q13442814".
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  FILTER NOT EXISTS { ?item wdt:P921 wd:Q936. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
# LIMIT 10000

Try it!

Papers with upper-case titles

(By year, to prevent time-outs)

SELECT ?item ?title
WHERE
{
  ?item wdt:P31 wd:Q13442814 . # Scientific article
  ?item wdt:P1476 ?title .
  FILTER (?title = UCASE(?title) &&  # Title doesn't change if converted to uppercase
          ?title != LCASE(?title))   # Title does change if converted to lowercase (to sort out scripts which don't distinguish like e.g. Chinese)
  
  ?item wdt:P577 ?publication_date. hint:Prior hint:rangeSafe true.
  FILTER (?publication_date >= "2019-00-00"^^xsd:dateTime && # Published in 2019
          ?publication_date < "2020-00-00"^^xsd:dateTime)
}

Try it!

websites without official URL

#websites without official URL
SELECT ?item ?itemLabel
{
 ?item wdt:P31/wdt:P279* wd:Q35127 .
 MINUS { ?item wdt:P856 [] . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Unexplained deprecation of ORCID iDs

SELECT ?item ?itemLabel where 
{
  ?item p:P496 ?statement.
  ?statement wikibase:rank wikibase:DeprecatedRank.
  filter not exists {?statement pq:P2241 [].}  
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Iraqi people with no Arabic label

SELECT ?item ?itemLabel WHERE 
{
  ?item wdt:P27 wd:Q796 .
  OPTIONAL {
    ?item rdfs:label ?label .
    FILTER(LANG(?label) = 'ar') .
  }
  FILTER(!BOUND($label)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Property label or description missing, in a given language

Where es is the code for the target language (note that it occurs twice) and en is the comparison language:

SELECT ?property ?propertyLabel ?propertyDescription ?datatype ?missingLbl ?missingDesc WHERE {
  ?property wikibase:propertyType ?datatype .
  OPTIONAL { ?property rdfs:label ?missingLbl . FILTER(LANG(?missingLbl) = 'es') }
  OPTIONAL { ?property schema:description ?missingDesc . FILTER(LANG(?missingDesc) = 'es') }
  FILTER(!BOUND(?missingLbl)|| !BOUND(?missingDesc)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Self portraits with artist, but not 'depicts'

SELECT ?item ?itemLabel ?artist ?artistLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q3305213.     #must have instance of painting or subclass thereof
  ?item wdt:P136 wd:Q192110.               #must have genre is self-portrait
  filter not exists {?item wdt:P180 [] . } #depicts is absent
  ?item wdt:P170 ?artist .                 #must have artist
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! (https://w.wiki/35k)

Wikidata properties without a label in Afrikaans

All Wikidata properties (which are not identifiers) without a label in Afrikaans! Help us translate!

Thanks to Harmonia_Amanda for the query.

SELECT DISTINCT ?item ?itemLabel ?itemDescription
WHERE {
 ?item wdt:P31/wdt:P279* wd:Q18616576 .
  MINUS { ?item wdt:P31/wdt:P279* wd:Q19847637 .}
 FILTER(NOT EXISTS {
   ?item rdfs:label ?lang_label.
   FILTER(LANG(?lang_label) = "af")
 })
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl,fr,de,it,es,no,pt". }
}
ORDER BY ?itemLabel

Try it!

UK MPs

Change Q16707842 to Q30524710 or the current parliament

With no English Wikipedia article

# No en.wiki article
SELECT distinct ?item ?itemLabel with 
{
  select distinct ?item where
  {  ?item wdt:P39/wdt:P279* wd:Q16707842 . }
} as %i
where
{include %i
  filter not exists {?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

With no image

# No image
SELECT distinct ?item ?itemLabel ?npg ?date_of_birth with 
{
  select distinct ?item where {
  {  ?item wdt:P39/wdt:P279* wd:Q16707842 . } union
  {  ?item wdt:P39/wdt:P279* wd:Q18018860 . } union
  {  ?item wdt:P39/wdt:P279* wd:Q18015642 . } union
  {  ?item wdt:P1614 ?hop } }
} as %i
where
{include %i
  filter not exists {?item wdt:P18 [] .}
  ?item wdt:P1816 ?npg. 
  OPTIONAL { ?item wdt:P569 ?date_of_birth. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

People by employer, with no UID

# People by employer, with no UID
select ?item ?itemLabel { 
  ?item wdt:P108 ?employer .
  filter not exists {
    ?item ?wdt [] .
    ?wdt ^wikibase:directClaim/wdt:P31/wdt:P279* wd:Q18614948
  } 
  service wikibase:label {bd:serviceParam wikibase:language "en"}
} values (?employer) {(wd:Q1138080)}

Try it!

UK theses, with no ETHoS ID

# UK theses, with no ETHoS ID
SELECT ?doctor ?doctorLabel ?thesis ?thesisLabel (sample(?university) as ?univ) {
  ?doctor wdt:P69 ?university 
          ;wdt:P1026 ?thesis
          filter not exists { ?thesis wdt:P4536 [] } 
          .
  ?university wdt:P17 wd:Q145 
              ; wdt:P361*/wdt:P31/wdt:P279* wd:Q3918
              .
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
} GROUP BY ?doctor ?doctorLabel ?thesis ?thesisLabel  having (count(?university) = 1 ) values (?university) {(wd:Q160302) }

Try it!

Author names needing items, by employer

#Author names needing items, by employer 
SELECT (COUNT(?paper) AS ?countPapers) ?item ?nameString {
  ?item wdt:P108 wd:Q1138080; #employer
        rdfs:label ?name .
  BIND(STR(?name) AS ?nameString)
  FILTER(LANG(?name)="en")
  FILTER NOT EXISTS {
    ?anotherPerson wdt:P31 wd:Q5;
                   rdfs:label ?name .
    FILTER(?anotherPerson != ?item)
  }
  ?paper wdt:P2093 ?nameString .
}
GROUP BY ?item ?nameString
ORDER BY DESC(?countPapers)

Try it!

Item with Wikipedia article, but no label

Item with no label in a given language, but a Wikipedia article exsits

SELECT ?Masonic_Lodge ?Masonic_LodgeLabel (sample(?Masonic_LodgeTitle) as ?wpTitle) WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Masonic_Lodge wdt:P31 wd:Q1454597.
  OPTIONAL {  
    ?article schema:about ?Masonic_Lodge ;
             schema:name ?Masonic_LodgeTitle
  }
} group by ?Masonic_Lodge ?Masonic_LodgeLabel

Try it!

Work in progres

UK physicians

SELECT ?person ?personLabel ?date_of_birth ?GMC WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?person wdt:P27 wd:Q145;
    wdt:P106 wd:Q39631.
  OPTIONAL { ?person wdt:P569 ?date_of_birth. }
  OPTIONAL { ?person wdt:P8273 ?GMC. }
}
LIMIT 100

Try it!