Oracle 1Z0-071 Dumps 2021

NEW QUESTION 1
A subquery is called a single-row subquery when .

• A. There is only one subquery in the outer query and the inner query returns one or more values
• B. The inner query returns a single value to the outer query.
• C. The inner query uses an aggregating function and returns one or more values.
• D. The inner query returns one or more values and the outer query returns a single value.

Answer: B

NEW QUESTION 2
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 3
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 4
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 5
The following are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, till 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, 1, 4, 3
• B. 4, 1, 2, 3
• C. 4, 2, 1, 3
• D. 2, 4, 1, 3

Answer: D

Explanation: References:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

NEW QUESTION 6
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?

• A. PUBLIC should be replaced with specific usernames.
• B. ALL should be replaced with a list of specific privileges.
• C. WITH GRANT OPTION should be added to the statement.
• D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Answer: D

Explanation: References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

NEW QUESTION 7
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 8
Which two tasks can be performed by using Oracle SQL statements?

• A. changing the password for an existing database user
• B. connecting to a database instance
• C. querying data from tables across databases
• D. starting up a database instance
• E. executing operating system (OS) commands in a session

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

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
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 11
Examine the structure of the EMPLOYEES table. (Choose two.)

You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

• A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
• B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;
• C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
• D. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Answer: BD

NEW QUESTION 12
View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME CATEGORY_ID SUPPLIER_ID
Inkjet C/8/HQ 12
102094
Inkjet C/4 12
102090
LaserPro 600/6/BW 12
102087
LaserPro 1200/8/BW 12
102099
Inkjet B/6 12
102096
Industrial 700/ID 12
102086
Industrial 600/DQ 12
102088
Compact 400/LQ 12
102087
Compact 400/DQ 12
102088
HD 12GB /R 13
102090
HD 10GB /I 13
102071
HD 12GB @7200 /SE 13
102057
HD 18.2GB @10000 /E 13
102078
HD 18.2GB @10000 /I 13
102050
HD 18GB /SE 13
102083
HD 6GB /I 13
102072
HD 8.2GB@5400 13
102093
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name 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 same column has been used in both sides of the AND logical operator to form the condition.
• B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
• C. It would execute and the output would display the desired result.
• D. It would execute but the output would return no rows.

Answer: D

NEW QUESTION 13
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 14
View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have “Not Available” displayed. Which SQL statement would produce the required result?

• A. SELECT NVL (TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers
• B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), ‘Not Available’)) “NEW CREDIT” FROMcustomers
• C. SELECT NVL (cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers
• D. SELECT NVL (cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers

Answer: C

NEW QUESTION 15
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 16
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?

• A. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');
• B. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
• C. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';
• D. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP 'MC%';

Answer: C

NEW QUESTION 17
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)

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. UNION
• B. MINUS
• C. INTERSECT
• D. UNION ALL

Answer: AD

NEW QUESTION 18
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?

• A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
• B. The EXECUTION_DATETIME must be of TIMESTAMP data type.
• C. The EXECUTION_DATETIME must be of DATE data type.
• D. The COMPONENT_ID must be of ROWID data type.
• E. The COMPONENT_ID must be of VARCHAR2 data type.
• F. The COMPONENT_ID column must be of CHAR data type.

Answer: CF