What is the outcome?

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY
tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY
tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?

A.
It executes successfully.

B.
It gives an error because a complex view cannot be used to add data into the
SALE_SUMMARY table.

C.
It gives an error because the column names in the subquery and the SALE_SUMMARY table
do not match.

D.
It gives an error because the number of columns to be inserted does not match with the number
of columns in the SALE_SUMMARY table.



Leave a Reply 14

Your email address will not be published. Required fields are marked *


Fj

Fj

the number of columns to be inserted are “prod_id,qty_sold.”
the number of columns in the sale_summary: prod_id,prod_name,and qty_sold.
they don’t match,because 2 to 3, right?

Bina

Bina

FJ I think this is a complex view. You can’t use DML query.

JD

JD

Hi Bina,

You mean the answer should be A not D right?

Justyna

Justyna

It still can be answer D if table Sale_Summary has more columns than 3.
Does anybody know what is the structure of that table?

hwfurlan

hwfurlan

Where is the structure of the table SALE_SUMMARY?

Gabriel K

Gabriel K

The structure of the table SALE_SUMMARY is:
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(30)
TOTAL_QTY_SOLD NUMBER(10,2)
PROD_CATEGORY VARCHAR2(5)
I still don’t get why the correct answer is D as PROD_CATEGORY is not a mandatory column…

Gabriel K

Gabriel K

Ahhh, now I got it 🙂
Because there is no column list in INSERT statement…

Vishal

Vishal

I think the answer is B because you cannot use the DML operations on a Complex View

Eamon

Eamon

@Vishal

A complex view itself cannot always be DMLed, because the mapping of the rows in a complex view back to the rows in the detail tables cannot always be established on a one-to-one basis. (refer to oracle documentation)

However a complex view can be used as a source of data for DMLing another object such as the table SALE_SUMMARY, which is the case here.

@Gabriel, thanks for the SALE_SUMMARY table def.

best regards
Eamon

lukas

lukas

Hey, I think this will execute as I tried it and please note:

“You can omit a column from the Oracle INSERT statement if the column allows NULL values”

As you can see from table structure it allows NULL values, so why answer D not A?

trinath

trinath

Hi,

INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;

In above query prod_name is not in view ,means column is not existed so it gives error

so ans : D

Jenny

Jenny

I think the answer is B, the view contains GROUP BY clause and it can not add data trough the view.
PD. I don’t see the SALE_SUMMARY table.