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.

Exercise 1

Complete the following query template to produce a query that returns rows name, total where the name column is the name of a person, and the total column indicates the number of movies of the genre Drama for which that person is a writer.

   SELECT name, count(*) AS total
      YOUR-CODE-GOES-HERE
   GROUP BY name
   ORDER BY total desc, name
   LIMIT 10;

Exercise 2

Now modify your solution for Exercise 1 so that names and totals are associated with those writers that write ONLY for movies associated ONLY with the genre Drama.

Exercise 3

The position column of the credits table indicates an actor's rank on the billing of a movie. So for example, position = 1 represents a billing of a top star. A movie may have several top stars sharing the top billing. Your task is to complete the following query template so that it returns rows of the form name1, name2, title where the names are of two (different) actors sharing the top billing in the movie having the associated title. The first name should be lexicographically less than the second.

   SELECT P1.name AS name1, P2.name AS name2, title
      YOUR-CODE-GOES-HERE
   ORDER BY name1, name2, title ;