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.
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?
FJ I think this is a complex view. You can’t use DML query.
thanks, Bina!
Hi Bina,
You mean the answer should be A not D right?
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?
Hello.
The answer is D, because the table has 4 fields SALE_SUMMARY. Also the SUM (Quantity_Sold) can enter the column QTY_SOLD TOTAL_QTY_SOLD field.
the table is in year 26 of the following link.
http://share.pdfonline.com/8946c9361f214efa851f797336dffe33/1z0-051%20other_lyf_2.htm
Where is the structure of the table SALE_SUMMARY?
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…
Ahhh, now I got it 🙂
Because there is no column list in INSERT statement…
I think the answer is B because you cannot use the DML operations on a Complex View
@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
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?
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
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.