Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 41:

  Examine the description of the PRODUCT_ DETAILS table:

  

  Which two statements are true?

  A. PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.

  B. PRODUCT_ ID can be assigned the PRIMARY KEY constraint.

  C. EXPIRY_ DATE cannot be used in arithmetic expressions.

  D. EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.

  E. PRODUCT_ PRICE contains the value zero by default if no value is assigned to it.

  F. PRODUCT_ NAME cannot contain duplicate values.

  Correct Answer: AB

• Question 42:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?

  A. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

  C. A TIMESTAMP data type column contains information about year, month, and day

  D. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  E. The CURRENT_TIMESTAMP function returns data without time zone information

  Correct Answer: ACD

• Question 43:

  Examine this query:

  SELECT SUBSTR (SYSDATE,1,5) `Result' FROM DUAL

  Which statement is true?

  A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

  B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

  C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

  D. It executes successfully with an implicit data type conversion

  Correct Answer: D

• Question 44:

  You need to calculate the number of days from 1st January 2019 until today.

  Dates are stored in the default format of DD-MON-RR.

  Which two queries give the required output?

  A. SELECT SYSDATE-TO_DATE ('01-JANUARY-2019') FROM DUAL;

  B. SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2019' FROM DUAL;

  C. SELECT ROUND (SYSDATE-TO_DATE ('01/JANUARY/2019')) FROM DUAL;

  D. SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2019' FROM DUAL;

  E. SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL:

  Correct Answer: AC

• Question 45:

  For each employee in department 90 you want to display:

  1.

  their last name

  2.

  the number of complete weeks they have been employed

  The output must be sorted by the number of weeks, starting with the longest serving employee first.

  Which statement will accomplish this?

  

  A. SELECT last_name, TRUNC( (SYSDATE - hire_ date) / 7) AS tenure FROM employees WHERE department_ id = 90 ORDER BY tenure ;

  B. SELECT last_name, ROUND( (SYSDATE - hire_ date) / 7) AS tenure FROM employees WHERE department_ id = 90 ORDER BY tenure ;

  C. SELECT last_name, ROUND( (SYSDATE - hire_ date) / 7) AS tenure FROM employees WHERE department_ id = 90 ORDER BY tenure DESC;

  D. SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) / 7) AS tenure FROM employees WHERE department_id = 90 ORDER BY tenure DESC;

  Correct Answer: D

• Question 46:

  Which two actions can you perform with object privileges?

  A. Create roles.

  B. Delete rows from tables in any schema except sys.

  C. Set default and temporary tablespaces for a user.

  D. Create FOREIGN KEY constraints that reference tables in other schemas.

  E. Execute a procedure or function in another schema.

  Correct Answer: AE

  Reference: http://www.cruzroja.es/help/wvtdbobp.htm

• Question 47:

  Which three are true about the MERGE statement

  A. It can merge rows only from tables.

  B. It can use views to produce source rows.

  C. It can combine rows from multiple tables conditionally to insert into a single table.

  D. It can use subqueries to produce source rows.

  E. It can update the same row of the target table multiple times.

  F. It can update, insert, or delete rows conditionally in multiple tables.

  Correct Answer: BCD

• Question 48:

  BOOK_SEQ is an existing sequence in your schema.

  Which two CREATE TABLE commands are valid?

  A. CREATE TABLE bookings ( bk_id NUMBER(4) NOT NULL PRIMARY KEY,

  start_date DATE NOT NULL,

  end_date DATE DEFAULT SYSDATE);

  B. CREATE TABLE bookings ( bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL, start_date DATE NOT NULL, end_date DATE DEFAULT SYSDATE);

  C. CREATE TABLE bookings ( bk_id NUMBER(4) DEFAULT book_seq.CURRVAL, start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT start date);

  D. CREATE TABLE bookings ( bk_id NUMBER(4), start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT (end_date >= start_date));

  E. CREATE TABLE bookings ( bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY, start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT SYSDATE NOT NULL);

  Correct Answer: AE

• Question 49:

  Which two statements are true regarding the UNION ALL operators?

  A. NULLS are not ignored during duplicate checking.

  B. Duplicates are eliminated automatically by the UNION ALL operator

  C. The names of columns selected in each SELECT statement must be identical.

  D. The number of columns selected in each SELECT statement must be identical

  E. The output is sorted by the UNION ALL operator.

  Correct Answer: AD

• Question 50:

  The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.

  Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)

  AS SELECT I prod_id,cust_id,time_id FROM sales.

  Which statement is true regarding the above command?

  A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

  B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

  C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

  D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

  Correct Answer: B