User:Idieh3/queries
Jump to navigation
Jump to search
People[edit]
Search person with NTA number in Wikidata[edit]
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P1006 "068899513" . # Search NTA author in Wikidata
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
WQS: Try it
Search persons with NTA number in Wikidata and show the entire formatted URL[edit]
SELECT ?person ?ntaValue
WHERE
{
?person wdt:P1006 ?ntaId .
wd:P1006 wdt:P1630 ?formatterUrl .
BIND(IRI(REPLACE(?formatterUrl, "\\$", ?ntaId)) AS ?ntaValue)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
} LIMIT 10
WQS: Try it
Awards received for author[edit]
SELECT ?p ?pLabel ?award ?awardLabel (YEAR(?when) as ?year)
WHERE
{
?p wdt:P50 wd:Q110731965 ; # author = Georgia Beers
p:P166 ?awardnode . # award received node
?awardnode ps:P166 ?award ; # award received
pq:P585 ?when . # point in time qualifier of award received
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
order by ?pLabel
WQS: Try it
Timeline of 1st women practising a given sports discipline[edit]
Timeline of 1st women practising a given sports discipline (for each sport profession, the earliest-born woman with that occupation) - also a nice showcase what subqueries can do.
#defaultView:Timeline
select ?sportswoman ?sportswomanLabel ?professionLabel ?first with {
select distinct ?profession where {
?profession wdt:P279* wd:Q2066131 .
} } as %sport_professions with {
select ?sportswoman ?profession ?date_of_birth where {
include %sport_professions
?sportswoman wdt:P21 wd:Q6581072 .
?sportswoman wdt:P569 ?date_of_birth .
?sportswoman wdt:P106 ?profession .
} } as %sportswomen with {
select ?profession (MIN(?date_of_birth) as ?first) where {
include %sportswomen
} group by ?profession } as %world_firsts with {
select ?sportswoman ?profession ?first where {
include %sportswomen
include %world_firsts
FILTER( ?first = ?date_of_birth && BOUND(?profession))
} } as %final_results where {
include %final_results
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
WQS: Try it
Query by: Vojtěch Dostál Source: https://twitter.com/medi_cago/status/1487549749830078471
title:musical works/compositions with titles that are female given names[edit]
# title:musical works/compositions with titles that are female given names
SELECT ?song ?female_name ?title (LANG(?title) AS ?title_language) ?note
WITH
{
SELECT DISTINCT ?song ?title
{
?song wdt:P31 / wdt:P279 * wd:Q105543609 .
?song wdt:P1476 ?title .
}
} AS %songs
{
{
INCLUDE %songs
?female_name wdt:P1705 ?title .
?female_name wdt:P31 wd:Q11879590 .
}
UNION
{
INCLUDE %songs
FILTER (LANG(?title) != "mul")
BIND (STRLANG(STR(?title), "mul") AS ?title_mul)
?female_name wdt:P1705 ?title_mul .
?female_name wdt:P31 wd:Q11879590 . hint:Prior hint:runLast true.
BIND ("match one to multiple" AS ?note)
}
}
WQS: Try it
Query by: Moebeus Source: https://twitter.com/exmusica/status/1573308311784521728
LGBT[edit]
List of LGBT Publishers[edit]
SELECT ?pub ?pubLabel
WHERE
{
?pub wdt:P31 wd:Q61755026 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
List of LGBT Bookshops[edit]
SELECT ?shop ?shopLabel ?countryLabel
WHERE
{
?shop wdt:P31 wd:Q61696039 ;
wdt:P17 ?country .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
List of LGBT Film Festivals[edit]
SELECT ?film ?filmLabel ?countryLabel
WHERE
{
?film wdt:P31 wd:Q62018250 ;
wdt:P17 ?country .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
ORDER BY ?countryLabel
WQS: Try it
List of LGBT Rights Activists - Female[edit]
SELECT ?human ?humanLabel ?occupationLabel
WHERE
{
?human wdt:P31 wd:Q5 ; # instance of Human
wdt:P106 wd:Q19509201 ; # occupation LGBTI rights activist
wdt:P21 wd:Q6581072 ; # gender female
wdt:P106 ?occupation . # all occupations, easy to find someone similar
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en
}
WQS: Try it
List of LGBT Literary Awards[edit]
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P279 wd:Q107627866. # subclass of = LGBT literary award
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
Fictional[edit]
Episodes of the TV series "Doctor Who"[edit]
SELECT ?episode_nb ?item ?itemLabel ?original_title ?season_nb ?episode_nb_in_season ?season ?sl ?st
WHERE
{
?item wdt:P31 wd:Q21191270 ; wdt:P4908 ?season ; wdt:P179 wd:Q34316 .
OPTIONAL { ?season p:P179 [ pq:P1545 ?season_nb ] }
OPTIONAL { ?item p:P4908 [ pq:P1545 ?episode_nb_in_season ] }
OPTIONAL { ?item p:P179 [ pq:P1545 ?episode_nb ] }
OPTIONAL { ?item wdt:P1476 ?original_title . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
OPTIONAL { ?item wikibase:sitelinks ?sl ; wikibase:statements ?st }
}
ORDER BY xsd:integer(?season_nb) xsd:integer(?episode_nb)
WQS: Try it
Grey's Anatomy: Q438406
Based on: Query: Episodes of the TV series "The Mentalist"
DC Comics items that have the official website, but not the DC comics ID[edit]
SELECT ?item ?itemLabel ?node ?charid
WHERE
{
?item p:P856 [ ps:P856 ?node ] . # get the node (literal inside) of the official website
OPTIONAL { ?item wdt:P9851 ?charid } # get the DC character ID
FILTER(CONTAINS(STR(?node), "dccomics.com/characters")) . # Only ask for official websites of dccomics characters
FILTER(!BOUND(?charid)) . # And only when the character ID is not yet set
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
LIMIT 10
WQS: Try it
Non-binary fictional characters[edit]
#non-binay fictional characters
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P31 wd:Q15632617 ; # Must be of a fictional human
wdt:P21 wd:Q48270 . # gender non-binary
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
LIMIT 100
WQS: [failed Try it]
Narrative locations for author[edit]
SELECT ?p ?pLabel ?year ?loc ?locLabel ?coord
WHERE
{
?p wdt:P50 wd:Q110731965 ; # author = Georgia Beers
wdt:P577 ?d . # Publication date
BIND (YEAR(?d) as ?year) . # publication date as year
OPTIONAL {
?p wdt:P840 ?loc . # narrative location
OPTIONAL { ?loc wdt:P625 ?coord . } # coordinate location
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
order by ?year
WDS: https://w.wiki/563H
Geo[edit]
Public art in Amsterdam[edit]
# Public artworks
#defaultView:Map
# SAMPLE (needs GROUP BY): only show one of the values of these properties. Otherwise the same piece of art is shown multiple times in the list.
SELECT DISTINCT ?item ?itemLabel (SAMPLE(?instance_ofLabel) as ?instance_ofLabel) (SAMPLE(?creatorLabel) as ?creatorLabel) (SAMPLE(?image) as ?image) ?coordinates WHERE {
?item (wdt:P31/(wdt:P279*)) wd:Q838948 ; # instance of/subclass: work of art
(wdt:P136/(wdt:P279*)) wd:Q557141 . # genre/subclass: public art
?item wdt:P17 wd:Q55 . # country Netherlands
{ ?item wdt:P131 wd:Q9899 . } # municipality amsterdam
UNION # or
{ ?item wdt:P131 wd:Q727 . } # capital Amsterdam
SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
OPTIONAL {
?item wdt:P31 ?instance_of.
?item wdt:P18 ?image.
?item wdt:P170 ?creator.
?item wdt:P625 ?coordinates .
}
}
GROUP BY ?item ?itemLabel ?coordinates
WQS: Try it
Based on: Query: all public artworks in Armenia on Wikidata
Mountains in Austria mapped with colours that correspond to the mountain height[edit]
Mountains in Austria mapped with colours that correspond to the mountains height: from red for the lowest through green to black for the highest.
# The heights of mountains in Switzerland
#defaultView:Map{"hide":["?x_coords","?rgb"]}
SELECT ?x ?xLabel ?description ?x_coords ?rgb WITH {
# extract the data
SELECT ?data ?x ?x_coords WHERE {
?x wdt:P31 wd:Q8502 .
?x p:P2044/psn:P2044 [
wikibase:quantityAmount ?data
] .
?x wdt:P17 wd:Q40 .
?x wdt:P625 ?x_coords .
}
} AS %totals
# determine the max and min values (used to calculate the spread
WITH {
SELECT (MAX(?data) AS ?max_data) (MIN(?data) AS ?min_data) WHERE {
INCLUDE %totals
}} AS %min_max
WHERE {
INCLUDE %totals
INCLUDE %min_max
?x rdfs:label ?xLabel. FILTER (LANG(?xLabel) = "en") .
BIND (CONCAT("The height of ", ?xLabel, " is about ", STR(ROUND(?data)), " metres" ) AS ?description)
BIND ( ?max_data - ?min_data AS ?spread )
BIND ( xsd:integer( 100 * (?data - ?min_data) / ?spread) AS ?percent ) # 0 -> 100
# some example hues 0:red, 20:orange, 50:yellow, 90:green, 180:cyan, 220:blue, 280:indigo, 330:violet, 340:red
# varying hue
# BIND ( 255 * ?percent / 100 + 0 AS ?hue )
# BIND (1 AS ?value) #
# BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's value
BIND ( (1 - ?percent / 100 + 0) AS ?value ) # to black
BIND ( 255 * ?percent / 100 + 0 AS ?hue )
BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's saturation
# BIND ( (1 - ?percent / 100 + 0) AS ?saturation ) # to -> white
# BIND (0 AS ?hue) # red
# BIND (1 AS ?value) . # 0 -> 1
# the following algorithm based on https://stackoverflow.com/questions/3018313/algorithm-to-convert-rgb-to-hsv-and-hsv-to-rgb-in-range-0-255-for-both
BIND ( ?hue / 60 AS ?hh )
BIND ( FLOOR (?hh) AS ?i ) .
BIND ( ?hh - ?i AS ?ff) .
BIND ( ?value * ( 1 - ?saturation ) AS ?p) .
BIND ( ?value * ( 1 - ( ?saturation * ?ff)) AS ?q ) .
BIND ( ?value * ( 1 - ( ?saturation * (1 - ?ff ))) AS ?t ) .
BIND ( COALESCE(
IF(?i = 0, ?value, 1/0),
IF(?i = 1, ?q, 1/0),
IF(?i = 2, ?p, 1/0),
IF(?i = 3, ?p, 1/0),
IF(?i = 4, ?t, 1/0),
IF(?i = 5, ?value, 1/0),
?value
) AS ?r )
BIND ( COALESCE(
IF(?i = 0, ?t, 1/0),
IF(?i = 1, ?value, 1/0),
IF(?i = 2, ?value, 1/0),
IF(?i = 3, ?q, 1/0),
IF(?i = 4, ?p, 1/0),
IF(?i = 5, ?p, 1/0),
?p
) AS ?g )
BIND ( COALESCE(
IF(?i = 0, ?p, 1/0),
IF(?i = 1, ?p, 1/0),
IF(?i = 2, ?t, 1/0),
IF(?i = 3, ?value, 1/0),
IF(?i = 4, ?value, 1/0),
IF(?i = 5, ?q, 1/0),
?q
) AS ?b )
BIND (FLOOR(255 * ?r) AS ?red)
BIND (FLOOR(255 * ?g) AS ?green)
BIND (FLOOR(255 * ?b) AS ?blue)
# red
BIND ( FLOOR (?red / 16) AS ?red_1 )
BIND ( COALESCE(
IF(?red_1 < 10, STR(?red_1), 1/0),
IF(?red_1 = 10, "a", 1/0),
IF(?red_1 = 11, "b", 1/0),
IF(?red_1 = 12, "c", 1/0),
IF(?red_1 = 13, "d", 1/0),
IF(?red_1 = 14, "e", 1/0),
IF(?red_1 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex1 )
BIND (FLOOR(?red - (16 * xsd:integer( ?red / 16 ))) AS ?red_2)
BIND ( COALESCE(
IF(?red_2 < 10, STR(?red_2), 1/0),
IF(?red_2 = 10, "a", 1/0),
IF(?red_2 = 11, "b", 1/0),
IF(?red_2 = 12, "c", 1/0),
IF(?red_2 = 13, "d", 1/0),
IF(?red_2 = 14, "e", 1/0),
IF(?red_2 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex2 )
BIND (CONCAT(STR(?red_hex1), STR(?red_hex2)) AS ?red_hex )
# green
BIND ( FLOOR (?green / 16) AS ?green_1 )
BIND ( COALESCE(
IF(?green_1 < 10, STR(?green_1), 1/0),
IF(?green_1 = 10, "a", 1/0),
IF(?green_1 = 11, "b", 1/0),
IF(?green_1 = 12, "c", 1/0),
IF(?green_1 = 13, "d", 1/0),
IF(?green_1 = 14, "e", 1/0),
IF(?green_1 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex1 )
BIND (FLOOR(?green - (16 * xsd:integer( ?green / 16 ))) AS ?green_2)
BIND ( COALESCE(
IF(?green_2 < 10, STR(?green_2), 1/0),
IF(?green_2 = 10, "a", 1/0),
IF(?green_2 = 11, "b", 1/0),
IF(?green_2 = 12, "c", 1/0),
IF(?green_2 = 13, "d", 1/0),
IF(?green_2 = 14, "e", 1/0),
IF(?green_2 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex2 )
BIND (CONCAT(STR(?green_hex1), STR(?green_hex2)) AS ?green_hex )
# blue
BIND ( FLOOR (?blue / 16) AS ?blue_1 )
BIND ( COALESCE(
IF(?blue_1 < 10, STR(?blue_1), 1/0),
IF(?blue_1 = 10, "a", 1/0),
IF(?blue_1 = 11, "b", 1/0),
IF(?blue_1 = 12, "c", 1/0),
IF(?blue_1 = 13, "d", 1/0),
IF(?blue_1 = 14, "e", 1/0),
IF(?blue_1 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex1 )
BIND (FLOOR(?blue - (16 * xsd:integer( ?blue / 16 ))) AS ?blue_2)
BIND ( COALESCE(
IF(?blue_2 < 10, STR(?blue_2), 1/0),
IF(?blue_2 = 10, "a", 1/0),
IF(?blue_2 = 11, "b", 1/0),
IF(?blue_2 = 12, "c", 1/0),
IF(?blue_2 = 13, "d", 1/0),
IF(?blue_2 = 14, "e", 1/0),
IF(?blue_2 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex2 )
BIND (CONCAT(STR(?blue_hex1), STR(?blue_hex2)) AS ?blue_hex )
BIND (CONCAT(STR(?red_hex), STR(?green_hex), STR(?blue_hex)) AS ?rgb)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Query by: Chris Groves Source: https://twitter.com/piecesofuk/status/1303969222222065664