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.


7 Years
Discussion Span
Last Post by debasisdas
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.