View the Exhibit to examine the description for the SALES and PRODUCTS tables.
You want to create a SALE_PROD view by executing the following SQL statement:
CREATE VIEW sale_prod
AS SELECT p.prod_id, cust_id, SUM(quantity_sold) “Quantity” , SUM(prod_list_price) “Price”
FROM products p, sales s
WHERE p.prod_id=s.prod_id
GROUP BY p.prod_id, cust_id;
Which statement is true regarding the execution of the above statement?
A.
The view will be created and you can perform DML operations on the view.
B.
The view will be created but no DML operations will be allowed on the view.
C.
The view will not be created because the join statements are not allowed for creating a view.
D.
The view will not be created because the GROUP BY clause is not allowed for creating a view.
can anyone explain why DML operations cannot be performed
Because it contains SUM(quantity_sold) and SUM(prod_list_price) so it is not simply view any more. You cannot update tables through view when view contains group functions.
thanks Justyna
with warm greets,
arya
But SELECT is also DML command. View is created and we can’t select from it? sure? )))
If think where is a mistake in this question
the view has NOT NULL columns in the base tables that are not selected by the view. So you cannot perform DML operations on it right?
No, see my comment above.
i know you said that. But doesn’t ORACLE disallow DML statement when you have NOT NULL columns in the base tables that are not selected by the view? This was discussed in another question.
yes, it is another question about this and you are right: oracle does not allow DML operations on base table when NOT NULL column/columns are not selected by the view.
justyna iam not geting this point,,plz expain ..what do mean by not null columns?and if some notnull columns selected and some notnull columns not selected then dml operation can be performed?
Thanks Justyna appreciate it!
SELECT p.prod_id, “cust_id” ….
there’s no identification for this column ! It’s supposed to be:
p.cust_id OR s.cust_id
So, the view shouldn’t be created!
cust_id is only in table Sales but not Products
create view sale_prod as select p.prod_id,s.cust_id,sum(quantitiy_sold)”Quantity”,sum(prod_unit_price)”Price”
from products p,sales s
where p.prod_id=s.prod_id
group by p.prod_id,s.cust_id
The view has created successfully and DML(select) is also possible.
Answer:A
no. Ans is B. The view contain non key preserved columns
vikram,
what about SELECT sttement which is DML statement