Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have this query. Sorry to bother you, I know it’s simple but I’m wracking my brain in order to solve it.

Consider a relation of scheme 

Boat(ID,Name,Color,Age) 

and a relation of scheme 

Rent(IDBoat, IDClient, Date, Duration) 

such that IDBoat is foreign key with respect to ID.

SQL: compute, for each boat, how many clients rent 
the boat in the last month and for a duration greater than 1 hour.

My solution is:

SELECT IDBoat, COUNT (*), IDClient 
FROM Rent 
WHERE Date Between 2012-01-25 
AND 2011-12-25 
AND Duration > 1;

Is it correct?

Thanks a lot

share|improve this question
So which database backend are you using, it makes a difference. Seems like homework, so I might suggest that you look up the group by keyword in your database help files. And also how to reference dates in a where clause. BYthe way if your teacher is teaching you to use SQL antipatterns like id fields named ID, I can't say he is very competent either. – HLGEM Jan 25 '12 at 22:33
thanks for your reply..it's homework as you said..So I should put ' for dates and use the group by clause..SELECT IDBoat, COUNT (*), IDClient FROM Rent WHERE Date Between '2012-01-25' AND '2011-12-25' AND Duration > 1 GROUP BY IDBoat; – user962800 Jan 25 '12 at 23:04
Close, you have two fields you should group by – HLGEM Jan 25 '12 at 23:45
1  
thanks a lot for your hint...I suppose the following should be correct : SELECT IDBoat, COUNT (*), IDClient FROM Rent WHERE Date Between '2012-01-25' AND '2011-12-25' AND Duration > 1 GROUP BY IDBoat,IDClient ; Am I right? Thanks – user962800 Jan 26 '12 at 8:48
Sorry guys but I don't see the reason why nobody answered my question and also nobody pointed out my mistakes..given the above mentioned problem the query should be: Select Count(IDBoat) as Nboat, Count(IDClient) as Nclient From Rent Where Date between ‘2011-12-27’ And ‘2012-01-27’ And Duration >1; Can anyone confirm that? This is supposed to tbe the place where such things are trivial..Thanks – user962800 Jan 27 '12 at 8:37
show 1 more comment

closed as too localized by gbn, Derek Downey, ConcernedOfTunbridgeWells, jcolebrand♦ Feb 15 '12 at 16:32

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

Browse other questions tagged or ask your own question.