SVN / public / code / lecture-07 / README.md

Revision 1564
Date2025-12-02T16:07:39+01:00
Committerhb1003
Download
Text

SPARQL query for table acted_in

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX schema: <http://schema.org/>
PREFIX wikibase: <http://wikiba.se/ontology#>
SELECT ?person ?film WHERE {
  ?person_id wdt:P31 wd:Q5 .
  ?film_id wdt:P31/wdt:P279* wd:Q11424 .
  ?film_id wdt:P161 ?person_id .
  ?person_id rdfs:label ?person .
  ?film_id rdfs:label ?film .
  ?person_id ^schema:about/wikibase:sitelinks ?person_sitelinks .
  ?film_id ^schema:about/wikibase:sitelinks ?film_sitelinks . 
  FILTER (LANG(?person) = "en") .
  FILTER (LANG(?film) = "en") .
  FILTER (?person_sitelinks > 10) .
  FILTER (?film_sitelinks > 10) .
}
ORDER BY DESC(?film_sitelinks) DESC(?person_sitelinks)

curl -s https://qlever.dev/api/wikidata-drau -H "Accept: text/tab-separated-values" -H "Content-type: application/sparql-query" --data "PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX wd: <http://www.wikidata.org/entity/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX schema: <http://schema.org/> PREFIX wikibase: <http://wikiba.se/ontology#> SELECT ?person ?film WHERE { ?person_id wdt:P31 wd:Q5 . ?film_id wdt:P31/wdt:P279* wd:Q11424 . ?film_id wdt:P161 ?person_id . ?person_id rdfs:label ?person . ?film_id rdfs:label ?film . ?person_id ^schema:about/wikibase:sitelinks ?person_sitelinks . ?film_id ^schema:about/wikibase:sitelinks ?film_sitelinks . FILTER (LANG(?person) = \"en\") . FILTER (LANG(?film) = \"en\") FILTER (?film_sitelinks > 10 && ?person_sitelinks > 10) } ORDER BY DESC(?film_sitelinks) DESC(?person_sitelinks)" | sed '1s/\?//g; s/"//g; s/@en//g' > acted_in.tsv

SPARQL query for table married_to

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX schema: <http://schema.org/>
PREFIX wikibase: <http://wikiba.se/ontology#>
SELECT ?person1 ?person2 WHERE {
  ?person1_id wdt:P31 wd:Q5 .
  ?person2_id wdt:P31 wd:Q5 .
  ?person1_id wdt:P26 ?person2_id .
  ?person1_id rdfs:label ?person1 .
  ?person2_id rdfs:label ?person2 .
  ?person1_id ^schema:about/wikibase:sitelinks ?person1_sitelinks .
  ?person2_id ^schema:about/wikibase:sitelinks ?person2_sitelinks . 
  FILTER (LANG(?person1) = "en") .
  FILTER (LANG(?person2) = "en") .
  FILTER (?person1_sitelinks > 10) .
  FILTER (?person2_sitelinks > 10) .
}
ORDER BY DESC(?person1_sitelinks) DESC(?person2_sitelinks)

curl -s https://qlever.dev/api/wikidata-drau -H "Accept: text/tab-separated-values" -H "Content-type: application/sparql-query" --data "PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX wd: <http://www.wikidata.org/entity/> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX schema: <http://schema.org/> PREFIX wikibase: <http://wikiba.se/ontology#> SELECT ?person1 ?person2 WHERE { ?person1_id wdt:P31 wd:Q5 . ?person2_id wdt:P31 wd:Q5 . ?person1_id wdt:P26 ?person2_id . ?person1_id rdfs:label ?person1 . ?person2_id rdfs:label ?person2 . ?person1_id ^schema:about/wikibase:sitelinks ?person1_sitelinks . ?person2_id ^schema:about/wikibase:sitelinks ?person2_sitelinks . FILTER (LANG(?person1) = \"en\") . FILTER (LANG(?person2) = \"en\") . FILTER (?person1_sitelinks > 10) . FILTER (?person2_sitelinks > 10) } ORDER BY DESC(?person1_sitelinks) DESC(?person2_sitelinks)" | sed '1s/\?//g; s/"//g; s/@en//g' > married_to.tsv

SQLite3 query for "all married couples who acted together in the same film"

.separator \t
.mode column
.import acted_in.tsv acted_in
.import married_to.tsv married_to
SELECT a1.person, a2.person, a1.film
FROM acted_in AS a1, acted_in AS a2, married_to AS m
WHERE a1.film = a2.film
AND a1.person = m.person1
AND a2.person = m.person2
AND a1.person < a2.person;

sqlite3 :memory: ".separator \t" ".mode column" ".import acted_in.tsv acted_in" ".import married_to.tsv married_to" "SELECT a1.person, a2.person, a1.film FROM acted_in AS a1, acted_in AS a2, married_to AS m WHERE a1.film = a2.film AND a1.person = m.person1 AND a2.person = m.person2 AND a1.person < a2.person;" | less