Oracle 1z1-071 Certification Exam Dumps with 437 Practice Test Questions [Q221-Q246]

Oracle 1z1-071 Certification Exam Dumps with 437 Practice Test Questions

New 1z1-071 Exam Dumps with High Passing Rate

Oracle 1z0-071 (Oracle Database SQL) certification exam is designed for individuals who want to validate their expertise in SQL programming language and its applications in the Oracle Database. 1z1-071 exam measures the candidates' knowledge and skills in various aspects of SQL programming, such as data retrieval, data manipulation, data control, and table creation, among others. Oracle Database SQL certification exam also tests the candidates' understanding of database architecture and schema objects, as well as their ability to write SQL queries and subqueries.

 

NEW QUESTION # 221
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?

• A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
  FROM customers
  WHERE cust_income_level <> NULL AND
  tax_amount <> NULL;
• B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
  FROM customers
  WHERE (cust_income_level, tax_amount) IS NOT NULL;
• C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
  FROM customers
  WHERE cust_income_level IS NOT NULL AND
  tax_amount IS NOT NULL;
• D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
  FROM customers
  WHERE cust_income_level IS NOT NULL AND
  cust_credit_limit IS NOT NULL;

Answer: D

NEW QUESTION # 222
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_cost, DISTINCT promo_category FROM promotions;
• B. SELECT DISTINCT promo_cost, promo_category FROM promotions
• C. SELECT promo_category, DISTINCT promo_cost FROM promotions
• D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

Answer: D

NEW QUESTION # 223
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
  FROM orders
  WHERE (SELECT order_id
  FROM order_items);
• B. DELETE orders
  WHERE order_total < 1000;
• C. DELETE order_id
  FROM orders
  WHERE order_total < 1000;
• D. DELETE orders o, order_items i
  WHERE o.order id = i.order id;

Answer: B

NEW QUESTION # 224
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.
EMPLOYEES
Name Null? Type
- ---------------- ----- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(10,2)
COMMISSION NUMBER(6,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
DEPARTMENTS
Name Null? Type
----------------- ----- -------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

Set department_idfor these employees to the department_idcorresponding to London

(location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.

Set the employees' commission in location_id2100 to 1.5 times the average commission of their

department.
You issue the following command:
SQL> UPDATE employees
SET department_id
( SELECT department_id
FROM departments
WHERE location_id = 2100),
( salary, commission)
( SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
( SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?

• A. It executes successfully but does not give the correct result.
• B. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.
• C. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
• D. It executes successfully and gives the correct result.

Answer: A

NEW QUESTION # 225
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.

Which set operator generates the required output?

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

Answer: E

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

NEW QUESTION # 226
A subquery is called a single-row subquery when _______.

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

Answer: A

NEW QUESTION # 227
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 and 4.
• B. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
• C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
• D. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

Answer: A

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

NEW QUESTION # 228
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. The outer query stops evaluating the result set of the inner query when the first value is found.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: A,C

Explanation:
Explanation
References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION # 229
Which three are true about privileges? (Choose three.)

• A. Only users with the DBA role can create roles.
• B. All schema objects have associated object privileges.
• C. Object privileges granted on a table automatically apply to all synonyms for that table.
• D. A combination of object and system privileges can be granted to a role.
• E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.
• F. Schema owners can grant any object privilege on any object in their schema to any other user or role.

Answer: C,E,F

Explanation:
Object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or by using a synonym.
A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user A user can grant any object privilege on any schema object he or she owns to any other user or role.
https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#DBSEG5000
https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#DBSEG5000
https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#DBSEG5000

NEW QUESTION # 230
View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?

• A. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
• B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
• C. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);
• D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Answer: C

NEW QUESTION # 231
Examine the ORDER _ITEms table:

Which two queries return rows where QUANTITY is a multiple of ten?

• A. SELECT" FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10);
• B. SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0;
• C. SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);
• D. SELECT" FROM order_ _items WHERE quantity = ROUND (quantity, 1);
• E. SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1);

Answer: B,E

NEW QUESTION # 232
Examine this incomplete query:
SELECT DATA'2019-01-01'+<INTERVAL CLAUSE>
FROM DUAL;
Which three clauses can replace<INTERVAL CLAUSE>ti add 22 hours to the date?

• A. INTERVAL'0,5'DAY
• B. INTERVAL'11:60'HOUR TO MINUTE
• C. INTERVAL'12' HOUR
• D. INTERVAL'720'MINUTE
• E. INTERVAL '12:00'
• F. INTERVAL'0 12'DAY TO HOUR

Answer: C,D,F

NEW QUESTION # 233
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.

You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
Set department_idfor these employees to the department_idcorresponding to London (location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.
Set the employees' commission in location_id2100 to 1.5 times the average commission of their department.
You issue the following command:

What is outcome?

• A. It executes successfully and gives the desired update
• B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
• C. It executes successfully but does not give the desired update
• D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

Answer: C

NEW QUESTION # 234
Examine this statement:
SELECT last name
FROM employees
ORDER BY CASE WHEN salary = (SELECT MAX(salary) FROM employees)
THEN 'A'
ELSE last_ name
END ,last_name DESC;
Which two statements are true?

• A. The names of employees earning the maximum salary will appear first In ascending order.
• B. All remaining employee names will appear in ascending order.
• C. The names of employees earning the maximum salary will appear first in an unspecified order.
• D. All remaining employee names will appear in descending order.
• E. The names of employees earning the maximum salary will appear first in descending order.
• F. All remaining employee names will appear in an unspecified order.

Answer: A,D

Explanation:
The ORDER BY clause with a CASE statement allows for conditional sorting based on the results of the CASE expression.
A . This is not true, as the 'A' in the THEN clause would cause the employees earning the maximum salary to be sorted at the top, but it does not determine the order in which their last names would appear.
B . This is true, the 'A' in the THEN clause will result in all maximum salary earners to be sorted at the top, and since 'A' comes before any last names alphabetically, they will be in ascending order.
C . This is not true, as the ELSE clause sorts the remaining employees by last_name but does not specify an order; hence, it defaults to ascending which contradicts the second part of the ORDER BY clause which specifies DESC.
D . This is not true, as the order of the remaining employee names is specified by the ORDER BY clause, which states last_name DESC.
E . This is true, after the employees with the maximum salary are sorted, the remaining employees' names will appear in descending order due to the last_name DESC in the ORDER BY clause.
F . This statement is not true as the specified 'A' in the CASE statement provides a definite order for employees earning the maximum salary.
Reference:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause" Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CASE Expression"

NEW QUESTION # 235
Which two statements are true about the results of using the INTERSECT operator in compound queries?

• A. Reversing the order of the intersected tables can sometimes affect the output.
• B. INTERSECT ignores NULLs.
• C. The number of columns in each SELECT in the compound query can be different.
• D. Column names in each SELECT in the compound query can be different.
• E. INTERSECT returns rows common to both sides of the compound query.

Answer: B,E

NEW QUESTION # 236
Which two tasks require subqueries?

• A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
• B. Display the minimum PROD_LIST_PRICE for each product status.
• C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.
• D. Display the total number of products supplied by supplier 102 which have a product status of obsolete.
• E. Display suppliers whose PROD_LIST_PRICE is less than 1000.

Answer: A,C

NEW QUESTION # 237
Which three statements are true about views in an Oracle database?

• A. Rows inserted into a table using a view are retained in the table if the view is dropped
• B. Views can join tables only if they belong to the same schema.
• C. Views have no segment.
• D. Views have no object number.
• E. A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query
• F. A view can be created that refers to a non-existent table in its defining query.

Answer: A,C,F

NEW QUESTION # 238
Which two statements are true about INTERVAL data types?

• A. INTERVAL YEAR TO MONTH columns support yearly intervals.
• B. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
• C. INTERVAL DAY TO SECOND columns support fractions of seconds.
• D. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
• E. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
• F. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

Answer: B,C

NEW QUESTION # 239
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" FROM customers
• C. SELECT NVL (cust_credit_limit, 'Not Available')*.15 "NEW CREDIT" FROM customers
• D. SELECT NVL (cust_credit_limit*.15, 'Not Available') "NEW CREDIT" FROM customers

Answer: D

NEW QUESTION # 240
Examine the structure proposed for the TRANSACTIONStable:

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

• A. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
• B. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
• C. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
• D. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.

Answer: A,D

NEW QUESTION # 241
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. NATURAL JOIN
• B. JOIN
• C. RIGHT OUTER JOIN
• D. LEFT OUTER JOIN
• E. FULL OUTER JOIN

Answer: B,C

NEW QUESTION # 242
Examine the structure of the MARKS table:

Which two statements would execute successfully? (Choose two.)

• A. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE 'R%';
• B. SELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
• C. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
• D. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;

Answer: C,D

NEW QUESTION # 243
Which statements are correct regarding indexes? (Choose all that apply.)

• A. Indexes should be created on columns that are frequently referenced as part of any expression.
• B. For each DML operation performed, the corresponding indexes are automatically updated.
• C. When a table is dropped, the corresponding indexes are automatically dropped.
• D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates a unique index.

Answer: B,C,D

Explanation:
Explanation
References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

NEW QUESTION # 244
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 orders
  WHERE order_date > ALL
  (SELECT order_date FROM orders WHERE customer_id = 101);
• B. SELECT order_id, order_date FROM orders
  WHERE order_date > IN
  (SELECT order_date FROM orders WHERE customer_id = 101);
• C. SELECT order_id, order_date FROM orders
  WHERE order_date > ALL
  (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• D. SELECT order_id, order_date FROM orders
  WHERE order_date >
  ANY
  (SELECT order_date FROM orders WHERE customer_id = 101);

Answer: A

NEW QUESTION # 245
Which statement fails to execute successfully?

• A.
• B.
• C.
• D.

Answer: C

Explanation:
In Oracle SQL, when performing a JOIN operation, the ON clause is used to specify the condition that relates the two tables being joined. The WHERE clause can be used to further filter the result set.
A) This is a valid join condition using the WHERE clause to filter the rows after the join has been made.
B) This statement will fail because the ON clause should only contain conditions that relate the two tables. The condition for filtering the departments table should be in the WHERE clause, not in the ON clause. This is a common mistake when writing JOIN statements.
C) This is a correct statement. The ON clause specifies how the tables are related and the WHERE clause specifies an additional filtering condition for the query.
D) This statement is also correct. It's similar to the first statement (A) and properly places the department_id filter in the ON clause, which is acceptable though not typically best practice as it can be less readable than using a WHERE clause for non-join conditions.
When the JOIN operation is executed, the database first pairs rows from the joined tables that meet the join condition specified by the ON clause. Then, it filters the result of the JOIN operation based on the condition specified in the WHERE clause.
References:
* Oracle Documentation on Joins:
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52359

NEW QUESTION # 246
......

Get 1z1-071 Braindumps & 1z1-071 Real Exam Questions: https://www.passcollection.com/1z1-071_real-exams.html

Oracle 1z1-071 Actual Questions and Braindumps: https://drive.google.com/open?id=1Yg5VRJyOKySl0vsahVMJti2tt3TpJPFv