outputting too many

[christiangirl]

I am just starting to learn sql and have to questions from my homework assignment.
The first is this:
Write a query which will project the average rental price and number of such videos for videos of Classic movies --- BUT! You must write this without explicitly using the category code for Classic movies; you must use the name 'Classic' in your query instead. Rename the columns to be "Avg Cost - Classic" and "# Videos - Classic", respectively, using precisely these spaces and case.

The code I wrote is:

select avg(vid_rental_price) "Avg Cost - Classic", count(*) "#         
Videos Classic"
from movie_category, video
where movie_category.category_code =
        (
          select category_code
          from movie_category
          where category_name = 'Classic'
        )
      and vid_rental_price <
      (
        select avg(vid_rental_price)
        from video
      );

But count is outputting a larger number then it is supposed to. I think it is counting all of the rentals, not just the ones labeled classic. But I am not sure how to fix this.

The second question is:
Using a join, (and NOT using ANY nesting or sub-selects), project the last names, first names, and date the video was due for clients who have ever rented the video with ID '130012'.

and my code is:

prompt 2-5
select distinct client_lname, client_fname, date_due, vid_id
from client, rental
where rental.vid_id = '130012';

It is outputting more rows then it should, and outputs that all vid_id's are 130012. I think this has to do with the join, but am not sure how to do this without a join.

Thanks!

[debasisdas]

kindly post your table structure.