1. If you are using Oracle, produce the report sho wn in Figure 7.29. ( Note: Y
ID: 3546824 • Letter: 1
Question
1. If you are using Oracle, produce the report shown in Figure 7.29. (Note: Your page number might appear in a different position.) Create a view for the report, if necessary. Write the script to produce the report and make any changes to LINE- SIZE and/or PAGESIZE that you feel are necessary.
Explanation / Answer
create view as BRANCH_VIEW AS
select BRANCH.BRANCH_NUM Br_Num,
BOOK.TITLE BOOK_TITLE,
PUBLISHER.PUBLISHER_NAME PUBLISHER_NAME,
BOOK.PRICE PRICE,
INVENTORY.ON_HAND UNITS_ON_HAND
from BOOK, PUBLISHER,INVENTORY,BRANCH
where BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND INVENTORY.BOOK_CODE = BOOK.BOOK_CODE
AND INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
UNION
select BRANCH.BRANCH_NUM Br_Num,
NULL BOOK_TITLE,
NULL PUBLISHER_NAME,
NULL PRICE,
sum(INVENTORY.ON_HAND) UNITS_ON_HAND
from BOOK, PUBLISHER,INVENTORY,BRANCH
where BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND INVENTORY.BOOK_CODE = BOOK.BOOK_CODE
AND INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
group by BRANCH.BRANCH_NUM;
Use the below query for concatenation from the above created view.
select decode(BOOK_TITLE,NULL,'SUM',Br_Num)||' '||BOOK_TITLE||' '||PUBLISHER_NAME||' '||PRICE||' '||UNITS_ON_HAND
from BRANCH_VIEW;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.