CS 383

Lab 3 -- Feb 2

More complex SQL Queries and Postgres

Work on the following for no more and 80 minutes. I do not expect you complete all of queries below; the goal is just to get practice.

Answers, or at least partial answers, will be posted Friday

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 hich the launch occurred. Do this two ways.   First without join, second with join. Can you use a natural join to get the same result?

-- 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.  Do NOT use a join.  Use only a single table, without using sort or limit
-- select one random use sorting and limit.

--in two difference 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

Send to gtowell@brynmawr.edu your last completed query, and what it is supposed to do.