Member Avatar for Nperic

Hey guys, im in deep need of help for mysql. Ive been given a few questions most of them answered although theres 3 which are really bringing me down. I was wondering if u could get some help :D
Given this schema

borrow(transactionID, personID*, borrowdate, duedate, returndate)
author(authorID, firstname, middlename, lastname)
book_copy(bookID, bookdescID*)
book(bookdescID, title, subtitle, edition, voltitle, volnumber, language, place, year, isbn, dewey, subjectID*)
borrow_copy(transactionID*, bookID*)
person(personID, firstname, middlename, lastname, address, city, postcode, phonenumber, emailaddress, studentno, idcardno)
publisher(publisherID, publisherfullname)
written_by(bookdescID*, authorID*, role)
published_by(bookdescID*, publisherID*, role)
subject(subjectID, subjecttype)


1. i need to display the firstname and lastname of the people who returned books more than 7 days late.
-i tried using the datediff function although it came back as invalid, is there another way?

2. A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS", but finds out all of its copies are already borrowed by others. Write two queries to display other recommended titles using the following methods.

  1. Using partial matching of the query string -- note that the borrower is interested in a "DATABASE" book.
  2. By searching of other books borrowed by people who also borrowed the above title

-this killed me, im clueless

and my last one

3. i have to display the title of the book which has most physical copies. If there are more than one book with the largest number of copies, show them all. Your query should show the number of copies along with the title.
-eh? lol

Help with these would be great, describing what you did would be a bonus but then again i'm asking for too much. Any help would be appreciated. Thankyou
N

  • 2 Contributors
  • 5 Replies
  • 568 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cfAllie
Member Avatar for cfAllie

Start with 1). Can you post your query and a description of what didn't work?

Member Avatar for Nperic

select firstname, lastname
from person, borrow
where person.personid = borrow.personid and datediff(dd, returndate, duedate) > 7

:S we were never properly taught to use datediff, so im a tad confused

Member Avatar for cfAllie

That's almost right! It looks like you're using sql server syntax. MySql's DateDiff() function works a little differently.

DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_datediff

So it doesn't require the interval ("dd" - for days). Try instead

where datediff(returndate, dueDate) > 7;
Member Avatar for Nperic

where person.personid = borrow.personid and datediff(returndate, dueDate) > 7
*
ERROR at line 3:
ORA-00904: "DATEDIFF": invalid identifier

Thats what i got, i dont think i can use the function at all. Should i nest it, so it becomes less sensitive?

I might just skip this one, given me enough problems already. Could you help me with the next two cfAllie :D your expertise is in need haha

Member Avatar for cfAllie

where person.personid = borrow.personid and datediff(returndate, dueDate) > 7
*
ERROR at line 3:
ORA-00904: "DATEDIFF": invalid identifier

Thats what i got, i dont think i can use the function at all. Should i nest it, so it becomes less sensitive?

I might just skip this one, given me enough problems already. Could you help me with the next two cfAllie :D your expertise is in need haha

I think you may posted this question in the wrong forum. Its under MySql db, but it looks like you're using Oracle. Unfortunately, I'm not and don't know Oracle syntax :(

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.