Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 161:

  You issued this command:

  DROP TABLE hr.employees;

  Which three statements are true? (Choose three.)

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

  B. Synonyms for HR.EMPLOYEES are dropped.

  C. Views referencing HR.EMPLOYEES are dropped.

  D. All constraints defined on HR.EMPLOYEES are dropped.

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

  F. All indexes defined on HR.EMPLOYEES are dropped

  Correct Answer: DEF

• Question 162:

  Which two statements are true about views? (Choose two.)

  A. The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.

  B. The WITH CHECK clause prevents certain rows from being displayed when querying the view.

  C. Views can be indexed.

  D. Views can be updated without the need to re-grant privileges on the view.

  E. Tables in the defining query of a view must always exist in order to create the view.

  Correct Answer: AD

• Question 163:

  Examine the description of the EMPLOYEES table:

  

  Which two statements will insert a row into the EMPLOYEES table? (Choose two.)

  A. INSERT INTO employees VALUES (101, 'John', 'Smith', 12000, SYSDATE);

  B. INSERT INTO employees VALUES (101, 'John', 'Smith', 10, 12000, SYSDATE);

  C. INSERT INTO employees (employee_id, salary, first_name, hiredate, last_name) VALUES (101, 12100, 'John', SYSDATE, 'Smith');

  D. INSERT INTO employees (employee_id, first_name, last_name, salary, hiresate) VALUES ( (SELECT 101, 'John', 'Smith'. 12000, SYSDATE FROM dual) );

  E. INSERT INTO employees SELECT 101, 'John', 'Smith', 12000, (SELECT SYSDATE FROM dual), 10 FROM dual;

  F. INSERT INTO employees VALUES (101, 'John', ' ', 12000, SYSDATE, 10);

  Correct Answer: CE

• Question 164:

  Which two are true about unused columns? (Choose two.)

  A. Setting an indexed column to unused results in an error.

  B. You can query the data dictionary to see the names of unused columns.

  C. You can specify multiple column names in an ALTER TABLE...SET UNUSED statement.

  D. If you set all the columns of a table to unused, the table is automatically dropped.

  E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

  Correct Answer: CE

• Question 165:

  Examine data in the BRICKS table:

  

  Examine the BOXES table:

  

  Which two queries only return CUBE? (Choose two.)

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: CD

• Question 166:

  Examine these statements which execute successfully:

  

  Both statements display departments ordered by their average salaries.

  Which two are true? (Choose two.)

  A. Both statements will execute successfully if you add E.AVG_SAL to the select list.

  B. Both statements will display departments with no employees.

  C. Only the second statement will execute successfully if you add E.AVG_SAL to the select list.

  D. Only the first statement will display departments with no employees.

  E. Only the second statement will display departments with no employees.

  F. Only the first statement will execute successfully if you add E.AVG_SAL to the select list.

  Correct Answer: BC

• Question 167:

  Examine the description of the EMPLOYEES table:

  

  Examine these requirements:

  1.

  Display the last name, date of hire and the number of years of service for each employee.

  2.

  If the employee has been employed 5 or more years but less than 10, display "5+ years of service".

  3.

  If the employee has been employed 10 or more years but less than 15, display "10+ years of service".

  4.

  If the employee has been employed 15 or more years, display "15+ years of service".

  5.

  If none of these conditions matches, display "<5 years of service".

  6.

  Sort the results by the HIRE_DATE column.

  Which statement satisfies all the requirements?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: D

  SELECT e.last_name, hire_date, sysdate,

  (CASE WHEN (sysdate -TO_YMINTERVAL('15-0')) >= hire_date THEN '15 years of service'

  WHEN (sysdate -TO_YMINTERVAL('10-0')) >= hire_date THEN '10 years of service'

  WHEN (sysdate - TO_YMINTERVAL('5-0')) >= hire_date THEN '5 years of service'

  ELSE '<5 years of service' END) AS years

  FROM employees

  ORDER BY hire_date

• Question 168:

  Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

  A. Create a directory object for a flat file.

  B. Create a directory object for an external table.

  C. Read data from an external table and load it into a table in the database.

  D. Execute DML statements on an external table.

  E. Read data from a table in the database and insert it into an external table.

  F. Query data from an external table.

  Correct Answer: CEF

• Question 169:

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

  Which two queries execute successfully? (Choose two.)

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

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

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

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

  E. SELECT TO_CHAR(NVL(cust_credit_iimit * .15, 'Not Available')) FROM customers;

  Correct Answer: BD

• Question 170:

  Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

  A. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

  B. A column that is set to UNUSED still counts towards the limit of 1000 columns per table.

  C. A DROP COLUMN command can be rolled back.

  D. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

  E. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

  F. Partition key columns cannot be dropped.

  Correct Answer: BDF