SQL HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL HELP

Author

Topic

kizilbas1
Starting Member

2 Posts

Posted - 2007-11-25 : 08:27:34

Hi guys,

I have been working on a database project which is about SQL queries. As I become new to SQL, i cannot manage to do the queries. Anyway here are the questions below;

(a) Search for a book by ISBN and display its availability for loans (i.e., display loan status of
all its copies).

(b) Make a reservation for a book (i.e., to record which book title is being reserved by which
user).

(c) Produce a list of loans to users (i.e., to display user name, user number, book title and bar
codes for those books on loan).

and the tables;

Book:

ISBN Number, Title, Publisher, Date of Publication, Availability.

Loan:

Loan Number, User Number, ISBN Number, Loan Date, Due Date

Reservation:

User Number, ISBN Number, Title, Publisher, Date of Publication

User:

User Number, Name, Surname, Address, Post Code, Telephone

IF YOU PLEASE HELP ME TO DO THESE QUERIES, I WIL BE MUCH APPRECIATED.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-25 : 12:47:38

(a)this query gives allotment status i.e,user who has been alloted the book or else it shows 'Unalloted'

SELECT ISNULL(u.Name,'Unalloted') AS 'Loan Status'FROM Book b
LEFT OUTER JOIN Loan l
ON l.ISBN Number=b.ISBN Number
LEFT OUTER JOIN User u
ON u.User Number = l.User Number

(b)this query gives reservation status i.e,user who has reserved the book or else it shows 'Unreserved'

SELECT ISNULL(u.Name,'Unreserved') AS 'Reservation Status'FROM Book b
LEFT OUTER JOIN Reservation r
ON r.ISBN Number=b.ISBN Number
LEFT OUTER JOIN User u
ON u.User Number = r.User Number

(c)the below query gives lists of loans (users & books).I've used ISBN Number itself as barcode as i cant find a similar column in structure shown.

SELECT ISNULL(u.Name,'') + ' ' +ISNULL(u.Surname,'') AS 'user name',
u.User number,
b.Title AS 'book title',
b.ISBN Number AS 'bar code'
FROM Book b
INNER JOIN Loan l
ON l.ISBN Number=b.ISBN Number
INNER JOIN User u
ON u.User Number = l.User Number

kizilbas1
Starting Member

2 Posts

Posted - 2007-11-25 : 13:21:12

I have tried the queries, somehow they all give syntax error. Is it because that you have typed the initials of the table such as;

SELECT ISNULL(u.Name,'Unalloted') AS 'Loan Status'FROM Book b

OR

ON l.ISBN Number=b.ISBN Number

And what does Book b do there?

Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources