===========================
XQuery Queries over Movies
===========================
-------------------------------------------------------------------------
1. Return an element "movie_titles" that contains a list of
alphabetically sorted titles of all movies in the document
let $doc := doc("movies.xml")
return
element movie_titles
{
for $t in $doc//title
order by $t
return $t
}
-------------------------------------------------------------------------
2. Modify the code so that each movie title has an attribute "number",
indicating the position of the title on the list.
let $doc := doc("movies.xml")
return
element movie_titles
{
let $ts := (for $t in $doc//title
order by $t
return $t)
for $t at $pos in $ts
return
element title
{
attribute number {$pos},
string($t)
}
}
-------------------------------------------------------------------------
3. Return an element “movies” with a list of movie elements,
containing in turn attributes title and year, ordered by year in
descending order. Write two versions, one with static and one with
dynamic attribute and element constructors.
let $doc := doc("movies.xml")
return
{
for $m in $doc//movie
order by $m/year descending
return
}
let $doc := doc("movies.xml")
return
element movies
{
for $m in $doc//movie
order by $m/year descending
return
element movie
{
attribute title {$m/title},
attribute year {$m/year}
}
}
-------------------------------------------------------------------------
4. Restructure the document so that
- movies appear according to their year, with the most recent years first,
and, within the same year, according to their title
- countries appear in alphabetical order
- for each director, the first name is given before the last name
- actors of a movie appear according to their last name and, for actors
with the same last name, according to their first name
Everything else should remain as before.
let $doc := doc("movies.xml")
let $ms := $doc/movies
return
element movies{
for $m in $ms/movie
order by $m/@year, $m/title
return
element movie{
$m/title,
$m/year,
for $c in $m/country
order by $c
return $c,
$m/genre,
$m/summary,
$m/director,
for $a in $m/actor
order by $a/last_name, $a/first_name
return $a
}
}
-------------------------------------------------------------------------
5. Restructure the document so that movies are ordered
according to their genre. That is:
- Below the node movies, there should be several genre elements,
one for each of the genres occurring in the document, with an
attribute name indicating the name of the genre.
- Below each genre element, there should be the movies of that
genre. Each movie should be structured as for the previous
problem, except that there should not be a genre element in the
movie.
let $doc := doc("movies.xml")
let $ms := $doc/movies
return
element movies{
let $gs := distinct-values($doc//genre)
for $g in $gs
return
element genre{
attribute name {$g},
for $m in $ms/movie[genre=$g]
order by $m/@year, $m/title
return
element movie{
$m/title,
$m/year,
for $c in $m/country
order by $c
return $c,
$m/summary,
$m/director,
for $a in $m/actor
order by $a/last_name, $a/first_name
return $a
}
}
}
-------------------------------------------------------------------------
6. Output movies (title and year) and within each movie those actors whose
role occurs in the summary of the movie.
let $doc := doc("movies.xml")
for $m in $doc//movie
return
element movie {
attribute title {$m/title},
attribute year {$m/year},
$m/actor[contains($m/summary, role)]
}
-------------------------------------------------------------------------
7. Return an element "actors" that contains for each actor occurring
in the document one element actor with attributes first_name,
last_name, yob (= year of birth) and an element "film" with
attributes title and year and element role, where the films are
ordered according to year.
Note that for each actor in the input document, there should be
exactly one actor in the output.
Make sure your code works also if you have to actors with the same
last name, say "Fred Smith" and "John Smith", that is, "Fred Smith"
should appear before "John Smith".
let $doc := doc("movies.xml")
let $as := $doc//actor
let $ms := $doc//movie
return
element actors
{
for $ln in distinct-values($as/last_name)
for $fn in distinct-values($as[last_name=$ln]/first_name)
order by $ln, $fn
return
element actor
{attribute last_name {$ln},
attribute first_name {$fn},
attribute yob {$as[last_name = $ln and first_name = $fn][1]/birth_date},
for $m in $ms[./actor[last_name = $ln and first_name = $fn]]
order by $m/year
return
element film
{attribute title {$m/title},
attribute year {$m/year},
$m/actor[last_name = $ln and first_name = $fn]/role
}
}
}
-------------------------------------------------------------------------
8. Rewrite the code produced under 7. by introducing the following
two functions:
declare function local:create-actor($ln as xs:string,
$fn as xs:string,
$ms as element()*)
as element()
- which takes as input two strings, the last name and the first name
of the actor, and a list of movies, returning the actor element for
the actor with that name.
declare function local:create-film-with-role($m as element(),
$ln as xs:string,
$fn as xs:string)
as element()
- which takes a movie and two strings, the last name and the first
name of the actor, returning a film element and with the role
played by the actor.
declare function local:create-actor($ln as xs:string,
$fn as xs:string,
$ms as element()*)
as element()
{element actor
{attribute last_name {$ln},
attribute first_name {$fn},
attribute yob {$ms/actor[last_name = $ln and first_name = $fn][1]/birth_date},
for $m in $ms[./actor[last_name = $ln and first_name = $fn]]
order by $m/year
return
local:create-film-with-role($m, $ln, $fn)
}
};
declare function local:create-film-with-role($m as element(),
$ln as xs:string,
$fn as xs:string)
as element()
{element film
{attribute title {$m/title},
attribute year {$m/year},
$m/actor[last_name = $ln and first_name = $fn]/role
}
};
let $doc := doc("movies.xml")
let $ms := $doc//movie
return
element actors
{for $ln in distinct-values($ms/actor/last_name)
for $fn in distinct-values($ms/actor[last_name=$ln]/first_name)
order by $ln, $fn
return
local:create-actor($ln, $fn, $ms)
}
-------------------------------------------------------------------------
9. Modify the code so that every actor has an attribute id and the values
of id are integers running from 1 to the total number of actors.
Hint: Introduce a function
declare function local:sorted-actor-names ($as as element()*)
as element()*
that takes a sequence of elements as input and outputs a sorted
list of elements of the form
.
Use this sequence to produce a numbered sequence with the functions you
have created before.
declare option saxon:output "indent=yes";
declare function local:create-actor($id as xs:integer,
$ln as attribute(),
$fn as attribute(),
$ms as element()*)
as element()
{element actor
{attribute id {$id},
$ln,
$fn,
attribute yob {($ms/actor[last_name = $ln and first_name = $fn])[1]/birth_date},
for $m in $ms[./actor[last_name = $ln and first_name = $fn]]
order by $m/year
return
local:create-film-with-role($m, $ln, $fn)
}
};
declare function local:create-film-with-role($m as element(),
$ln as xs:string,
$fn as xs:string)
as element()
{element film
{attribute title {$m/title},
attribute year {$m/year},
$m/actor[last_name = $ln and first_name = $fn]/role
}
};
declare function local:sorted-actor-names ($as as element()*)
as element()*
{
for $ln in distinct-values($as/last_name)
for $fn in distinct-values($as[last_name=$ln]/first_name)
order by $ln, $fn
return
element actor_name
{attribute last_name {$ln},
attribute first_name {$fn}
}
};
declare function local:sorted-actors-with-id($ms as element()*)
as element()
{element actors{
let $as := $ms//actor (: all actors, including duplicates :)
for $a at $pos in local:sorted-actor-names($as)
return
local:create-actor($pos, $a/@last_name, $a/@first_name, $ms)
}
};
let $doc := doc("movies.xml")
let $ms := $doc//movie (: all movies :)
return
local:sorted-actors-with-id($ms)
-------------------------------------------------------------------------
10. Write XQuery code that disentangles movies, directors and actors in
the folowing way.
The result document has a top element cinema with two child
elements, artists and movies. Below artists, there are artist
elements and below movies there are movie elements.
An artist has an id attribute, with a unique id, and subelements
last_name, first_name, and birth_year.
A movie element has the same kind of children as before, except
for two changes
- a director element has an attribute director_id, with the id of
the artist who directed the film, and no child elements
- an actor element has an attribute actor_id, with the id of the
artist who acted in the film, and a child element, role as
before.
The XQuery code should take as input a document satisfying the
movies DTD and output a document satisfying the description above.
declare function local:sorted-artist-infos ($as as element()*)
as element()*
{for $ln in distinct-values($as/last_name)
for $fn in distinct-values($as[last_name=$ln]/first_name)
order by $ln, $fn
return
element artist_info
{element last_name {$ln},
element first_name {$fn},
element birth_year
{$as[last_name=$ln and first_name=$fn][1]/birth_date}
}
};
declare function local:artist-list($sorted-artist-infos as element()*)
as element()*
{for $info at $pos in $sorted-artist-infos
return
element artist
{attribute id {$pos},
$info/last_name,
$info/first_name,
$info/birth_year
}
};
declare function local:movie-list($movies as element()*,
$artists as element()*)
as element()*
(: ------------ input are movies $movies, numbered artists $artists ------------ :)
{for $movie in $movies
return
element movie
{$movie/title,
$movie/year,
$movie/country,
$movie/genre,
$movie/summary,
element director
{attribute director_id
{let $director := $movie/director
return
$artists[last_name = $director/last_name and
first_name = $director/first_name]/@id
}
},
for $actor in $movie/actor
return
element actor
{attribute actor_id {$artists[last_name=$actor/last_name and first_name=$actor/first_name]/@id},
$actor/role
}
}
};
let $doc := doc("movies.xml")
let $sorted-artist-infos := local:sorted-artist-infos ($doc/movies//(director | actor))
let $artist-list := local:artist-list($sorted-artist-infos)
let $movie-list := local:movie-list($doc//movie, $artist-list)
return
element cinema
{element artists {$artist-list},
element movies {$movie-list}
}
-------------------------------------------------------------------------