Jump to content
  • Advertisement
Sign in to follow this  
l jsym l

PL/SQL

This topic is 2569 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey, I have a question, yes on homework, and I can't seem to figure out a little part of the question out. I'm suppose to create a procedure and do this and that within it. However, I was just wondering if there is a simple select statement where I can get for example,
The total number of one certain type of cars for 30,000 dollars.

I was thinking something like
SELECT COUNT(*)
FROM CARS C
WHERE C.NAME = 'LAMBO'
AND ( SELECT SUM( C1.PRICE )
FROM CARS C1
WHERE C1.NAME = 'LAMBO' ) >= 30000;

This is just an example not the actual question. I know this would only return 1 row. So i Guess I'm just unsure how to handle this.

Share this post


Link to post
Share on other sites
Advertisement
The query you've written returns the number of 'LAMBO' cars, where the total price of buying all those cars exceeds 30,000.

What exactly do you want the query to return? The purpose of the query is better than a raw description. For example, "I want to count all the expensive LAMBO cars available for purchase".

Share this post


Link to post
Share on other sites
What im looking for is like this:

Say a lambo costs 10,000. Just an example i know its WAY off. Therefore I should get 3 rows considering I can buy 3 lambos for 30,000 or less. Is this any better?

Share this post


Link to post
Share on other sites

What im looking for is like this:

Say a lambo costs 10,000. Just an example i know its WAY off. Therefore I should get 3 rows considering I can buy 3 lambos for 30,000 or less. Is this any better?


I think the clarify he wants to input a dollar amount and output a row for each car while staying below or equal to his dollar amount.
I think your approach might by flawed.

Are you wanting to output all cars that are less than $30,000?
Because your telling us you want to output a car for each row until you reach your goal amount of $30,000. (which realistically wouldn't have a use)

Share this post


Link to post
Share on other sites
K. Ill try to be more clear.

Say a lambo costs 300,000 USD now. I need to see how much Lambos I can buy with 1,000,000 USD. So my answer should be 3.

Share this post


Link to post
Share on other sites
My SQL-foo is failing me here, I cannot think of a reasonable way of doing that query purely in SQL. Essentially you're looking for the maximum count of all combinations of Lambo cars, where the price of the combination does not exceed the money you have.

One simple way is to select an ordered list of prices from car where the model is the given value. In the application you can sum the prices until you've exceeded the money threshold, while counting the number of rows. This would give you the maximum number of cheap Lambos that you can afford.

If the price for all Lambos is indeed the same, the query would be easier. You would need to discover the price of the model, and number of instances of that model. The result would be the minimum of (number of instances, integer division of $money / price).

Your schema doesn't enforce the price similarity however, it allows different instances of the same model of car to have differing prices. If you don't want to allow this, I would rewrite the schema to separate the concept of a model as (name, price) away from the car table. The car would have a foreign key into the model table.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!