OracleOCP11g考试题库.docx

1、OracleOCP11g考试题库1.Which statement is true regarding the COALESCE function?A. It can have a maximum of five expressions in a listB. It returns the highest NOT NULL value in the list for all rowsC. It requires that all expressions in the list must be of the same data typeD. It requires that at least o

2、ne of the expressions in the list must have a NOT NULL value Answer: C2.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) DiscountFROM promotion

3、s;B. SELECT promo_id, DECODE (promo_cost, 10000,DECODE (promo_category, G1, promo_cost *.25, NULL), NULL) CatcostFROM promotions;C SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, N/A) CatcostFROM promotions;D. SELECTpromo_id,DECODE(promo_cost, 10000, High, SELECT cust_city,

4、COUNT(cust_last_name)FROM customersWHERE cust_credit_limit 1000GROUP BY cust_cityHAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query?A. It executes successfullyB. It returns an error because the BETWEEN operator cannot be used in the H

5、AVING clauseC. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statementD. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same columnAnswer: A5.View the Exhibit and examine the structure of the PROMOTIONS table.Ex

6、amine the following two SQL statements: Which statement is true regarding the above two SQL statements?A. statement 1 gives an error, statement 2 executes successfullyB. statement 2 gives an error, statement 1 executes successfullyC. statement 1 and statement 2 execute successfully and give the same

7、 outputD. statement 1 and statement 2 execute successfully and give a different output Answer: D6.You created an ORDERS table with the following description: You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.

8、 Which statement is true in this scenario?A. You cannot have two constraints on one columnB. You cannot add a primary key constraint if data exists in the columnC. The primary key constraint can be created only at the time of table creationD. You can add the primary key constraint even if data exist

9、s, provided that there are no duplicate values Answer: D7.When does a transaction complete? (Choose all that apply.)A. when a DELETE statement is executedB. when a ROLLBACK command is executedC. when a PL/SQL anonymous block is executedD. when a data definition language (DDL) statement is executedE.

10、 when a TRUNCATE statement is executed after the pending transact ionAnswer: B, D, E8.You need to display the first names of all customers from the CUSTOMERS table that contain the character e and have the character a in the second last positionWhich query would give the required output?A. SELECT cu

11、st_first_name FROM customers WHERE INSTR(cust_first_name, e) 0 AND SUBSTR(cust_first_name, -2, l) =a;B. SELECT cust first name FROM customers WHERE INSTR(cust_first_name, e) AND SUBSTR(cust_first_name, -2, l)=a;C. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e) IS NOT NULL AND

12、SUBSTR(cust_first_name, l, -2)=a;D. SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)=a;Answer: A9. The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.Which state

13、ment would create a synonym ORD so that HR can execute the following query successfully?SELECT * FROM ord;A. CREATE SYNONYM ord FOR orders; This command is issued by OEB. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OEC. CREATE SYNONYM ord FOR oe.orders; This command is issued by

14、the database administratorD. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator Answer: D10.View the Exhibit and examine the structure of the PRODUCTS tableYou need to generate a report in the following format:Which two queries would give the required outpu

15、t? (Choose two.)A. SELECT prod_name | qs category is | prod_category CATEGORIES FROM products;B. SELECT prod_name | qscategory is | prod_category CATEGORIES FROM products;C. SELECT prod_name | qs category is | prod_category CATEGORIES FROM products;D. SELECT prod_name |q| category is | prod_category

16、 CATEGORIESFROM products; Answer: C, D11.Which statement is true regarding the INTERSECT operator?A. It ignores NULL valuesB. Reversing the order of the intersected tables alters the resultC. The names of columns in all SELECT statements must be identicalD. The number of columns and data types must

17、be identical for all SELECT statements in the query Answer: D12.Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)A. Both USING and ON clauses can be used for equijoins and nonequijoinsB. A maximum of one pair of columns can be joined between two tables us

18、ing the ON clauseC. The ON clause can be used to join tables on columns that have different names but compatible data typesD. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause Answer: C, D13.Examine the structure of the PROGRAMS ta

19、ble Which two SQL statements would execute successfully? (Choose two.)A. SELECT NVL(ADD_MONTHS(END_DATE,l),SYSDATE) FROM programs;B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE) FROM programs;C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),Ongoing) FROM programs;D. SELECT NVL(TO_CHAR(MONTHS_BETWEE

20、N(start_date,end_date),Ongoing) FROM programs; Answer: A, D14.Where can subqueries be used? (Choose all that apply.)A. field names in the SELECT statementB. the FROM clause in the SELECT statementC. the HAVING clause in the SELECT statementD. the GROUP BY clause in the SELECT statementE. the WHERE c

21、lause in only the SELECT statementF. the WHERE clause in SELECT as well as all DML statements Answer: A, B, C, F15. View the Exhibits and examine the structures of the PRODUCTS, SAL ES, and CUSTOMERS tablesYou need to generate a report that gives details of the customers last name, name of the produ

22、ct, and the quantity sold for all customers in TokyoWhich two queries give the required result? (Choose two.)A. SELECT c.cust_last_name,p.prod_name,s.quantity_sold FROM sales s JOIN products pUSING(prod_id)JOIN customers cB. SELECT c.cust_last_name, p.prod_name, s.quantity_sold FROM products p JOIN

23、sales s JOIN customers c ON(p.prod_id=s.prod_id)ON(s.cust_id=c.cust_id) WHERE c.cust_city=Tokyo;C. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales s ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id)AND c.cust_city=Tokyo;D. SELECT c.cust_id, c.cust_last_

24、name, p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales sUSING(prod_id)JOIN customers c USING(cust_id)WHERE c.cust_city=Tokyo; Answer: A, C16.View the Exhibit and evaluate the structure and data in the CUST_STATUS tableYou issue the following SQL statementSQL SELECT custno, NVL2(NUL

25、LIF(amt_spent,credit_limit), 0, 1000) BONUS FROM cust_status;Which statement is true regarding the execution of the above query?A. It produces an error because the AMT_SPENT column contains a null valueB. It displays a bonus of 1000forall customers whose AMT_SPENT is less than CREDIT_LIMITC. It disp

26、lays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nullD. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functionAnswer: C17. Examine the structure and data in the PRICE LIST tableYou plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.Which SQL statement would give the required result?A. SELECT TO_CHAR(prod_price* .2