User:Misc/Queries
Common queries I use
[edit]Sexual orientation
[edit]Usage of each label in Wikidata
[edit]#defaultView:BubbleChart
SELECT ?orientationLabel (count(*) as ?count)
WHERE
{
?item wdt:P31 wd:Q5 .
?item wdt:P91 ?orientation .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?orientationLabel
ORDER BY DESC(?count) ASC(?orientationLabel)
Usage of each label in Wikidata with genders
[edit]Inspired from Stack overflow
SELECT ?genderLabel ?orientationLabel (count(*) AS ?count) {
SELECT ?genderLabel ?orientationLabel where {
?item wdt:P31 wd:Q5 .
?item wdt:P21 ?gender .
?item wdt:P91 ?orientation .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
} GROUP BY ?genderLabel ?orientationLabel
Usage of each label in Wikidata with genders, in a graph
[edit]#defaultView:BarChart
SELECT (CONCAT(STR(?genderLabel), " / ", STR(?orientationLabel)) AS ?lab) (COUNT(*) AS ?count) WHERE {
SELECT ?genderLabel ?orientationLabel WHERE {
?item wdt:P31 wd:Q5;
wdt:P21 ?gender;
wdt:P91 ?orientation.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}
GROUP BY ?genderLabel ?orientationLabel
Unsourced P91 by orientation
[edit]SELECT ?oLabel (COUNT(?oLabel) AS ?c) WHERE {
?item wdt:P91 ?o;
p:P91 ?orientation.
FILTER(NOT EXISTS { ?orientation prov:wasDerivedFrom ?ref. })
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?oLabel
Bisexual people age chat
[edit]#defaultView:AreaChart
SELECT ?age (count(*) as ?c) WHERE {
?item wdt:P91 wd:Q43200;
wdt:P31 wd:Q5;
wdt:P21 ?gender.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?item wdt:P569 ?birth .
BIND(YEAR(NOW())-YEAR(?birth) AS ?age)
MINUS {
?item wdt:P570 ?mort .
}
FILTER(?age < 120)
} GROUP BY ?age
Gender
[edit]Folks not using female/male for sex or gender (P21)
[edit]SELECT DISTINCT ?itemLabel ?link ?item ?genderLabel ?pronomLabel
WHERE
{
?item wdt:P31 wd:Q5 .
?item wdt:P21 ?gender .
FILTER ( ?gender != wd:Q6581072 )
FILTER ( ?gender != wd:Q6581097 )
OPTIONAL { ?item wdt:P6553 ?pronom }
# ?link is about ?item
?link schema:about ?item .
# ?link is on english wikipedia
?link schema:isPartOf <https://en.wikipedia.org/>
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }
}
ORDER BY ?genderLabel
Broad query for LGBTQ+ folks in Occitanie without a article in french
[edit]SELECT DISTINCT ?item ?itemLabel ?genreLabel ?orientationLabel WHERE {
# personne né dans une ville dans l'occitanie
?item wdt:P31 wd:Q5 ;
# enchaine les propriétés
wdt:P19 [ wdt:P131 [ wdt:P131 wd:Q18678265 ] ] .
# https://en.wikibooks.org/wiki/SPARQL/UNION
{
# tout
?item wdt:P21 ?genre .
# sauf les cis
MINUS {
?item wdt:P21 wd:Q6581097 .
}
MINUS {
?item wdt:P21 wd:Q6581072 .
}
}
UNION
{
# tout, sauf les heteros
?item wdt:P91 ?orientation .
MINUS {
?iteam wdt:P91 wd:Q1035954 .
}
}
# retire les items avec un lien en francais
MINUS {
?link schema:about ?item ;
schema:inLanguage "fr" .
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}
Gender usage on wikidata
[edit]SELECT ?gender ?genderLabel (count(*) as ?count)
WHERE
{
?item wdt:P31 wd:Q5 .
?item wdt:P21 ?gender .
FILTER ( ?gender != wd:Q6581072 )
FILTER ( ?gender != wd:Q6581097 )
FILTER (! isBLANK(?gender)) .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?gender ?genderLabel
ORDER BY DESC(?count) ASC(?gender)
Pronouns
[edit]Non binary folks without a preferred pronoun listed
[edit]SELECT DISTINCT ?itemLabel ?link ?item ?pronomDisplay
WHERE
{
?item wdt:P31 wd:Q5 ;
wdt:P21 wd:Q48270 .
MINUS { ?item wdt:P6553 ?pronom }
# ?link is about ?item
?link schema:about ?item ;
# is on english wikipedia
schema:isPartOf <https://en.wikipedia.org/> .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }
}
ORDER BY ?pronomDisplay
People not using he/she as a pronoun
[edit]SELECT DISTINCT ?itemLabel ?item ?pronounDisplay ?pronoun
WHERE
{
?item wdt:P31 wd:Q5 .
?item wdt:P6553 ?pronoun .
# L484 => she
FILTER ( ?pronoun != wd:L484 ) .
# L485 => he
FILTER ( ?pronoun != wd:L485 ) .
?pronoun wikibase:lemma ?label .
# get the language of the lexem
BIND( LANG(?label) AS ?language )
# create a string to display
BIND(CONCAT(STR(?label), " [", STR(?language), "]") AS ?pronounDisplay)
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY ?itemLabel
Chart of humans pronouns on Wikidata
[edit]#defaultView:BarChart
SELECT ?display (COUNT(*) AS ?count)
WHERE {
?item wdt:P31 wd:Q5 .
?item wdt:P6553 ?pronoun .
# get the human readable part of a lexeme
?pronoun wikibase:lemma ?label .
# get the language of the lexeme
BIND( LANG(?label) AS ?language )
# create a string to display
BIND(CONCAT(STR(?label), " [", STR(?language), "]") AS ?display)
}
GROUP BY ?display
ORDER BY DESC(?count)
List of each pronoun defined in the base
[edit]See https://en.wikibooks.org/wiki/SPARQL/WIKIDATA_Lexicographical_data
Return all pronouns, not just personal ones (cause they seems to not be tagged as such for now). Used to see what can be entered on personal pronoun (P6553) (even if I am unclear on the whole lexeme stuff for now)
SELECT DISTINCT ?word ?lang ?languageLabel ?pronoun
WHERE {
?pronoun a ontolex:LexicalEntry;
wikibase:lemma ?word;
wikibase:lexicalCategory ?category .
# only keep pronouns (Q36224)
FILTER(?category = wd:Q36224) # pronoun
# ?lang is LANG(word)
BIND(LANG(?word) as ?lang)
# P424 => code de langue
?language wdt:P424 ?lang .
# Q34770 is "language", so ?language is a language, or a inherited class
?language wdt:P31/wdt:P279* wd:Q34770 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en,auto" }
}
ORDER BY ?language
French wikipedia articles about people using 'they' pronoun
[edit]SELECT DISTINCT ?itemLabel ?link ?item
WHERE
{
# ?item is a human person
?item wdt:P31 wd:Q5 .
# L371 is "they"
# ?item use they as personal pronon
?item wdt:P6553 wd:L371 .
# ?link is about ?item
?link schema:about ?item .
# ?link is in french
?link schema:inLanguage "fr"
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }
}
ORDER BY ?itemLabel
French articles with non binary people + pronouns
[edit]SELECT DISTINCT ?itemLabel ?link ?item ?pronomDisplay
WHERE
{
?item wdt:P31 wd:Q5 .
?item wdt:P21 wd:Q48270 .
OPTIONAL { ?item wdt:P6553 ?pronom .
?pronom wikibase:lemma ?pronomDisplay
}
# ?link is about ?item
?link schema:about ?item .
# ?link is in french
?link schema:inLanguage "fr"
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }
}
ORDER BY ?pronomDisplay
Video games
[edit]What to add for indiedb
[edit]SELECT ?item ?itemLabel
WHERE
{
?item wdt:P136 wd:Q2762504 .
MINUS { ?item wdt:P6717 ?indiedb }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
What to add for tads
[edit]select ?item ?itemLabel ?tads where
{
?item wdt:P136 wd:Q1143118 .
?item wdt:P31 wd:Q7889 .
OPTIONAL { ?item wdt:P6748 ?tads }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO],en" }
}
ORDER BY ?tads
Videogame with a QRM link on french wikipedia
[edit]SELECT DISTINCT ?itemLabel ?link ?item ?queerly ?queerlyDisplay
WHERE
{
# ?item is a human person
?item wdt:P31 wd:Q7889 .
# has a QRM property
?item wdt:P6554 ?queerly .
# ?link is about ?item
?link schema:about ?item .
# ?link is in french
?link schema:inLanguage "fr"
BIND(CONCAT("<https://queerlyrepresentme/title/", STR(?queerly), ">") AS ?queerlyDisplay)
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }
}
ORDER BY ?itemLabel
Value to clean for Nintendo E-Shop
[edit]SELECT ?state ?item ?itemLabel ?eshop WHERE {
# get the statement, required to delete with QuickStatement
?item p:P4685 ?state.
# get the value from that statement
?state ps:P4685 ?eshop.
# filter to keep only the one that should be here
FILTER(REGEX(?eshop, "^.*-(switch|3ds|wii-u)$"))
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
See Help:QuickStatements#Removing_statements.
Code snippet: awk -F, '{print $1}' query.csv | grep http | awk -F/ '{print $6}' | sed 's/-/\$/' | sed 's/^/-STATEMENT\t/'
Others
[edit]50 films interdit aux moins de 18 ans quelque part ayant eu une récompense
[edit]SELECT DISTINCT ?itemLabel ?rewardLabel ?classifLabel ?pLabel WHERE {
# get list of property related to classification of movie in ?p2
?p rdf:type wikibase:Property;
wdt:P31 wd:Q24716199;
wikibase:directClaim ?p2.
# get movie
?item wdt:P31 wd:Q11424;
# with a reward
wdt:P166 ?reward;
# and a classification
?p2 ?classif.
# get age limit
?classif wdt:P2899 ?c.
# and keep classification for more than 18
FILTER(?c >= 18 )
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 50
Compte des ADQ sur WP
[edit]SELECT ?badgeLabel ?badge (COUNT(?link) AS ?count) WHERE {
?link wikibase:badge ?badge;
schema:about ?item;
schema:inLanguage "fr";
schema:isPartOf <https://fr.wikipedia.org/>.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?badge ?badgeLabel
Code postal d'un département francais
[edit]SELECT DISTINCT ?codePostal
WHERE
{
?item wdt:P31 wd:Q484170 .
?item wdt:P281 ?codePostal .
?item wdt:P131 ?departement .
?departement wdt:P31 wd:Q6465 .
?departement wdt:P2586 "94" .
}
Listes des épisodes de She-Ra
[edit]SELECT ?order ?item ?avantLabel ?itemLabel ?suiviLabel WHERE
{
?item wdt:P31 wd:Q21191270 ;
wdt:P179 wd:Q48731484 .
?item p:P179 ?statement .
?statement pq:P1545 ?order .
OPTIONAL { ?item wdt:P156 ?suivi }
OPTIONAL { ?item wdt:P155 ?avant }
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" }
}
ORDER BY xsd:integer(?order)
Femmes (cis et trans) née dans l'héraut sans avoir d'article en francais
[edit]SELECT DISTINCT ?item ?itemLabel ?lang WHERE {
# personne né dans une ville de l'héraut
?item wdt:P31 wd:Q5 ;
# enchaine les propriétés
wdt:P19 [ wdt:P131 wd:Q12545 ] .
# qui est féminin (féminin, et ses sous classes)
?item wdt:P21/wdt:P279* wd:Q6581072 .
# retire les items avec un lien en francais
MINUS {
?link schema:about ?item;
schema:inLanguage "fr".
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
}
Description attaché à des articles de "pejoratif"
[edit]SELECT ?item ?itemLabel ?desc WHERE {
?item wdt:P31 wd:Q545779 .
# get the desc
?item schema:description ?desc .
# only in english
FILTER(LANG(?desc) = "en").
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Films présentés lors du 28eme festival Chéries-chéris sans articles sur fr.wp
[edit]SELECT ?item ?itemLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?item wdt:P5072 wd:Q120858230 .
MINUS {
?link schema:about ?item;
schema:inLanguage "fr".
}
}
Tools I always search
[edit]http://wikipediatools.appspot.com/linksearch.jsp
Notes
[edit]Books
[edit]A book requires 2 item, 1 for the general object, 1 for the edition. Some property go on the first, some on the 2nd. I need to list them here: Go on version, edition or translation (Q3331189):
- ISBN-13 (P212)
- Amazon Standard Identification Number (P5749)
- Open Library ID (P648)
- OCLC control number (P243)
Go on the written work (Q47461344)