Oracle relational database, querying/joining multiple tables

Question

I know most people here tend to frown on people asking homework questions on here, but I am really stumped on one question in my database systems class.

1. List all cities (city name and zip code where there is at least one property for rent managed by staff who works in the branch office at ‘1119 Leighton Ave’. Order your result by ascending order of city name. Otherwise, zero point.

The database schema is:

Branch (bNo, street, zipcode)
Staff (sNo, fName, lName, position, sex, dob, salary, bNo)
Property (pNo, street, zipcode, type, room, rent, oNo, sNo, bNo)
Owner (oNo, fName, fName, street, zipcode, phone)
Client (cNo, fName, lName, phone, prefType, maxRent)
Viewing (pNo, cNo, viewDate, cmmt)
Zip (city, state, zipcode)

And this is the query that I have tried to put together, very unsuccessfully...

SELECT z.city, p.zipcode
FROM Zip z, Property p
WHERE p.bNo = (SELECT bNo
                   FROM Branch
                   WHERE street = '1119 Leighton Ave');

I am having a bit of hard time with the joins. This statement above does NOT even come close to working right. I am digging through my book and the internet, trying to find some sort of help. Any help would be greatly appreciated. Thanks in advance.

UPDATE

I am currently trying:

SELECT DISTINCT Zip.city, Zip.zipcode AS Zip
FROM Zip
    JOIN Property ON Zip.zipcode = Property.zipcode
    JOIN Branch ON Zip.zipcode = Branch.zipcode
WHERE Property.bNo = (SELECT bNo
                      FROM Branch
                      WHERE Street = '1119 Leighton Ave')
ORDER BY Zip.city;

The output looks a lot better, but I don't think it is correct. I am checking it now.

UPDATE #2

Okay, so by running this query:

SELECT DISTINCT zipcode
FROM Property
WHERE bNo = 'B001';

and just as an FYI B001 is the branch number for the Branch located at 1119 Leighton Ave. Anyways, that command gave me the following zip codes:

36205
36251
36264
36206
36277
36272
36265
36203
36201

When I run the above command:

SELECT DISTINCT Zip.city, Zip.zipcode AS Zip
FROM Zip
    JOIN Property ON Zip.zipcode = Property.zipcode
    JOIN Branch ON Zip.zipcode = Branch.zipcode
WHERE Property.bNo = (SELECT bNo
                      FROM Branch
                      WHERE Street = '1119 Leighton Ave')
ORDER BY Zip.city;

This is my output:

City                  Zip
--------------------- -----
ANNISTON              36206
JACKSONVILLE          36265

So, I believe that something is a little off with this query. But you guys have given me a GREAT start, I will continue playing with it and I am confident that I can probably figure it out. Thanks a ton guys.

I think I got it figured out thanks to all the help from everyone.

SELECT DISTINCT Zip.city, Zip.zipcode AS Zip
FROM Zip
    JOIN Property ON Zip.zipcode = Property.zipcode
    JOIN Branch ON Zip.zipcode = Property.zipcode
    JOIN Staff ON Staff.bNo = Branch.bNo
WHERE Property.bNo = (SELECT bNo FROM Branch WHERE Street = '1119 Leighton Ave')
ORDER BY Zip.city

Answer 1

There are multiple ways to write JOIN queries in Oracle. A more standard SQL way is to do something like this:

SELECT t1.colNameA, t1.colNameB, t2.colNameC
FROM t1
  JOIN t1.pid = t2.fkid
WHERE
 t1.colNameA='whatever'

With that in mind, you might try the following steps:

1. List out all the columns that you need:

   SELECT Zip.city, Zip.zipcode

2. List out all the tables that you need, including the relationships between them:

   FROM Zip
     JOIN Property ON Zip.zipcode=Property.zipcode
     JOIN Branch ON Zip.zipCode=Branch.zipcode
     JOIN etc
   

3. Add the WHERE criteria.

   WHERE Branch.street='1119 LeightonAve'
     AND etc
   

4. Add ORDER BY clause

   ORDER BY Zip.city
   

This is not the complete answer, but hopefully helps get you in the right direction.

EDIT #2 After your edit, I would suggest you need to research the keyword "DISTINCT". Something like this might be more what you are looking for:

SELECT DISTINCT Zip.city, Zip.zipcode

Answer 2

Try this

SELECT z.city, p.zipcode
FROM Zip z
     JOIN Property p ON z.zipcode = p.zipcode  -- city name and zip code where there is at least one property for rent 
     JOIN Staff s ON p.sNo = p.sNo -- managed by staff 
     JOIN Branch b ON s.bNo = b.bNo AND b.street = '1119 Leighton Ave' -- who works in the branch office at ‘1119 Leighton Ave’
ORDER BY z.city

JOIN(INNER JOIN) makes sure both table participating has the same value in at least one row, otherwise no record is returned.