# Exercise 2 - SPARQL

### 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#>
PREFIX schema: <http://schema.org/>

### 1. The DBPedia SPARQL endpoint hosts more than one named graphs. Find all those named graphs.
Dengan menggunakan query berikut:

SELECT DISTINCT ?g 
FROM dbo:
WHERE {
  GRAPH ?g { ?s ?p ?o }
} LIMIT 20

Saya mendapatkan hasil sebagai berikut:
| g |
| - |
| http://www.openlinksw.com/schemas/virtrdf# |
| http://www.w3.org/ns/ldp# |
| http://dbpedia.org/sparql |
| http://dbpedia.org/DAV/ |
| http://www.w3.org/2002/07/owl# |
| file:wikidata_to_schema_mappings.ttl |
| http://dbpedia.org/schema/property_rules# |
| dbprdf-label |
| http://dbpedia.org/resource/classes# |
| http://www.ontologyportal.org/SUMO# |
| http://www.ontologyportal.org/WordNet# |
| b3sonto |
| b3sifp |
| urn:rules.skos |
| http://www.openlinksw.com/schemas/oplweb# |
| virtrdf-label |
| facets |
| urn:virtuoso:val:acl:schema |
| http://www.openlinksw.com/virtpivot/icons |
| http://www.openlinksw.com/schemas/virtpivot |
| virtpivot-rules |

### 2. DBpedia contains RDFS triples...(too long)

Dengan query berikut:

SELECT ?class ?subclass ?equivalentclass
FROM dbo:
WHERE {
    {?subclass rdfs:subClassOf ?class .}
    UNION
    {?class owl:equivalentClass ?equivalentclass .}
} LIMIT 20

Saya mendapatkan hasil sebagai berikut:
| class |
| ----- |
| http://dbpedia.org/ontology/Activity |
| http://dbpedia.org/ontology/Name |
| http://dbpedia.org/ontology/Place |
| http://dbpedia.org/ontology/Language |
| http://dbpedia.org/ontology/Agent |
| http://dbpedia.org/ontology/Altitude |
| http://dbpedia.org/ontology/AnatomicalStructure |
| http://dbpedia.org/ontology/Area |
| http://dbpedia.org/ontology/Award |
| http://dbpedia.org/ontology/Biomolecule |
| http://dbpedia.org/ontology/Blazon |
| http://dbpedia.org/ontology/ChartsPlacements |
| http://dbpedia.org/ontology/ChemicalSubstance |
| http://dbpedia.org/ontology/Colour |
| http://dbpedia.org/ontology/Currency |
| http://dbpedia.org/ontology/Demographics |
| http://dbpedia.org/ontology/Depth |
| http://dbpedia.org/ontology/Device |
| http://dbpedia.org/ontology/Diploma |
| http://dbpedia.org/ontology/Disease |

### 3. Among the answer in question (2) above, you should be able to find dbo:Place... (too long)

Dari query berikut:

SELECT ?class ?label
FROM dbo:
WHERE {
    
  {?class rdfs:subClassOf* dbo:Place }
  UNION
  {?class owl:equivalentClass* dbo:Place } .
  ?class rdfs:label ?label . 
  filter(lang(?label) = 'de')
  

} LIMIT 20

Saya mendapatkan hasil query sebagai berikut:
| class | label |
| ----- | ----- |
| http://dbpedia.org/ontology/Place | Ort |
| http://dbpedia.org/ontology/Department | Distrikt |
| http://dbpedia.org/ontology/Abbey | Abtei |
| http://dbpedia.org/ontology/AdministrativeRegion | Verwaltungsregion |
| http://dbpedia.org/ontology/Agglomeration | Ballungsgebiet |
| http://dbpedia.org/ontology/Airport | Flughafen |
| http://dbpedia.org/ontology/AmusementParkAttraction | Vergnügungsparkattraktion |
| http://dbpedia.org/ontology/Archipelago | Archipel |
| http://dbpedia.org/ontology/ArchitecturalStructure | Bauwerk |
| http://dbpedia.org/ontology/Arena | stadion |
| http://dbpedia.org/ontology/Arrondissement | arrondissement |
| http://dbpedia.org/ontology/ArtificialSatellite | künstlicher Satellit |
| http://dbpedia.org/ontology/Asteroid | Asteroid |
| http://dbpedia.org/ontology/Atoll | Atoll |
| http://dbpedia.org/ontology/Bay | Bucht |
| http://dbpedia.org/ontology/Beach | Strand |
| http://dbpedia.org/ontology/BodyOfWater | Gewässer |
| http://dbpedia.org/ontology/Bridge | Brücke |
| http://dbpedia.org/ontology/BrownDwarf | Brauner Zwerg |
| http://dbpedia.org/ontology/Building | Gebäude |

### 4. Find all properties whose range is either dbo:City or dbo:Country

Query:

SELECT ?prop
WHERE {
    
    { ?prop rdf:type rdf:Property .
    ?prop rdfs:range dbo:City . }
    UNION
    { ?prop rdfs:range dbo:Country . }

} LIMIT 20

Hasil query:

| prop |
| ---- |
| 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 |

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

Query:

SELECT ?person_name
WHERE {
    ?x rdf:type dbo:Award .
    ?x ?property dbc:Nobel_Prize .
    ?person dbo:award ?x .
    ?person dbo:birthPlace ?birthplace . 
    dbr:East_Asia dbo:isPartOf ?birthplace .
    ?person rdfs:label ?person_name .
    filter(lang(?person_name) = 'en')
} LIMIT 20

Hasil query:
| person_name |
| ----------- |
| Walter Houser Brattain |
| Osamu Shimomura |
| Kenichi Fukui |
| Makoto Kobayashi (physicist) |
| Toshihide Maskawa |
| Yuan T. Lee |
| Hideki Yukawa |
| Takaaki Kajita |
| Shuji Nakamura |
| Ei-ichi Negishi |

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

Query:

SELECT DISTINCT ?city ?state ?city_name
WHERE {
  ?city dbo:country dbr:United_States .
  ?city rdf:type dbo:City .
  ?city dbo:isPartOf ?county .
  ?county dbo:state ?state .
  ?city rdfs:label ?city_name .
  filter (regex(?city_name, "^Springfield")) .
  filter (lang(?city_name) = 'en') .
} LIMIT 20

Hasil query:
| city | state | city_name |
| ---- | ----- | --------- |
| http://dbpedia.org/resource/Springfield,_Florida | http://dbpedia.org/resource/Florida | Springfield, Florida |
| http://dbpedia.org/resource/Springfield,_Minnesota | http://dbpedia.org/resource/Minnesota | Springfield, Minnesota |
| http://dbpedia.org/resource/Springfield,_Michigan | http://dbpedia.org/resource/Michigan | Springfield, Michigan |
| http://dbpedia.org/resource/Springfield,_Ohio | http://dbpedia.org/resource/Ohio | Springfield, Ohio |
| http://dbpedia.org/resource/Springfield,_Georgia | http://dbpedia.org/resource/Georgia_(U.S._state) | Springfield, Georgia |
| http://dbpedia.org/resource/Springfield,_Massachusetts | http://dbpedia.org/resource/Massachusetts | Springfield, Massachusetts |
| http://dbpedia.org/resource/Springfield,_Nebraska | http://dbpedia.org/resource/Nebraska | Springfield, Nebraska |
| http://dbpedia.org/resource/Springfield,_Kentucky | http://dbpedia.org/resource/Kentucky | Springfield, Kentucky |
| http://dbpedia.org/resource/Springfield,_South_Dakota | http://dbpedia.org/resource/South_Dakota | Springfield, South Dakota |
| http://dbpedia.org/resource/Springfield,_Tennessee | http://dbpedia.org/resource/Tennessee | Springfield, Tennessee |
| http://dbpedia.org/resource/Springfield,_Missouri | http://dbpedia.org/resource/Missouri | Springfield, Missouri |
| http://dbpedia.org/resource/Springfield,_Oregon | http://dbpedia.org/resource/Oregon | Springfield, Oregon |

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

Query:

SELECT DISTINCT ?dissoluted_country ?year
WHERE {
  ?dissoluted_country dbo:dissolutionDate ?end_date .
  ?dissoluted_country dbo:foundingDate ?start_date .
  filter(?end_date > ?start_date)
  bind( year(?end_date) - year(?start_date) as ?year) .
}
ORDER BY ASC (?year)
LIMIT 20

Hasil Query:

| dissoluted_country | year |
| ------------------ | ---- |
| 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/Albona_Republic | 0 |
| http://dbpedia.org/resource/Centrocaspian_Dictatorship | 0 |
| http://dbpedia.org/resource/Republic_of_Lower_Canada | 0 |
| http://dbpedia.org/resource/Republic_of_Madawaska | 0 |
| http://dbpedia.org/resource/Republic_of_Mahabad | 0 |

> Notes: Query hanya melakukan pengurangan year, sehingga hasinya nol semua. Lalu ada juga resource yang memiliki lebih dari satu date, sehingga hasilnya menjadi beragam.

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

Query:

SELECT ?country_name ?population
WHERE {
  ?country a dbo:Country .
  ?country dct:subject dbc:Countries_in_Europe .
  ?country dct:subject dbc:Landlocked_countries .
  ?country dbo:populationTotal ?population .
  ?country rdfs:label ?country_name .
  filter(lang(?country_name) = 'en') .
  filter(?population > 5000000) .
} 
LIMIT 20

Hasil query:
| country_name | population |
| ------------ | ---------- |
| Azerbaijan | 9754830 |
| Belarus | 9498700 |
| Czech Republic | 10553443 |
| Hungary | 9855571 |
| Kazakhstan | 17693500 |
| Slovakia | 5426252 |
| Switzerland | 8341000 |
| Serbia | 7041599 |
| Austria | 8725931 |

### 9. List all the titles, directors, and grossing amount (if any) ... (too long)

Query:

SELECT DISTINCT ?title ?director ?gross_amount
WHERE {
  ?title a dbo:Film .
  ?title dbo:starring dbr:Tom_Cruise .
  ?title dbo:starring ?actor .
  ?title dbo:director ?director .
  
  {
    SELECT DISTINCT ?actor
    WHERE {
      ?title2 dbo:starring dbr:Tom_Cruise .
      ?title2 dbo:starring ?actor .
      ?actor dbo:birthYear ?year .
      FILTER(?actor != dbr:Tom_Cruise) .
    }
    ORDER BY ASC (year(xsd:date(?year)))
    LIMIT 5
  }
  OPTIONAL { ?title dbo:gross ?gross_amount .}
}
ORDER BY DESC ( xsd:decimal(?gross_amount) )
LIMIT 20

> Asumsi movie yang diambil adalah movie yang juga dibintangi oleh Tom Cruise

Hasil Query:
| title | director | grossing_amount |
| ----- | -------- | --------------- |
| http://dbpedia.org/resource/Minority_Report_(film) | http://dbpedia.org/resource/Steven_Spielberg | 358400000 |
| http://dbpedia.org/resource/The_Firm_(1993_film) | http://dbpedia.org/resource/Sydney_Pollack | 270248367 |
| http://dbpedia.org/resource/The_Color_of_Money | http://dbpedia.org/resource/Martin_Scorsese | 52200000 |
| http://dbpedia.org/resource/Magnolia_(film) | http://dbpedia.org/resource/Paul_Thomas_Anderson | 48500000 |

### 10. List all actors/actresses who co-starred in at least two ... (too long)

Query:

SELECT DISTINCT ?actor_name (group_concat(distinct ?movies_name; separator="; ") as ?movies)
WHERE {
  ?movies a dbo:Film .
  ?movies dbo:starring dbr:Meryl_Streep .
  ?movies dbo:starring ?actor .
  ?actor rdfs:label ?actor_name .
  ?movies rdfs:label ?movies_name
  FILTER(lang(?actor_name) = 'en') .
  FILTER(lang(?movies_name) = 'en') .
  FILTER(?actor != dbr:Meryl_Streep) .
}
GROUP BY ?actor_name
HAVING(COUNT(?movies) > 1)
LIMIT 20

Hasil Query:
| actor_name | movies |
| ---------- | ------ |
| Nicolas Cage | Adaptation (film); The Ant Bully (film) |
| Jeremy Irons | The French Lieutenant's Woman (film); The House of the Spirits (film) |
| Aidan Quinn | Dark Matter (film); Music of the Heart |
| Julia Roberts | August: Osage County (film); The Ant Bully (film) |
| Tracey Ullman | Into the Woods (film); Plenty (film) |
| Emily Blunt | Into the Woods (film); The Devil Wears Prada (film) |
| Vanessa Redgrave | Evening (film); The House of the Spirits (film) |
| Sam Neill | Evil Angels (film); Plenty (film) |
| Jack Nicholson | Heartburn (film); Ironweed (film) |
| Diane Keaton | Manhattan (film); Marvin's Room (film) |
| Paul Giamatti | The Ant Bully (film); The Roosevelts (film) |
| John C. Reilly | A Prairie Home Companion (film); The River Wild |
| Chris Cooper | Adaptation (film); August: Osage County (film) |
| Robert De Niro | Falling in Love (1984 film); Marvin's Room (film); The Deer Hunter |
| Christine Baranski | Into the Woods (film); Mamma Mia! (film) |
| Glenn Close | Evening (film); The House of the Spirits (film) |
| Robert Redford | Lions for Lambs; Out of Africa (film) |
| Kevin Kline | A Prairie Home Companion (film); Ricki and the Flash; Sophie's Choice (film) |