Oracle 1Z0-071 Study Guides 2021

NEW QUESTION 1
Which two statements are true regarding roles? (Choose two.)

• A. A role can be granted to itself.
• B. A role can be granted to PUBLIC.
• C. A user can be granted only one role at any point of time.
• D. The REVOKE command can be used to remove privileges but not roles from other users.
• E. Roles are named groups of related privileges that can be granted to users or other roles.

Answer: BE

Explanation: References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28

NEW QUESTION 2
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

• A. SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• B. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Answer: B

NEW QUESTION 3
Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

• A. ALTER TABLE SPLIT PARTITION
• B. ALTER TABLE MERGE PARTITION
• C. ALTER TABLE TRUNCATE PARTITION
• D. ALTER TABLE ADD PARTITION
• E. ALTER TABLE DROP PARTITION
• F. ALTER TABLE MOVE PARTITION

Answer: CE

NEW QUESTION 4
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It produces an error because subqueries cannot be used with the CASE expression.
• B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
• C. It shows COST_REMARK for all the promos in the table.
• D. It produces an error because the subquery gives an error.

Answer: C

NEW QUESTION 5
Examine the structure of the MEMBERS table: NameNull?Type
------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?

• A. It fails because the alias name specified after the column names is invalid.
• B. It fails because the space specified in single quotation marks after the first two column names is invalid.
• C. It executes successfully and displays the column details in a single column with only the alias column heading.
• D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

NEW QUESTION 6
You issued the following command: SQL> DROP TABLE employees; Which three statements are true?

• A. All uncommitted transactions are committed.
• B. All indexes and constraints defined on the table being dropped are also dropped.
• C. Sequences used in the employees table become invalid.
• D. The space used by the employees table is reclaimed immediately.
• E. The employees table can be recovered using the rollback command.
• F. The employees table is moved to the recycle bin

Answer: ABF

NEW QUESTION 7
View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?

• A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total”FROM EMPLOYEES;
• B. SELECT first_name, salary, salary*12+salary*commission_pct “Total”FROM EMPLOYEES;
• C. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”FROM EMPLOYEES;
• D. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, “Total”FROM EMPLOYEES;

Answer: A

NEW QUESTION 8
Which three tasks can be performed using SQL functions built into Oracle Database?

• A. displaying a date in a nondefault format
• B. finding the number of characters in an expression
• C. substituting a character string in a text expression with a specified string
• D. combining more than two columns or expressions into a single column in the output

Answer: ABC

NEW QUESTION 9
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 10
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.

Answer: B

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

NEW QUESTION 11
Which two statements are true regarding constraints?

• A. A foreign key column cannot contain null values.
• B. A column with the UNIQUE constraint can contain null values.
• C. A constraint is enforced only for INSERT operation on the table.
• D. A constraint can be disabled even if the constraint column contains data.
• E. All constraints can be defined at the column level and at the table level.

Answer: BD

NEW QUESTION 12
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

• A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
• B. It would execute but would return no rows.
• C. It would not execute because the same column has been used twice with the AND logical operator.
• D. It would execute and return the desired.

Answer: B

NEW QUESTION 13
Which two statements are true regarding single row functions? (Choose two.)

• A. MOD : returns the quotient of a division.
• B. TRUNC : can be used with NUMBER and DATE values.
• C. CONCAT : can be used to combine any number of values.
• D. SYSDATE : returns the database server current date and time.
• E. INSTR : can be used to find only the first occurrence of a character in a string.
• F. TRIM : can be used to remove all the occurrences of a character from a string.

Answer: BD

NEW QUESTION 14
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_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);
• B. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);
• C. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id);
• D. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

Answer: A

NEW QUESTION 15
Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

• A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
• B. They are evaluated by the first WHEN claus
• C. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
• D. They are evaluated by the first WHEN claus
• E. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
• F. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

Answer: A

Explanation: References:
http://psoug.org/definition/WHEN.htm

NEW QUESTION 16
Evaluate the following query:

Which is the correct output of the above query?

• A. +00-300, +54-02,+00 11:12:10.123457
• B. +00-300,+00-650,+00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00,+00-650,+00 11:12:10.123457

Answer: C

NEW QUESTION 17
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.)
SQL > CREATE TABLE transaction (trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE, End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

• A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
• B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
• C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
• D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Answer: C

NEW QUESTION 18
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

• A. WHERE and HAVING clauses cannot be used together in a SQL statement.
• B. The HAVING clause conditions can have aggregate functions.
• C. The HAVING clause conditions can use aliases for the columns.
• D. The WHERE clause is used to exclude rows before the grouping of data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer: ABD