最新Oracle_OCP11g考试题库

Oracle_OCP11g考试题库

1.

Which statement is true regarding the COALESCE function? A. It can have a maximum of five expressions in a list B. It returns the highest NOT NULL value in the list for all rows C. It requires that all expressions in the list must be of the same data type D. It requires that at least one of the expressions in the list must have a NOT NULL

value Answer: C 2.

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.) A. SELECT promo_id, DECODE (NVL(promo_cost,0), promo_cost,

promo_cost * 0.25, 100) \FROM promotions;

B. SELECT promo_id, DECODE (promo_cost, 10000,

DECODE (promo_category, 'G1', promo_cost *.25, NULL), NULL) \

1

FROM promotions;

C SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') \FROM promotions;

D. SELECTpromo_id,DECODE(promo_cost, >10000, 'High',<10000, 'Low') \

FROM promotions; Answer: A, B 3.

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust _last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600? A. INSERT INTO orders VALUES (1, '10-mar-2007', 'direct',

(SELECT customer_id

2

FROM customers

WHERE cust last name= 'Roberts' AND credit_limit=600), 1000);

B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers

WHERE cust last name= 'Roberts' AND credit_limit=600),order_total)

VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000); C. INSERT o.order_total

FROM orders o, customers c WHERE o.customer_id=c.customer_id

AND c.cust_last_name='Roberts' ANDc.credit_limit=600 | VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers

WHERE cust_last_name= 'Roberts' AND credit_limit=600 ), 1000); D. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers

WHERE cust_last_name= 'Roberts' AND credit_limit=600),order_total)

VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000); Answer: A

3

INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id,

4.

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement

SQL> SELECT cust_city, COUNT(cust_last_name) FROM customers

WHERE cust_credit_limit > 1000 GROUP BY cust_city

HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;

Which statement is true regarding the outcome of the above query? A. It executes successfully

B. It returns an error because the BETWEEN operator cannot be used in the HAVING

clause

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement

D. It returns an error because WHERE and HAVING clauses cannot be used to apply

conditions on the same column Answer: A

4

5.

View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements? A. statement 1 gives an error, statement 2 executes successfully B. statement 2 gives an error, statement 1 executes successfully C. statement 1 and statement 2 execute successfully and give the same output D. statement 1 and statement 2 execute successfully and give a different output

Answer: D 6.

You created an ORDERS table with the following description:

5