Oracle

outputting too many

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2008

Posts: 108

Reputation: christiangirl is an unknown quantity at this point

Solved Threads: 1

christiangirl christiangirl is offline

Offline

Junior Poster

outputting too many

Oct 18th, 2009

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:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
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
      );
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:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
prompt 2-5
SELECT DISTINCT client_lname, client_fname, date_due, vid_id
FROM client, rental
WHERE rental.vid_id = '130012';
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!