Oracle 1Z0-071 Braindumps 2021

NEW QUESTION 1
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 AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• C. SELECT member_id 'MEMBER ID', due_date '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: B

NEW QUESTION 2
Which two statements are true regarding subqueries? (Choose two.)

• A. A subquery can appear on either side of a comparison operator.
• B. Only two subqueries can be placed at one level.
• C. A subquery can retrieve zero or more rows.
• D. A subquery can be used only in SQL query statements.
• E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Answer: AC

NEW QUESTION 3
Which statement is true about Data Manipulation Language (DML)?

• A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.
• B. Each DML statement forms a transaction by default.
• C. A transaction can consist of one or more DML statements.
• D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

Answer: C

NEW QUESTION 4
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 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. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
• B. All rows are evaluated by all the three WHEN clauses.
• C. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
• D. The INSERT statement will return an error because the ELSE clause is missing.

Answer: B

NEW QUESTION 5
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 6
Examine the structure of the SALES table. (Choose two.)

Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

• A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
• B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
• C. It will not be created because of the invalid WHERE clause.
• D. It is created with no rows.
• E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Answer: BD

NEW QUESTION 7
Which three statements are true regarding group functions? (Choose three.)

• A. They can be used on columns or expressions.
• B. They can be passed as an argument to another group function.
• C. They can be used only with a SQL statement that has the GROUP BY clause.
• D. They can be used on only one column in the SELECT clause of a SQL statement.
• E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: ABE

Explanation: References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

NEW QUESTION 8
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?

• A. 150
• B. 200
• C. 160
• D. 16
• E. 100

Answer: C

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

NEW QUESTION 9
Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A. Removing all data only from a single column on which a primary key constraint is defined.
• B. Removing all data from a single column on which a unique constraint is defined.
• C. Adding a column with a default value while inserting a row into a table.
• D. Adding a column constraint while inserting a row into a table.

Answer: A

NEW QUESTION 10
View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:
SELECT TO_CHAR (list_price, '$9,999') From product_information;
Which two statements are true regarding the output?

• A. A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
• B. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
• C. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
• D. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Answer: AC

NEW QUESTION 11
Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output?

• A. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
• B. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
• C. In the second column, 'c' indicates a check constraint.
• D. The STATUS column indicates whether the table is currently in use.

Answer: AC

NEW QUESTION 12
Examine the structure of the MEMBERS table: NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?

• A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
• B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
• C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
• D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Answer: B

NEW QUESTION 13
View the exhibit and examine the structure of the CUSTOMERS table.

Which two tasks would require subqueries or joins to be executed in a single statement?

• A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
• B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• C. listing of customers who do not have a credit limit and were born before 1980
• D. finding the number of customers, in each city, who’s marital status is 'married'.
• E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Answer: AE

NEW QUESTION 14
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?

• A. Compare Period ADDM report.
• B. AWR Compare Period report.
• C. Active Session History (ASH) report.
• D. Taking a new snapshot and comparing it with a preserved snapshot.

Answer: B

NEW QUESTION 15
Which statement is true regarding the UNION operator?

• A. By default, the output is not sorted.
• B. Null values are not ignored during duplicate checking.
• C. Names of all columns must be identical across all select statements.
• D. The number of columns selected in all select statements need not be the same.

Answer: B

NEW QUESTION 16
Examine the structure of the INVOICE table. NameNull?Type
-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?

• A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
• B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
• C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
• D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Answer: AC

NEW QUESTION 17
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

• A. It executes successfully but no rows updated.
• B. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
• C. It gives an error because multiple NVL functions are used in an expression.
• D. It gives an error because NVL function cannot be used with UPDATE.

Answer: B

NEW QUESTION 18
Examine the structure of the MEMBERS table. NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATENOT NULL VARCHAR2 (3)
Which query can be used to display the last names and city names only for members from the states MO and MI?

• A. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
• B. SELECT last_name, city FROM members WHERE state LIKE 'M%';
• C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
• D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Answer: C