Oracle Oracle Database 1Z0-071 Questions & Answers

• Question 371:

  The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.

  Which two queries execute successfully?

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

  B. SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;

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

  D. SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;

  E. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;

  Correct Answer: DE

• Question 372:

  Which two queries execute successfully?

  A. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

  B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

  C. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;

  D. select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;

  E. SELECT SYSDATE "INTERRVAL '1' DAY FROM DUAL;

  Correct Answer: BC

• Question 373:

  Which statement is true about aggregate functions?

  A. The AVG function implicitly converts NULLS to zero

  B. The MAX and MIN functions can be used on columns with character data types

  C. Aggregate functions can be used in any clause of a SELECT statement

  D. Aggregate functions can be nested to any number of levels

  Correct Answer: B

• Question 374:

  Which two statements are true about the COUNT function?

  A. It can only be used for NUMBER data types.

  B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

  C. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

  D. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

  E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

  Correct Answer: BC

• Question 375:

  Which three statements are true about defining relations between tables in a relational database?

  A. Foreign key columns allow null values.

  B. Unique key columns allow null values

  C. Primary key columns allow null values.

  D. Every primary or unique key value must refer to a matching foreign key value.

  E. Every foreign key value must refer to a matching primary or unique key value.

  Correct Answer: ABE

• Question 376:

  In Which three situations does a new transaction always start?

  A. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

  B. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

  C. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

  D. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

  E. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

  F. When issuing a DML statement after a DML statement filed in the same session.

  Correct Answer: ABE

• Question 377:

  You execute these commands:

  CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));

  INSERT INTO customers VALUES (1`Custmoer1 `);

  SAVEPOINT post insert;

  INSERT INTO customers VALUES (2, 'Customer2 `);

  SELECTCOUNT (*) FROM customers;

  Which two, used independently, can replace so the query retums 1?

  A. ROLLBACK;

  B. COMMIT;

  C. ROLIBACK TO SAVEPOINT post_ insert;

  D. CONOIT TO SAVEPOINT post_ insert;

  E. ROLLEBACK TO post_ insert;

  Correct Answer: CE

• Question 378:

  You issued this command: DROP TABLE hr. employees; Which three statements are true?

  A. ALL constraints defined on HR, EMPLOYEES are dropped.

  B. The HR. EMPLOYEES table may be moved to the recycle bin.

  C. Synonyms for HR EMPLOYEES are dropped.

  D. Sequences used to populate columns in the HR. EMPLOYEES table are dropped.

  E. All indexes defined on HR, EMPLOYEES are dropped.

  F. Views referencing HR, EMPLOYEES are dropped.

  Correct Answer: ABE

• Question 379:

  Examine this business rule:

  Each student can work on multiple projects and earth project can have multiple students.

  You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:

  STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

  Which two statements are true?

  A. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, Which is the foreign key linked to the STUDENTS and PROJECTS entities.

  B. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

  C. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

  D. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

  E. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

  Correct Answer: AB

• Question 380:

  Examine this query:

  SELECT employee_id, first_name, salary

  FROM employees

  WHERE hiredate > 61*

  Which two methods should yours to prevent prompting for hire date value when this queries executed?

  A. Execute the SET VERIFY ON command before executing the query.

  B. Execute the SET VERIFY OFF command before executing the query.

  C. Store the query in a script and pass the substitution value to the script when executing it.

  D. Replace 's1' with and1'in the query:

  E. Use the UNDEFINE command before executing the query.

  F. Use the DEFINE command before executing the query

  Correct Answer: CF