Accurate 1Z0-071 Exam Dumps 2021

NEW QUESTION 1
Which statement is true regarding the INTERSECT operator?

• A. The names of columns in all SELECT statements must be identical.
• B. It ignores NULL values.
• C. Reversing the order of the intersected tables alters the result.
• D. The number of columns and data types must be identical for all SELECT statements in the query.

Answer: D

Explanation: INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
References:
http://oraclexpert.com/using-the-set-operators/

NEW QUESTION 2
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

• A. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
• B. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
• C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
• D. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Answer: AB

NEW QUESTION 3
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?

• A. SYSDATE cannot be used with the CHECK constraint.
• B. The BETWEEN clause cannot be used for the CHECK constraint.
• C. The CHECK constraint cannot be placed on columns having the DATE data type.
• D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: A

NEW QUESTION 4
Which two statements are true regarding multiple-row subqueries? (Choose two.)

• A. They can contain group functions.
• B. They always contain a subquery within a subquery.
• C. They use the < ALL operator to imply less than the maximum.
• D. They can be used to retrieve multiple rows from a single table only.
• E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Answer: AE

NEW QUESTION 5
Which two statements are true regarding the COUNT function?

• A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
• B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
• C. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
• D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
• E. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Answer: BD

NEW QUESTION 6
View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

• A. UPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
• B. UPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
• C. UPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455;
• D. UPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;

Answer: C

NEW QUESTION 7
When does a transaction complete? (Choose all that apply.)

• A. When a PL/SQL anonymous block is executed
• B. When a DELETE statement is executed
• C. When a data definition language statement is executed
• D. When a TRUNCATE statement is executed after the pending transaction
• E. When a ROLLBACK command is executed

Answer: CDE

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
You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

• A. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
• B. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
• C. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
• D. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

Answer: A

NEW QUESTION 10
Which statement correctly grants a system privilege?

• A. GRANT CREATE VIEWON table1 TOuser1;
• B. GRANT ALTER TABLETO PUBLIC;
• C. GRANT CREATE TABLETO user1, user2;
• D. GRANT CREATE SESSIONTO ALL;

Answer: C

NEW QUESTION 11
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
ORDERS_MASTER ORDER_ID ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS ORDER_ID ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement: MERGE_INTO orders_master o
USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

• A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
• B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
• C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
• D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

Answer: B

Explanation: References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

NEW QUESTION 12
View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE, (SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?

• A. The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
• B. The statement will fail because a subquery cannot be used in a VALUES clause.
• C. The statement will execute and a new row will be inserted in the SALES table.
• D. The statement will fail because the VALUES clause is not required with the subquery.

Answer: C

NEW QUESTION 13
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table NameNullType
----------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
SALES2 table NameNullType
---------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER
ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
Which set operator generates the required output?

• A. INTERSECT
• B. UNION
• C. PLUS
• D. MINUS
• E. SUBTRACT

Answer: D

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

NEW QUESTION 14
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 15
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
• B. During executing, the oracle server may read data from storage if the required data is not already in memory.
• C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer: B

NEW QUESTION 16
View the exhibit and examine the structure and data in the invoice table. (Choose two.)

Which two SQL statements would execute successfully?

• A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
• B. SELECT AVG(inv_date) FROM invoice
• C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
• D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Answer: CD

NEW QUESTION 17
View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.

Which SQL statement would you execute?

• A. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON m.employee_id = e.manager_id WHERE m.manager_id=100;
• B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.managerjd=100;
• C. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON e.employee_id = m.manager_id WHERE m.manager_id=100;
• D. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.managerjd=100;

Answer: B

NEW QUESTION 18
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)

• A. The CUST_STATUS column would store exactly one character.
• B. The TRANS_VALIDITY column would have a maximum size of one character.
• C. The CUST_CREDIT_LIMIT column would be able to store decimal values.
• D. The CUST_STATUS column would give an error.
• E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
• F. The TRANS_VALIDITY column would give an error.

Answer: AF