Pass Your 1z0-071 Dumps as PDF Updated on 2022 With 305 Questions
Oracle 1z0-071 Real Exam Questions and Answers FREE
For more information, kindly read the exam references
Oracle 1Z0-071 Certification Exam Reference
NEW QUESTION 90
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY 3;
- C. ORDER BY 'b';
- D. ORDER BY DEPT_ID;
Answer: B,D
NEW QUESTION 91
Evaluate the following two queries:
Which statement is true regarding the above two queries?
- A. Performance would degrade in query 2.
- B. Performance would improve in query 2.
- C. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- D. There would be no change in performance.
Answer: D
NEW QUESTION 92
These are the steps for a correlated subquery, listed in random order:
1. The WHEREclause of the outer query is evaluated.
2. A candidate row is fetched from the table specified in the outer query.
3. This is repeated for the subsequent rows of the table, until all the rows are processed.
4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
- A. 2, 4, 1, 3
- B. 4, 2, 1, 3
- C. 2, 1, 4, 3
- D. 4, 1, 2, 3
Answer: A
Explanation:
Explanation/Reference:
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
NEW QUESTION 93
Which two statements are true about Oracle synonyms?
- A. Users must have the required privileges on the underlying objects to use public synonyms
- B. Synonyms can be created for roles.
- C. Synonyms cannot be created for sequences.
- D. Users must have the DBA role to create public synonyms.
- E. Synonyms cannot be created for synonyms.
- F. Synonyms can be created for packages.
Answer: A,F
NEW QUESTION 94
Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
Answer: D
Explanation:
Explanation/Reference:
Reference https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm
NEW QUESTION 95
View the Exhibit and examine the structure of the ORDERS table.
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?
- A. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);
- B. SELECT order_id, order_date FROM ordersWHERE order_date >ANY(SELECT order_date FROM orders WHERE customer_id = 101);
- C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
- D. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
Answer: D
NEW QUESTION 96
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.
- A. INTERSECT
- B. UNION
- C. UNION ALL
- D. MINUS
Answer: B,C
NEW QUESTION 97
The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
- A. FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT - B. FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT - C. FROM customers
WHERE (cust_income_level, tax_amount) IS NOT NULL; - D. FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
Answer: A
NEW QUESTION 98
Examine the data in the ENPLOYEES table:
Which statement will compute the total annual compensation tor each employee?
- A. SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp
FROM employees - B. SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp
FROM employees; - C. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)
AS annual_comp FROM employees - D. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL
(monthly_commission_pct, 0)) AS annual_comp FROM employees
Answer: D
NEW QUESTION 99
View the Exhibit and examine the structure of the BOOKStable.
The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:
Which statement is true?
- A. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
- B. Both ROLLBACKcommands restore the 100 rows that were deleted.
- C. The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
- D. Both ROLLBACKcommands restore the 101 rows that were deleted.
Answer: C
NEW QUESTION 100
Examine the structure of the EMPLOYEES table. (Choose the best answer.) 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.
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id
100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE
hire_date > SYSDATE- 180; - 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 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;
Answer: D
NEW QUESTION 101
The PRODUCTStable has the following structure.
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ' ') FROM products; SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products; Which statement is true regarding the outcome?
- A. Only the first SQL statement executes successfully
- B. Both the statements execute and give different results
- C. Both the statements execute and give the same result
- D. Only the second SQL statement executes successfully
Answer: B
Explanation:
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null
Expr3 is the value that is returned if expr1 is null
NEW QUESTION 102
You want to display the date for the first Monday of the next month and issue the following command:
What is the outcome?
- A. In generates an error because TO_CHARshould be replaced with TO_DATE.
- B. It executes successfully but does not return the correct result.
- C. In generates an error because fmand double quotation marks should not be used in the format string.
- D. In generates an error because rrrrshould be replaced by rrin the format string.
- E. It executes successfully and returns the correct result.
Answer: E
NEW QUESTION 103
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
- A. Both CASE and DECODE are functions.
- B. Neither CASE nor DECODE is a function.
- C. DECODE is a function and CASE is not.
- D. All conditions evaluated using CASE can also be evaluated using DECODE.
- E. AIl conditions evaluated using DECODE can also be evaluated using CASE.
- F. CASE is a function and DECODE is not.
Answer: C,D
NEW QUESTION 104
These are the steps for a correlated subquery, listed in random order:
* The WHERE clause of the outer query is evaluated.
* A candidate row is fetched from the table specified in the outer query.
* This is repeated for the subsequent rows of the table, until all the rows are processed.
* Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
- A. 2, 4, 1, 3
- B. 4, 2, 1, 3
- C. 2, 1, 4, 3
- D. 4, 1, 2, 3
Answer: A
Explanation:
Explanation
References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
NEW QUESTION 105
Which two are true about creating tables in an Oracle database?
- A. A primary key constraint is manadatory.
- B. A system privilege is required.
- C. Creating an external table will automatically create a file using the specified directory and file name.
- D. The same table name can be used for tables in different schemas.
- E. A create table statement can specify the maximum number of rows the table will contain.
Answer: D,E
NEW QUESTION 106
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
Evaluate the following MERGEstatement:
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_MASTERtable would contain the ORDER_IDs1, 2 and 3.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
Answer: D
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION 107
Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
- A. Full outer-join with self-join
- B. Subquery
- C. Left outer-join with self-join
- D. Self-join
- E. Right outer-join with self-join
Answer: B,D
NEW QUESTION 108
View the Exhibit and examine the descriptions of the DEPT and LOCATIOMS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?
- A. UPDATE dept d
SET city = ANY (SELECT city FROM locations l); - B. UPDATE dept d
SET city = ALL (SELECT city
FROM locations l
WHERE d.location_id = l.location_id); - C. UPDATE dept d
SET city = (SELECT city FROM locations l)
WHERE d.location_id = l.location_id; - D. UPDATE dept d
SET city = (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);
Answer: D
NEW QUESTION 109
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
- A. ORDER BY "Last Name"
- B. ORDERBY2, 1
- C. ORDER BY CUSTNO
- D. ORDER BY"CUST NO"
- E. ORDER BY 2, cust_id
Answer: A,B,E
NEW QUESTION 110
Which two statements are true about single-row functions? (Choose two.)
- A. CEIL: can be used for positive and negative numbers
- B. FLOOR: returns the smallest integer greater than or equal to a specified number
- C. CONCAT: can be used to combine any number of values
- D. TRUNC: can be used with NUMBER and DATE values
- E. MOD: returns the quotient of a division operation
Answer: D,E
NEW QUESTION 111
Examine this statement:
What is returned upon execution?
- A. 2 rows
- B. 0 rows
- C. an error
- D. 1 row
Answer: D
NEW QUESTION 112
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
- A. It results in an error because the ORDER BY clause should be the last clause.
- B. It displays 5 percent of the products with the highest amount sold.
- C. It displays the first 5 percent of the rows from the SALES table.
- D. It displays 5 percent of the products with the lowest amount sold.
Answer: D
Explanation:
Explanation
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION 113
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)
- A. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
- B. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
- C. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
- D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
- E. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
- F. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
Answer: A,F
Explanation:
Explanation
References:
http://www.techonthenet.com/sql/and_or.php
NEW QUESTION 114
......
Pass Oracle 1z0-071 Exam Info and Free Practice Test: https://www.actual4test.com/1z0-071_examcollection.html
New 2022 Latest Questions 1z0-071 Dumps - Use Updated Oracle Exam: https://drive.google.com/open?id=1ul_NBwZr_fCO-2tlzi9-mKoaOCuqBAwx