Oracle 1Z0-071 Quiz 32 Topic 3 Questions 156-160

Question: 1

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

q1_1Z0-071

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt

FROM (SELECT product_id, COUNT (*) item_cnt

FROM order_items

GROUP BY product_id) i RIGHT OUTER JOIN products p

ON i.product_id = p.product_id;

What would happen when the above statement is executed?

AThe statement would execute successfully to produce the required output.

BThe statement would not execute because inline views and outer joins cannot be used together.

CThe statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

DThe statement would not execute because the GROUP BY clause cannot be used in the inline.

Show Answer

Question: 2

Which statement is true regarding the UNION operator?

ABy default, the output is not sorted.

BNull values are not ignored during duplicate checking.

CNames of all columns must be identical across all select statements.

DThe number of columns selected in all select statements need not be the same.

Show Answer

Question: 3

You issued the following command:

SQL> DROP TABLE employees;

Which three statements are true? (Choose three.)

AAll uncommitted transactions are committed.

BAll indexes and constraints defined on the table being dropped are also dropped.

CSequences used in the employees table become invalid.

DThe space used by the employees table is reclaimed immediately.

EThe employees table can be recovered using the rollback command.

FThe employees table is moved to the recycle bin

Show Answer

Question: 4

Examine the create table statements for the stores and sales tables.

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);

SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));

You executed the following statement:

SQL> DELETE from stores

WHERE store_id=900;

The statement fails due to the integrity constraint error:

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

Which three options ensure that the statement will execute successfully? (Choose three.)

ADisable the primary key in the STORES table.

BUse CASCADE keyword with DELETE statement.

CDELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.

DDisable the FOREIGN KEY in SALES table and then delete the rows.

ECreate the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Show Answer

Question: 5

In the customers table, the CUST_CITY column contains the value 'Paris' for the

CUST_FIRST_NAME 'Abigail'.

Evaluate the following query:

q5_1Z0-071

What would be the outcome?

AAbigail PA

BAbigail Pa

CAbigail IS

DAn error message

Show Answer

Oracle 1Z0-071 Quiz:32 Topic:3 Questions:156-160