0

I am trying to write some queries and I am confused on exactly what to do. Here are my tables that reference my 3 questions.

Branch
(branch_id, branch_name, street_address, city, state, zipcode, phone, branch_manager, library_id)
FK – library_id > library

Library
(library_id, library_name, street_address, city, state, zipcode, phone, manager_name)

Patron
(patron_id, first_name, last_name, street_address, city, state, zipcode, phone, fee_balance)

Book
(book_id, book_title, publication_date, publisher_id, précis)
FK – publisher_id > publisher

Copy
(copy_id, book_id, cost, branch_id)
FK – Book_id > book
FK – branch_id > branch

Checkout
(checkout_id, copy_id, patron_id, out_date, due_date, return_date)
FK – copy_id > copy
FK – patron_id > patron

Directions:
1. list the books that each patron currently has checked out with the patron number, patron name, phone#, book #, and title.

2. same as #1 except that the list should be for any book the patyron has ever checked out.

3. same as #1 except for all books that are past due along with the past due fee.

I know the format of the syntax
SELECT
FROM
WHERE

however the directions leads me to believe there is a special function I need to put in but I have no idea what it might be. My textbook is pretty bland on giving several examples to help me determine the correct syntax. If you can explain in detail the queries I need I would greatly appreciate it so I can better understand what I am not understanding here. thank you so much in advance to anyone that can help me.

This is what I got so far...its not much because I am lost at the WHERE.

SELECT p.patron_id, p.last_name, p,.first_name, p.phone, b.book_id, b.title
FROM patron p, book b, copy c, checkout co
WHERE
ORDER BY

I am thinking I may need a boolean statement but my book only gives me 1 example of using that function. This seems so easy that it's killing me.

2
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by C++ Beginner
0

This may seem a little vague to you, but stay with me.

1. One major question is DBMS dependent. If it's MSSQL: are you familiar with JOIN syntax? If it is (MSSQL), and you are not (familiar with JOIN syntax), then you need to study that to be able to specify how one table points to other tables you want to get data from. HINT: Foreign Keys (FKs) point from one table to another.
2. If it is not (MSSQL), and since you have listed the tables you have, and you seem to understand the way the key columns point from one table to another, this should give you a hint about how your joins should work.
Specifically:

SELECT a.MyCol. b.MyOtherCol
from TABLE1 a, TABLE2 b
on a.key = b.foreignKey

3. Your WHERE statement should be a natural outcome from your JOIN statements. Specifically, anything in your selection criteria that isn't about how to point from one table to another should be in your where.
Specifically:

SELECT a.MyCol. b.MyOtherCol
from TABLE1 a, TABLE2 b
on a.key = b.foreignKey
WHERE b.SelectionCol = "MyValue"

Given all those things, I have a decision to make. I can either give you the answer (which you will either use verbatim, or you will use to study and figure why the answer works) or I can let you try to work it out yourself from the hints I gave you above and hope you don't hate me forever. Tough choice.
<Jeopardy Music>
</Jeopardy Music>
Okay, I will give you a chance to work it out for yourself. If you try and you still can't get it, just post (or send me a message) and I will help further. BUT...you need to give more detail about the steps you take and what worked/didn't work. I don't want to torture you, but if you don't put in effort, you'll never really get it.

Almost forgot: You need to understand the difference between Inner Join, Left Join, and Right Join to answer your three questions.

Edited by BitBlt: Last line, and formatting.

0

Thanks for the help. This is what I got for #1
SELECT p.patron_id, p.last_name, p,.first_name, p.phone, b.book_id, b.title
FROM patron p, book b, copy c, checkout co
WHERE co.return_date IS NULL
AND co.patron_id = p.patron_id
AND co.copy_id = c.copy_id
AND c.book_id = b.book_id
ORDER BY c.copy_id, p.last_name;

#2 will be similar but without the IS NULL statement.

For #3 how would I run the query showing only the past due books and their fees?

0

I cant run the query since my class is only about theory at this point, next quarter I actually get to start using the DBMS.

Edited by C++ Beginner: n/a

0

Aside from one typo (look at p.first_name) and one column name error (b.title should be b.book_title) this is great.

As far as #3, just add something to test if return_date is null AND due_date < system date (which function is DBMS-dependent).

Last little "style" critique: it is generally considered good form to list the key joins first, then the selection criteria second...so it would look more like:

<snip>
WHERE co.patron_id = p.patron_id
AND co.copy_id = c.copy_id
AND c.book_id = b.book_id
AND co.return_date IS NULL
</snip>

Otherwise, fine job.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.