Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 11:

  View the Exhibit and examine the structure of the ORDER_ITEMS table.

  

  You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

  Which query would produce the desired result?

  A. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);

  B. SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;

  C. SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;

  D. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)

  Correct Answer: A

• Question 12:

  View the Exhibit and examine the structure of the EMP table Which is not partitioned and not an index-organized table. (Choose two.)

  

  Evaluate this SQL statement:

  ALTER TABLE emp

  DROP COLUMN first_name;

  Which two statements are true?

  A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

  B. The FIRST_NAME column would be dropped provided at least one column remains in the table.

  C. The FIRST_NAME column would be dropped provided it does not contain any data.

  D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

  Correct Answer: B

• Question 13:

  Evaluate the following CREATE TABLE commands:

  CREATE_TABLE orders

  (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,

  ord_date DATE,

  cust_id NUMBER (4) );

  CREATE TABLE ord_items

  (ord _no NUMBER (2),

  item_no NUMBER(3),

  qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),

  expiry_date date CHECK (expiry_date> SYSDATE),

  CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),

  CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );

  The above command fails when executed. What could be the reason?

  A. SYSDATE cannot be used with the CHECK constraint.

  B. The BETWEEN clause cannot be used for the CHECK constraint.

  C. The CHECK constraint cannot be placed on columns having the DATE data type.

  D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

  Correct Answer: A

• Question 14:

  Examine the structure of the PROGRAMS table:

  

  Which two SQL statements would execute successfully?

  A. SELECT NVL (ADD_MONTHS (END_DATE,1) 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_BETWEEN (start-date, end_date)), `Ongoing') FROM programs

  Correct Answer: BD

• Question 15:

  View the Exhibit and examine the structure of the CUSTOMERS table.

  

  Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

  Customers with no credit limit should have "Not Available" displayed.

  Which SQL statement would produce the required result?

  A. SELECT NVL(TO_CHAR(cust_credit_limit*.15), `Not Available') "NEW CREDIT" FROM customers;

  B. SELECT TO_CHAR(NVL(cust_credit_limit*.15, `Not Available')) "NEW CREDIT" FROM customers;

  C. SELECT NVL(cust_credit_limit*.15, `Not Available') "NEW CREDIT" FROM customers;

  D. SELECT NVL(cust_credit_limit, `Not Available')*.15 "NEW CREDIT" FROM customers;

  Correct Answer: A

• Question 16:

  Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

  A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and Which is cached in memory, is always reused for the current execution.

  B. During execution, the Oracle server may read data from storage if the required data is not already in memory.

  C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

  D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

  Correct Answer: B

• Question 17:

  View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

  

  Evaluate this SQL statement:

  SELECT TO_CHAR (list_price, '$9,999')

  From product_information;

  Which two statements are true regarding the output? (Choose two.)

  A. A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.

  B. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.

  C. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.

  D. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

  Correct Answer: AC

• Question 18:

  You must create a SALES table with these column specifications and data types: (Choose the best answer.)

  SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified

  Which statement would create the table?

  A. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");

  B. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT 'SYSDATE', payment VARCHAR2(30) DEFAULT CASH);

  C. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");

  D. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');

  Correct Answer: D

• Question 19:

  View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

  

  Examine this query Which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.

  SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'

  SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';

  Choose two correct SET operators Which would cause the query to return the desired result.

  A. UNION

  B. MINUS

  C. INTERSECT

  D. UNION ALL

  Correct Answer: AD

• Question 20:

  View the Exhibit and examine the description of the ORDERS table.

  

  Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

  A. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )

  B. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')

  C. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'

  D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

  Correct Answer: CD