Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 181:

  Which two statements are true? (Choose two.)

  A. The USER SYNONYMS view can provide information about private synonyms.
  B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
  C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
  D. The USER OBJECTS view can provide information about the tables and views created by the user only.
  E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
  A. The USER SYNONYMS view can provide information about private synonyms.
  E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

• Question 182:

  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) ;
  B. GROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);

• Question 183:

  View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

  

  Examine this query Which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.

  SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'

  SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';

  Choose two correct SET operators Which would cause the query to return the desired result.

  A. UNION
  B. MINUS
  C. INTERSECT
  D. UNION ALL
  A. UNION
  D. UNION ALL

• Question 184:

  Examine this schema information:

  1.

  EMPLOYEES.DEPARTMENT_ID has a foreign key referencing DEPARTMENTS.DEPARTMENT_ID.

  2.

  EMP_VIEW is based on the EMPLOYEES and DEPARTMENTS tables.

  3.

  EMP_VIEW has columns EMPLOYEE_ID, EMPLOYEE_NAME and DEPARTMENT_NAME.

  You must add a new column, MANAGER_ID, from the EMPLOYEES table, to the view, showing each employee's manager.

  Which statement will do this?

  A. ALTER VIEW emp_view ADD (employee.manager_id);
  B. ALTER VIEW emp_view MODIFY ( SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id);
  C. ALTER VIEW emp_view ADD (SELECT manager_id FROM employees);
  D. CREATE OR REPLACE VIEW emp_view AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
  D. CREATE OR REPLACE VIEW emp_view AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

• Question 185:

  You must find the number of employees whose salary is lower than employee 110.

  Which statement fails to do this?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 186:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.

  Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
  B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
  C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
  D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
  A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;

• Question 187:

  Examine the command to create the BOOKS table.

  

  The BOOK_ID value 101 does not exist in the table. Examine the SQL statement:

  

  Which statement is true?

  A. It executes successfully and the row is inserted with a null PLBLISHER_ID.
  B. It executes successfully only if NULL is explicitly specified in the INSERT statement.
  C. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
  D. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
  A. It executes successfully and the row is inserted with a null PLBLISHER_ID.

• Question 188:

  Evaluate the following SQL statements that are issued in the given order:

  CREATE TABLE emp

  (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,

  ename VARCHAR2(15),

  salary NUMBER (8,2),

  mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

  ALTER TABLE emp

  DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

  ALTER TABLE emp

  ENABLE CONSTRAINT emp_emp_no_pk;

  What would be the status of the foreign key EMP_MGR_PK?

  A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
  B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
  C. It would be automatically enabled and immediate.
  D. It would be automatically enabled and deferred.
  B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

• Question 189:

  The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE

  You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week Which query can be used?

  A. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
  B. SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
  C. SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
  D. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
  A. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

• Question 190:

  Which statement is true about transactions?

  A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
  B. Each Data Definition Language (DDL) statement executed forms a single transaction.
  C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
  D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
  B. Each Data Definition Language (DDL) statement executed forms a single transaction.

  ---

  Explanation/Reference:

  References: https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038