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.