Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Gabriel E
Gabriel E
8,916 Points

Really stuck on first two practice session tasks.

I'm really utterly stuck on the first two practice session tasks for reporting with SQL. Here is the direct link to the direct playground: https://teamtreehouse.com/sql_playgrounds/482#/queries/e42aa948-fcc4-4ff7-87cb-52a1ba51b355?_k=74dayo. I only need assistance on the first two. I sometimes have trouble piecing together multiple operators in order to get one result.

Gabriel E
Gabriel E
8,916 Points

But how do you know whether the book is overdue? There's no legend or key that says "This many days means its overdue".

Gabriel E
Gabriel E
8,916 Points

Actually my real question is, since the DATE("now") is obviously way later than the date return_by in the schema, how do you really know if it's overdue?

Gabriel E
Gabriel E
8,916 Points

I'm so sorry. :) I totally understand now.

Gabriel E
Gabriel E
8,916 Points

The third challenge is actually stumping me a bit more than usual. Do you have any ideas on that? I've got most of it, but I can't seem to get the dates of each specific loan.

Jennifer Nordell
seal-mask
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher
Jennifer Nordell
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher

Sorry for the delay. Here's how I did the third practice challenge. But again, you have a little creative license with these :)

-- Format dates in all the loans table in the UK format without the year. For example, April 1st is 01/04.

SELECT STRFTIME("%d/%m",return_by) AS "Due date", STRFTIME("%d/%m", loaned_on) AS "Checked out", STRFTIME("%d/%m", returned_on) AS "Date retruned" FROM loans;
Gabriel E
Gabriel E
8,916 Points

Thank you so much! That made perfect sense.

4 Answers

Jennifer Nordell
seal-mask
STAFF
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher
Jennifer Nordell
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher

I'm assuming you mean the loan practice sessions. Here were my answers for the first two.

-- Find all loans that are overdue.

SELECT * FROM loans WHERE return_by < DATE("now") AND returned_on IS NULL;

-- Find all loans that are due back this week.

SELECT * FROM loans WHERE return_by BETWEEN DATE("now") AND DATE("now", "+6 days") AND returned_on IS NULL;

Now the second one is debatable, because we don't really know how they're defining "week". Do they mean the business week starting on Monday? A traditional American week starting on Sunday? Or do they mean between today and the next 6 days? Hard to say.

Marcia Haledjian
Marcia Haledjian
7,562 Points
Marcia Haledjian
Marcia Haledjian
7,562 Points

Jennifer on your first answer you are not returned all books overdue. The calculation has to be base on loan date + how many days you are allowed to keep, in the table has a book that was overdue on Dec 7 that is not returned yet and your query is not detecting it. My code is this, I believed this will returned all books overdue. Check this and give me your opinion, thanks.

SELECT * FROM loans WHERE DATE(return_by, "-7 days") = DATE(loaned_on) and returned_on IS Null;

This is the code for the second question

SELECT * FROM loans WHERE DATE(loaned_on, "+7 days") BETWEEN DATE("now") AND DATE("now", "+7 days");

Jennifer Nordell
seal-mask
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher
Jennifer Nordell
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher

Marcia Haledjian Would it be possible for you to link the challenge/practice sessions? This question was asked 8 months ago. A large portion of the Database courses have been redone since then. So the database you're looking at and the database I was looking at 8 months ago could very well be different.

Mark Chesney
Mark Chesney
11,747 Points

Hi Jennifer. I appreciated this thread and your mention:

Now the second one is debatable, because we don't really know how they're defining "week".

  • Can I pass a message on to the staff who can tag an extra sentence or two into the SQL playground? Just to clear up interpretation, can something simple like assume that today is Monday be written in?

Thanks for clarifying that it's 6 days and not 7:

BETWEEN DATE("now") AND DATE("now", "+6 days")

That helps me a lot!

Marcia Haledjian
Marcia Haledjian
7,562 Points
Marcia Haledjian
Marcia Haledjian
7,562 Points

https://teamtreehouse.com/library/reporting-with-sql/date-and-time-functions/practice-session, you have to launch de SQL Playground - Stage 4: Practice. I am looking the database today 12/08/2016, I do not have the database from 8 months ago.

Jennifer Nordell
seal-mask
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher
Jennifer Nordell
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher

I've looked at it again, and still contend that my answer for the practice sessions are correct. You say that it's not dectecting one that's listed on December 7th. But keep in mind that these numbers are generated by your system time. If you were to retry this challenge in a few days, the dates will change based on the current time. I get a return of one overdue book, which by my reasoning is correct. The id of the row selected is 1. I'm guessing that you believe the row with the id of 2 should also be returned, but this isn't the case. That book is due today. But it won't be overdue until tomorrow. Remember, a customer has all day to turn it in. Is this the row you're referring to?

Also, if you sincerely believe my answers to be incorrect, I suggest you make a new thread about this in the Community forums :) It's going to be the best way to assure you get the fastest possible assistance! :sparkles:

Charlie Harcourt
Charlie Harcourt
8,046 Points

I was looking thorough your code Marcia I believe it's not completely correct as the second task 'Loans Due' -- find all loans that ARE DUE BACK this week. Your code gives all the books that are due + the one that has already been handed back. I read the task as all those that are due back which means those that have yet to be handed back in; I think its just a matter of opinion on this one.

SELECT * FROM loans WHERE DATE(loaned_on, "+7 days") BETWEEN DATE("now") AND DATE("now", "+7 days") AND returned_on IS NULL;

This only returns the ones that are due in within the next week and have yet to be handed in.

:)

Maiwand Hussain
Maiwand Hussain
5,864 Points
Maiwand Hussain
Maiwand Hussain
5,864 Points

This is what I managed to put together for the Loans Overdue,

SELECT * FROM loans WHERE DATE("loaned_on", "+7 days") BETWEEN DATE("return_by") AND DATE("now", "-1 day") AND returned_on IS NULL;

AND for the loans due,

SELECT * FROM loans WHERE DATE("loaned_on", "+7 days") BETWEEN DATE("now") AND DATE("now", "+7 days") AND returned_on IS NULL;

Ariadna Rodriguez
Ariadna Rodriguez
6,281 Points
Ariadna Rodriguez
Ariadna Rodriguez
6,281 Points

SELECT * FROM loans WHERE return_by BETWEEN
DATE("now") AND DATE("now","+7 days");