Oracle Help Needed

[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

[davidcairns]

I'm not going to do this for you but I will give you some starters

1. You don't need a function as using the - operator returns the answer in days: returndate - duedate > 7 2. For the first part use the LIKE operator

3. Look into the GROUP BY clause and the COUNT function using the HAVING clause to find the book(s) with the highest count

Remember the basics of a simple select statement
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

I know I left out INTO, but that's only for specific situations, the rest are used all the time in all kinds of statements

Cheers

D

[karenjones]

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

helo,

im a student of computer Sceince few weeks back i was making assignment on Oracle Database Administration and was seeking help,by browsing net i found www.oracleplace.com asite one Oracle DataBase Administraion i got a great help from it.

well i dont know the exact solution of yoru problm hop oracleplace.com canfind the solution of your problem

best of luk

karen jones

[manoshailu]

hi,

1. i need to display the firstname and lastname of the people who returned books more than 7 days late.

\\Query
select p.firstname,p.lastname from borrow b,person p where b.personid=p.personid and b.returndate>b.duedate

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.
Using partial matching of the query string -- note that the borrower is interested in a "DATABASE" book.

\\Query
select btitle from book bk where title like '%DATABASE%'

By searching of other books borrowed by people who also borrowed the above title

\\Query
select p.firstname,p.lastname from borrow b,person p,book bk,book_copy bkc, borrow_copy bc where b.personid=p.personid and bc.transactionID=b.transactionID and bkc.bookid =bc.bookid and bk.bookdescID=bkc.bookdescID and bk.title like '%DATABASE%'

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.

\\Query
select title, count(*) from book group by title

regards
shailu