USYD - INFO 2120 - SQL Challenge #2 Solution

1- select count(*) from film where length >180

2- select count(*) from film where language_id <> original_language_id

3- select title, release_year,rental_rate from film where rating='PG' and rental_rate<1.00

4- select count(*) from film where description like '%Mad Scientist%'

5- select * from language order by name desc

6- select first_name,last_name from actor where nationality='AU' 
                                                         order by last_name, first_name

7- select name from category where parent_cat IS NULL order by name

8- select count(*) from film_actor where film_id=(
                                    select film_id from film where title='VELVET TERMINATOR')

9- select title,release_year from film 
     where 
     rating='R' and special_features like '%Deleted Scenes%' order by title

10- create view ActionMovies as
      select F.film_id as filmID, F.title,F.release_year as year
      from film F, film_category FC, category C
      where 
            F.film_id=FC.film_id and
            FC.category_id= C.category_id and
            C.category_id=(
                                     select category_id from category where name='Action'
             )
            order by year desc