1z0-071 Practice Exam Tests Latest Updated on Aug-2021 [Q120-Q135]

1z0-071 Practice Exam Tests Latest Updated on Aug-2021

Pass 1z0-071 Exam in First Attempt Guaranteed Dumps!

NEW QUESTION 120
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. (Choose two.)

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

Answer: A,B

 

NEW QUESTION 121
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?

• A. DELETE orders o, order_items IWHERE o.order_id = i.order_id;
• B. DELETE order_idFROM ordersWHERE order_total < 1000;
• C. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• D. DELETE ordersWHERE order_total < 1000;

Answer: C

 

NEW QUESTION 122
Examine the structure of the EMPLOYEES table.

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?

• A. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
• B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
• C. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
• D. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;

Answer: A

 

NEW QUESTION 123
You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

• A. Any uncommitted transaction in the session is committed.
• B. All the table's indexes if any exist, are invalidated but retained.
• C. Table data and the table structure are deleted.
• D. Table data is deleted but the table structure is retained.
• E. All existing views and synonyms that refer to the table are invalidated but retained.

Answer: B,C,E

 

NEW QUESTION 124
Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

• A. executing operating system (OS) commands in a session
• B. querying data from tables in different databases
• C. changing the password for an existing database user
• D. connecting to a database instance
• E. starting up a database instance

Answer: B,C

Explanation:
Explanation/Reference:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

 

NEW QUESTION 125
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID- must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME- contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?

• A. The EXECUTION_DATETIME must be of TIMESTAMP data type.
• B. The COMPONENT_IDmust be of ROWID data type.
• C. The COMPONENT_IDmust be of VARCHAR2 data type.
• D. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
• E. The COMPONENT_IDcolumn must be of CHAR data type.
• F. The EXECUTION_DATETIME must be of DATE data type.

Answer: E,F

 

NEW QUESTION 126
Which two are true about external tables that use the ORACLE_DATAPUMPaccess driver? (Choose two.)

• A. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
• B. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
• C. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
• D. Creating an external table creates a directory object.
• E. Creating an external table creates a dump file that can be used only by an external table in the same database.

Answer: C,D

Explanation:
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
You must create the directory object before you create the external table.
Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
https://docs.oracle.com/cd/E11882_01/server.112/e22490/et_concepts.htm#SUTIL011

 

NEW QUESTION 127
Which three statements are true about dropping and unused columns in an Oracle database?

• A. An UNUSED column's space is remained automatically when the block containing that column is next queried.
• B. A column that is set to NNUSED 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. Partition key columns cannot be dropped.
• F. An UNUSED column's space is remained automatically when the row containing that column is next queried.

Answer: B,D,E

 

NEW QUESTION 128
View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

• A. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)
• B. SELECT order_idFROM order_itemsWHERE(unit_price*quantity)
  MAX(unit_price*quantity)GROUP BY order_id);
• C. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);
• D. SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

Answer: D

 

NEW QUESTION 129
Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?

• A. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.
• B. DELTE and TRUNCATE statements can have a rollback done to restore data into a table.
• C. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.
• D. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table.

Answer: A

 

NEW QUESTION 130
Which two statements are true about a self join? (Choose two.)

• A. The join key column must have an index.
• B. It must be a full outer join.
• C. It can be an inner join.
• D. It can be a left outer join.
• E. It must be an equijoin.

Answer: C,D

 

NEW QUESTION 131
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?

• A. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• B. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date,
  COUNT(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;
• C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date,
  COUNT(i.line_item_id)||'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_dateWHITH CHECK OPTION;
• D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date,
  COUNT(i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id,o.order_date;

Answer: B

 

NEW QUESTION 132
Which two statements are true about the rules of precedence for operators? (Choose two.)

• A. Arithmetic operators with equal precedence area evaluated from left to right within an expression
• B. The + binary operator has the highest precedence in an expression in a SQL statement
• C. Multiple parentheses can be used to override the default precedence of operators in an expression
• D. NULLS influence the precedence of operators in an expression
• E. The concatenation operator | | is always evaluated before addition and subtraction in an expression

Answer: A,C

 

NEW QUESTION 133
The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

• A. It recovers the table structure and data but not the related indexes.
• B. It recovers the table structure, data, and the indexes.
• C. It is not possible to recover the table structure, data, or the related indexes.
• D. It recovers only the table structure.

Answer: C

Explanation:
Explanation
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

 

NEW QUESTION 134
Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

• A. Every relationship between the two tables must be implemented in a join condition
• B. An inner join relates rows within the same table
• C. Relating data from a table with data from the same table is implemented with a self join
• D. Rows from unrelated tables cannot be joined
• E. Implementing a relationship between two tables might require joining additional tables

Answer: B,E

 

NEW QUESTION 135
......

Oracle PL/SQL Developer Certified Associate  Free Certification Exam Material from ExamDumpsVCE with 305 Questions: https://www.examdumpsvce.com/1z0-071-valid-exam-dumps.html

1z0-071 Dumps Full Questions - Exam Study Guide: https://drive.google.com/open?id=1oNlLz2fBY1XjD4nwghkQrdMYYMo80TUg