Database Administration

[Diana Magers]

I need to write and execute a command to retrieve the branch number and units for each branch having more than 25 books on hand.

Tables

BRANCH: BRANCH_NUM, BRANCH_NAME, BRANCH_LOCATION, NUM_EMPLOYEES

INVENTORY: BOOK_CODE, BRANCH_NUM, ON_HAND

THIS IS WHAT i HAVE SO FAR:

SELECT BRANCH.BRANCH_NUM, INVENTORY.ON_HAND

FROM INVENTORY, BRANCH

WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

AND INVENTORY.ON_HAND > '25';

ALL I AM COMING UP WITH IS THE TWO COLUMN HEADINGS: BRANCH_NAME AND NUM_EMPLOYEES.

I DEFINITELY NEED HELP WITH THIS ONE. PLEASE.

[peterlaursen]

Well .. then te query returns an empty set  (no rows satisfies the condition).

Take an analytical approach .. simplify.  Do these queries return what you'd expect?

1)

SELECT COUNT(ON_HAND)

FROM INVENTORY

WHERE INVENTORY.ON_HAND > '25';

2)

SELECT BRANCH.BRANCH_NUM, INVENTORY.ON_HAND

FROM INVENTORY, BRANCH

WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM';

If somebody should be able to help you you will need to post an exact case with table definitions and sample data.

[Diana Magers]

'peterlaursen' wrote:

Well .. then te query returns an empty set  (no rows satisfies the condition).

Take an analytical approach .. simplify.  Do these queries return what you'd expect?

1)

SELECT COUNT(ON_HAND)

FROM INVENTORY

WHERE INVENTORY.ON_HAND > '25';

2)

SELECT BRANCH.BRANCH_NUM, INVENTORY.ON_HAND

FROM INVENTORY, BRANCH

WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM';

If somebody should be able to help you you will need to post an exact case with table definitions and sample data.

Actually it came out to be:

SELECT BRANCH.BRANCH_NUM, SUM(INVENTORY.ON_HAND) as UnitsOnHand

FROM BRANCH JOIN INVENTORY

ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

GROUP BY BRANCH.BRANCH_NUM

HAVING SUM(INVENTORY.ON_HAND)>25

[Diana Magers]

'peterlaursen' wrote:

Well .. then te query returns an empty set  (no rows satisfies the condition).

Take an analytical approach .. simplify.  Do these queries return what you'd expect?

1)

SELECT COUNT(ON_HAND)

FROM INVENTORY

WHERE INVENTORY.ON_HAND > '25';

2)

SELECT BRANCH.BRANCH_NUM, INVENTORY.ON_HAND

FROM INVENTORY, BRANCH

WHERE BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM';

If somebody should be able to help you you will need to post an exact case with table definitions and sample data.

Actually it came out to be:

SELECT BRANCH.BRANCH_NUM, SUM(INVENTORY.ON_HAND) as UnitsOnHand

FROM BRANCH JOIN INVENTORY

ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

GROUP BY BRANCH.BRANCH_NUM

HAVING SUM(INVENTORY.ON_HAND)>25

[Author]

Posts