User:Hdevine825/Queries
Jump to navigation
Jump to search
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
A place to store some useful Wikidata queries.
Paralympics Queries
[edit]A few queries to identify and fill in Property:P1532 Country for Sport for Paralympic athletes.
National Paralympic/Olympic Committee Databases
[edit]SELECT ?database ?databaseLabel ?dbCountry ?dbCountryLabel ?dbQID ?dbQIDLabel ?statedIn ?statedInLabel WHERE {
?database wdt:P31 wd:Q30225604. #all NOC database properties
?database wdt:P17 ?dbCountry. #get country
OPTIONAL{
?dbQID wdt:P1687 ?database. #get QIDs that use properties from line 2
?dbQID wdt:P31/wdt:P279* wd:Q7094076. #filter down to online databases and subclasses (removes a lot of wiki templates)
}
OPTIONAL{ ?database wdt:P9073 ?statedIn. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Athletes with Paralympic.org ID and Olympic Committee ID but no Country for Sport
[edit]Uses national Paralympic committee identifiers to infer country for sport. Goal of importing the country results of this query as Property:P1532 Country for Sport with stated in used as a reference.
SELECT DISTINCT ?athlete ?athleteLabel ?dbCountry ?dbCountryLabel ?statedInRef ?statedInRefLabel WHERE {
?athlete wdt:P7550 ?paraID. #retrieve all athletes with a global paralympic id
MINUS{?athlete wdt:P1532 ?country.} #remove those with country for sport specified
?database wdt:P31 wd:Q30225604. #find all national database id properties related to paralympics
?database wdt:P17 ?dbCountry. #find countries for those databases
?database wikibase:directClaim ?databaseProp.
?athlete ?databaseProp ?countryID. #find athletes who appear in those databases
OPTIONAL{
?dbQID wdt:P1687 ?database.
?dbQID wdt:P31/wdt:P279* wd:Q7094076.
}
OPTIONAL{ ?database wdt:P9073 ?statedIn. }
BIND(COALESCE(?statedIn, ?dbQID) as ?statedInRef)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Burials for a Cemetery with Coordinates and Count of Sitelinks
[edit]#Cemetery Burials with coordinates and sitelink count
SELECT
?item
?itemLabel
?itemDescription
?location
( COUNT( ?sitelink ) AS ?sitelink_count )
WHERE
{
?item wdt:P31 wd:Q5. #instance of human
?item wdt:P119 wd:Q6723847. #place of burial, insert cemetery qid here
OPTIONAL{?item p:P119 [ ps:P119 ?placeburial; pq:P625 ?location ].} #return the coordinate location qualifier of place of burial if it exists
?sitelink schema:about ?item. #get site links
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } #default label thing
Optional{?item schema:description"en".} #get English description
}
GROUP BY ?item ?itemLabel ?location ?itemDescription #group items, if other field are to be shown they need to be added to this
HAVING ( COUNT( ?sitelink ) > 0 ) #only show if there is at least 1 sitelink
ORDER BY DESC( COUNT( ?sitelink ) ) #sort by number of sitelinks for each item
List of Schools by Research Papers with Specific Subjects
[edit]Not fully accurate to what schools produce the papers since it uses all schools the authors attended.
SELECT ?school ?schoolLabel ?subject ?subjectLabel (count(distinct(?paper)) as ?countPapers)
WHERE {
{?subject wdt:P279* wd:Q638328.} #subject is dynamical system
UNION
{?subject wdt:P279* wd:Q1787424.} #subject is chaos
UNION
{?subject wdt:P279* wd:Q185961.} #subject is time perception
{?paper wdt:P31/wdt:P279* wd:Q13442814.} #is a scholarly article
UNION
{?paper wdt:P31/wdt:P279* wd:Q18918145.} #is a academic journal article
?paper wdt:P921 ?subject;
wdt:P50 ?author.
{?author wdt:P69 ?school.} #author educated at
UNION
{?author wdt:P108 ?school.} #author employed at
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
group by ?school ?schoolLabel ?subject ?subjectLabel
order by desc(?countPapers)
Video Games by Number of Cast Members
[edit]SELECT ?item ?itemLabel ?languageLabel (count(?actor) as ?countActors) (count(?charName) as ?countCharsNames) (count(?charItem) as ?countCharItems)
WHERE
{
?item wdt:P31 wd:Q7889.
?item (p:P725|p:161) ?cast.
?cast (ps:P725|ps:161) ?actor.
optional{?cast pq:P4633 ?charName.}
optional{?cast pq:P453 ?charItem.}
optional{?cast pq:P407 ?language.}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
group by ?item ?itemLabel ?languageLabel
order by DESC(?countActors)
Film, TV, and Video Games set in Seattle by Release Year
[edit]#first line for testing, second for bar chart
#SELECT ?item ?itemLabel ?typeLabel ?year_ ?locationLabel Where {
SELECT (str(SAMPLE(?year_)) AS ?year ) (COUNT( ?item) as ?count) ?typeLabel WHERE {
VALUES ?type {wd:Q11424 wd:Q7889 wd:Q15416}
?item wdt:P31/wdt:P279* ?type.
?item wdt:P840 ?location.
?location wdt:P131* wd:Q5083.
?item wikibase:sitelinks ?linkcount.
?item wdt:P577 ?releaseDate.
BIND(YEAR(?releaseDate) as ?year_).
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,es,ar,fr" }
FILTER( ?year_ > 1900)
}
#first line for testing, second for bar chart
#GROUP BY ?item ?itemLabel ?typeLabel ?year_ ?locationLabel
GROUP BY ?year_ ?count ?typeLabel
ORDER BY ASC(?year_)
#defaultView:BarChart