Wikidata

User:Misc/Queries

< User:Misc

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)
Try it!

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
Try it!

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
Try it!

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
Try it!

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
Try it!

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
Try it!

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". }
}
Try it!

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)
Try it!

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
Try it!

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
Try it!

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)
Try it!

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
Try it!

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
Try it!

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
Try it!

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" }
}
Try it!

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
Try it!

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
Try it!

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". }
}
Try it!

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
Try it!

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
Try it!

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" .
}
Try it!

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)
Try it!

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". }
}
Try it!

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". }
}
Try it!

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".
  }
}
Try it!

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):

Go on the written work (Q47461344)

Retrieved from "https://www.wikidata.org/w/index.php?title=User:Misc/Queries&oldid=1939702720"