Relational databases: Practical Exercises

We assume you have already completed the tutorial on a separate page.

Please use SQL to solve each of the following exercises.

In Moodle, please submit three files: exercise1a.sql, exercise1b.sql, and exercise1c.sql. If you haven't completed all parts, you can submit blank files. However, we recommend developing and debugging on your own machine before going to Moodle.

Exercise 1.a

Some writers died before their associated movies were made. Write a query that returns records with columns name, gap where name is the name of a deceased writer that was credited with a movie made in a year after their deathYear. The gap is the difference between the movie's year and the writer's deathYear.

   select distinct p.name as name, m.year - p.deathYear as gap
      YOUR-CODE-GOES-HERE
   order by gap desc, p.name
   limit 10;

Exercise 1.b

Write a query that returns columns name, role, movie_count where the actor with name has played role in movie_count number of regular movies (not a TV Movie).

     select p.name as name, pr.role as role, count(*) as movie_count
         YOUR-CODE-GOES-HERE
     group by name, role
     order by movie_count desc, name, role
     limit 10;

Exercise 1.c

Write a query that returns records with columns role, name, movie_title, tv_movie_title where name is the name of an actor that plays the same role in both the movie of title movie_title and the TV movie with title tv_movie_title.

   select r1.role as role, p.name as name, m1.title as movie_title, m2.title as tv_movie_title
      YOUR-CODE-GOES-HERE
   order by r1.role, p.name, m1.title, m2.title;