Wikidata:WikiProject University degrees/Queries

SPARQL queries :

What degree-granting universities does a country have in which cities?

Either the university is an institution of higher education or it is a subclass of that, and is in a city that is located in a country, in this case, Germany. And the institution (probably) still exists.

The following query uses these:

Items: higher education institution (Q38723)   , Germany (Q183)

Properties: instance of (P31)   , subclass of (P279)   , located in the administrative territorial entity (P131)   , country (P17)   , end time (P582)  
```
SELECT DISTINCT ?university ?universityLabel ?city ?cityLabel
WHERE {
  ?university wdt:P31/wdt:P279* wd:Q38723 ;
          wdt:P131 ?city .
  ?city wdt:P17 wd:Q183 .
  FILTER NOT EXISTS { ?statement pq:P582 ?university } # ... but the statement has no P582 (end date) qualifier
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
```

Try it!

Extend query with exact coordinates shown in a map using Portugal as example:

The following query uses these:

Items: university (Q3918)   , Portugal (Q45)

Properties: instance of (P31)   , subclass of (P279)   , country (P17)   , coordinate location (P625)   , official website (P856)

Features: map (Q24515275)  

#Locations of universities in Portugal
#added before 2016-10
#defaultView:Map
SELECT ?universityLabel ?universityDescription ?website ?coord WHERE {
  ?university (wdt:P31/wdt:P279*) wd:Q3918.
  ?university wdt:P17 wd:Q45.
  ?university wdt:P625 ?coord.
  OPTIONAL { ?university wdt:P856 ?website. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de". }
}

Try it!

What currently existing universities are located in a specific city?

The following query uses these:

Items: higher education institution (Q38723)   , Berlin (Q64)

Properties: instance of (P31)   , subclass of (P279)   , located in the administrative territorial entity (P131)   , end time (P582)   , followed by (P156)  
```
SELECT ?university ?universityLabel ?cityLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?university wdt:P31/wdt:P279* wd:Q38723 .
  ?university wdt:P131 ?city 
              FILTER (?city = wd:Q64 ). # Berlin
  ?city rdfs:label ?cityLabel 
              FILTER (lang(?cityLabel) = "en").
  FILTER NOT EXISTS {?university wdt:P582 ?endDate} 
  FILTER NOT EXISTS {?university wdt:P156 ?followingInstitution} . 
}
LIMIT 100
```

Try it!

Instead of university (Q3918) use higher education institution (Q38723)! Exclude universities that have an end data or a following institution.

What degree-granting universities does a city have?

Start off with collecting up the existing universities of a city, then see if any of them offer degrees. We will only get those that are actually entered into Wikidata, so there are currently not many.

The following query uses these:

Items: higher education institution (Q38723)   , Berlin (Q64)

Properties: instance of (P31)   , subclass of (P279)   , located in the administrative territorial entity (P131)   , grants (P5460)   , end time (P582)   , followed by (P156)  
```
SELECT DISTINCT ?university ?universityLabel ?cityLabel ?degreeLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?university wdt:P31/wdt:P279* wd:Q38723 .
  ?university wdt:P131 ?city 
              FILTER (?city = wd:Q64 ). # Berlin
  ?city rdfs:label ?cityLabel 
              FILTER (lang(?cityLabel) = "en").
  ?university wdt:P5460 ?degree .
  FILTER NOT EXISTS {?university wdt:P582 ?endDate;
                                 wdt:P156 ?followingInstitution} . 
  }
ORDER BY ?universityLabel ?cityLabel ?degreeLabel
```

Try it!

What degrees does a university offer?

The following query uses these:

Items: HTW Berlin (Q875138)

Properties: grants (P5460)   , academic major (P812)  
```
SELECT ?degreeLabel ?majorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  wd:Q875138 p:P5460 ?statement . # HTW Berlin
  ?statement ps:P5460 ?degree .
  ?statement pq:P812 ?major .
  
  }
ORDER BY ?degreeLabel
```

Try it!

Which university/ies offers a specific degree in a specific major?

The university is an instance of university and offers a given degree (in the example a master's degree) in a specific major (in the example - Electrical Engineering)

The following query uses these:

Items: university (Q3918)   , master's degree (Q183816)   , Master of Electrical Engineering (Q55636433)

Properties: instance of (P31)   , grants (P5460)   , academic major (P812)  
```
SELECT DISTINCT ?university ?universityLabel ?degreeLabel ?majorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?university wdt:P31 wd:Q3918  .
  ?university wdt:P5460 ?degree .
OPTIONAL { ?university p:P5460 ?acadDegree . ?acadDegree pq:P812 ?major}
 
FILTER(?degree = wd:Q183816) #master's degree
FILTER(?major = wd:Q55636433) #Electrical Engineering 
}

ORDER BY ?universityLabel ?degreeLabel ?majorLabel
```

Try it!

People with a bachelor's degree in astronomy

There are different ways of modelling an academic major.

This one models: Q educated at University and the "educated at" has both qualifiers academic degree = bachelor or subclass thereof and academic major = astronomy (to keep the lists small...)

The following query uses these:

Items: human (Q5)   , bachelor's degree (Q163727)   , astronomy (Q333)

Properties: instance of (P31)   , coordinate location (P625)   , subclass of (P279)   , educated at (P69)   , academic degree (P512)   , academic major (P812)  
```
# List of persons who hold a bachelor's degree in Astronomy
SELECT DISTINCT ?institution ?institutionLabel ?person ?personLabel ?degree ?degreeLabel ?majorLabel WHERE {
  ?person wdt:P31 wd:Q5 ; # instances (P31) of humans (Q5)
            p:P69 ?statement . # check for an "educated at" (P69) statement
  
  ?statement ps:P69 ?institution . # get value of the "educated at" statement, i.e. the institution
  ?institution wdt:P625 ?geoloc . # get the geolocation of the institution

  ?statement pq:P512 ?degree . # get qualifier "academic degree" (P512) 
  ?statement pq:P812 ?major . # get qualifier "academic major (P812)
  ?degree wdt:P31*/wdt:P279* wd:Q163727 . # filter for bachelor degrees (Q163727) 
  ?major wdt:P31*/wdt:P279* wd:Q333 . # filter major for astronomy (Q333)
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Try it!

Astronomy is too small for the other queries I want to ask. So I tried Computer Science (Q21198) and get some bizarre results. There also appear to be no persons with a sub-qualifier of academic major.

People with a bachelor's degree in computer science

But when I try "computer science", I get Latin American studies, too:

The following query uses these:

Items: human (Q5)   , bachelor's degree (Q163727)   , computer science (Q21198)

Properties: instance of (P31)   , coordinate location (P625)   , subclass of (P279)   , educated at (P69)   , academic degree (P512)   , academic major (P812)  
```
# List of persons who hold a bachelor's degree in Computer science
SELECT DISTINCT ?institution ?institutionLabel ?person ?personLabel ?degree ?degreeLabel ?majorLabel WHERE {
  ?person wdt:P31 wd:Q5 ; # instances (P31) of humans (Q5)
            p:P69 ?statement . # check for an "educated at" (P69) statement
  
  ?statement ps:P69 ?institution . # get value of the "educated at" statement, i.e. the institution
  ?institution wdt:P625 ?geoloc . # get the geolocation of the institution

  ?statement pq:P512 ?degree . # get qualifier "academic degree" (P512) 
  ?statement pq:P812 ?major . # get qualifier "academic major (P812)
  ?degree wdt:P31*/wdt:P279* wd:Q163727 . # filter for bachelor degrees (Q163727) 
  ?major wdt:P31*/wdt:P279* wd:Q21198 . # filter major for computer science (Q21198)
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Try it!

How many members of the Bundestag have a university degree?

How could we potentially ask this question in SPARQL? Turns out, most of them don't have educational data stored in Wikidata. You have to pick each little bit of information out of the Wikipedia pages.

The following query uses these:

Items: member of the German Bundestag (Q1939555)   , 2017 German federal election (Q15062956)   , doctorate (Q849697)

Properties: coordinate location (P625)   , instance of (P31)   , subclass of (P279)   , position held (P39)   , educated at (P69)   , elected in (P2715)   , academic degree (P512)  
```
# German members of the German Bundestag with a doctorate university degree
# 
SELECT ?politician ?politician_label ?institution ?institutionLabel ?degreeLabel
WHERE
{
    ?politician p:P39 # find items that have the position 
                [ps:P39 wd:Q1939555 ;      # of member of German Bundestag
                 pq:P2715 wd:Q15062956 ] . # and a qualifier "elected in the 2017 German federal election"
                                
    ?politician p:P69 ?educatedAt .      # has an "educated at" statement 
    ?educatedAt ps:P69 ?institution .    # get value of the "educated at" statement, i.e. the institution
    ?institution wdt:P625 ?geoloc .     # get the geolocation of the institution just for fun
  
    OPTIONAL {?educatedAt pq:P512 ?degree }        # get qualifier "academic degree" (P512) 
#    ?degree wdt:P31*/wdt:P279* wd:Q849697 . # filter for doctoral degrees
	 
 		 
	OPTIONAL {?politician rdfs:label ?politician_label filter (lang(?politician_label) = "en") .}
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}
ORDER BY ASC (?politician)
```

Try it!