CS 383

Lab 3 -- Feb 2

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
   select first_name, last_name, title from actor, film,film_actor where film_actor.film_id=film.film_id and film_actor.actor_id=actor.actor_id order by title;


   -- sort so that you easily see what moves each actor was in
   select first_name, last_name, title from actor, film,film_actor where film_actor.film_id=film.film_id and film_actor.actor_id=actor.actor_id order by last_name, first_name;

   -- 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
   with ccc as (select count(*) as count, film_id from film_actor group by film_id order by count desc limit 3) select first_name, last_name, title from actor, ccc, film,film_actor where ccc.film_id=film.film_id and film_actor.film_id=film.film_id and film_actor.actor_id=actor.actor_id order by title;
   
   -- an alternate approach to the same problem using arrays.  Gratuitously adds string concatenation also. 
   select title,array_agg(first_name||' '||last_name) from actor join film_actor using (actor_id) join film using (film_id) group by title order by array_length(array_agg(actor_id), 1) DESC limit 3;

   -- I got to wondering if I could do this query using the rank() function. Yes, but I ended up having to use group by anyway so the first version is much easier ... this version is incomplete but it gets started
   with fa(fi, ai, rk) as (select film_id, actor_id, rank() over (partition by film_id order by actor_id) as rank from film_actor) select fi, max(rk) as max from fa group by fi order by max

-- find the names of all films in the Sports category
with ccc as (select count(*) as count, film_id from film_actor group by film_id order by count desc limit 1) select title from ccc,film where ccc.film_id=film.film_id;

select title, category.name from film, category, film_category where category.name='Sports' and category.category_id=film_category.category_id and film_category.film_id=film.film_id;

-- find all names of all films in the Sci-Fi category the have an actor whose last name is Bale
select title, category.name, last_name from film, category, film_category, actor, film_actor where film_actor.film_id=film.film_id and actor.last_name='BALE' and category.name='Sci-Fi' and category.category_id=film_category.category_id and film_category.film_id=film.film_id;

-- 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)?
with aaa as (select count(*) as cc, film_id from film_category group by film_id), bbb as (select max(cc) as xx from aaa) select xx>1 from bbb;


-- What is the name of the film with the most listed stars? (There may be more than one)
with ccc as (select count(*) as count, film_id from film_actor group by film_id order by count desc limit 1) select title from ccc,film where ccc.film_id=film.film_id;

-- What is the name of the most rented movie and how many times was it rented?
with
    rented as (
        select inventory_id, count(inventory_id) as c
        from rental
        group by inventory_id
    ),
    mostRentedID as (
        select film_id, sum(c) as c
        from inventory join rented on inventory.inventory_id = rented.inventory_id
        group by film_id
        order by sum(c) desc
        limit 1
    )
 
select title, c from film natural join mostRentedID;


with aaa as (select inventory.film_id, count(*) as cc from rental, inventory where rental.inventory_id=inventory.inventory_id group by inventory.film_id) select film.title, aaa.cc as "rental count" from aaa,film where cc=(select max(cc) from aaa) and film.film_id=aaa.film_id;

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 join, second with join. Can you use a natural join to get the same result?

select * from launch, site where launch.launchsite=site.sitecode limit 20;

select * from launch join site on launch.launchsite=site.sitecode limit 20;

-- 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
with aaa as (select jd, random() as rrr from launch) select * from launch natural join aaa where rrr=(select max(rrr) from aaa);
-- NOTE without limit this is not guaranteed to return only one item.

-- select one random use sorting and limit.
select *
from launch 
order by random()
limit 1;

--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

select apogee, date from launch where apogee > all ( select apogee from launch where date_part('year', date)=1957);
select apogee, date from launch where apogee > ( select max(apogee) from launch where date_part('year', date)=1957);

-- the max version is -- or should be -- much faster. Max version is O(n) + O(m) whereas any version is O(m*n) 
-- realistically optimizer gets in the way and is may be hard to see any difference

What to turn in