USYD - INFO 2120 - SQL Challenge #4 Solution

Hi all
from now on we put everything about educational or university related stuff to the following address
http://unihub.wordpress.com/

good luck all

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

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

USYD - INFO 2120 - SQL Challenge #1 Solution

1-  select * from country

2-  select count(*) from film 

3-  select * from film where fil_id=271

4-  select release_year from film where title='FLASH WARS' 

5-  select first_name, last_name from actor where nationality='AU'

6-  insert into actor (actor_id,first_name, last_name,nationality) 
                             values (4711,'Arnold','Schwarzenegger','AT')

7-

CREATE TABLE Person (
    license INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
    );

CREATE TABLE CAR (
    model VARCHAR(30),
  owner INTEGER REFERENCES Person (license),
  regno CHAR(6) PRIMARY KEY
    );

8- UPDATE film SET rental_rate=2.52 where title='ANGELS LIFE';  

9-  create table Studio (
  studio_id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  address VARCHAR(100) ,  
  country CHAR(2) NOT NULL REFERENCES Country (short_code)
);