User:Epìdosis/Queries
Jump to navigation
Jump to search
Try it!
Property replacement for QuickStatements
Property removal for QuickStatements
All identifiers in a single item
Count of biographical dictionaries connected to a library catalog
Count all properties used with a defined value
Humans with more than 150 identifiers
Number of incoming links for authors of articles published in Bibliothecae.it
Average number of statements for Italian schools by region
Ancestors (fathers only) of Charles V - inspired from Wikidata Graph Builder
Interpersonal relations of typographers active in Venice until 1850
Labels in two languages
Same label in two languages
Label containing
Description containing
Description exactly containing
Italian homonym people with or without SBN
Hononym things in Luxembourg
Try it!
Try it!
Try it!
Count by prefix
Try it!
Articles surely without author
Inscriptions with unknown collection
Try it!
Try it!
Try it!
Try it!
Try it!
Excluding items having only a specific qualifier-value
Items with PIAC and only one occupation (university professor)
VIAF appearing two times in the same item (= single-value violation)
NUKAT appearing two times equal in two different items (= unique-value violation)
VIAF unique value violations (recent items with GND)
A birth date referenced only from Wikimedia projects
Only one birth date referenced only from a Wikimedia project
Two similar sources (P248+P248) for the same value
Two similar sources (P248+P854) for the same value
References by retrieval date
Wrong P248 value
Single items containing references with only P813
Items containing date of birth referenced from NKC both as ID and as URL
Some items containing date of birth referenced from two URLs wrongly in the same reference
Items with redundant dates of death referenced only with Open Library, to be removed (note: run on QLever)
Try it!
Birth date referenced, only one having best rank, with precision year or higher and possible precision qualifiers
SBN people with birth date having a precision lower than day
Try it!
Try it!
Try it!
Morti recenti
Mappa dei luoghi di nascita degli autori italiani morti nel 1952
People born in 1950 with VIAF URL as source
Authors in IRIS SNS by decade of birth
Try it!
Most frequent birth places for SBN authors
Exclude single results
Places of birth for IRIS authors
Authors in IRIS SNS by presence in VIAF members
SBN IDs from BNCF not qualified
URL contains
reference URL contains
reference URL instead of ID
Order by number
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
VIAF members
Decrease of the number of VIAFs thanks to Wikidata
Try it!
Deprecated rank with eventual motivation
One value with deprecated rank, no value with normal rank
Same GND in two items, deprecated rank in the first, normal rank in the second
Deprecated values sourced with Beweb, and corresponding correct values
Most-recently-created items containing Parsifal cluster ID (P12458)
item creator (complete information)
Authority control properties for authority files, sorted by year of creation (approximate on the basis of milestones)
Try it!
Materials about SPARQL queries
[edit]- Wikidata:SPARQL query service (gentle introduction + complete tutorial + examples + weekly query examples + wikibook; newbie's guide; tutorial; general reference point about SPARQL); Prefixes used;
- Wikidata:Request a query
- QLever endpoint
- Whitelist of federated SPARQL endpoints
Other queries:
- User:Epìdosis/Queries/Exp: queries for beginners
- User:Epìdosis/Queries/Showcase: showcase queries divided by topic
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 . } }
}
|
#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 . } }
}
|
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
#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)
}
#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
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 .
}
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
|
#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
|
#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
|
#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
|
#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)
#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)
#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
#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)
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)
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
#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
|
#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)
|
Chain graph
[edit]#defaultView:Graph
#title:Ancestors (fathers only) of Charles V - inspired from Wikidata Graph Builder
SELECT ?persona ?personaLabel ?padre ?padreLabel
WHERE {
wd:Q32500 wdt:P22* ?persona .
OPTIONAL { ?persona wdt:P22 ?padre } .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
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" . }
}
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
#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
#title:Label containing
SELECT ?q ?orcid
WHERE {
?q rdfs:label "Stefano Bargioni"@en .
OPTIONAL { ?q wdt:P496 ?orcid . }
}
#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")
}
#title:Description exactly containing
SELECT ?item
WHERE
{
?item wdt:P106 wd:Q82955 .
?item schema:description "Alman politikacılar"@tr
}
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
#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
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}")).
}
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") })
}
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
#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)
Somevalue and novalue
[edit]SELECT ?p
WHERE {
{ ?p p:P9073/a wdno:P9073 . } UNION
{ ?p wdt:P9073 ?value . FILTER (isBlank(?value)) }
}
#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
#title:Inscriptions with unknown collection
SELECT DISTINCT ?item ?edr ?coll
WHERE {
?item wdt:P12495 ?edr ; p:P195 ?c . ?c ps:P195 ?coll . FILTER(wikibase:isSomeValue(?coll))
}
ORDER BY ?edr
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". }
}
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
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
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)
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
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 )
}
One value
[edit]#title:Items with PIAC and only one occupation (university professor)
SELECT ?item ?itemLabel ?itemDescription ?value
WHERE
{
?item wdt:P8982 ?value ; wdt:P106 wd:Q1622272, ?v .
FILTER NOT EXISTS { ?item wdt:P106 ?v2 . FILTER( ?v != ?v2 ) }
SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" } .
}
Multiple values
[edit]
SELECT ?item (count(?item) as ?num)
WHERE {
?item wdt:P131* wd:Q16245.
} GROUP BY ?item
ORDER BY DESC(?num)
|
SELECT ?item (count(?item) as ?num)
WHERE {
?item wdt:P131* wd:Q16245.
}
GROUP BY ?item
HAVING(count(?item) > 1)
ORDER BY DESC(?num)
|
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
#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
#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
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
|
#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
|
#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)
#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)
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.
}
#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")
}
#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
#title:Wrong P248 value
SELECT ?item ?ref WHERE {
?item wdt:P396 ?sbn.
?ref pr:P248 wd:Q576951.
?statement prov:wasDerivedFrom ?ref.
?item ?p ?statement.
}
#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 ) }
}
#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 .
}
#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
#title:Items with redundant dates of death referenced only with Open Library, to be removed (note: run on QLever)
SELECT ?item ?date (COUNT(DISTINCT ?ref) AS ?nref)
WHERE {
?item p:P570 ?st, ?st2 . ?st2 prov:wasDerivedFrom ?ref3 . MINUS { ?ref3 pr:P143 ?wp } .
FILTER(?st != ?st2 && YEAR(?date) = YEAR(?date2))
?st ps:P570 ?date ; prov:wasDerivedFrom ?ref, ?ref2 . ?ref2 pr:P648 ?v . ?st2 ps:P570 ?date2 .
}
GROUP BY ?item ?date
HAVING(?nref = 1)
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
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
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))
}
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 }
}
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
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". }
}
#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
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". }
}
#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"))
}
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
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". }
}
#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)
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 .
}
#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)
#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
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
#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
#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
#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)
}
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)
Sitelinks and badges
[edit]SELECT ?item ?article ?id
WHERE {
?item wdt:P8849 ?id .
?article schema:about ?item ; schema:isPartOf <https://bg.wikipedia.org/> .
}
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/> } .
}
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
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)
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)
Search in Wikipedia category
[edit]- Found working (at least from May 2019) - documentation in mw:MW2SPARQL (experimental project)
- Then not working (at least from June 2020): for alternatives (working, but much more complex indeed), see Wikidata:Request_a_query/Archive/2020/06#Search_in_Wikipedia_category
#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". }
}
|
#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
|
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)
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
#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)
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)
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". }
}
#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
#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
#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
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)))
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" .
}
}
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". }
}
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)
}