source: main/trunk/model-sites-dev/eurovision-lod/collect/eurovision/prepare/working-sparql-queries.txt@ 34945

SELECT *
WHERE {
 dbr:Belgium_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
  dbr:Belgium_in_the_Eurovision_Song_Contest_1956 dbp:entrant/rdfs:label ?entrant_lang_label.
  }



# get the language labels for the artist

SELECT ?entrant ?entrant_lang_label WHERE {
  dbr:Belgium_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
    {
         SELECT ?entrant ?entrant_lang_label WHERE {
            ?entrant rdfs:label ?entrant_lang_label
             }
               }
               }


# Match the song and the artist, in cases where there are multiple entrants/songs for a country (e.g. 1956(

SELECT ?entrant ?song WHERE {
  dbr:Belgium_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
   dbr:Belgium_in_the_Eurovision_Song_Contest_1956 dbp:song ?song.
    ?song  dbp:artist ?entrant.


# More interesting example where 2 entries from France both have URI for artist and song
# matching goes correctly

SELECT ?entrant ?song WHERE {
  dbr:France_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
    dbr:France_in_the_Eurovision_Song_Contest_1956 dbp:song ?song.
     ?song  dbp:artist ?entrant.

}






SELECT ?countries_in_esc_by_year ?country_in_year ?year ?country ?entrant ?song
WHERE {
    ?countries_in_esc_by_year skos:broader dbc:Countries_in_the_Eurovision_Song_Contest_by_year.

    ?country_in_year dct:subject ?countries_in_esc_by_year.
        bind( REPLACE(str(?country_in_year), ".*(\\d{4})", "$1") as ?year).
        FILTER ( xsd:integer(?year) < 2020).

    ?country_in_year dbp:country ?country.
        ?country_in_year dbp:entrant ?entrant.
        ?country_in_year dbp:song ?song
            FILTER (isLITERAL(?entrant) || isLITERAL(?song))
            # ?song rdfs:label ?song_label
                #   FILTER (lang(?song_label) = 'en').


}
ORDER BY DESC(?countries_in_esc_by_year)


####



SELECT ?countries_in_esc_by_year ?country_in_year ?year ?country ?entrant ?song_uri
WHERE {
    ?countries_in_esc_by_year skos:broader dbc:Countries_in_the_Eurovision_Song_Contest_by_year.

    ?country_in_year dct:subject ?countries_in_esc_by_year.
        bind( REPLACE(str(?country_in_year), ".*(\\d{4})", "$1") as ?year).
        FILTER ( xsd:integer(?year) < 2020).

    ?country_in_year dbp:country ?country.
        ?country_in_year dbp:entrant ?entrant.
        ?country_in_year dbp:song ?song
            FILTER (isLITERAL(?song))
            BIND(?song as ?song_uri)
            #    BIND (IF(isLITERAL(?song),URI(CONCAT("http://dbpedia.org/resource/", ?song)),?song) AS ?song_uri)

}
ORDER BY DESC(?countries_in_esc_by_year)

####


SELECT ?countries_in_esc_by_year ?country_in_year ?year ?country ?entrant ?song_uri
WHERE {
    ?countries_in_esc_by_year skos:broader dbc:Countries_in_the_Eurovision_Song_Contest_by_year.

    ?country_in_year dct:subject ?countries_in_esc_by_year.
        bind( REPLACE(str(?country_in_year), ".*(\\d{4})", "$1") as ?year).
        FILTER ( xsd:integer(?year) < 2020).

    ?country_in_year dbp:country ?country.
        ?country_in_year dbp:entrant ?entrant.
        ?country_in_year dbp:song ?song
            FILTER (isLITERAL(?song))
        #    BIND(?song as ?song_uri)

    BIND (IF(isLITERAL(?song),
          REPLACE(?song,"^\"Amami se vuoi$","Amami se vuoi"), ?song) AS ?song_curated)

    BIND (IF(isLITERAL(?song_curated),URI(CONCAT("http://dbpedia.org/resource/", ?song_curated)),?song_curated) AS ?song_uri)

}
ORDER BY DESC(?countries_in_esc_by_year)

##======



SELECT ?countries_in_esc_by_year ?country_in_year ?year ?country ?entrant ?song_uri
WHERE {
    ?countries_in_esc_by_year skos:broader dbc:Countries_in_the_Eurovision_Song_Contest_by_year.

    ?country_in_year dct:subject ?countries_in_esc_by_year.
        bind( REPLACE(str(?country_in_year), ".*(\\d{4})", "$1") as ?year).
        FILTER ( xsd:integer(?year) < 2020).

    ?country_in_year dbp:country ?country.
        ?country_in_year dbp:entrant ?entrant.
        ?country_in_year dbp:song ?song
            FILTER (isLITERAL(?song))
            BIND(?song as ?song_uri)

    BIND (IF(isLITERAL(?song),
          REPLACE(?song,"^\"Amami se vuoi$","Amami se vuoi"), ?song) AS ?song_curated)

#    BIND (IF(isLITERAL(?song_curated),URI(CONCAT("http://dbpedia.org/resource/", ?song_curated)),?song_curated) AS ?song_uri)

}

#====



INSERT DATA
{
  <http://example/book1> dc:title "A new book" ;
                           dc:creator "A.N.Other" .
               }





### Trying to get federated search going
# but generated error:

# Virtuoso 42000 Error SQ070:SECURITY: Must have SELECT privileges on view DB.DBA.SPARQL_SINV_2 for group ID 112 (SPARQL), user ID 112 (SPARQL)
#
# SPARQL query:
# define sql:big-data-const 0
#output-format:text/html
# define sql:signal-unconnected-variables 1
# define sql:signal-void-variables 1
# define input:default-graph-uri <http://dbpedia.org>
# SELECT ?entrant ?song WHERE {
#  dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
#  SERVICE <https://bedrock.resnet.cms.waikato.ac.nz/sparql/greenstone/sparql> {
#    dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:song ?song.
#   }
#
#}


SELECT ?entrant ?song WHERE {
  dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
    SERVICE <https://bedrock.resnet.cms.waikato.ac.nz/sparql/greenstone/sparql> {
        dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:song ?song.
       }
       }

## The following worked on local sparql endpoitn

prefix dbr:    <http://dbpedia.org/resource/>
prefix dbp:    <http://dbpedia.org/property/>

SELECT ?entrant ?song WHERE {
  SERVICE <https://dbpedia.demo.openlinksw.com/sparql> {
      dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:entrant ?entrant.
          ?song  dbp:artist ?entrant
        }
          dbr:Italy_in_the_Eurovision_Song_Contest_1956 dbp:song ?song
          }