Which statement is true regarding the execution of the following SQL statement?

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?

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.



Leave a Reply 17

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


arya

arya

can anyone explain why DML operations cannot be performed

Justyna

Justyna

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.

arya

arya

thanks Justyna

with warm greets,
arya

Dmitry

Dmitry

But SELECT is also DML command. View is created and we can’t select from it? sure? )))

Dmitry

Dmitry

If think where is a mistake in this question

Neal

Neal

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?

Justyna

Justyna

No, see my comment above.

Neal

Neal

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.

Justyna

Justyna

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.

john

john

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?

user

user

Thanks Justyna appreciate it!

Eric Sacramento

Eric Sacramento

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!

Justyna

Justyna

cust_id is only in table Sales but not Products

pranab

pranab

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.

pranab

pranab

Answer:A

Vikram

Vikram

no. Ans is B. The view contain non key preserved columns

sky

sky

vikram,

what about SELECT sttement which is DML statement