Work in progress
Intro
Queries exp
My queries (showcase)
Sources
GWMAB
IRIS
Strategy
All icons
All subpages

User:Epìdosis/sandbox

From Wikidata
Jump to navigation Jump to search


Queries[edit]

Materials about SPARQL queries[edit]

Other queries:

Miscellaneous queries[edit]

Replacement[edit]

#title:P301 > P1753
SELECT ?item ?is
WHERE {
  ?item wdt:P301 ?is .
  ?is wdt:P31 wd:Q13406463 .
  MINUS { ?is wdt:P31 ?n . 
  MINUS { ?n wdt:P279 wd:Q12139612 . } }
}
P301 > P1753 

#title:P910 > P1754
SELECT ?item ?v
WHERE {
  ?item wdt:P910 ?v .
  ?item wdt:P31 wd:Q13406463 .
  MINUS { ?item wdt:P31 ?n . 
  MINUS { ?n wdt:P279 wd:Q12139612 . } }
}
P910 > P1754 

SELECT DISTINCT ?occ ?occLabel (URI(CONCAT("https://www.wikidata.org/wiki/Special:Search/haswbstatement:P106=",?itemId)) AS ?fix)
WHERE {
  ?item wdt:P8034 ?id .
  ?item wdt:P106 ?occ .
  MINUS { ?occ wdt:P279 ?sub . }
  BIND(REPLACE(STR(?occ), "http://www.wikidata.org/entity/","") AS ?itemId)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en,de,es,fr". }
}
ORDER BY ?occLabel
Try it! 
#title:Property replacement for QuickStatements
SELECT (CONCAT("-",?itemId,",","P301",",",?valProp,",,",?itemId,",","P971",",",?valProp) as ?command)
WHERE {
?item wdt:P971 wd:Q15079786 .
?item wdt:P301 ?val .
?val wdt:P31 wd:Q5 .
BIND(REPLACE(STR(?item), "http://www.wikidata.org/entity/","") AS ?itemId)
BIND(REPLACE(STR(?val), "http://www.wikidata.org/entity/","") AS ?valProp)
}
Property replacement for QuickStatements 
#title:Property removal for QuickStatements
SELECT (CONCAT("-",?itemId,",","P7859",",^",?id,"^") as ?command)
WHERE {
  ?item p:P7859 [ ps:P7859 ?id ; prov:wasDerivedFrom [ pr:P214 ?viaf ] ] .
  MINUS { ?item wdt:P214 ?viaf } .
  BIND(REPLACE(STR(?item), "http://www.wikidata.org/entity/","") AS ?itemId)
}
LIMIT 2000
Property removal for QuickStatements

Identifiers in a single item[edit]

#title:All identifiers in a single item
SELECT ?id ?value
WHERE {
  wd:Q314447 ?idst ?value .
  ?id wikibase:directClaim ?idst .
  ?id wikibase:propertyType wikibase:ExternalId .
}
All identifiers in a single item

Counts and DirectClaim[edit]

#title:Count each external identifier only once
SELECT (COUNT(DISTINCT ?wdt) as ?count) ?n 
WHERE 
{
  ?p wikibase:propertyType wikibase:ExternalId .   
  ?p wikibase:directClaim ?wdt .
  wd:Q11922067 ?wdt [] .
  wd:Q11922067 wikibase:identifiers ?n .
}
GROUP BY ?n
Count each external identifier only once 

#title:Count all external identifiers excluding P214
SELECT (COUNT(*) as ?count) ?n 
WHERE 
{
  ?p wikibase:propertyType wikibase:ExternalId .   
  ?p wikibase:directClaim ?wdt .
  FILTER( ?wdt != wdt:P214 )
  wd:Q11922067 ?wdt [] .
  wd:Q11922067 wikibase:identifiers ?n .
}
GROUP BY ?n
Count all external identifiers excluding P214 

#title:Count all external identifiers being from Italy
SELECT ?ids_count (COUNT(?item) AS ?item_count) WHERE { 
  SELECT ?item (COUNT(?ids) AS ?ids_count) WHERE {
    ?item wdt:P9114 ?id .
    OPTIONAL { 
      ?item ?prop ?ids.
      ?propItem wikibase:directClaim ?prop.
      ?propItem wikibase:propertyType wikibase:ExternalId.
      ?propItem wdt:P17 wd:Q38.}
    } GROUP BY ?item
} GROUP BY ?ids_count
ORDER BY ?ids_count
Count all external identifiers being from Italy 

#title:Count of centuries of birth
#defaultView:LineChart
SELECT ?century (COUNT(?item) AS ?count) #(CONCAT(STR(?century),"..") as ?centuries)
WHERE {
  ?item wdt:P9114 ?id .
  ?item wdt:P569 ?birth .
  BIND(YEAR(?birth)-1 as ?year). #-1 means 1801-1900, without -1 means 1800-1899
  FILTER(BOUND(?year)).
  BIND (FLOOR(?year/100) as ?century).
} GROUP BY ?century
ORDER BY ?century
Count of centuries of birth 

#title:Count of biographical dictionaries connected to a library catalog
SELECT ?propItem ?propItemLabel (COUNT(?ids) AS ?ids_count) WHERE {
  hint:Query hint:optimizer "Runtime".
  ?item wdt:P5739 ?id .
  ?item wdt:P31 wd:Q5 .
  OPTIONAL { 
    ?item ?prop ?ids.
    ?propItem wikibase:directClaim ?prop.
    ?propItem wikibase:propertyType wikibase:ExternalId.
    ?propItem wdt:P31 wd:Q97584729 . 
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?propItem ?propItemLabel
ORDER BY DESC(?ids_count)
Count of biographical dictionaries connected to a library catalog 
#title:Count all properties used with a defined value
SELECT ?prop ?propLabel (COUNT(?item) AS ?n)
WHERE {
  ?item ?p wd:Q25339110 .
  ?prop wikibase:directClaim ?p .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?prop ?propLabel
ORDER BY DESC(?n)
Count all properties used with a defined value 
#title:Humans with more than 150 identifiers
SELECT ?item ?itemLabel ?identifiers WITH {
  SELECT ?item ?identifiers WHERE {
    ?item wikibase:identifiers ?identifiers. hint:Prior hint:rangeSafe true.
    FILTER(?identifiers > 150)
  }
} AS %itemsWithManyIdentifiers WHERE {
  INCLUDE %itemsWithManyIdentifiers.
  ?item wdt:P31 wd:Q5.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?identifiers)
LIMIT 1000
Humans with more than 150 identifiers 
#title:Number of incoming links for authors of articles published in Bibliothecae.it
SELECT ?linkingitems (COUNT(?linkingitems) AS ?numberofitems)
WHERE {
  { SELECT ?author (COUNT(DISTINCT ?x) AS ?linkingitems)
  WHERE { ?x ?st ?author . ?p wikibase:directClaim ?st . ?article wdt:P50 ?author ; wdt:P1433 wd:Q50811189 . }
  GROUP BY ?author }
}
GROUP BY ?linkingitems
ORDER BY DESC(?linkingitems)
Number of incoming links for authors of articles published in Bibliothecae.it

Averages[edit]

#title:Average number of statements for Italian schools by region
SELECT ?r ?reg (AVG(?number) AS ?media_st)
WHERE {
  ?item wdt:P5114 [] ; wdt:P131* ?r ; wikibase:statements ?number .
  ?r wdt:P31 ?v ; wdt:P1705 ?reg . VALUES ?v { wd:Q16110 wd:Q1710033 } . FILTER(LANG(?reg) = 'it')
}
GROUP BY ?r ?reg
ORDER BY DESC(?media_st)
Average number of statements for Italian schools by region

Line and bubble graphs[edit]

#title:P8034 by number of external identifiers
#defaultView:LineChart
SELECT ?ids (COUNT(DISTINCT ?item) AS ?count) WHERE {
  hint:Query hint:optimizer "Runtime".
  ?item wdt:P8034 ?id .
  ?item wikibase:identifiers ?ids .
}
GROUP BY ?ids
ORDER BY ?ids
P8034 by number of external identifiers 
#title:P8034 by number of external identifiers (group 15-25, 26+)
#defaultView:BubbleChart
SELECT ?group (SUM(?count) AS ?count_group) #(SAMPLE(?sampl) AS ?sample)
WITH
{
  SELECT ?ids (COUNT(DISTINCT ?item) AS ?count) #(SAMPLE(?item) AS ?sampl) 
  WHERE 
  {
    ?item wdt:P8034 ?id .
    ?item wikibase:identifiers ?ids .
  }
  GROUP BY ?ids
} AS %what
WHERE
{
  INCLUDE %what
  BIND( IF(?ids < 16, CONCAT("0",STR(?ids)),  #zero added for sorting by group
        IF(?ids < 26, "16-25", "26+")) 
        AS ?group) 
}
GROUP BY ?group
ORDER BY ?group
P8034 by number of external identifiers (group 15-25, 26+) 

#title:P8034 by number of 20 most frequent occupations
#defaultView:BubbleChart
SELECT (SUM(?count) AS ?occ_count) (IF(?group=">20", " other", SAMPLE(?itemLabel)) AS ?occ)
WITH  {  
        SELECT DISTINCT ?item (COUNT(?p) AS ?count) WHERE { ?p wdt:P8034 []; wdt:P106 ?item } GROUP BY ?item ORDER BY DESC(?count) 
      } AS %all 
WITH  {
        SELECT (?item AS ?item2) (?count AS ?count2) WHERE { INCLUDE %all }
      } AS %all2 
WITH  {
        SELECT ?item (SUM(?x) AS ?rank) ?count WHERE {
          INCLUDE %all .
          INCLUDE %all2 .
          BIND( IF( ?count  <= ?count2 , 1, 0 ) AS ?x ) .
        } GROUP BY ?item ?count
      } AS %main 
WHERE
{
  hint:Query hint:optimizer "None".    
  INCLUDE %main .
  BIND( IF(?rank < 21, STR(?rank), 
        ">20")
        AS ?group)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?item rdfs:label ?itemLabel }
}
GROUP BY ?group
ORDER BY (?group=">20") DESC(?occ_count)
P8034 by number of 20 most frequent occupations

Network graphs[edit]

#title:Interpersonal relations of typographers active in Venice until 1850
#defaultView:Graph
select ?person ?linkedPerson ?linkedPersonLabel ?type ?personLabel (?type as ?edgeLabel) {
   ?person wdt:P937 wd:Q641;
           wdt:P106 ?activity .
  
  values ?activity { 
    wd:Q175151 wd:Q1229025 wd:Q998550 wd:Q2516866 wd:Q40881196
  }
  
  ?person wdt:P2032|wdt:P570 ?datafine .
  
{ ?person wdt:P1327 ?linkedPerson .  bind("business partner" as ?type) } union 
{ ?person wdt:P802 ?linkedPerson .  bind("student" as ?type) } union 
{ ?person wdt:P1066 ?linkedPerson .  bind("teacher" as ?type) } union 
{ ?person wdt:P22 ?linkedPerson .  bind("father" as ?type) } union 
{ ?person wdt:P23 ?linkedPerson .  bind("mother" as ?type)  } union 
{ ?person wdt:P40 ?linkedPerson .  bind("son" as ?type) } union 
{ ?person wdt:P26 ?linkedPerson .  bind("partner" as ?type) }
FILTER ("1850-01-01"^^xsd:dateTime > ?datafine) 
SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" . }
}
Interpersonal relations of typographers active in Venice until 1850

Labels in more languages[edit]

#title:Labels in two languages
SELECT ?item ?itemLabelIt ?itemLabelEn (URI(CONCAT("http://www.treccani.it/enciclopedia/",?treccani)) AS ?treccaniUrl)
WHERE {
  ?item wdt:P3365 ?treccani .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". ?item rdfs:label ?itemLabelEn . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". ?item rdfs:label ?itemLabelIt . }
}
LIMIT 10
Labels in two languages 
#title:Same label in two languages
SELECT ?item ?enlabel WHERE {
  ?item wdt:P2358 ?roman_praenomen .
  ?item rdfs:label ?enlabel .
  FILTER(LANG(?enlabel) = 'en') .
  ?item rdfs:label ?calabel .
  FILTER(LANG(?calabel) = 'ca') .
  FILTER(STR(?enlabel) = STR(?calabel)) .
  #OPTIONAL { ?item rdfs:label ?lalabel . FILTER(LANG(?lalabel) = 'la') . } FILTER(!BOUND(?lalabel)) .
}
ORDER BY ?enlabel
Same label in two languages 
#title:Label containing
SELECT ?q ?orcid 
WHERE {
  ?q rdfs:label "Stefano Bargioni"@en .
  OPTIONAL { ?q wdt:P496 ?orcid . }
}
Label containing 
#title:Description containing
SELECT ?item ?itemLabel ?itemDescription
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "search".
    bd:serviceParam mwapi:gsrsearch "Alman politikacılar".
    bd:serviceParam mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item schema:description ?itemDescription.
  FILTER (LANG(?itemDescription) = "tr")
  FILTER CONTAINS(?itemDescription, "Alman politikacılar")
}
Description containing 
#title:Description exactly containing
SELECT ?item 
WHERE
{
  ?item wdt:P106 wd:Q82955 .
  ?item schema:description "Alman politikacılar"@tr 
}
Description exactly containing

Identical labels[edit]

#title:Italian homonym people with or without SBN
SELECT
  (SAMPLE(?itemlabel_) AS ?itemlabel)
  (GROUP_CONCAT(STRAFTER(STR(?item_), STR(wd:)); SEPARATOR=", ") AS ?items)
  (GROUP_CONCAT(STR(?authid_); SEPARATOR=", ") AS ?authid)
  (COUNT(?item_) AS ?count)
WITH { SELECT DISTINCT ?item_ WHERE {
  VALUES ?v { wd:Q172579 wd:Q38 }
  ?item_ wdt:P27 ?v ; wdt:P214 [].
} } as %i
WHERE {
  INCLUDE %i
  ?item_ rdfs:label ?itemlabel_ . FILTER(LANG(?itemlabel_) = "it")
  OPTIONAL { ?item_ wdt:P396 ?authid_ . }
}
GROUP BY ?itemlabel_
HAVING (?count > 1)
ORDER BY DESC(?count) (?itemlabel)
LIMIT 500
Italian homonym people with or without SBN 
#title:Hononym things in Luxembourg
SELECT ?item1 ?item2 ?l
WHERE {
  ?item1 wdt:P17 wd:Q32 . ?item2 wdt:P17 wd:Q32 .
  ?item1 rdfs:label ?l . FILTER(LANG(?l) = 'en') .
  ?item2 rdfs:label ?l . FILTER(LANG(?l) = 'en') .
  FILTER(?item1 != ?item2 && STR(?item1) < STR(?item2)) .
}
ORDER BY ?l
Hononym things in Luxembourg

Regex search in descriptions[edit]

SELECT ?item ?desc
WHERE {
  ?item wdt:P549 ?mgp .
  MINUS { ?item wdt:P214 ?viaf . }
  #?m wdt:P802 ?item . MINUS { ?m wdt:P1412 ?l . }
  ?item schema:description ?desc .
  FILTER(LANG(?desc) = "en").
  #FILTER(STRSTARTS(?desc, "University")).
  FILTER(REGEX(?desc, "1[6-7]{1}[0-9]{2}")).
}
Try it! 
SELECT ?q (SUBSTR(STR(?q),32) AS ?qid) ("Den" AS ?den) ("\"male given name\"" AS ?fgn)
WHERE {
  ?q schema:description "nombre masculino"@es.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(NOT EXISTS { ?q schema:description ?dit. FILTER((LANG(?dit)) = "en") })
}
Try it!

Regex variables[edit]

SELECT ?item ?id ?name ?first ?second ?birth ?death
WHERE {
  ?item p:P396 [ ps:P396 ?id ; pq:P1810 ?name ] .
  FILTER REGEX(?name, "<(.*\\d+.*)-(.*)")
  BIND(REPLACE(?name, "^.*<(.*\\d+.*)-(.*)>$", "$1") AS ?first)
  BIND(REPLACE(?name, "^.*<(.*\\d+.*)-(.*)>$", "$2") AS ?second)
  OPTIONAL { ?item wdt:P569 ?born } . BIND(YEAR(?born) AS ?birth)
  OPTIONAL { ?item wdt:P570 ?died } . BIND(YEAR(?died) AS ?death)
  FILTER(STR(?first) != STR(?birth))
}
ORDER BY ?birth
Try it! 
#title:Count by prefix
SELECT ?pref (COUNT(?item) AS ?n)
WHERE {
  ?item wdt:P691 ?nkc .
  BIND(REPLACE(STR(?nkc), "^([a-z]*)\\d+$", "$1") AS ?pref)
}
GROUP BY ?pref
ORDER BY DESC(?n)
Count by prefix

Somevalue and novalue[edit]

SELECT ?p
WHERE {
  { ?p p:P9073/a wdno:P9073 . } UNION
  { ?p wdt:P9073 ?value . FILTER (isBlank(?value)) }
}
Try it! 
#title:Articles surely without author
SELECT DISTINCT ?articolo ?articoloLabel ?rivistaLabel
WHERE {
  ?rivista wdt:P31 wd:Q5633421 .
  ?rivista wdt:P495 wd:Q38 .
  ?rivista wdt:P921 wd:Q13420675 .
  ?articolo wdt:P31 wd:Q13442814 .
  ?articolo wdt:P1433 ?rivista .
  ?articolo a wdno:P50 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?rivistaLabel
Articles surely without author

Union of missing statements/references[edit]

SELECT DISTINCT ?person ?personLabel
WHERE {
  ?person wdt:P902 ?dss .
  ?person wdt:P8750 ?urbs .
  ?person wdt:P31 wd:Q5 .
  { MINUS { ?person p:P569 ?bd . ?bd prov:wasDerivedFrom ?refb } } UNION
  { MINUS { ?person p:P570 ?dd . ?dd prov:wasDerivedFrom ?refd } } UNION
  { MINUS { ?person wdt:P106 ?occ . } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Group_concat[edit]

SELECT 
 ?i ?iLabel ?iDescription ?nickname
 (GROUP_CONCAT(DISTINCT ?iAlias; SEPARATOR=", ") AS ?iAliases)
 ?P19Label ?P20Label ?P21Label
 ?P569p ?P569v ?P570p ?P570v ?P18
 (GROUP_CONCAT(DISTINCT ?okkMa; SEPARATOR=", ") AS ?okkupMas)
 (GROUP_CONCAT(DISTINCT ?okkFe; SEPARATOR=", ") AS ?okkupFes)
WHERE {
 BIND(wd:Q1616094 AS ?i)
 OPTIONAL { ?i wdt:P21 ?P21 . } # genere
 OPTIONAL { ?i wdt:P18 ?P18 . } # foto
 OPTIONAL { ?i wdt:P19 ?P19 . } # luogo nascita
 OPTIONAL { ?i wdt:P20 ?P20 . } # luogo morte
 OPTIONAL { ?i p:P569 [ a wikibase:BestRank ; psv:P569 [ wikibase:timePrecision ?P569p ; wikibase:timeValue ?P569v ] ] . } # data nascita
 OPTIONAL { ?i p:P570 [ a wikibase:BestRank ; psv:P570 [ wikibase:timePrecision ?P570p ; wikibase:timeValue ?P570v ] ] . } # data morte
 OPTIONAL { ?i wdt:P1449 ?nickname . } # soprannome
 OPTIONAL { ?i skos:altLabel ?iAlias . } # aliases
 OPTIONAL { ?i wdt:P106 ?okkup . ?okkup rdfs:label ?okkMa . FILTER(LANG(?okkMa) = "it")
   OPTIONAL { ?okkup wdt:P2521 ?okkFe . FILTER(LANG(?okkFe) = "it") }
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
GROUP BY ?i ?iLabel ?iDescription ?nickname
 ?P19Label ?P20Label ?P21Label
 ?P569p ?P569v ?P570p ?P570v ?P18
Try it! 
select 
 ?item ?itemLabel ?itemDescription
 (GROUP_CONCAT(DISTINCT ?article; SEPARATOR=" ; ") AS ?articles)
 (GROUP_CONCAT(DISTINCT ?id; SEPARATOR=" ; ") AS ?ids)
where {
 ?item wdt:P396 "IT\\ICCU\\CFIV\\013063" .
 optional { ?item ?external ?external_id .}
 values ?external { wdt:P214 wdt:P213 wdt:P227 wdt:P268 wdt:P950 wdt:P244 wdt:P1986 }
 bind(concat(str(?external),"   ",?external_id) AS ?id)
 optional { ?article schema:about ?item . 
   FILTER (regex(str(?article), "wikipedia.org" ))
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
GROUP BY ?item ?itemLabel ?itemDescription
Try it! 
SELECT DISTINCT ?item ?n (COUNT(DISTINCT ?article2) AS ?num) (?n - ?num AS ?difference)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q486972 ; wdt:P131* wd:Q16245 ; wikibase:sitelinks ?n .
  MINUS { ?item wdt:P31 wd:Q747074 } .
  MINUS { ?item wdt:P31 wd:Q954172 } .
  ?article schema:about ?item . 
  OPTIONAL { ?article2 schema:about ?item ; schema:isPartOf ?w .
  VALUES ?w { <https://ceb.wikipedia.org/> <https://sh.wikipedia.org/> <https://sr.wikipedia.org/> } . }
}
GROUP BY ?item ?n
ORDER BY DESC(?n)
Try it!

Bind in Select[edit]

SELECT ?item (GROUP_CONCAT(STR(?rec); SEPARATOR=", ") AS ?recs) ?data (IF(BOUND(?irisid),"SÌ","NO") AS ?iris)
WHERE {
  ?item p:P227 ?v .
  OPTIONAL { ?item ?irisst ?irisid . ?iris wikibase:directClaim ?irisst ; wdt:P1629 [ wdt:P408 wd:Q107492957 ] . } .
  ?v a wdno:P227 ; prov:wasDerivedFrom [ pr:P854 ?rec ; pr:P813 ?data ] .
  FILTER ("2023-01-31"^^xsd:dateTime < ?data && ?data < "2023-03-01"^^xsd:dateTime)
}
GROUP BY ?item ?data ?irisid
Try it!

Excluding values[edit]

#title:Excluding items having only a specific qualifier-value
SELECT DISTINCT ?item
WHERE {
  ?item p:P4223 ?ei .
  ?ei pq:P50 ?author .
  FILTER (?author != wd:Q3772798 )
}
Excluding items having only a specific qualifier-value

Multiple values[edit]

SELECT ?item (count(?item) as ?num)
WHERE {
  ?item wdt:P131* wd:Q16245.
          
} GROUP BY ?item 
ORDER BY DESC(?num)
Try it! 

SELECT ?item (count(?item) as ?num)
WHERE {
  ?item wdt:P131* wd:Q16245.  
} 
GROUP BY ?item 
HAVING(count(?item) > 1)
ORDER BY DESC(?num)
Try it!

Duplicated values[edit]

#title:VIAF appearing two times in the same item (= single-value violation)
SELECT DISTINCT ?item ?v1
WHERE {
  ?item p:P214 ?viaf1 .
  ?viaf1 ps:P214 ?v1 .
  ?item p:P214 ?viaf2 .
  ?viaf2 ps:P214 ?v2 .
  FILTER(?viaf1 != ?viaf2)
  FILTER(?v1 = ?v2)
}
LIMIT 10
VIAF appearing two times in the same item (= single-value violation) 
#title:NUKAT appearing two times equal in two different items (= unique-value violation)
SELECT DISTINCT ?v1 ?item1 ?item2
WHERE { 
  ?item1 p:P1207 ?s1 .
  ?s1 ps:P1207 ?v1 .
  ?item2 p:P1207 ?s2 .
  ?s2 ps:P1207 ?v2 .
  FILTER(?item1 != ?item2)
  FILTER(?v1 = ?v2)
}
LIMIT 10
NUKAT appearing two times equal in two different items (= unique-value violation) 
#title:VIAF unique value violations (recent items with GND)
SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?value 
{
	?item1 wdt:P214 ?value .
	?item2 wdt:P214 ?value .
    ?item1 wdt:P31 wd:Q5 .
    ?item2 wdt:P227 [] .
    FILTER ((xsd:integer(SUBSTR(STR(?item2),33))) > 90000000) .
    FILTER( ?item1 != ?item2 && STR( ?item1 ) < STR( ?item2 ) ) .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" } .
}
ORDER BY ?item2
VIAF unique value violations (recent items with GND)

Statements without references[edit]

#title:Humans needing fundamental references
SELECT DISTINCT ?item ?itemLabel {
  ?item wdt:P7613 ?bf .
  { ?item p:P569 ?dbs . MINUS { ?dbs prov:wasDerivedFrom ?ref . } }
  UNION
  { ?item p:P570 ?dds . MINUS { ?dds prov:wasDerivedFrom ?ref . } }
  UNION
  { ?item p:P19 ?pbs . MINUS { ?pbs prov:wasDerivedFrom ?ref . } }
  UNION
  { ?item p:P20 ?pds . MINUS { ?pds prov:wasDerivedFrom ?ref . } }
  ?item wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
Humans needing fundamental references 

#title:Humans needing fundamental statements
SELECT DISTINCT ?item ?itemLabel ?db ?dd ?pb ?pd {
  ?item wdt:P7613 ?bf .
  OPTIONAL { ?item wdt:P569 ?db . }
  OPTIONAL { ?item wdt:P570 ?dd . }
  OPTIONAL { ?item wdt:P19 ?pb . }
  OPTIONAL { ?item wdt:P20 ?pd . }
  ?item wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?db ?dd ?pb ?pd
Humans needing fundamental statements 

#title:A birth date referenced only from Wikimedia projects
SELECT ?item (COUNT(?ref) AS ?n)
WHERE {
  ?item wdt:P1986 ?dbi ; p:P569 ?st .
  ?st prov:wasDerivedFrom ?ref, ?ref2 . ?ref2 pr:P143 [] .
}
GROUP BY ?item
HAVING(?n = 1)
A birth date referenced only from Wikimedia projects 
#title:Only one birth date referenced only from a Wikimedia project
SELECT ?item (COUNT(DISTINCT ?st) AS ?nst)
WHERE
{
  hint:Query hint:optimizer "None" .
  ?item wdt:P1986 ?dbi; p:P569 ?st . ?st prov:wasDerivedFrom ?ref . ?ref pr:P143 [] . 
  FILTER NOT EXISTS { ?st prov:wasDerivedFrom ?other . FILTER( ?other != ?ref ) }
}
GROUP BY ?item
HAVING(?nst = 1)
Only one birth date referenced only from a Wikimedia project

Possibly imprecise or duplicated references[edit]

#title:Two similar sources (P248+P248) for the same value
SELECT ?item ?rank
WHERE
{
  ?reference1 pr:P248 wd:Q19938912.
  ?reference2 pr:P248 wd:Q15222191.
  ?statement prov:wasDerivedFrom ?reference1.
  ?statement prov:wasDerivedFrom ?reference2.
  ?item p:P569 ?statement.
  VALUES ?rank { wikibase:NormalRank wikibase:PreferredRank } # Wanted ranks
  ?statement wikibase:rank ?rank.  
}
Two similar sources (P248+P248) for the same value 
#title:Two similar sources (P248+P854) for the same value
SELECT ?item ?rank
WHERE
{
  {
    ?reference1 pr:P248 wd:Q19938912.
    ?reference2 pr:P854 ?URL.
    ?statement prov:wasDerivedFrom ?reference1.
    ?statement prov:wasDerivedFrom ?reference2.
    FILTER (?reference1 != ?reference2) # Requested to be different references
  }
  hint:Prior hint:runFirst "true".
  
  ?item p:P569 ?statement.
  VALUES ?rank { wikibase:NormalRank wikibase:PreferredRank } # Wanted ranks
  ?statement wikibase:rank ?rank.
  FILTER CONTAINS(STR(?URL), "data.bnf.fr")  
}
Two similar sources (P248+P854) for the same value 
#title:References by retrieval date
SELECT ?subject ?subjectLabel ?subjectDescription ?date ?cons
WHERE {  
   ?subject p:P569/psv:P569 ?date_node .
   ?date_node wikibase:timePrecision "7"^^xsd:integer .
   ?date_node wikibase:timeValue ?date . 
   FILTER (year(?date) > 1702 ) .
   FILTER (year(?date) < 1802 ) .
   ?subject p:P569/prov:wasDerivedFrom/pr:P248 wd:Q23833686 .
   ?subject p:P569/prov:wasDerivedFrom/pr:P813 ?cons .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?cons
References by retrieval date 
#title:Wrong P248 value
SELECT ?item ?ref WHERE {
  ?item wdt:P396 ?sbn.
  ?ref pr:P248 wd:Q576951.
  ?statement prov:wasDerivedFrom ?ref.
  ?item ?p ?statement.
}
Wrong P248 value 
#title:Single items containing references with only P813
SELECT ?item ?id ?property ?statement ?retrieved
WHERE
{
  hint:Query hint:optimizer "None".
  VALUES ?item { wd:Q107331778 }
  ?item ?property ?statement . 
  ?property ^ wikibase:claim [] .
  ?statement prov:wasDerivedFrom ?b .
  ?b pr:P813 ?retrieved . 
  FILTER NOT EXISTS { ?b ?other [] . FILTER( ?other != pr:P813 && ?other != prv:P813 ) }
}
Single items containing references with only P813 
#title:Items containing date of birth referenced from NKC both as ID and as URL
SELECT ?item
WHERE {
  ?item wdt:P691 ?nkc ; p:P569 [ prov:wasDerivedFrom ?ref1, ?ref2 ] .
  ?ref1 pr:P854 ?url . FILTER(CONTAINS(STR(?url), "aleph.nkp.cz"))
  ?ref2 pr:P691 ?nkc .
}
Items containing date of birth referenced from NKC both as ID and as URL 
#title:Some items containing date of birth referenced from two URLs wrongly in the same reference
SELECT DISTINCT ?item ?itemLabel ?itemDescription
WHERE {
  ?item wdt:P396 [] ; p:P569 [ prov:wasDerivedFrom [ pr:P854 ?u1, ?u2 ] ] . FILTER(?u1 != ?u2)
  MINUS { ?item wdt:P106 ?occupazione } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
ORDER BY ?itemLabel
Some items containing date of birth referenced from two URLs wrongly in the same reference

Years instead of complete dates[edit]

SELECT ?person ?personLabel ?sdy ?edy
WHERE {
  ?person p:P69 [ ps:P69 wd:Q1576779 ; pq:P580 ?sd ; pq:P582 ?ed ] .
  BIND(YEAR(?sd) AS ?sdy)
  BIND(YEAR(?ed) AS ?edy)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?sdy
Try it!

Dates with possible qualifiers[edit]

#title:Birth date referenced, only one having best rank, with precision year or higher and possible precision qualifiers
SELECT ?id ?person ?date ?prec ?source_condLabel ?refineLabel ?low_limit ?high_limit
WHERE {
  ?person wdt:P5739 ?id .
  ?person p:P569 ?st . ?st prov:wasDerivedFrom ?ref ; a wikibase:BestRank ; psv:P569 [ wikibase:timePrecision ?prec ; wikibase:timeValue ?date ] .
  OPTIONAL { ?st pq:P1480 ?source_cond } .
  OPTIONAL { ?st pq:P4241 ?refine } .
  OPTIONAL { ?st pq:P1319 ?low_limit } .
  OPTIONAL { ?st pq:P1326 ?high_limit } .
  FILTER NOT EXISTS { ?person p:P569 ?st2 . ?st2 a wikibase:BestRank . FILTER( ?st2 != ?st ) }
  FILTER(?prec IN ("9"^^xsd:integer, "10"^^xsd:integer, "11"^^xsd:integer))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?date
Birth date referenced, only one having best rank, with precision year or higher and possible precision qualifiers

Dates with precision lower than day[edit]

#title:SBN people with birth date having a precision lower than day
SELECT ?item
WHERE {
  ?item wdt:P396 [] ; p:P569 ?st . ?st a wikibase:BestRank ; psv:P569 [ wikibase:timePrecision ?prec ; wikibase:timeValue ?date ] .
  FILTER(?prec IN ("7"^^xsd:integer, "8"^^xsd:integer, "9"^^xsd:integer, "10"^^xsd:integer))
}
SBN people with birth date having a precision lower than day 
SELECT ?item ?label ?dn ?dm ?active WHERE {
  ?item p:P569 ?stdn; p:P570 ?stdm; wdt:P106 ?activity .
  VALUES ?activity { wd:Q175151 wd:Q1229025 wd:Q998550 wd:Q2516866 wd:Q40881196 } .
  ?stdn a wikibase:BestRank ; psv:P569 [ wikibase:timePrecision ?precn ; wikibase:timeValue ?dn ] .
  ?stdm a wikibase:BestRank ; psv:P570 [ wikibase:timePrecision ?precm ; wikibase:timeValue ?dm ] .
  FILTER(?precn IN ("7"^^xsd:integer, "8"^^xsd:integer, "9"^^xsd:integer, "10"^^xsd:integer))
  FILTER(?precm IN ("7"^^xsd:integer, "8"^^xsd:integer, "9"^^xsd:integer, "10"^^xsd:integer))
  BIND ((YEAR(?dm)-YEAR(?dn)) AS ?active) .
  FILTER (YEAR(?dm)<1600) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it" . ?item rdfs:label ?label }
}
Try it!

Dates showed with year precision[edit]

SELECT ?person ?year
WHERE {
  ?person p:P569/psv:P569 [ wikibase:timePrecision "9"^^xsd:integer ; wikibase:timeValue ?time ] .
  BIND(YEAR(?time) AS ?year)
}
LIMIT 10
Try it! 
SELECT DISTINCT ?i ?iLabel ?year ?precisedate {
  ?i wdt:P39/wdt:P279 wd:Q16707842 .
  ?i p:P569/psv:P569 [ wikibase:timePrecision "9"^^xsd:integer ; wikibase:timeValue ?time ] . BIND(YEAR(?time) AS ?year)
  ?i p:P569/psv:P569 [ wikibase:timePrecision ?prec ; wikibase:timeValue ?precisedate ] . FILTER(?prec IN ("10"^^xsd:integer, "11"^^xsd:integer))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! 
#title:Morti recenti
SELECT ?q ?qLabel 
  (YEAR(?died) as ?ydied) (MONTH(?died) as ?mdied) (DAY(?died) as ?ddied) ?pdied
  (YEAR(?born) as ?yborn) (MONTH(?born) as ?mborn) (DAY(?born) as ?dborn) ?pborn
  ?qDescription ?podLabel ?pod ?podnazioneLabel ?pobLabel ?pob ?pobnazioneLabel ?pusc
WHERE {
  ?q wdt:P5739 ?pusc ; p:P570/psv:P570 [ wikibase:timePrecision ?pdied ; wikibase:timeValue ?died ] . 
  
  OPTIONAL { ?q wdt:P20 ?pod . ?pod wdt:P17 ?podnazione . }
  OPTIONAL { ?q p:P569/psv:P569 [ wikibase:timePrecision ?pborn ; wikibase:timeValue ?born ] . }
  OPTIONAL { ?q wdt:P19 ?pob . ?pob wdt:P17 ?pobnazione . }

  BIND (NOW() - "P3M"^^xsd:duration AS ?month_ago)
  FILTER (?died >= ?month_ago && ?died < NOW())
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?died
Morti recenti

Dates with applied filters[edit]

#title:Mappa dei luoghi di nascita degli autori italiani morti nel 1952
#defaultView:Map{ "markercluster": true}
SELECT DISTINCT ?autore ?autoreLabel ?autoreDescription ?datanascita ?datamorte ?luogonascita ?luogonascitaLabel ?coord
WHERE {
  ?autore wdt:P27 ?p ; wdt:P570 ?datamorte ; ?id [] . VALUES ?p { wd:Q172579 wd:Q38 } . VALUES ?id { wdt:P214 wdt:P396 } .
  FILTER ("1951-12-31"^^xsd:dateTime < ?datamorte && ?datamorte < "1953-01-01"^^xsd:dateTime)
  OPTIONAL { ?autore wdt:P569 ?datanascita } .
  OPTIONAL { ?autore wdt:P19 ?luogonascita . ?luogonascita wdt:P625 ?coord } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
Mappa dei luoghi di nascita degli autori italiani morti nel 1952 
#title:People born in 1950 with VIAF URL as source
SELECT *
WHERE {
  ?item p:P569 [ ps:P569 ?v ; prov:wasDerivedFrom [ pr:P854 ?url ] ] .
  FILTER("1950-00-00"^^xsd:dateTime = ?v)
  FILTER(REGEX(STR(?url), "viaf"))
}
People born in 1950 with VIAF URL as source

Decades for counts[edit]

#title:Authors in IRIS SNS by decade of birth
#defaultView:BubbleChart
SELECT ?decennio (COUNT(?item) AS ?count)
WHERE {
  ?item wdt:P9761 [] ; p:P569/psv:P569 [ wikibase:timePrecision ?prec ; wikibase:timeValue ?birth ]  .
  FILTER(?prec IN ("9"^^xsd:integer, "10"^^xsd:integer, "11"^^xsd:integer))
  BIND(YEAR(?birth) AS ?year).
  FILTER(BOUND(?year)).
  BIND(FLOOR(?year/10)*10 AS ?decade).
  BIND(REPLACE(STR(?decade), "0","0s") AS ?decennio)
} GROUP BY ?decennio
ORDER BY ?decennio
Authors in IRIS SNS by decade of birth

Birth and death places on map[edit]

#defaultView:Map{ "markercluster": true}
SELECT ?person ?personLabel ?place ?placeLabel  ?coord ?layer
WHERE {  
  {?person wdt:P69 wd:Q1576779 .
  OPTIONAL { ?person wdt:P19 ?place . ?place wdt:P625 ?coord . bind("birth" as ?layer)} }
  UNION
  {?person wdt:P69 wd:Q1576779 .
  OPTIONAL { ?person wdt:P20 ?place . ?place wdt:P625 ?coord . bind("death" as ?layer)} }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
Try it! 
#title:Most frequent birth places for SBN authors
#defaultView:Map
SELECT DISTINCT ?l ?lLabel ?coord ?number ?layer
WITH
{
  SELECT DISTINCT ?l ?lLabel ?coord (COUNT(DISTINCT ?item) AS ?number)
  WHERE 
  {
  ?item wdt:P396 [] ; wdt:P19 ?l . ?l wdt:P625 ?coord .
  }
  GROUP BY ?l ?lLabel ?coord ?number
} AS %what
WHERE
{
  INCLUDE %what
  BIND( IF(?number < 6, "1-5", IF(?number < 21, "6-20", IF(?number < 51, "21-50","51+"))) AS ?layer)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
GROUP BY ?l ?lLabel ?coord ?number ?layer
ORDER BY DESC(?number)
Most frequent birth places for SBN authors

Nestled selects[edit]

#title:Exclude single results
SELECT ?item ?place ?trec
WHERE
{
  {
    SELECT (count(DISTINCT ?item) AS ?items) ?place
    WHERE
    {
      ?item wdt:P31 wd:Q5 .
      ?item wdt:P3365 ?trec .
      ?item wdt:P19 ?place .
      ?place wdt:P131* wd:Q1273 .
    }
    GROUP BY ?place
    HAVING (?items > 1)
  }
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P3365 ?trec .
  ?item wdt:P19 ?place .
}
Exclude single results 
#title:Places of birth for IRIS authors
SELECT DISTINCT ?l ?lLabel ?coord ?number ?layer
WITH
{
  SELECT DISTINCT ?l ?lLabel ?coord (COUNT(DISTINCT ?item) AS ?number)
  WHERE 
  {
  ?iris wikibase:directClaim ?irisst ; wdt:P1629 [ wdt:P408 wd:Q107492957 ] .
  ?item ?irisst ?irisid .
  ?item wdt:P19 ?l . ?l wdt:P625 ?coord .
  }
  GROUP BY ?l ?lLabel ?coord ?number
} AS %what
WHERE
{
  INCLUDE %what
  BIND( IF(?number < 6, "1-5", IF(?number < 16, "6-15", IF(?number < 26, "16-25","26+"))) AS ?layer) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
GROUP BY ?l ?lLabel ?coord ?number ?layer
ORDER BY DESC(?number)
Places of birth for IRIS authors 
#title:Authors in IRIS SNS by presence in VIAF members
#defaultView:BubbleChart
SELECT ?bibp ?bib (COUNT(?item) AS ?count)
WHERE {
  { SELECT ?bibp ?bib WHERE { ?bibp wdt:P31 wd:Q55586529 ; p:P1552 [ ps:P1552 wd:Q26921380 ; pq:P3295 ?bib ] . } }
  ?item wdt:P9761 [] ; ?bibst [] . ?bibp wikibase:directClaim ?bibst .
} GROUP BY ?bib ?bibp
ORDER BY ?count
Authors in IRIS SNS by presence in VIAF members

URL or ID contains[edit]

#title:SBN IDs from BNCF not qualified
SELECT DISTINCT ?item ?itemLabel ?vid ?ind
WHERE {
  ?item wdt:P31 wd:Q5 ; p:P396 [ ps:P396 ?vid ; pq:P1810 ?ind ] .
  FILTER(CONTAINS(?vid, "CFI"))
  FILTER(!REGEX(?ind, "<"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
ORDER BY ?itemLabel
SBN IDs from BNCF not qualified 
#title:URL contains
SELECT ?p ?site
WHERE {
  ?p wdt:P5739 ?pusc .
  ?p wdt:P973 ?site .
  FILTER(!REGEX(STR(?site), "mcnbiografias")) 
  #FILTER(CONTAINS(STR(?site),"mcnbiografias"))
}
ORDER BY ?site
URL contains 
#title:reference URL contains
SELECT ?item ?prefet ?site
WHERE {
  ?item wdt:P4944 ?prefet .
  ?item p:P569 ?db .
  ?db prov:wasDerivedFrom ?ref .
  ?ref pr:P854 ?site .
  FILTER(CONTAINS(STR(?site),"francearchives.fr/fr/facomponent/"))
}
ORDER BY ?site
reference URL contains 
#title:reference URL instead of ID
SELECT ?ref ?url ?id
WHERE {
  ?ref prov:wasDerivedFrom [ pr:P248 wd:Q104074149 ; pr:P854 ?url ].
  BIND (REPLACE(STR(?url),"^.*/([0-9]+)[-/].*$","$1") as ?id)
}
reference URL instead of ID

Order IDs by number[edit]

#title:Order by number
SELECT ?item ?itemLabel ?value
{
	?item wdt:P5492 ?value .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en"  }    
}
ORDER BY xsd:integer(?value)
Order by number

Sitelinks and badges[edit]

SELECT ?item ?article ?id
WHERE {
  ?item wdt:P8849 ?id .
  ?article schema:about ?item ; schema:isPartOf <https://bg.wikipedia.org/> .
}
Try it! 
SELECT ?item ?article ?id
WHERE {
  ?item wdt:P31 wd:Q44613 .
  ?de schema:about ?item ; schema:isPartOf <https://de.wikipedia.org/> .
  MINUS { ?it schema:about ?item ; schema:isPartOf <https://it.wikipedia.org/> } .
}
Try it! 
SELECT ?item ?itemLabel ?article
WHERE {
  ?item wikibase:statements 0 .
  ?item wikibase:sitelinks 1 .
  ?article schema:about ?item ; schema:isPartOf <https://ca.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ca". }
}
ORDER BY ?itemLabel
Try it! 
SELECT ?item1 ?item2
WHERE {
  ?article schema:about ?item2 ; schema:isPartOf <https://uk.wikipedia.org/> .
  #?item1 wdt:P31 wd:Q5 .
  #?item2 wikibase:statements 1 .
  FILTER( ?item1 != ?item2 && STR( ?item1 ) < STR( ?item2 ) ) .
  ?item1 wdt:P2671 ?g .
  ?item2 wdt:P2671 ?g .
}
ORDER BY DESC(?item2)
Try it! 
SELECT ?item ?article ?badge
WHERE {
  ?item wikibase:statements 0 ; wikibase:sitelinks 1 .
  ?article schema:about ?item ; schema:isPartOf <https://el.wikipedia.org/> .
  OPTIONAL { ?article wikibase:badge ?badge } .
}
ORDER BY DESC(?badge)
Try it!

Search in Wikipedia category[edit]

#title:Search in category
PREFIX mw: <http://tools.wmflabs.org/mw2sparql/ontology#>
SELECT DISTINCT ?item ?itemLabel {
  hint:Query hint:optimizer "None" .
  ?cat schema:about wd:Q8882780
  SERVICE <http://tools.wmflabs.org/mw2sparql/sparql> {
    ?page mw:inCategory ?cat
  }
  ?page schema:about ?item .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}
Search in category 

#title:Search categories in category
PREFIX mw: <http://tools.wmflabs.org/mw2sparql/ontology#>
SELECT DISTINCT ?catit ?catitLabel {
  hint:Query hint:optimizer "None" .
  ?cat schema:about wd:Q6969113 #catbase
  SERVICE <http://tools.wmflabs.org/mw2sparql/sparql> {
    ?page mw:inCategory ?cat
  }
  ?page schema:about ?catit .
  ?catit wdt:P31/wdt:P279* wd:Q4167836 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en,fr,de,ru,[AUTO_LANGUAGE]". }
}
ORDER BY ?catitLabel
Search categories in category

External-ids without P9073[edit]

SELECT DISTINCT ?p ?pLabel ?si
WHERE {
  ?p wikibase:propertyType wikibase:ExternalId . 
  ?p wdt:P1629 ?si . 
  ?si wdt:P31 ?inst .
  VALUES ?inst { wd:Q2352616 wd:Q8513 wd:Q3331189 wd:Q386724 wd:Q1789476 wd:Q121182 wd:Q170584 }
  MINUS { ?p wdt:P9073 ?av . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY xsd:integer(?p)
Try it!

VIAF members[edit]

#title:VIAF members
SELECT ?id ?idLabel ?cod
WHERE {
  ?id wdt:P31 wd:Q55586529 ;
      p:P1552 [ ps:P1552 wd:Q26921380; pq:P3295 ?cod ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?cod
VIAF members 
#title:Decrease of the number of VIAFs thanks to Wikidata
SELECT ?item (COUNT(DISTINCT ?viaf_ref) AS ?n_ref) (COUNT(DISTINCT ?viaf) AS ?n)
WHERE {
  ?item wdt:P69 wd:Q1576779 .
  ?item ?id [ prov:wasDerivedFrom [ pr:P214 ?viaf_ref ] ] .
  ?item wdt:P214 ?viaf .
}
GROUP BY ?item ?n_ref ?n
HAVING(?n_ref > ?n)
Decrease of the number of VIAFs thanks to Wikidata

Best rank[edit]

SELECT DISTINCT ?elemento ?elementoLabel ?elementoDescription ?anno_nascita ?anno_morte ?luogo ?luogoLabel ?nw ?ni #?coord
WHERE {
  ?elemento wdt:P19 ?luogo .
  ?luogo wdt:P131* wd:Q16245 .
  OPTIONAL { ?luogo wdt:P625 ?coord . }
  ?elemento wikibase:sitelinks ?nw . FILTER(?nw > 9)
  ?elemento wikibase:identifiers ?ni . FILTER(?ni > 9)
  OPTIONAL { ?elemento p:P569 [ a wikibase:BestRank; psv:P569 [ wikibase:timeValue ?nascita ] ] . BIND(YEAR(?nascita) AS ?anno_nascita) }
  OPTIONAL { ?elemento p:P570 [ a wikibase:BestRank; psv:P570 [ wikibase:timeValue ?morte ] ] . BIND(YEAR(?morte) AS ?anno_morte) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}
ORDER BY DESC(?ni)
Try it!

Deprecated rank and qualifiers[edit]

#title:Deprecated rank with eventual motivation
SELECT ?item ?itemLabel ?gnd ?msLabel
WHERE {
 ?item p:P227 ?st .
 ?st wikibase:rank wikibase:DeprecatedRank .
 ?st ps:P227 ?gnd .
 OPTIONAL { ?st pq:P2241 ?ms . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
}
Deprecated rank with eventual motivation 
#title:One value with deprecated rank, no value with normal rank
SELECT ?item ?itemLabel ?gnd ?msLabel
WHERE {
 ?item p:P227 ?st .
 ?st wikibase:rank wikibase:DeprecatedRank .
 ?st ps:P227 ?gnd .
 OPTIONAL { ?st pq:P2241 ?ms . }
 MINUS { ?item p:P227 ?stt .
 ?stt wikibase:rank wikibase:NormalRank . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
}
ORDER BY ?ms
One value with deprecated rank, no value with normal rank 
#title:Same GND in two items, deprecated rank in the first, normal rank in the second
SELECT ?item1 ?item2 ?gnd ?msLabel
WHERE {
 { SELECT ?gnd
 WHERE {
 ?item p:P227 ?stdep .
 ?stdep wikibase:rank wikibase:DeprecatedRank .
 ?stdep ps:P227 ?gnd .
 } }
 ?item1 p:P227 ?stdep .
 ?stdep wikibase:rank wikibase:DeprecatedRank .
 ?stdep ps:P227 ?gnd .
 OPTIONAL { ?stdep pq:P2241 ?ms . }
 ?item2 wdt:P227 ?gnd .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
}
ORDER BY ?msLabel
Same GND in two items, deprecated rank in the first, normal rank in the second 
#title:Deprecated values sourced with Beweb, and corresponding correct values
SELECT DISTINCT ?item ?itemLabel ?pLabel ?deprecatedValueLabel ?BeWeB ?valueToUseLabel
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
  ?item ?property ?deprecatedStatement .
  ?p wikibase:claim ?property ; wikibase:statementProperty ?stprop .
  ?deprecatedStatement ?stprop ?deprecatedValue ; prov:wasDerivedFrom ?ref ; wikibase:rank wikibase:DeprecatedRank .
  OPTIONAL { ?item ?property ?normalStatement . ?normalStatement ?stprop ?normalValue ; wikibase:rank wikibase:NormalRank } .
  OPTIONAL { ?item ?property ?preferredStatement . ?preferredStatement ?stprop ?preferredValue ; wikibase:rank wikibase:PreferredRank } .
  BIND(COALESCE(?preferredValue,?normalValue) AS ?valueToUse).
  ?ref pr:P248 wd:Q77541206 .
  ?ref pr:P7796 ?beweb.
  wd:P7796 wdt:P1630 ?fmt .
  BIND(IRI(REPLACE(?beweb,  CONCAT('(',?beweb,')'), ?fmt)) AS ?BeWeB) .
}
ORDER BY ?pLabel
Deprecated values sourced with Beweb, and corresponding correct values

Most-recently-created items containing something[edit]

#title:Most-recently-created items containing Parsifal cluster ID (P12458)
SELECT ?item ?itemLabel {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:endpoint "www.wikidata.org";
                    wikibase:api "Generator" ;
                    wikibase:limit "once" ;
                    mwapi:generator "search";
                    mwapi:gsrsearch "haswbstatement:P12458" ;
                    mwapi:gsrsort "create_timestamp_desc" ;
                    mwapi:gsrlimit "100".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item), 33)))
Most-recently-created items containing Parsifal cluster ID (P12458)

Item creator[edit]

#title:item creator (complete information)
SELECT ?item ?creator ?timestamp ?edit_comment
WHERE
{
  ?input_item wdt:P9506 [] .
  BIND(strafter(str(?input_item),"/entity/") as ?titles)
  
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "allpages" .
    bd:serviceParam mwapi:gapfrom ?titles .
    bd:serviceParam mwapi:gapto ?titles .
    bd:serviceParam mwapi:prop "revisions" .
    bd:serviceParam mwapi:rvprop "user|timestamp|comment" .
    bd:serviceParam mwapi:rvdir "newer" .
    bd:serviceParam mwapi:rvlimit "1" .
    ?item wikibase:apiOutputItem mwapi:title .
    ?creator wikibase:apiOutput "revisions/rev/@user" .
    ?timestamp wikibase:apiOutput "revisions/rev/@timestamp" .
    ?edit_comment wikibase:apiOutput "revisions/rev/@comment" .
    bd:serviceParam wikibase:limit "once" .
  }
}
item creator (complete information)

Properties by creation date[edit]

#title:Authority control properties for authority files, sorted by year of creation (approximate on the basis of milestones)
SELECT ?property ?propertyLabel ?number (YEAR(?date) as ?year) WITH {
  SELECT ?property ?number (MIN(?milestone_) as ?milestone)
WHERE {
  ?property wdt:P31/wdt:P279* wd:Q96776953.
  BIND(xsd:integer(strafter(str(?property),"P")) as ?number)
  wd:Q73616850 wdt:P1114 ?milestone_ .   
  FILTER(?milestone_ > ?number)
  } group by ?property ?number   } as %i
WHERE
{
  INCLUDE %i
  wd:Q73616850 p:P1114 ?stat . 
  ?stat ps:P1114 ?milestone .   
  ?stat pq:P585 ?date
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Authority control properties for authority files, sorted by year of creation (approximate on the basis of milestones)

Check merged items[edit]

SELECT DISTINCT ?item ?redirect
WHERE {
  ?redirect wdt:P31 wd:Q5 .
  ?redirect wdt:P1047 ?id .
  ?redirect wdt:P570 ?date_of_death .
  ?item owl:sameAs ?redirect .
  ?item schema:dateModified ?change .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  FILTER(?change > "2021-09-01T00:00:00Z"^^xsd:dateTime)
}
Try it!

More[edit]

Petscan[edit]

Forma standard

Existing content items - cleaning (periodically)
Superfluous content in items - cleaning (periodically)
Superfluous items
Existing Wikimedia items - cleaning (periodically)
Items to be created (periodically)

it.wiki pl.wiki

Mistakes in library catalogs to be fixed[edit]

SBN author ID (P396)
CONOR.SI ID (P1280)

Things possibly to do[edit]

Routine[edit]

Queries for works in progress[edit]

Lists to be emptied[edit]

Imports to be checked[edit]

Check items not having VIAF ID (P214), potentially duplicates:

Queries to be checked[edit]

Miscellany[edit]

P301
Ancient world jobs
#Classical philologists without Open Library
SELECT ?item ?itemLabel ?birth ?death ?linkcount
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P106 wd:Q16267607 .
  OPTIONAL { ?item wdt:P569 ?birth . }
  OPTIONAL { ?item wdt:P570 ?death . }
  MINUS { ?item wdt:P648 ?op . }
  ?item wikibase:sitelinks ?linkcount . FILTER (?linkcount >= 1) .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?linkcount)
Try it!
Greek mythology characters lonely items
Italy jobs
#URL contains
SELECT ?p ?site ?a ?b WHERE {
  ?p wdt:P973 ?site .
  OPTIONAL { ?p wdt:P528 ?a . }
  filter(contains(str(?site),"dati.beniculturali.it/iccd/cf/resource/CulturalInstituteOrSite/"))
}
Try it!
Book authors jobs
#OpenMLOL author merges
SELECT ?item (GROUP_CONCAT(DISTINCT CONCAT("https://openmlol.it/autore/",?op); separator=", ") AS ?openmlol) (count(?op) AS ?num)
       (GROUP_CONCAT(DISTINCT CONCAT("https://viaf.org/viaf/",?v); separator=", ") AS ?viaf) (count(?v) AS ?num2)
WHERE {
  ?item wdt:P3762 ?op . 
  OPTIONAL { ?item wdt:P214 ?v . }
  #?article schema:about ?item .
  #?article schema:isPartOf <https://it.wikipedia.org/> .
  #SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it,en". }
  } 
GROUP BY ?item
HAVING(?num > 3)
ORDER BY DESC(?num2)
Try it! 
#Open Library author merges
SELECT ?item (URI(CONCAT("https://openlibrary.org/authors/merge?key=",GROUP_CONCAT(DISTINCT ?op; separator="&key="))) AS ?opurl) (count(?op) AS ?num)
WHERE {
  ?item wdt:P648 ?op . 
  ?item wdt:P31 wd:Q5 . 
  ?article schema:about ?item .
  ?article schema:isPartOf <https://it.wikipedia.org/> .
  } 
GROUP BY ?item
HAVING(?num > 1)
ORDER BY DESC(?opurl)
Try it!
International Museum Day (IMD)
#title:IMD persons
SELECT DISTINCT ?a ?aLabel ?aDescription ?id ?viaf
WHERE {
  ?a wdt:P108 [ wdt:P31 wd:Q33506 ; wdt:P17 wd:Q142 ] ; wikibase:identifiers ?id .
  #?a wdt:P106 wd:Q1650915 .
  OPTIONAL { ?a wdt:P214 ?viaf } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?id ?aLabel
IMD persons 
#title:IMD museums
SELECT DISTINCT ?item ?itemLabel ?viaf ?tr ?id
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q33506 ; wdt:P17 wd:Q142 ; wikibase:identifiers ?id .
  #?item wdt:P214 ?viaf .
  ?item wdt:P214 ?viaf .
  OPTIONAL { ?item wdt:P3134 ?tr } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
}
ORDER BY ?id ?itemLabel
IMD museums

Merges[edit]

Standard messages[edit]

  • Hi! Thank you for the descriptions you added. Just two suggestions: descriptions should not start with the capital letter and should not end with "." (example). Thank you,
  • Ciao! Grazie per le descrizioni che hai aggiunto! Ricorda però che, su Wikidata, l'iniziale della descrizione è normalmente minuscola (esempio), a meno che la parola non richieda di per sé la maiuscola. Grazie mille,
  • Descriptions with wrong uppercase letters: {{subst:Uw-uppercase}}

In sospeso[edit]

  • nuova proprietà "stato di conservazione libro": V + disc.
  • riflettere sulle opere greche con titolo sia "grc" sia "la": H
  • ridiscutere vincitori Olimpiadi antiche: V + disc.
  • nuovo tool per connettere voce a categoria di grado superiore (sennò si va ad intuito, come sempre): V
  • altro: 1, 2, 3
  • consistency check: F
  • Memento: Wikidata PPS2019, PPS2021
Requests (old)
  1. migliorie a Petscan: etichette in più lingue contemporaneamente, come nelle Query (B); ricerca link esterni (B = B*); sostituzione dichiarazioni (B) - qualificatori e note (M); etichette in più lingue contemporaneamente e sostituzione dichiarazioni (M)
  2. nuovo supertool per categorie di diversi progetti: L, M -> in realtà si può fare, almeno in parte, con Query, cfr. sopra
    • filters: items/unconnected pages; depth of categories; negative categories; (maybe) exclude some languages
  3. problemi quantitative metrical pattern (P2552): V1, V2 - needed a new datatype according to User:Phil wink/Quantitative scansion notes, se possibile
Mix'n'match (and around)
  1. most important:
    1. too many Greek-script labels
    2. autoupdate formatter URLs (e.g. this)
    3. allow formatter URL without a property
    4. various 2020 ideas
  2. other:
    1. specific catalogues: many, Hrvatska, SNK, SBN (needs marking as Q5), PTBNP
    2. check Auxiliary data matcher
    3. loading issues
  3. around: misinterpretation of GND fields
  4. QuickStatements: issue with running from Shell

Promemoria[edit]

Omonimie[edit]

  • Esempi di omonimie risolte (anche in SBN; cfr. soprattutto SBNV a partire da SBNV088181): Heraclitus; Luigi Vannutelli; Francesco Tirelli; Vincenzo Torrieri; Domenico Cardella; Domenico Berardi; Elvio Calderoni; Adele Bianchi; Giuseppe Bini; Mario Carelli; (Marco Bernardi); Paola Valentini; Carlo Baldini; Federica Fontana; Pietro Longo; Lorenzo Fontana; Fabio Marchetti; Alberto Castelli; Paolo Gasparini; Luigi Ventura; Carlo Rossetti; Nicola Cipriani (cfr. 1, 2, 3, 4); Giovanni Persico; Giovanni Cecchini; Mauro Moroni; Michele Mancini; Franco Viola; Gaetano Capasso; Piero/Pietro Nelli; Domenico Cambareri; Andrea Lazzarini; Paolo Rotta; Alberto Olivetti; Paolo Bernardi; Pierre Brunet; Giuseppe Mirabelli; Giuseppe Mirabella; Andrea Giordano; Piero Schiavazzi; Giuseppe Guicciardi; Francesco D'Alessio; Franco Monaco; Mario Calvetti; Angelo Rubino; Giuseppe Solarino; Mario Corsi; Peter Wolf; Giuseppe Ricci; Francesco Cappa; Franco Fossati; Francesco Capalbo; Emilio Zanette; Domenico Bartolini; Luigi Miraglia; Giuseppe Boni; Carlo Ruggiero; Stefano Bartolini; Angelo Maggi; Antonio Piazza; Piero Donnini; Vittorio Cantù; Cesare Manzoni; Giuseppe Patanè; Antonio Pelliccia; Carlo Mauri; Mario Tosi; Carmine Guarino; Vincenzo Festa; Vittorio Fontana; Domenico Spada; Carlo Ambrosoli
  • Omonimie agli onori delle cronache: Bruno Segre (Morning, 25 agosto 2023; cf. GND); Gigi Riva (Morning, 24 gennaio 2024; cf. GND)

Ontologia[edit]

Miscellanea[edit]

Unioni e conflazioni
Riferimenti e dintorni
Identificativi e loro problemi
Ontologia
Ecc.

Idee di tesi su Wikidata[edit]

Quattro proposte di tesi su Wikidata
  1. uso prosopografico di Wikidata: dato un corpus di persone definito da una fonte (suggerirei una fonte cartacea, es. un dizionario biografico; segnalo a mo' di esempi http://id.sbn.it/bid/BVE0572891 http://id.sbn.it/bid/BVE0583468 http://id.sbn.it/bid/BVE0572891 http://id.sbn.it/bid/RAV1956670), si inseriscono in Wikidata tutte le persone descritte nella fonte, desumendo da essa i dati anagrafici ed eventuali altre info significative (es. per i soprintendenti si aggiungerebbe la carica di soprintendente con anno di inizio e fine); alla fine del lavoro si stendono delle query per studiare il corpus di persone in questione (esempi: la percentuale di donne; quanti sono nati in quale regione; quanti soprintendenti nati in regione X hanno svolto la loro carica nella medesima regione; ecc.); nella tesi si documenta il lavoro svolto su Wikidata nelle sue varie fasi e si aggiunge una sezione sui dati che è stato possibile ottenere dalle query SPARQL, dimostrando che strutturare i dati in Wikidata permette di fare studi statistici altrimenti impossibili; è un'opzione secondo me facile e che può dare risultati interessanti
  2. allineamento tra ontologia di Wikidata e thesaurus: si prendono un thesaurus generalista (prima opzione sicuramente il Nuovo Soggettario) e/o un thesaurus settoriale, si definisce un gruppo di termini su cui lavorare (es. termine X e tutti i narrower terms ricorsivamente subordinati ad esso), dopodiché si sincronizzano tutti quei termini con Wikidata, cercando al contempo di sistemare l'ontologia di Wikidata in quel settore; nella tesi si descrivono il lavoro svolto e le difficoltà incontrate, soffermandosi magari su alcuni casi specifici (es. termini molto simili che è stato difficile modellare in modo soddisfacente, magari perché fonti diverse erano in disaccordo sulla loro definizione); può essere molto interessante provare a coinvolgere lo staff del/dei thesaurus su cui si lavora, soprattutto per discutere i casi più problematici
  3. uso bibliografico di Wikidata: si prende un insieme di citazioni bibliografiche già disponibile in forma strutturata (.ris, o almeno in Excel o simili), può trattarsi di una bibliografia su un dato argomento oppure dello spoglio di un tot di numeri di una rivista; l'opzione di lavorare sugli articoli di una sola rivista, o di poche riviste, è ovviamente più facile rispetto a gestire una bibliografia che contenga tipologie di pubblicazioni diverse (monografie, articoli, capitoli di libri ecc.); consiglio di lavorare su pubblicazioni di ambito umanistico perché è un settore quasi del tutto scoperto in Wikidata e perché è anche un settore in cui, mancando spesso buoni database bibliografici aperti, strutturare bene tali dati in Wikidata può essere di grande importanza; si importano tali citazioni bibliografiche in Wikidata, possibilmente usando strumenti di import semiautomatici (OpenRefine e/o QuickStatements), e si effettua una revisione manuale, particolarmente importante per sistemare autori omonimi e per linkare le varie pubblicazioni ai loro argomenti; nella tesi si descrive il lavoro svolto nelle sue varie parti e si possono discutere delle query che fanno statistiche es. sugli argomenti più frequenti delle pubblicazioni, sulle provenienze degli autori ecc.
  4. confronto tra Wikidata e uno o più database: si prende un insieme di elementi di Wikidata (consiglierei elementi di persone) definiti da una query; per tali elementi si sceglie un identificatore X che contenga dati strutturati (io consiglio un authority file, che quindi abbia dati in MARC21 o UNIMARC; es. SBN); a seconda della preferenza, si può scegliere un campione di elementi di Wikidata che abbiano già tutti l'identificatore X (esempio di query: tutti i filologi classici italiani che abbiano l'identificatore X), oppure si può scegliere il campione e poi si verifica, nei casi in cui l'identificatore X manca, se manca perché non esiste veramente oppure se esiste lo si aggiunge (esempio di query: tutti i filologi classici italiani; dove manca X, si cerca se X esiste e lo si aggiunge nei casi in cui lo si trova); ovviamente la prima opzione è più veloce; una volta che si hanno tutte le corrispondenze tra Wikidata e X, si fanno delle analisi sulla completezza dei dati forniti da Wikidata e da X (per Wikidata è facile, si possono usare query SPARQL; per X bisogna scaricare con uno scrape tutti i singoli identificatori e poi scrivere un programma per fare queste stesse analisi); nella tesi si presentano i risultati dell'analisi svolta; ovviamente nulla vieta di confrontare Wikidata non con un solo database X, ma con due o tre database X Y Z ...
Retrieved from "https://www.wikidata.org/w/index.php?title=User:Epìdosis/sandbox&oldid=2118891957"