3. Create a view named BOOK_INVENTORY. It consists of the branch number, and the

Question

3. Create a view named BOOK_INVENTORY. It consists of the branch number, and the total number of books on hand for each branch. Use UNITS as the name for the count of books on hand. Group and order the rows by branch number.

       b. Write and execute the command to retrieve the branch number and units on hand for each branch having more than 25 books on hand.
       c. Write and execute the query that the DBMS actually executes against the base tables.

Answer- b. select * from BOOK_INVENTORY where UNITS > 25;

                c. select BRANCH.BRANCH_NUM,count(INVENTORY.ON_HAND) UNITS
                    from BRANCH,INVENTORY
                    where BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
                    group by BRANCH.BRANCH_NUM
                    order by BRANCH_NUM;

For b and c, I got "no row selected" command when I apply this code, so please see does the coding looks right?

8. List the author number, first name, and last name for all authors. The first name should appear in lowercase letters and the last name should appear in uppercase letters.

Answer- SELECT AUTHOR_NUM, (LOWER) AUTHOR_FIRST, (UPPER) AUTHOR_LAST
                FROM AUTHOR;

And for this coding, I got an error, so please look into this too.

Thank you!

Explanation / Answer

3. Create a view named BOOK_INVENTORY. It consists of the branch number, and the total number of books on hand for each branch. Use UNITS as the name for the count of books on hand. Group and order the rows by branch number.

b. Write and execute the command to retrieve the branch number and units on hand for each branch having more than 25 books on hand.
c. Write and execute the query that the DBMS actually executes against the base tables.

Answer- b. select * from BOOK_INVENTORY where UNITS > 25;

in your inventry table manually check sum of ON_HAND group by branch_num. I think there is no data morethen 25 group by
BRANCH_NUM.

That's why you got no rows.

c. select BRANCH_NUM,sum(ON_HAND) UNITS
from INVENTORY
group by BRANCH_NUM
order by BRANCH_NUM;

use this query you will get data.

8. List the author number, first name, and last name for all authors. The first name should appear in lowercase letters and the last name should appear in uppercase letters.

select AUTHOR_NUM,upper(AUTHOR_LAST) AUTHOR_LAST, lower(AUTHOR_FIRST) AUTHOR_FIRST from AUTHOR;

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.