SQL: tables and queries in school project

cold · 10-29-2005, 05:32 PM

Hello,
I'm a SQL beginner and I have a problem with design of tables and SQL queries for my school project so that they are effective. I need to design tables and SQL queries for simple car rental company. The problem is that I have a car and the car is rented for different prices according to month. Let's say:

* 100 per week in January
* 110 per week in Feruary
* etc. etc.

One can rent a car at least for a week but can rent a car for more weeks. The problem I cannot solve in some good way is how to count price of a car in the situation when car is rented for more weeks in different month where prices for week differ. Let's have an exmaple: client want to rent a car from 15.1. till 7.2., so this are two weeks in January and one week in February. The final price would be 100 + 100 + 110 = 310 (don't count weeks in this example; the point is that I have same dates and I need to get the information about prices in between these dates).
The simple algorithm is to get a price for every week and to add these prices then. But this leads to as many SQL queries as weeks I handle. Is it possible to do it in some better way? Can I design the tables and SQL queries in that way that I can get the price in one query?
PS: I use mysql 4.1 but I thnik postgresql is also installed on our school machine (but IAC I don't know the version of it).

Thanks in advance.
Joseph.

Tinkster · 10-29-2005, 07:58 PM

Not knowing how the tables are actually laid out you could
(in meta-sql) do something like:

Code:

select sum(price) 
  from price-map 
  where date between 15.1.2005 and 7.2.2005

Cheers,
Tink