Oracle 1Z0-071 Quiz 77 Topic 15 Questions 381-385

Question: 1

Which two statements are true regarding the COUNT function?

AA SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

BCOUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

CCOUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.

DCOUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.

EThe COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Show Answer

Question: 2

In which three situations does a transaction complete?

Awhen a PL/SQL anonymous block is executed

Bwhen a DELETE statement is executed

Cwhen a ROLLBACK command is executed

Dwhen a data definition language (DDL) statement is executed

Ewhen a TRUNCATE statement is executed after the pending transaction

Show Answer

Question: 3

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

ORDERS_MASTER

ORDER_ID

ORDER_TOTAL

1

1000

2

2000

3

3000

4

MONTHLY_ORDERS

ORDER_ID

ORDER_TOTAL

2

2500

3

Evaluate the following MERGE statement:

MERGE_INTO orders_master o

USING monthly_orders m

ON (o.order_id = m.order_id)

WHEN MATCHED THEN

UPDATE SET o.order_total = m.order_total

DELETE WHERE (m.order_total IS NULL)

WHEN NOT MATCHED THEN

INSERT VALUES (m.order_id, m.order_total)

What would be the outcome of the above statement?

AThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

BThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

CThe ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

DThe ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

Show Answer

Question: 4

View the exhibit and examine the ORDERS table.

ORDERS

Name

Null?

Type

ORDER ID

NOT NULL

NUMBER(4)

ORDATE DATE

DATE

CUSTOMER ID

NUMBER(3)

ORDER TOTAL

NUMBER(7,2)

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

AALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

BALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

CALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

DALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Show Answer

Question: 5

Examine the structure of the INVOICE table.

NameNull?Type

--------------------------------------------------

INV_NONOT NULLNUMBER(3)

INV_DATEDATE

INV_AMTNUMBER(10,2)

Which two SQL statements would execute successfully?

ASELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;

BSELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;

CSELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;

DSELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Show Answer

Oracle 1Z0-071 Quiz:77 Topic:15 Questions:381-385