•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 397,603 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,608 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Oracle advertiser:
Views: 1518 | Replies: 3
 |
•
•
Join Date: May 2007
Posts: 8
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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 
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.
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
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.
- Using partial matching of the query string -- note that the borrower is interested in a "DATABASE" book.
- By searching of other books borrowed by people who also borrowed the above title
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
•
•
Join Date: Feb 2007
Location: London
Posts: 114
Reputation:
Rep Power: 2
Solved Threads: 8
Junior Poster
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:
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
1. You don't need a function as using the - operator returns the answer in days:
returndate - duedate > 72. 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
•
•
Join Date: Jun 2007
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by 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
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.-this killed me, im clueless
- Using partial matching of the query string -- note that the borrower is interested in a "DATABASE" book.
- By searching of other books borrowed by people who also borrowed the above title
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
•
•
Join Date: Jun 2007
Posts: 78
Reputation:
Rep Power: 2
Solved Threads: 6
Junior Poster in Training
Re: Oracle Help Needed 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
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Oracle Marketplace
Linear Mode
Similar Threads
- learning php (PHP)
- ORA-12157 error when installing Oracle 10g on Fedora 5 (Oracle)
- Sr Oracle DBA needed in Tampa Bay 6 months -to- Perm (Software Development Job Offers)
- URGENT HELP NEEDED - critically important...!!! (Windows NT / 2000 / XP / 2003)
- Project idea needed using oracle as back end for the dbase and asp.net in the front.. (C)
- help much needed !! (OS X)
Other Threads in the Oracle Forum
- Previous Thread: create attach email with Pl/SQL
- Next Thread: how to create an auto-increment process?
