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

A romantic comedy (romcom) is a movie that has both Romance and Comedy as a genre. Complete the following query template to produce a query that returns rows the titles of all rom-coms.

   select title
      YOUR-CODE-GOES-HERE
   order by title;

Exercise 1.b

Now modify your solution for Exercise 1.a so that the same movies are listed, but all of their genres other than Romance and Comedy are produced. That is, this query should answer the question "What other genres are assigned to our romcoms?"

   select title, genre
      YOUR-CODE-GOES-HERE
   order by title, genre;

Exercise 1.c

Rather than using genres to classify movies, we might want to see how much similarity there is with using their associated keywords. Fill in the template below to count for each movie the number of keywords it shares with the movie Skyfall (2012). The results should not include Skyfall itself.

    select title, count(*) as total
      YOUR-CODE-GOES-HERE
    order by total desc, title
    limit 10;