---
# System prepended metadata

title: Exercise 2 - SPARQL

---

---
title: 'Exercise 2 - SPARQL'
disqus: hackmd
---
Muhammad Jihad Rinaldi - 1606883392

Sparql Exercise
===


Prefix
---
```
PREFIX dbr: <http://dbpedia.org/resource/>
PREFIX dbp: <http://dbpedia.org/property/>
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
```

Question 1
---
### Find all DBPedia SPARQL endpoint hosts graphs named.

Query:
```sparql
SELECT DISTINCT ?graph
WHERE {
  GRAPH ?graph { ?s ?p ?o }
}
ORDER BY ?graph
```

Results:


| graph |
| ----- |
| asEquivalent |
| b3sifp |
| b3sonto |
| dbprdf-label |
| facets |
| file:wikidata_to_schema_mappings.ttl |
| http://dbpedia.org |
| http://dbpedia.org/DAV/ |
| http://dbpedia.org/page_links |
| http://dbpedia.org/resource/classes# |
| http://dbpedia.org/schema/property_rules# |
| http://dbpedia.org/sparql |
| http://dbpedia.org/void/ |
| http://people.aifb.kit.edu/ath/#DBpedia_PageRank |
| http://pivot_test_data/campsites |
| http://pivot_test_data/ski_resorts |
| http://www.ontologyportal.org/SUMO# |
| http://www.ontologyportal.org/WordNet# |
| http://www.openlinksw.com/schemas/oplweb# |
| http://www.openlinksw.com/schemas/virtcxml# |


Question 2
---
### Find all classes whose IRI is in the http://dbpedia.org/ontology/ namespace such that these classes appear in at least one triple whose predicate is ```rdfs:subClassOf``` or ```owl:equivalentClass```.

Query:
```sparql
SELECT DISTINCT ?class
WHERE {
    FILTER(strstarts(str(?class), "http://dbpedia.org/ontology/"))
    {?class rdfs:subClassOf ?subclass}
    UNION
    {?class owl:equivalentClass ?equivalentclass}
}
ORDER BY ?class
```

Results:

| Class |
| ----- |
| http://dbpedia.org/ontology/Abbey |
| http://dbpedia.org/ontology/AcademicConference |
| http://dbpedia.org/ontology/AcademicJournal |
| http://dbpedia.org/ontology/AcademicSubject |
| http://dbpedia.org/ontology/Activity |
| http://dbpedia.org/ontology/Actor |
| http://dbpedia.org/ontology/AdministrativeRegion |
| http://dbpedia.org/ontology/AdultActor |
| http://dbpedia.org/ontology/Agent |
| http://dbpedia.org/ontology/Agglomeration |
| http://dbpedia.org/ontology/Aircraft |
| http://dbpedia.org/ontology/Airline |
| http://dbpedia.org/ontology/Airport |
| http://dbpedia.org/ontology/Album |
| http://dbpedia.org/ontology/Altitude |
| http://dbpedia.org/ontology/AmateurBoxer |
| http://dbpedia.org/ontology/Ambassador |
| http://dbpedia.org/ontology/AmericanFootballCoach |
| http://dbpedia.org/ontology/AmericanFootballLeague |
| http://dbpedia.org/ontology/AmericanFootballPlayer |


Question 3
---
### Find all direct and indirect subclasses of Place and its German label, if any. Return only those from the http://dbpedia.org/ontology/ namespace.

Query:
```sparql
SELECT DISTINCT ?class ?label
WHERE {
    FILTER(strstarts(str(?class), "http://dbpedia.org/ontology/"))
    {?class rdfs:subClassOf dbo:Place}
    UNION
    {?class owl:equivalentClass dbo:Place}
    ?class rdfs:label ?label
    FILTER(lang(?label) = 'de')
}
```

Results:

| class | label |
| ----- | ----- |
| http://dbpedia.org/ontology/ArchitecturalStructure | Bauwerk |
| http://dbpedia.org/ontology/CelestialBody | Himmelskörper |
| http://dbpedia.org/ontology/Cemetery | Friedhof |
| http://dbpedia.org/ontology/ConcentrationCamp | Konzentrationslager |
| http://dbpedia.org/ontology/CountrySeat | Landgut
| http://dbpedia.org/ontology/Garden | Garten
| http://dbpedia.org/ontology/HistoricPlace | historischer Ort |
| http://dbpedia.org/ontology/Mine | Mine (Bergwerk) |
| http://dbpedia.org/ontology/Monument | Denkmal |
| http://dbpedia.org/ontology/NaturalPlace | natürlicher Ort |
| http://dbpedia.org/ontology/Park | Park |
| http://dbpedia.org/ontology/PopulatedPlace | bewohnter Ort |
| http://dbpedia.org/ontology/ProtectedArea | Schutzgebiet |
| http://dbpedia.org/ontology/SiteOfSpecialScientificInterest | wissenschaftliche Interessenvertretung für Denkmalschutz |
| http://dbpedia.org/ontology/WineRegion | Weinregion |
| http://dbpedia.org/ontology/WorldHeritageSite | Weltkulturerbe |



Question 4
---
### Find all properties whose range is either ```dbo:City``` or ```dbo:Country```

Query:
```sparql
SELECT DISTINCT ?class
WHERE {
    {?class rdfs:range dbo:City}
    UNION
    {?class rdfs:range dbo:Country}
}
```

Results:

| class |
| ----- |
| http://dbpedia.org/ontology/administrativeHeadCity |
| http://dbpedia.org/ontology/beltwayCity |
| http://dbpedia.org/ontology/capital |
| http://dbpedia.org/ontology/city |
| http://dbpedia.org/ontology/currentCity |
| http://dbpedia.org/ontology/foundationPlace |
| http://dbpedia.org/ontology/locationCity |
| http://dbpedia.org/ontology/capitalCountry |
| http://dbpedia.org/ontology/country |
| http://dbpedia.org/ontology/countryOrigin |
| http://dbpedia.org/ontology/countryWithFirstAstronaut |
| http://dbpedia.org/ontology/countryWithFirstSatellite |
| http://dbpedia.org/ontology/countryWithFirstSatelliteLaunched |
| http://dbpedia.org/ontology/countryWithFirstSpaceflight |
| http://dbpedia.org/ontology/fastestDriverCountry |
| http://dbpedia.org/ontology/firstDriverCountry |
| http://dbpedia.org/ontology/governmentCountry |
| http://dbpedia.org/ontology/locationCountry |
| http://dbpedia.org/ontology/managementCountry |
| http://dbpedia.org/ontology/mouthCountry |


Question 5
---
### List the name of all Nobel prize winners who were born in East Asia region.

Query:
```sparql
SELECT ?winner ?country
WHERE {
    ?award a dbo:Award .
    ?award dct:subject dbc:Nobel_Prize .
    ?winner dbo:award ?award .
    ?winner dbo:birthPlace ?country .
    dbr:East_Asia dbo:isPartOf ?country .
}
ORDER BY ?country
```

Results:

| winner | country |
| ------ | ------- |
| http://dbpedia.org/resource/Ei-ichi_Negishi | http://dbpedia.org/resource/China |
| http://dbpedia.org/resource/Walter_Houser_Brattain | http://dbpedia.org/resource/China |
| http://dbpedia.org/resource/Toshihide_Maskawa | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Kenichi_Fukui | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Osamu_Shimomura | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Makoto_Kobayashi_(physicist) | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Shuji_Nakamura | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Takaaki_Kajita | http://dbpedia.org/resource/Japan |
| http://dbpedia.org/resource/Yuan_T.Lee | http://dbpedia.org/resource/Taiwan |
| http://dbpedia.org/resource/HidekiYukawa | http://dbpedia.org/resource/Tokyo |

Question 6
---
### List all cities in the United States named Springfield together with the state where they are located.

Query:
```sparql
SELECT DISTINCT ?city ?state
WHERE {
    ?city dbo:country dbr:United_States
    filter regex(?city, "Springfield")
    ?city dbo:type dbr:City
    ?city dbo:isPartOf ?state
    ?state <http://purl.org/linguistics/gold/hypernym> dbr:State .
}    
```

Results:


| city | state |
| ---- | ----- |
| http://dbpedia.org/resource/Springfield,Georgia | http://dbpedia.org/resource/Georgia(U.S.state) |
| http://dbpedia.org/resource/Springfield,Kentucky | http://dbpedia.org/resource/Kentucky |
| http://dbpedia.org/resource/Springfield,Michigan | http://dbpedia.org/resource/Michigan |
| http://dbpedia.org/resource/Springfield,Minnesota | http://dbpedia.org/resource/Minnesota |
| http://dbpedia.org/resource/Springfield,Nebraska | http://dbpedia.org/resource/Nebraska |
| http://dbpedia.org/resource/Springfield,Florida | http://dbpedia.org/resource/Florida |
| http://dbpedia.org/resource/Springfield,Tennessee | http://dbpedia.org/resource/Tennessee |
| http://dbpedia.org/resource/Springfield,Missouri | http://dbpedia.org/resource/Missouri |
| http://dbpedia.org/resource/Springfield,SouthDakota | http://dbpedia.org/resource/SouthDakota |
| http://dbpedia.org/resource/Springfield,Oregon | http://dbpedia.org/resource/Oregon |


Question 7
---
### Find all countries which has been dissolved and sort the result from the most short-lived to the most long-lived.

Query:
```sparql
SELECT ?country ?age
WHERE {
    ?country rdf:type dbo:PopulatedPlace .
    ?country rdf:type schema:Country .
    ?country dbo:dissolutionDate ?disdate .
    ?country dbo:foundingDate ?founddate .
    bind ( year(?disdate) - year(?founddate) as ?age ) .
}
ORDER BY ?age
```

Results:


| country | age |
| ------- | --- |
| http://dbpedia.org/resource/Commonwealth_of_England | -6 |
| http://dbpedia.org/resource/Tagalog_Republic | -5 |
| http://dbpedia.org/resource/Arab_Kingdom_of_Syria | 0 |
| http://dbpedia.org/resource/Talysh-Mughan_Autonomous_Republic | 0 |
| http://dbpedia.org/resource/Republic_of_Ostrów | 0 |
| http://dbpedia.org/resource/Parthenopean_Republic | 0 |
| http://dbpedia.org/resource/Republic_of_Zakopane | 0 |
| http://dbpedia.org/resource/Republic_of_Prekmurje | 0 |
| http://dbpedia.org/resource/Juliana_Republic | 0 |
| http://dbpedia.org/resource/Republic_of_Galicia | 0 |
| http://dbpedia.org/resource/Republic_of_Sonora | 0 |
| http://dbpedia.org/resource/Lithuanian–Byelorussian_Soviet_Socialist_Republic | 0 |
| http://dbpedia.org/resource/Republic_of_West_Florida | 0 |
| http://dbpedia.org/resource/Republic_of_the_Rio_Grande | 0 |
| http://dbpedia.org/resource/SAO_Krajina | 0 |
| http://dbpedia.org/resource/Tiberina_Republic | 0 |
| http://dbpedia.org/resource/Banat_Republic | 0 |
| http://dbpedia.org/resource/Reuss-Ebersdorf | 0 |
| http://dbpedia.org/resource/Albona_Republic | 0 |
| http://dbpedia.org/resource/Centrocaspian_Dictatorship | 0 |


Question 8
---
### Find the English name of all landlocked European countries whose population exceeds 5 million.

Query:
```sparql
SELECT ?name
WHERE {
    ?country dct:subject dbc:Countries_in_Europe .
    ?country dct:subject dbc:Landlocked_countries .
    ?country dbo:populationTotal ?population .
    ?country rdfs:label ?name . 
    FILTER( lang(?name) = 'en' && ?population > 5000000)
}
```

Results:

| name |
| ---- |
| Azerbaijan |
| Belarus |
| Czech Republic |
| Hungary |
| Kazakhstan |
| Slovakia |
| Switzerland |
| Serbia |
| Austria |


Question 9
---
### List all the titles, directors, and grossing amount (if any) of movies starring five oldest actors/actresses who co-starred with Tom Cruise. Sort the results from the highest grossing amount to the lowest one.

Query:
```sparql
SELECT DISTINCT ?title ?director (xsd:decimal(?grossing) as ?gross)
WHERE {
    ?movie dbo:starring dbr:Tom_Cruise , ?costar ;
           rdfs:label ?title ;
           dbo:director ?dir .
    
    {SELECT ?costar
     WHERE{
         ?movie dbo:starring dbr:Tom_Cruise , ?costar .
         ?costar dbo:birthYear ?birthyear .
         filter(?costar != dbr:Tom_Cruise)
         }
     ORDER BY ?birthyear
     LIMIT 5
    }
    ?dir rdfs:label ?director .
    OPTIONAL { ?movie dbo:gross ?grossing } .
    filter(lang(?title) = 'en' && (lang(?director) = 'en'))
}
ORDER BY DESC(xsd:decimal(?gross))
```

Results:


| title | director | gross |
| ------| -------- | ----- |
| Minority Report (film) | Steven Spielberg | 358400000 |
| The Firm (1993 film) | Sydney Pollack | 270248367 |
| The Color of Money | Martin Scorsese | 52200000 |
| Magnolia (film) | Paul Thomas Anderson | 48500000 |




Question 10
---
### List all actors/actresses who co-starred in at least two different movies with Meryl Streep, and also list the movie titles where they co-starred with Meryl Streep where the titles are returned as one literal values (separated by ’ ; ') 

Query:

```sparql
SELECT ?name (group_concat(DISTINCT ?title;separator=";") as ?films)
WHERE {
    ?movie dbo:starring dbr:Meryl_Streep, ?costar ;
           rdfs:label ?title .
    ?costar rdfs:label ?name .
    FILTER(?costar != dbr:Meryl_Streep && (lang(?title) = 'en') && (lang(?name) = 'en')) 
}
GROUP BY ?name ?costar
HAVING (count(?costar) > 1)
```

Results:

| name | films |
| ---- | ----- |
| David Warner (actor) | Holocaust (miniseries);The French Lieutenant's Woman (film) |
| Jack Nicholson | Heartburn (film);Ironweed (film) |
| Nicolas Cage | Adaptation (film);The Ant Bully (film) |
| Emily Blunt | Into the Woods (film);The Devil Wears Prada (film) |
| Glenn Close | Evening (film);Rabbit Ears Productions;The House of the Spirits (film) |
| Diane Keaton | Manhattan (film);Marvin's Room (film) |
| Julia Roberts | August: Osage County (film);The Ant Bully (film) |
| John C. Reilly | A Prairie Home Companion (film);The River Wild |
| Sam Neill | Evil Angels (film);Plenty (film) |
| Vanessa Redgrave | Evening (film);The House of the Spirits (film) |
| Chris Cooper | Adaptation (film);August: Osage County (film) |
| Robert Redford | Lions for Lambs;Out of Africa (film) |
| Christine Baranski | Into the Woods (film);Mamma Mia! (film) |
| Jeremy Irons | The French Lieutenant's Woman (film);The House of the Spirits (film) |
| Patrick Wilson (American actor) | Angels in America (miniseries);Evening (film) |
| Michael Moriarty | Holocaust (miniseries);The Deadliest Season |
| Tracey Ullman | Into the Woods (film);Plenty (film) |
| Kevin Kline | A Prairie Home Companion (film);Ricki and the Flash;Sophie's Choice (film) |
| Denzel Washington | Rabbit Ears Productions;The Manchurian Candidate (2004 film) |
| Paul Giamatti | The Ant Bully (film);The Roosevelts (film) |
