Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 161:

  Which three are true about the MERGE statement? (Choose three.)

  A. It can use subqueries to produce source rows.
  B. It can update the same row of the target table multiple times.
  C. It can update, insert, or delete rows conditionally in multiple tables.
  D. It can use views to produce source rows.
  E. It can merge rows only from tables.
  F. It can combine rows from multiple tables conditionally to insert into a single table.
  A. It can use subqueries to produce source rows.
  D. It can use views to produce source rows.
  F. It can combine rows from multiple tables conditionally to insert into a single table.

• Question 162:

  Examine the BRICKS table: You write this query:

  

  SELECT

  FROM bricks b1 CROSS JOIN bricks b2

  WHERE b1. Weight < b2. Weight:

  How many rows will the query return?

  A. 1
  B. 16
  C. 10
  D. 6
  E. 4
  D. 6

• Question 163:

  Examine the description of the EMPLOYEES table:

  

  The session time zone is the same as the database server

  Which two statements will list only the employees who have been working with the company for more than five years? (Choose two.)

  A. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 12 > 5;
  B. SELECT employee_name FROM employees WHERE (SYSTIMSSTAMF - hire_date) / 12 > INTERVAL '5' YEAR;
  C. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 12 > 5
  D. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 365 >
  E. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;
  F. SELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 365 > INTERVAL '1825' DAY;
  D. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 365 >
  E. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;

• Question 164:

  Which statement will return the last sequence number generated by the EMP_SEQ sequence?

  A. SELECT CURRVAL FROM emp_seq;
  B. SELECT emp_seq.CURRVAL FROM DUAL;
  C. SELECT NEXTVAL FROM emp_seq;
  D. SELECT emp_seq.NEXTVAL FROM DUAL;
  B. SELECT emp_seq.CURRVAL FROM DUAL;

• Question 165:

  View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

  

  Evaluate the following SQL statement:

  

  What would be the outcome of the above SQL statement?

  A. It displays prod IDs in the promo with the lowest cost.
  B. It displays prod IDs in the promos with the lowest cost in the same time interval.
  C. It displays prod IDs in the promos with the highest cost in the same time interval.
  D. It displays prod IDs in the promos Which cost less than the highest cost in the same time interval.
  D. It displays prod IDs in the promos Which cost less than the highest cost in the same time interval.

• Question 166:

  Examine the structure of the PROGRAMS table:

  

  Which two SQL statements would execute successfully?

  A. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
  B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
  C. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), `Ongoing') FROM programs;
  D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), `Ongoing') FROM programs
  B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
  D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), `Ongoing') FROM programs

• Question 167:

  View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

  

  You want to update EMPLOYEES table as follows:

  Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

  Set department_id for these employees to the department_id corresponding to London (location_id 2100).

  Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.

  Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

  You issue the following command:

  

  What is outcome?

  A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
  B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
  C. It executes successfully and gives the desired update
  D. It executes successfully but does not give the desired update
  D. It executes successfully but does not give the desired update

• Question 168:

  Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined? (Choose three.)

  A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
  B. Delete statements can always be done on a table through a view.
  C. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
  D. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
  E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword,
  F. Insert statements can always be done on a table through a view.
  C. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
  D. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
  E. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword,

• Question 169:

  Which two queries execute successfully? (Choose two.)

  A. SELECT NULLIF (NULL, 100) FROM DUAL;
  B. SELECT NULLIF (100, `A') FROM DUAL;
  C. SELECT NULLIF (100, 100) FROM DUAL;
  D. SELECT COALESCE (100, NULL, 200) FROM DUAL;
  E. SELECT COALESCE (100, `A') FROM DUAL;
  C. SELECT NULLIF (100, 100) FROM DUAL;
  D. SELECT COALESCE (100, NULL, 200) FROM DUAL;

• Question 170:

  Examine the description of the CUSTOMERS table:

  

  You want to display details of all customers who reside in cities starting with the letter D followed by at least two character. Which query can be used?

  A. SELECT * FROM customers WHERE city ='D_%';
  B. SELECT * FROM customers WHERE city ='%D_';
  C. SELECT * FROM customers WHERE city LIKE'D %';
  D. SELECT * FROM customers WHERE city LIKE'D_';
  C. SELECT * FROM customers WHERE city LIKE'D %';