Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 1:

  Which two statements execute successfully?

  A. SELECT TO_ DATE('2019-DEC-25 15:30' 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE =AMERICAN' ) FROM DUAL;

  B. SELECT TO_CHAR('2019-DEC-25 15:30" YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE = AMERICAN') FROM DUAL;

  C. SELECT TO _DATE (TO_ CHAR (`2019-DEC-25 03:30', `YYYY-MON-DD HH12:MI')) FROM DUAL;

  D. SELECT TO _ CHAR (TO_ DATE (`2019-DEC-25 03:30','YYYY-MON-DD HH12:MI')) FROM DUAL

  E. SELECT TO _ CHAR (`2019-DEC-25 15:30'.'YYYY-MON-DD HH24:MI') FROM DUAL

  Correct Answer: AD

• Question 2:

  Which two statements will return the names of the three employees with the lowest salaries?

  A. SELECT last_name, salary FROM employees WHERE ROWNUM<=3

  B. SELECT last_name,salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;

  C. SELECT last_name,salary FROM employees WHERE ROWNUM<=3 ORDER BY (SELECT salary FROM employees);

  D. SELECT last_name,salary FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM <=3;

  E. SELECT last_name,salary FROM employees FETCH FIRST 3 ROWS ONLY ORDER BY salary;

  Correct Answer: BD

• Question 3:

  Examine the description of the SALES table:

  

  The SALES table has 5,000 rows.

  Examine this statement:

  CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)

  AS

  SELECT product_id, customer_id, quantity_sold, price

  FROM sales

  WHERE 1=1

  Which two statements are true?

  A. SALES1 is created with 1 row.

  B. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns Which had those constraints in the SALES table.

  C. SALES1 Is created with 5,000 rows.

  D. SALES1 has NOT NULL constraints on any selected columns Which had those constraints in the SALES table.

  Correct Answer: CD

• Question 4:

  You execute these commands successfully:

  CREATE GLOBAL TEMPORARY TABLE invoices _ gtt

  ( customer id INTEGER, invoice_ total NUMBER (10, 2) ) ON COMMIT PRESERVE ROWS;

  INSERT INTO invoices_ gtt VALUES (1 100);

  COMMIT;

  Which two are true?

  A. You can add a foreign key to the table.

  B. When you terminate your session, the row will be deleted.

  C. To drop the table in this session, you must first truncate it.

  D. You can add a column to the table in this session.

  E. Other sessions can view the committed row.

  Correct Answer: BD

• Question 5:

  Examine this partial command:

  

  Which two clauses are required for this command to execute successfully?

  A. the DEFAULT DIRECTORY clause

  B. the REJECT LIMIT clause

  C. the LOCATION clause

  D. the ACCESS PARAMETERS clause

  E. the access driver TYPE clause

  Correct Answer: AC

• Question 6:

  Which two statements are true about the rules of precedence for operators?

  A. Arithmetic operators with equal precedence are evaluated from left to right within an expression.

  B. Multiple parentheses can be used to override the default precedence of operators in an expression.

  C. The + binary operator has the highest precedence in an expression in a SQL statements.

  D. NULLS influence the precedence of operators in an expression.

  E. The concatenation operator || is always evaluated before addition and subtraction in an expression.

  Correct Answer: AB

• Question 7:

  In your session NLS_ DATE_ FORMAT is set to DD-MON_RR.

  Which two queries display the year as four digits?

  A. SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;

  B. SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;

  C. SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;

  D. SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;

  E. SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;

  F. SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;

  Correct Answer: EF

• Question 8:

  Which three statements are true about the DESCRIBE command?

  A. It can be used from SQL Developer.

  B. It can be used to display the structure of an existing view.

  C. It can be used only from SQL*Plus.

  D. It displays the NOT NULL constraint for any columns that have that constraint.

  E. It displays all constraints that are defined for each column.

  F. It displays the PRIMARY KEY constraint for any column or columns that have that constraint.

  Correct Answer: ABD

• Question 9:

  You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed.

  The variables used in your query are never undefined in your session .

  Which query can be used?

  A. SELECT andco11, andco12 FROM andandtable WHERE andcondition;

  B. SELECT andco11, andco12 FROM "andtable" WHERE andcondition;

  C. SELECT andandco11,andandco12 FROM andtable WHERE andandcondition= andandcond;

  D. SELECT'andco11','andandco12' FROM andtable WHERE'andandcondition' ='andcond';

  E. SELECTandandco11, andandco12 FROM andtable WHERE andandcondition;

  Correct Answer: A

• Question 10:

  Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

  A. The first column in the last SELECT of the compound query

  B. The first NUMBER column in the first SELECT of the compound query

  C. The first VARCHAR2 column in the first SELECT of the compound query

  D. The first column in the first SELECT of the compound query

  E. The first NUMBER or VARCHAR2 column in the last SELECTof the compound query

  Correct Answer: A