USYD - INFO 2120 - SQL Challenge #3 Solution

1- select count(distinct original_language_id) from film

2- select film_id,title,replacement_cost from film
    where replacement_cost between 10 and 20
    order by replacement_cost,title

3- select count(*) from film
    where extract(year from CURRENT_DATE)-release_year <= 10

4- select film_id,title,trunc(length/60,0) as hours, mod(length,60) as mins
   from film where rating='R'
   order by length desc

5- select trunc(355.0/113.0,2), trunc(355.0/113.0,3), trunc(355.0/113.0,4)

6- select film_id,title, length, rating  from film
    where (length>120 and rating='R')
              or (length<60 and rating='PG')
    order by title

7- select actor.first_name, actor.last_name from actor, film_actor, film
     where film.title ='AMERICAN CIRCUS'
                and actor.actor_id=film_actor.actor_id
                and film.film_id=film_actor.film_id
     order by last_name asc

8- Select f.film_id,f.title,f.release_year
    From film f, film_category fc,category c
    Where f.film_id=fc.film_id
         and c.category_id=fc.category_id
         and f.rating='R'
         and c.name='Action'
   Order by f.title

9- select f.title
   from actor a, film_actor fa, film f
   where a.first_name = 'FRED'
       and a.last_name = 'COSTNER'
       and a.actor_id = fa.actor_id
       and fa.film_id = f.film_id
       and (f.special_features like '%Commentaries%'
       or f.special_features like '%Behind the Scenes%')
   order by f.title;

10- select c.category_id, c.name AS category, pa.name as parent
      from category c inner join category pa on pa.category_id = c.parent_cat
      order by c.name