User:Frog23/My Queries

Things To Do edit

Instances of Year that are not fixed in time and are not assigned to a calendar edit

The following query uses these:

Items: year (Q577)   , Hebrew calendar (Q44722)   , Islamic calendar (Q28892)   , Solar Hijri calendar (Q950135)   , North Korean calendar (Q3353159)

Properties: point in time (P585)   , start time (P580)   , end time (P582)   , part of (P361)   , instance of (P31)  
```
SELECT ?year ?yearLabel WHERE {
  ?year p:P31 ?statement.
  ?statement ps:P31 wd:Q577.
  FILTER NOT EXISTS {?year wdt:P585 ?time1 }
  FILTER NOT EXISTS {?year wdt:P580 ?time2 }
  FILTER NOT EXISTS {?year wdt:P582 ?time3 }
  FILTER NOT EXISTS {?statement pq:P361 wd:Q44722 }
  FILTER NOT EXISTS {?year wdt:P361 wd:Q44722 }
  FILTER NOT EXISTS {?statement pq:P361 wd:Q28892 }
  FILTER NOT EXISTS {?year wdt:P361 wd:Q28892 }
  FILTER NOT EXISTS {?statement pq:P361 wd:Q950135 }
  FILTER NOT EXISTS {?year wdt:P361 wd:Q950135 }
  FILTER NOT EXISTS {?statement pq:P361 wd:Q3353159}
  FILTER NOT EXISTS {?year wdt:P361 wd:Q3353159 }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Try it!

Wikidata Editing and Maintenance edit

Instances of something by the number of properties they have edit

(in this case all instances of a Roller Coaster)

The following query uses these:

Items: roller coaster (Q204832)

Properties: instance of (P31)   , subclass of (P279)  
```
SELECT ?item ?itemLabel (COUNT (?prop) as ?count) WHERE{
  ?item wdt:P31/wdt:P279* wd:Q204832 ;
        ?prop ?statement.
  ?property wikibase:directClaim ?prop.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY $item ?itemLabel
ORDER BY DESC(?count)
```

Try it!

Most common properties used by all of the instances of something edit

The following query uses these:

Items: roller coaster (Q204832)

Properties: instance of (P31)   , subclass of (P279)  
```
SELECT ?property ?propertyLabel (COUNT (?item) as ?count) WHERE{
  ?item wdt:P31/wdt:P279* wd:Q204832 ;
        ?prop ?statement.
  ?property wikibase:directClaim ?prop.
  FILTER (?property != wd:P31)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" .}
  
}
GROUP BY $property ?propertyLabel
ORDER BY DESC(?count)
```

Try it!

Properties that are used to describe themselves edit

The following query uses these:

# Make a list of the properties that are used to describe themselves
SELECT ?property ?propertyLabel ?propertyDescription (COUNT(?propertyclaim) AS ?count) WHERE { 

  ?property a wikibase:Property .
  ?property wikibase:claim ?propertyclaim .

  ?property ?propertyclaim [] .
	SERVICE wikibase:label {            # ... include the labels
		bd:serviceParam wikibase:language "en" .
	}   
} GROUP BY ?property ?propertyLabel ?propertyDescription ORDER BY DESC (?count)

Try it!

Just for Fun edit

Age of Musicians at their death edit

The following query uses these:

Items: human (Q5)   , musician (Q639669)

Properties: instance of (P31)   , occupation (P106)   , subclass of (P279)   , date of birth (P569)   , date of death (P570)  
```
#defaultView:BarChart
# Query to find all musicians who have already died 
# calculate their age (full years) at death
# count how many of them died at each age

SELECT ?age  (COUNT (DISTINCT ?a) AS ?count) WHERE {
  ?a wdt:P31 wd:Q5 . #instance of human
  ?a wdt:P106/wdt:P279 wd:Q639669 . #occupation a subclass of musician
  ?a p:P569/psv:P569 ?birth_date_node . 
  ?a p:P570/psv:P570 ?death_date_node .
  ?birth_date_node wikibase:timeValue ?birth_date .
  ?death_date_node wikibase:timeValue ?death_date .
  FILTER(?age > 10 && ?age < 100) . #ignore outlyers, several of which are probably errors
  BIND( year(?death_date) - year(?birth_date) - if(month(?death_date)<month(?birth_date)
```

Try it!

Beetles named after The Beatles edit

(none in WD as of 2017-04-23)

The following query uses these:

Items: taxon (Q16521)   , beetles (Q22671)   , The Beatles (Q1299)

Properties: instance of (P31)   , parent taxon (P171)   , has part(s) (P527)   , named after (P138)  
```
SELECT ?beetle ?beetleLabel{
	?beetle wdt:P31 wd:Q16521 . #?beetle <instances of> <Taxon>
	?beetle wdt:P171* wd:Q22671 . #?beetle <parent taxon>* (= higher taxon) <Beetle>
	wd:Q1299 wdt:P527 ?beatle . #<The Beatles> <has part> ?beatle
	?beetle wdt:P138 ?beatle . #?beetle <named after> ?beetle
	SERVICE wikibase:label {bd:serviceParam wikibase:language "en"}
}
```

Try it!

Movies with past futures edit

The following query uses these:

Items: film (Q11424)

Properties: instance of (P31)   , subclass of (P279)   , start time (P580)   , point in time (P585)   , publication date (P577)   , set in period (P2408)   , earliest date (P1319)   , latest date (P1326)  
```
#movies which were set in the future when they were releast, but this future is now in the past
SELECT ?film ?filmLabel  (MIN(?pubDate) AS ?pubDate) ?periodTime WHERE {
  #instance of film (or any subclass)
  ?film wdt:P31/wdt:P279* wd:Q11424.
  #set in a period
  ?film p:P2408 ?periodStatement.
  ?periodStatement ps:P2408 ?period.
  #either the period statement has an earliest date (P1319) or latest date (P1326) qualifier
  OPTIONAL { ?periodStatement pq:P1319 ?periodTime1. }
  OPTIONAL { ?periodStatement pq:P1326 ?periodTime2. }
  #or the period has a start time(P580) or a point in time (P585)
  OPTIONAL { ?period wdt:P580 ?periodTime3. }
  OPTIONAL { ?period wdt:P585 ?periodTime4. }
  BIND(COALESCE(?periodTime1, ?periodTime2, ?periodTime3, ?periodTime4) AS ?periodTime)

  #film has a publication date
  ?film wdt:P577 ?pubDate.
  #period is after publication date but before current time
  FILTER ((?periodTime >= ?pubDate) && (?periodTime <= NOW()))
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?film ?filmLabel ?periodTime
```

Try it!

However a lot are missing w:List of stories set in a future now past

Actors or Actresses who have won an Oscar (Acadamy Award) for playing an Oscar winner edit

The following query uses these:

Items: Academy Awards (Q19020)

Properties: award received (P166)   , instance of (P31)   , cast member (P161)   , for work (P1686)   , character role (P453)  
```
#actors or actresses who have won an Oscar (Acadamy Award) for playing an Oscar winner
SELECT DISTINCT ?actorA ?actorALabel ?movie ?movieLabel ?actorB ?actorBLabel WHERE {
    ?actorA wdt:P166/wdt:P31 wd:Q19020.
    ?award wdt:P31 wd:Q19020.
    ?actorB p:P166 ?awardStatement.
    ?awardStatement ps:P166 ?award.
    #put a comment in front of the line below to show all oscar winners portait by oscar winners
    ?awardStatement pq:P1686 ?movie.
    ?movie p:P161 ?castStatement.
    ?castStatement ps:P161 ?actorB.
    ?castStatement pq:P453 ?actorA.
    FILTER (?actorA != ?actorB) #this line is only in here to filter out actors/actresses who play themselves when the condition is removed that the Oscar had to be won for the same movie
  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Try it!

People who are buried in the places they created/designed/build edit

The following query uses these:

Properties: place of burial (P119)   , subproperty of (P1647)  
```
SELECT ?person ?personLabel ?place ?placeLabel WHERE {
	hint:Query hint:optimizer "None" .
	?person wdt:P119 ?place.
  
	?property wdt:P1647*  wd:P170 .
	?property wikibase:statementProperty ?v .
	?property wikibase:claim ?p .
  
  	?place ?p ?wds .
	?wds ?v ?person .

	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Try it!