SQL Challenge 5 Solutions - unihub

SQL Challenge 5 Solutions:

1. select name, film_id, title

from film join film_category using (film_id)

join category

using (category_id)

where title like 'B%'

2. select film_id, title, release_year, rating,

case

when rating='G' then 'General Audiences. All Ages Admitted.'

when rating='PG'

then 'Parental Guidance Suggested. Some

Material May Not Be Suitable For Children.'

when rating='PG-13' then 'Parents Strongly Cautioned. Some

Material May Be Inappropriate For Children Under 13.'

when rating='R'

then 'Restricted. Children Under 17

Require Accompanying Parent or Adult Guardian.'

when rating='NC-17' then 'No One 17 and Under Admitted.'

end

from film

where release_year between 2000 and 2002

3. select title, COALESCE(first_name || ' ' || last_name, 'tbc.')

from film left outer join film_actor using(film_id)

left outer join actor

using (actor_id)

where release_year=2000

4. select nationality, count(*)

from actor

group by nationality

order by 2 desc, nationality;

5. select film_id, title, count(*)

from film join film_actor using(film_id)

group by film_id, title

having count(*) >= 5

order by title

6. select release_year, avg(rental_rate),

max(rental_rate), min(rental_rate)

from film

group by release_year

having count(*) >= 10

order by avg(rental_rate) desc;

7. select a.first_name, a.last_name

from actor a join film_actor fa using (actor_id)

join film f using (film_id)

where f.release_year < 2011

group by a.actor_id, a.first_name, a.last_name

having count(*) >= 15

order by a.last_name, first_name;

8. select actor_id, first_name, last_name, count(*)

from film_actor natural join actor

group by actor_id, first_name, last_name

having count(*) = ( select max(cnt) as max_cnt

from ( select count(*) as cnt

from film_actor

group by actor_id

) as Tmp

);

9. select first_name || ' ' || last_name as fullname

from actor natural join film_actor natural join film

where upper(title) like 'CHOCOLAT%'

group by fullname

having count(*) = (select count(*)

from film

where upper(title) like'CHOCOLAT%' )

order by fullname

10.

we didn¡¯t come up with any solution for question 10.If

anybody have the answer please send it to us, we put it here.

11.

Write a query which lists all the films that were released

between 2000 and 2002 (inclusive) by film_id, title, release_year

and rating along with fifth column showing the full

interpretation of the ratings. Use the following list for this

purpose:

? G means 'General Audiences. All Ages Admitted.'

? PG means 'Parental Guidance Suggested. Some Material May

Not Be Suitable For Children.'

? PG-13 means 'Parents Strongly Cautioned. Some Material May

Be Inappropriate For Children Under 13.'

? R means 'Restricted. Children Under 17 Require Accompanying

Parent or Adult Guardian.'

? NC-17 means 'No One 17 and Under Admitted.'

12.

Write an SQL query that returns all films (by their title)

that were released in 2000 together with the full name of actors

or actress' playing in it.

The result shall have two columns:

? the film title

? the actors' fullname: ' '

If there is any film without any associated actor or actress, the

film shall still be listed, but with tbc. in the fullname column

(for 'to be confirmed').

13.

Write a query that lists all nationalities along with how

many actors belong to each nationality. The result set must be

ordered primarily by descending number of actors (so the most

frequent nationality first), and then for nationalities with the

same number of actors those alphabetically by nationality.

14.

Write a query that lists all those movies that have at

least 5 actors playing in them by film_id, title and cast size.

Show the result in alphabetical order of the film titles.

15.

Write a query that lists the average, the maximum and the

minimum rental rate for each year. Show statistics only for those

years in which at least 10 films were released. (List four

columns: release_year, AVERAGE, MAXIMUM, and MINIMUM. The result

set should be sorted by AVERAGE in descending order - which means

that the year with the highest average rental rate should come

first)

16.

Write a query to list the first_name and last_name of

actors who have featured in at least 15 films released in 2010

and before. (List both their first and last names and in

ascending order of the last name, and for actors with the same

last name, secondarily by first name)

17.

Write a query to list the actor who has appeared in the

highest number of films. List actor_id, first_name and last_name

along with the number of appearances.

18.

Write a query that lists the fullnames ("

") of all actors who acted in every film that had a

title starting with ¡¯CHOCOLAT¡¯. List these actors in alphabetical

order.

19.

Write a single (recursive) SQL query that lists the name of

all sub-categories of the Non-Fiction category (including NonFiction itself) together with their corresponding level. The

categories should be listed in ascending level (Non-Fiction being

level 0, its direct children level 1 etc.) and within the same

level alphabetically by name.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download