Write approproate SQL code for the following: Create a VIEW with the name CUSTOM

Question

Write approproate SQL code for the following:

Create a VIEW with the name CUSTOMER_VC that includes the name, and marital status (i.e. CUST_LAST_NAME, CUST_FIRST_NAME, MARITAL_STATUS, CREDIT_LIMIT) of each customer in the OE2.OEHR_CUSTOMERS table whose credit limit (i.e. CREDIT_LIMIT) is over 2400.

Write & execute a SQL query that will list the name & credit limit of married customers (i.e. MARITAL_STATUS = ‘M’), ordering the output in descending sequence by credit limit (i.e. CREDIT_LIMIT). This query should reference the viewCUSTOMER_VC.

Write & execute a SQL query that is equivalent to part (b). This query should not reference the view CUSTOMER_VC but should reference the base tableOE2.OEHR_CUSTOMERS.

Question CE2.2

Create a copy of the OE2.OEHR_PRODUCT_INFORMATION table in your schema & name it MY_PRODUCTS.

Create a view MY_PRODUCTS_V that will include all columns in yourMY_PRODUCTS table for those products whose WEIGHT_CLASS > 2 &LIST_PRICE > MIN_PRICE. Note that the SQL statement that is used in your view definition should not reference the OE2.OEHR_PRODUCT_INFORMATION table.

Grant SELECT & DELETE permission on the MY_PRODUCTS_V view to the userMOJA.

Grant UPDATE permission on the LIST_PRICE & MIN_PRICE columns of theMY_PRODUCTS_V view to the user MBILI such that LIST_PRICE > MIN_PRICE.

Grant SELECT, INSERT, UPDATE & DELETE permission on theMY_PRODUCTS_V view to the user TATU.

Question CE2.3

Create a view MY_ORDER_INFO_V that will include the columnsCUST_LAST_NAME, CUST_FIRST_NAME, MARITAL_STATUS, NLS_TERRITORY, PRODUCT_ID, PRODUCT_NAME, ORDER_ID, ORDER_DATE, UNIT_PRICE, QUANTITY columns for all orders of married customers (i.e. MARITAL_STATUS = ‘M’).

Use MY_ORDER_INFO_V view to output the CUST_LAST_NAME,CUST_FIRST_NAME, PRODUCT_ID, PRODUCT_NAME, ORDER_ID, ORDER_DATE, UNIT_PRICE, QUANTITY columns for all orders of married Indian customers.

Explanation / Answer

create view CUSTOMER_VC as select OE2.CUST_LAST_NAME,OE2.CUST_FIRST_NAME,OE2.MARITAL_STATUS,OE2.CREDIT_LIMIT FROM OEHR_CUSTOMERS OE2 WHERE CREDIT_LIMIT>2400;

SELECT CUST_FIRST_NAME,CUST_LAST_NAME,CREDIT_LIMIT FROM CUSTOMER_VC WHERE MARITAL_STATUS='M';

SELECT OE2.CUST_FIRST_NAME,OE2.CUST_LAST_NAME,OE2.CREDIT_LIMIT FROM OEHR_CUSTOMERS OE2 WHERE OE2.MARITAL_STATUS='M';

CREATE TABLE MY_PRODUCTS AS SELECT * FROM OEHR_PRODUCT_INFORMATION;

CREATE VIEW MY_PRODUCTS_V AS SELECT * FROM MY_PRODUCTS WHERE WEIGHT_CLASS>2 AND LIST_PRICE>MIN_PRICE;

GRANT SELECT,DELETE ON TABLE MY_PRODUCTS_V TO MOJA;

grant update(LIST_PRICE,MIN_PRICE) on MY_PRODUCTS_V to TATU

CREATE MY_ORDER_INFO_V AS SELECT CUST_LAST_NAME,CUST_FIRST_NAME,MARITAL_STATUS,NLS_TERRITORY,PRODUCT_ID,PRODUCT_NAME,ORDER_ID,ORDER_DATE,UNIT_PRICE,QUANTITY FROM OEHR_PRODUCT_INFORMATION WHERE MARTIAL_STATUS='M';

SELECT CUST_LAST_NAME,CUST_FIRST_NAME,PRODUCT_ID,PRODUCT_NAME,ORDER_ID,ORDER_DATE,UNIT_PRICE,QUANTITY FROM MY_ORDER_INFO_V;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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