Open main menu

Deansfa

Joined 30 October 2012

Family treeEdit

Some SPARQL stuff of my ownEdit

Articles that are "Featured Articles" in one language and don't exist in an otherEdit

# Featured articles in Russian that don't have a French Wikipedia page

SELECT DISTINCT ?item ?itemLabel 
WHERE { 
  ?featuredArticle schema:about ?item ;
      			   schema:inLanguage "ru" ;
                   schema:isPartOf <https://ru.wikipedia.org/> ; 
                   wikibase:badge wd:Q17437796 . 

  MINUS {
    ?doesNotExistIn schema:about ?item ; 
                    schema:inLanguage "fr" ;
                    schema:isPartOf <https://fr.wikipedia.org/> .
  }
    
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ru" .
  }
}
ORDER BY ?itemLabel

Try it!

Alternative to the previous oneEdit

# Featured articles in Korean that don't have a French Wikipedia page
SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?featuredArticle schema:about ?item ; 
                   schema:inLanguage "ko" ; 
                   wikibase:badge wd:Q17437796 . 
  OPTIONAL { 
    ?sitelink schema:about ?item ; 
              schema:inLanguage "fr" .
  } 
  FILTER (!BOUND(?sitelink))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ja,ko" .
  }
}
ORDER BY ?itemLabel

Try it!

Articles that are "Featured", "Good" or "Features list" in one language and don't exist in an otherEdit

# Featured/Good articles in Korean that don't have a French Wikipedia page

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?featuredArticle schema:about ?item ; 
                   schema:inLanguage "ko" ; 
                   wikibase:badge ?badgeValues .

  VALUES ?badgeValues { 
    wd:Q17437796 # that are featured articles
  	wd:Q17506997 # or featured lists
    wd:Q17437798 # or good articles
  } 

  OPTIONAL { 
    ?sitelink schema:about ?item ; 
              schema:inLanguage "fr" .
  } 
  FILTER (!BOUND(?sitelink))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ja,ko" .
  }
}
ORDER BY ?itemLabel

Try it!

Horse breeds that don't have an article in FrenchEdit

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q3745054 . 
  OPTIONAL { 
    ?sitelink schema:about ?item ; 
              schema:inLanguage "fr" .
  } 
  FILTER (!BOUND(?sitelink))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de" .
  }
}
ORDER BY ?itemLabel

Try it!

People born in 1899 in PolotskEdit

# People born in 1899 in Polotsk

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  
  ?item wdt:P19 wd:Q200797 ; 
        wdt:P569 ?date .
  FILTER (YEAR(?date) = 1899)
 
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,ru" .
  }
}
ORDER BY ?itemLabel

Try it!

Poems by Arthur RimbaudEdit

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q5185279 ;
        wdt:P50 wd:Q493 . 

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" .
  }
}
ORDER BY ?itemLabel

Try it!

Poems that has Ophélie in their labelEdit

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31 wd:Q5185279 ;
        rdfs:label ?label .

  FILTER(LANG(?label) IN ("en", "es", "fr", "de")) . 
  FILTER(CONTAINS(?label, "Ophélie")) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de,es" .
  }
}
ORDER BY ?itemLabel

Try it!

Velodromes in the United StatesEdit

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  ?item wdt:P31 wd:Q830528 ;
        wdt:P17 wd:Q30 . 

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" .
  }
}
ORDER BY ?itemLabel

Try it!

#Locations of velodromes
#defaultView:Map
SELECT ?objectLabel ?objectDescription ?link ?coord
WHERE {
  ?object wdt:P31 wd:Q830528 ; wdt:P625 ?coord .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

Architecture in ManhattanEdit

#Buildings on Park Avenue
#defaultView:Map
SELECT ?objectLabel ?objectDescription ?link ?coord
WHERE {
  ?object wdt:P669 wd:Q109711 ; wdt:P625 ?coord .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

#Buildings with Park Avenue in their label that don't have the property "voie" set 
SELECT DISTINCT ?objectLabel ?objectDescription ?voieLabel
WHERE {
  ?object wdt:P31 wd:Q13402009 ; rdfs:label ?label .
  OPTIONAL { ?object wdt:P669 ?voie }
  FILTER(CONTAINS(?label, "Park Avenue")) .
  FILTER (!BOUND(?voie)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr"
  }
}

Try it!

#a little of a repeat of the previous one.
#apartment buildings in the USA that don't have the property "voie" set 
SELECT DISTINCT ?objectLabel ?objectDescription ?voieLabel
WHERE {
  ?object wdt:P17 wd:Q30 ; wdt:P31 wd:Q13402009 ; rdfs:label ?label .
  OPTIONAL { ?object wdt:P669 ?voie }
  #FILTER(CONTAINS(?label, "89th Street")) .
  FILTER (!BOUND(?voie)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en"
  }
}

Try it!

#Buildings on Park Avenue that don't have a street number 
SELECT DISTINCT ?object ?objectLabel ?voieLabel ?streetNumber
WHERE {
  ?object wdt:P669 wd:Q109711 . 
  ?object p:P669 ?voie . 
  OPTIONAL { ?voie pq:P670 ?streetNumber } .
  FILTER (!BOUND(?streetNumber)) .

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr"
  }
}

Try it!

#Apartment buildings in Manhattan that don't have coordonates 
SELECT ?object ?objectLabel $objectDescription $coord
WHERE {
  ?object wdt:P31 wd:Q13402009 .
  ?object wdt:P131 wd:Q11299 .
  OPTIONAL { ?object wdt:P625 ?coord } .
  FILTER (!BOUND(?coord)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

# REGEX usage example:
# buildings in Manhattan that have "Drive", "Avenue" or "Street" in their label
SELECT DISTINCT ?object ?objectLabel ?voieLabel ?streetNumber
  WHERE {
    ?object wdt:P131 wd:Q11299 ; rdfs:label ?label .
    ?object p:P669 ?voie.
    OPTIONAL { ?voie pq:P670 ?streetNumber. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr". }
    FILTER REGEX(STR(?label), "Drive|Avenue|Street")
    FILTER(!BOUND(?streetNumber))
  }

Try it!

Interdit d'interdireEdit

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

#List of episodes with aggregated comma-separated guests
SELECT DISTINCT ?episode ?numero_episode ?date ?episodeLabel (GROUP_CONCAT(DISTINCT ?guestLabel; SEPARATOR = ", ") AS ?liste_invites) (URI(CONCAT("https://www.youtube.com/watch?v=", ?youtube)) AS ?lien_youtube)
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?episode wdt:P577 ?date.
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?numero_episode.
  OPTIONAL { ?episode wdt:P1651 ?youtube. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?episode rdfs:label ?episodeLabel.
  }
}
GROUP BY ?episode ?numero_episode ?date ?episodeLabel ?youtube
ORDER BY DESC(xsd:integer(?numero_episode))

Try it!

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

#List of episodes with episode number per season and aggregated comma-separated guests
SELECT DISTINCT ?episode ?no_episode ?no_episode_saison ?date ?episodeLabel (GROUP_CONCAT(DISTINCT ?guestLabel; SEPARATOR = ", ") AS ?liste_invites) (URI(CONCAT("https://www.youtube.com/watch?v=", ?youtube)) AS ?lien_youtube)
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  ?episode p:P4908 ?season.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?episode wdt:P577 ?date.
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?no_episode.
  ?season pq:P1545 ?no_episode_saison.
  OPTIONAL { ?episode wdt:P1651 ?youtube. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?episode rdfs:label ?episodeLabel.
  }
}
GROUP BY ?episode ?no_episode ?no_episode_saison ?date ?episodeLabel ?youtube
ORDER BY DESC(xsd:integer(?no_episode))

Try it!

#List of guests per number of appearance
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT (COUNT (?guest) as ?count) ?guestLabel 
WHERE {
  ?episode (wdt:P31/wdt:P279*) wd:Q1983062.
  ?episode p:P179 ?statement.
  OPTIONAL { ?episode wdt:P5030 ?guest. }
  ?statement ps:P179 wd:Q56816469.
  ?statement pq:P1545 ?numero_episode.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en".
                         ?guest rdfs:label ?guestLabel.}
}
GROUP BY (?guestLabel)
ORDER BY DESC (?count)

Try it!

SELECT DISTINCT ?episode ?guestLabel ?genderLabel ?numero_episode ?occupationLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  OPTIONAL { ?guest wdt:P106 ?occupation. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
ORDER BY ASC(xsd:integer(?numero_episode))

Try it!

#Parité homme/femme dans Interdit d'interdire

SELECT DISTINCT (COUNT (?gender) as ?count) ?genderLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY ?genderLabel

Try it!

# Profession des invités dans Interdit d'interdire
SELECT DISTINCT (COUNT (?occupation) as ?count) ?occupationLabel
WHERE {
  ?episode (wdt:P31/(wdt:P279*)) wd:Q1983062;
    p:P179 ?statement;
    wdt:P5030 ?guest.
  ?statement ps:P179 wd:Q56816469;
    pq:P1545 ?numero_episode.
  ?guest wdt:P21 ?gender.
  ?guest wdt:P106 ?occupation.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en".
    ?guest rdfs:label ?guestLabel.
    ?gender rdfs:label ?genderLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY ?occupationLabel
ORDER BY DESC (?count)

Try it!

Doubloug PrizeEdit

#Swedish recipients
SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?swedish_recipients) 
WHERE {
  ?item wdt:P27 wd:Q34;
    p:P166 ?prize.
  ?prize ps:P166 wd:Q1233326;
    pq:P585 ?date_received.
  BIND(YEAR(?date_received) AS ?year_received)
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "fr,en,de,es". 
    ?item rdfs:label ?itemLabel.
  }
}
GROUP BY ?year_received
ORDER BY (?year_received)

Try it!

#Join Swedish and Norway recipients
SELECT ?year_received ?swedish_recipients ?norway_recipients
WHERE {
{
  SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?swedish_recipients) 
  WHERE {
    ?item wdt:P27 wd:Q34; p:P166 ?prize.
    ?prize ps:P166 wd:Q1233326; pq:P585 ?date_received.
    BIND(YEAR(?date_received) AS ?year_received)
    SERVICE wikibase:label { 
      bd:serviceParam wikibase:language "fr,en,de,es". 
      ?item rdfs:label ?itemLabel.
    }
  }
  GROUP BY ?year_received
}
{
  SELECT DISTINCT ?year_received (GROUP_CONCAT(DISTINCT ?itemLabel; SEPARATOR = ", ") AS ?norway_recipients) 
  WHERE {
    ?item wdt:P27 wd:Q20; p:P166 ?prize.
    ?prize ps:P166 wd:Q1233326; pq:P585 ?date_received.
    BIND(YEAR(?date_received) AS ?year_received)
    SERVICE wikibase:label { 
      bd:serviceParam wikibase:language "fr,en,de,es". 
      ?item rdfs:label ?itemLabel.
    }
  }
  GROUP BY ?year_received
}
}
ORDER BY (?year_received)

Try it!