SQL Henry Books Chapter 7
Top of Form
Submitted by Robinson,Latesha on 4/4/2014 11:50:07 PM
Points Awarded
90.00
Points Missed
0.00
Percentage
100%
1.
Create a view named PLUME. It consists of the book code, title, type, and price for every book published by the publisher whose code is PL
a. Write and execute the CREATE VIEW command to create the PLUME view.
b. Write an execute the command using the PLUME view to retrieve the book code, title, and price for every book with a price of less than $13.
c. Write and execute the query that the DBMS actually executes.
d. You do not need to answer this part.
Points Earned:
10.0/10.0
Correct Answer(s):
a. CREATE VIEW PLUME AS
SELECT BOOK_CODE, TITLE, TYPE, PRICE
FROM BOOK
WHERE PUBLISHER_CODE = 'PL ';
b. SELECT BOOK_CODE, TITLE, PRICE
FROM PLUME
WHERE PRICE < 13;
c. SELECT BOOK_CODE, TITLE, PRICE
FROM BOOK
WHERE PUBLISHER_CODE = 'PL '
AND PRICE < 13;
2.
Create a view named NONPAPERBACK. It consists of the book code, title, publisher name, and price for every book that is not available in paperback.
a. Write and execute the CREATE VIEW command to create the NONPAPERBACK view.
b. Write an execute the command to retrieve the book title, publisher name, and price for every book in the NONPAPERBACK view with a price less than $20.
c. Write and execute the query that the DBMS actually executes.
d. You do not need to answer this part.
Points Earned:
10.0/10.0
Correct Answer(s):
a. CREATE VIEW NONPAPERBACK AS
SELECT BOOK_CODE, TITLE, PUBLISHER_NAME, PRICE
FROM BOOK, PUBLISHER
WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND PAPERBACK = 'N ';
b. SELECT TITLE, PUBLISHER_NAME, PRICE
FROM NONPAPERBACK
WHERE PRICE < 20;
c. SELECT TITLE, PUBLISHER_NAME, PRICE
FROM BOOK, PUBLISHER
WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND PAPERBACK = 'N '
AND PRICE < 20;
3.
Create a view named BOOK_INVENTORY. It consists of the branch number and the total number of books on
Submitted by Robinson,Latesha on 4/4/2014 11:50:07 PM
Points Awarded
90.00
Points Missed
0.00
Percentage
100%
1.
Create a view named PLUME. It consists of the book code, title, type, and price for every book published by the publisher whose code is PL
a. Write and execute the CREATE VIEW command to create the PLUME view.
b. Write an execute the command using the PLUME view to retrieve the book code, title, and price for every book with a price of less than $13.
c. Write and execute the query that the DBMS actually executes.
d. You do not need to answer this part.
Points Earned:
10.0/10.0
Correct Answer(s):
a. CREATE VIEW PLUME AS
SELECT BOOK_CODE, TITLE, TYPE, PRICE
FROM BOOK
WHERE PUBLISHER_CODE = 'PL ';
b. SELECT BOOK_CODE, TITLE, PRICE
FROM PLUME
WHERE PRICE < 13;
c. SELECT BOOK_CODE, TITLE, PRICE
FROM BOOK
WHERE PUBLISHER_CODE = 'PL '
AND PRICE < 13;
2.
Create a view named NONPAPERBACK. It consists of the book code, title, publisher name, and price for every book that is not available in paperback.
a. Write and execute the CREATE VIEW command to create the NONPAPERBACK view.
b. Write an execute the command to retrieve the book title, publisher name, and price for every book in the NONPAPERBACK view with a price less than $20.
c. Write and execute the query that the DBMS actually executes.
d. You do not need to answer this part.
Points Earned:
10.0/10.0
Correct Answer(s):
a. CREATE VIEW NONPAPERBACK AS
SELECT BOOK_CODE, TITLE, PUBLISHER_NAME, PRICE
FROM BOOK, PUBLISHER
WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND PAPERBACK = 'N ';
b. SELECT TITLE, PUBLISHER_NAME, PRICE
FROM NONPAPERBACK
WHERE PRICE < 20;
c. SELECT TITLE, PUBLISHER_NAME, PRICE
FROM BOOK, PUBLISHER
WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
AND PAPERBACK = 'N '
AND PRICE < 20;
3.
Create a view named BOOK_INVENTORY. It consists of the branch number and the total number of books on
References: PUBLISHER(PUBLISHER_CODE); 9. Ensure that the PAPERBACK column in the BOOK table can accept only values of Y or N. Points Earned: 10.0/10.0 Correct Answer(s): ALTER TABLE BOOK ADD CHECK (PAPERBACK IN ( 'Y ', 'N ')); Bottom of Form