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
   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;
   -- From Keeton, 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;

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

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