Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 181:

  Examine this statement:

  

  Which two statements are true? (Choose two.)

  A. The names of employees earning the maximum salary will appear first in an unspecified order.

  B. All remaining employee names will appear in descending order.

  C. All remaining employee names will appear in an unspecified order.

  D. All remaining employee names will appear in ascending order.

  E. The names of employees earning the maximum salary will appear first in ascending order.

  F. The names of employees earning the maximum salary will appear first in descending order.

  Correct Answer: DF

• Question 182:

  Examine the description of the EMPLOYEES table:

  

  Examine these requirements:

  1.

  Display the manager id and salary of the lowest paid employee for that manager.

  2.

  Exclude anyone whose manager is not known.

  3.

  Exclude any managers where the minimum salary is 6000 or less.

  4.

  Sort the output by minimum salary with the highest salary shown first.

  Which statement will do this?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: D

• Question 183:

  Which three statements are true about the Oracle join and ANSI Join syntax?

  A. The Oracle join syntax only supports right outer joins,

  B. The Oracle join syntax supports creation of a Cartesian product of two tables.

  C. The SQL:1999 compliant ANSI join syntax supports natural joins.

  D. The Oracle join syntax supports natural joins.

  E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

  F. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

  G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.

  Correct Answer: BCF

• Question 184:

  Examine the description of the EMPLOYEES table:

  

  Which statement increases each employeeandapos;s SALARY by the minimum SALARY for their DEPARTM ENT_ID?

  A. UPDATE employees e1 SET salary =(SELECT e2. salary + MIN(e2.salary) FROM employees e2 WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;

  B. UPDATE employees e1 SET salary = salary + (SELECT MIN(e1. salary) FROM employees e2 WHERE e1.department_id = e2 .department_id);

  C. UPDATE employees e1 SET salary = salary+(SELECT MIN (salary) FROM employees e2) ;

  D. UPDATE employees e1 SET salary= (SELECT e1.salary + MIN(e2.salary) FROM employees e2 WHERE e1. department_ id = e2.department_id);

  Correct Answer: D

• Question 185:

  Examine this data in the EMPLOYERS table

  

  Which statement will execute successfully?

  A. SELECT dept_id, INSTR(last_name, `A'), SUM(salary) FROM employees GROUP BY dept_id;

  B. SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY dept_id;

  C. SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY dept_id;

  D. SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id;

  Correct Answer: D

• Question 186:

  Which two are true about unused columns?

  A. The DESCRIBE command displays unused columns

  B. A primary key column cannot be set to unused.

  C. A query can return data from unused columns, but no DML is possible on those columns.

  D. Once a column has been set to unused, a new column with the same name can be added to the table.

  E. A foreign key column cannot be set to unused.

  F. Unused columns retain their data until they are dropped

  Correct Answer: DF

  https://decidhermfosvs.worddress.com/2Q07/11/15/back-to-the-basics-droDDing-unused-columns-in-oracle/

• Question 187:

  Which two statements are true about * _TABLES views?

  A. You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.

  B. USER TABLES displays all tables owned by the current user.

  C. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.

  D. ALL TABLES displays all tables owned by the current user.

  E. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

  F. All users can query DBA_TABLES successfully.

  Correct Answer: AB

• Question 188:

  Examine this partial query:

  

  Examine this output:

  

  Which GROUP BY clause must be added so the query returns the results shown?

  A. GROUP BY ch.channel_type, t.month, co.country code;

  B. GROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);

  C. GROUP BY CUBE (ch. channel_ type, t .month, co. country code);

  D. GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;

  Correct Answer: B

• Question 189:

  The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER.

  Which two queries execute successfully?

  A. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;

  B. SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;

  C. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;

  D. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY COUNT(*)> 10;

  E. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)> 10;

  Correct Answer: AB

• Question 190:

  Examine this list of requirements for a sequence:

  1.

  Name:EMP_SEQ

  2.

  First value returned:1

  3.

  Duplicates are never permitted.

  4.

  Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

  5.

  Reduce the chances of gaps in the values.

  Which two statements will satisfy these requirements?

  A. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

  B. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

  C. CREATE SEQUENCE emp_seq NOCACHE;

  D. CREATE SEQUENCE emp_seq START WITH 1 CACHE;

  E. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

  F. CREATE SEQUENCE emp_seq;

  Correct Answer: AC