Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 21:

  Examine the description of the PRODUCTS table:

  

  Which three queries use valid expressions?

  A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;

  B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

  C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

  D. SPLECT product_id, expiry_date * 2 FROM products;

  E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;

  F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;

  Correct Answer: BCE

• Question 22:

  Which three statements are true about multiple row subqueries?

  A. They can contain HAVING clauses.

  B. Two or more values are always returned from the subquery.

  C. They cannot contain subquery.

  D. They can return multiple columns.

  E. They can contain GROUP BY clauses.

  Correct Answer: ADE

• Question 23:

  Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS

  A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

  B. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

  C. CURRENT_DATE returns the current date and time as per the session time zone

  D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

  E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

  F. SYSDATE can be queried only from the DUAL table.

  Correct Answer: CE

• Question 24:

  Which two statements are true about an Oracle database?

  A. A table can have multiple primary keys.

  B. A table can have multiple foreign keys.

  C. A NUMBER column without data has a zero value.

  D. A column definition can specify multiple data types.

  E. A VARCHAR2 column without data has a NULL value.

  Correct Answer: BE

• Question 25:

  Examine the description of the BRICKS table;

  

  Examine the description of the BRICKS_STAGE table;

  

  Which two queries execute successfully?

  A. SELECT shape,color,weight from bricks MINUS SELECT * FROM bricks_stage;

  B. SELECT shape,color FROM bricks MINUS SELECT WEIGHT,color FROM bricks_stage;

  C. select * from bricks MINUS select * from bricks_stage;

  D. SELECT shape,color FROM bricks MINUS SELECT color,shape FROM bricks_stage;

  E. SELECT brick_id,shape FROM bricks MINUS SELECT WEIGHT,COLOR from bricks_stage;

  Correct Answer: DE

• Question 26:

  Examine these statements executed in a single Oracle session:

  

  Which three statements are true?

  A. The code for pen is 10.

  B. There is no row containing fountain pen.

  C. There is no row containing pencil.

  D. The code for pen is 1.

  E. The code for fountain pen is 3

  F. There is no row containing pen

  Correct Answer: ACE

• Question 27:

  Examine this query:

  SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;

  What is returned upon execution?

  A. 0 rows

  B. an error

  C. 8 rows

  D. 6 rows

  E. 1 row

  F. 3 rows

  Correct Answer: E

• Question 28:

  Examine this query:

  SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;

  What is the result?

  A. 16

  B. 160

  C. 150

  D. 200 E. 100

  Correct Answer: D

• Question 29:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose COST_LAST_MANE values are Anderson and Ausson. Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

  B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;

  C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;

  D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;

  Correct Answer: A

• Question 30:

  Examine the data in the PRODUCTS table:

  

  Examine these queries:

  1.

  SELECT prod name, prod list FROM products WHERE prod 1ist NOT IN(1020) AND category _id=1;

  2.

  SELECT prod name, | prod _ list FROM products WHERE prod list < > ANY (1020) AND category _id= 1; SELECT prod name, prod _ list FROM products WHERE prod_ list <> ALL (10 20) AND category _ id= 1; Which queries generate the same output?

  A. 1 and 3

  B. 1, 2 and 3

  C. 2 and 3

  D. 1 and 2

  Correct Answer: A