CS 383

Lab 3

Representing databases

Here is a representation of the University database showing each table and how the table interconnect.

Make a figure like the one for the university but for sakila DB. In your figure include only the tables: actor, actor_info, film, film_actor, film_category, category, inventory customer and rental. hand drawn is fine.

In the queries below, if you cannot figure one out, move on.

Queries using sakila

-- show all the names of all actors in every movie.
   -- sort the results so that you easily see all the actors in each movie
   -- sort so that you easily see what moves each actor was in
   -- show the names of all actors in the movies that have the 3 longest lists of actors.   Show results for only the these three movies

-- find the names of all films in the Sports category


-- find all names of all films in the Sci-Fi category the have an actor whose last name is Bale

-- Write a query that returns true or false for: are there any films with more than one category  (This may require only a single table)?

-- What is the name of the film with the most listed stars? (There may be more than one)

-- What is the name of the most rented movie and how many times was it rented?

Queries using rocket

-- for 20 launches show all details about the launch and the site at which the launch occurred. Do this two ways.   First without the JOIN clause in your SELECT statement; second with JOIN (probably an inner join). Can you use a natural join to get the same result?

-- THIS IS NOT A QUESTION TO ANSWER -- suppose you want to get a random item from a select one random item -- using join -- Not using sorting or limit.
To do this you will need to use the random function ... for instance
     select random() as r;

-- select one random launch. use sorting and limit.
--select one random launch without using sort or limit (one way to do this uses random(), rank(), and WITH ). Another way uses WITH, random() and max.

--in two different ways find all launches whose apogee was higher than the apogee of any launch in 1957.
    -- way 1: using the "any" operator
    -- way 2: using max
    -- compare the time required for each query ... which is faster, why?
    -- to turn on timing: \timing on

What to turn in