For each question component below: Write appropriate SQL code. In your answer do

Question

For each question component below:

Write appropriate SQL code.

In your answer document, include a screenshot that displays your SQL code & the results of the execution of the SQL code for question 1.

Running and execution of queries in workspace is not required for question 2. Providing SQL codes (Text) is enough for this question.

Question 1

Create a VIEW with the name BOOK_VC that includes only book titles (BK_TITLE) for books whose list price is more than $150. (20)

Create a VIEW with the name PUBLISHER_VC that includes name, zip code and phone number of publishers who has never published a book whose list price is less than $100. (30)

Create a VIEW with the name AUTHOR_VC that includes name of each author born after January 18, 1976. (20)

Question 2

Grant SELECT & DELETE permission on the BOOK_VC view to the user MOJA. (10)

Grant UPDATE permission on the PB_NAME & PB_ZIPCODE columns of the PUBLISHER_VC view to the user MBILI such that PB_PHONE_NUMBER = 1023489823. (10)

Grant SELECT, INSERT, UPDATE & DELETE permission on the AUTHOR_VC view to all users. (10)

Explanation / Answer

Create a VIEW with the name BOOK_VC that includes only book titles (BK_TITLE) for books whose list price is more than $150. (20)
create view BOOK_VC as select BK_TITLE from Books where list_price >150;

Create a VIEW with the name PUBLISHER_VC that includes name, zip code and phone number of publishers who has never published a book whose list price is less than $100. (30)

create view PUBLISHER_VC as select name, zip_code, phonenumber from publisher p inner join books b where b.publisherId =
p.publisherId and b.listPrice>100

Create a VIEW with the name AUTHOR_VC that includes name of each author born after January 18, 1976. (20)

create view AUTHOR_VC as select name from author where dateofbirth > 18-01-1976;

Question 2

Grant SELECT & DELETE permission on the BOOK_VC view to the user MOJA. (10)

GRANT SELECT ON BOOK_VC TO MOJA
GRANT DELETE ON BOOK_VC TO MOJA  

Grant UPDATE permission on the PB_NAME & PB_ZIPCODE columns of the PUBLISHER_VC view to the user MBILI such that PB_PHONE_NUMBER = 1023489823. (10)

GRANT UPDATE ON PUBLISHER_VC TO MBILI

Grant SELECT, INSERT, UPDATE & DELETE permission on the AUTHOR_VC view to all users. (10

GRANT SELECT ON AUTHOR_VC TO ALL
GRANT INSERT ON AUTHOR_VC TO ALL
GRANT UPDATE ON AUTHOR_VC TO ALL
GRANT DELETE ON AUTHOR_VC TO ALL

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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