Oracle 1Z0-071 Exam Questions and Answers 2021

NEW QUESTION 1
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SQL>SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?

• A. The statement would execute successfully to produce the required output.
• B. The statement would not execute because inline views and outer joins cannot be used together.
• C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
• D. The statement would not execute because the GROUP BY clause cannot be used in the inline.

Answer: A

NEW QUESTION 2
Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE; What does ON DELETE CASCADE imply?

• A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
• B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted butthe table structure is retained.
• C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
• D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Answer: C

NEW QUESTION 3
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 4
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

• A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
• B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

Answer: C

NEW QUESTION 5
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

• A. It displays all cities in descending order, within which the last names are further sorted in descending order.
• B. It fails because a column alias cannot be used in the ORDER BY clause.
• C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• D. It displays all cities in ascending order, within which the last names are further sorted in descending order.

Answer: D

NEW QUESTION 6
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

• A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
• B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
• C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
• D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOINinventories iON (pi.product_id=i.product_id)USING (product_id) AND quantity_on_hand < 5;

Answer: AC

NEW QUESTION 7
See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause.
• B. It executes successfully and gives the required result.
• C. It generates an error because CASE cannot be used with group functions.
• D. It generates an error because NULL cannot be specified as a return value.

Answer: B

Explanation: CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END

NEW QUESTION 8
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 9
Which three statements are true regarding subqueries? (Choose three.)

• A. The ORDER BY Clause can be used in a subquery.
• B. A subquery can be used in the FROM clause of a SELECT statement.
• C. If a subquery returns NULL, the main query may still return rows.
• D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
• E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Answer: ABC

NEW QUESTION 10
Which two statements are true regarding working with dates? (Choose two.)

• A. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
• B. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
• C. The default internal storage of dates is in character format.
• D. The default internal storage of dates is in numeric format.

Answer: AD

NEW QUESTION 11
You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

• A. SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL
• B. SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL
• C. SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL
• D. SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL

Answer: C

NEW QUESTION 12
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true regarding the above command?

• A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
• B. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
• C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

Answer: A

NEW QUESTION 13
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement: ALTER TABLE emp
DROP COLUMN first_name; Which two statements are true?

• A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
• B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
• C. The FIRST_NAME column would be dropped provided it does not contain any data.
• D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Answer: B

NEW QUESTION 14
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. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• C. DELETE ordersWHERE order_total < 1000;
• D. DELETE order_idFROM ordersWHERE order_total < 1000;

Answer: B

NEW QUESTION 15
Which statement is true regarding the default behavior of the ORDER BY clause?

• A. In a character sort, the values are case-sensitive.
• B. NULL values are not considered at all by the sort operation.
• C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
• D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Answer: A

NEW QUESTION 16
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?

• A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
• B. SELECT promo_category, DISTINCT promo_cost FROM promotions
• C. SELECT DISTINCT promo_cost, promo_category FROM promotions
• D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Answer: A

NEW QUESTION 17
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)

The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them. The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?

• A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details dON (p.based_on = d.task_id);
• C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
• D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details dON (p.based_on = d.task_id);

Answer: D

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

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

Answer: BCD