Dec-2021 Oracle 1z1-071 Actual Questions and 100% Cover Real Exam Questions
1z1-071 Free Exam Questions & Answers PDF Updated on Dec-2021
NEW QUESTION 32
Which two are true about multitable insert statements?
- A. The conditional insert first statement always Inserts a row Into a single table.
- B. The conditional insert all statement inserts rows into a single table by aggregating source rows.
- C. The unconditional insert all statement must have the same number of columns In both the source and target tables.
- D. They can transform a row from a source table Into multiple rows In a target table.
- E. They always use subqueries.
Answer: D,E
NEW QUESTION 33
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)
- A. ORDER BY "Last name"
- B. ORDER BY "CUST_NO"
- C. ORDER BY 2,1
- D. ORDER BY 2, cust_id
- E. ORDER BY CUST_NO
Answer: A,C,D
Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION 34
Evaluate the following query:
Which is the correct output of the above query?
- A. +25-00, +54-02, +00 11:12:10.123457
- B. +00-300, +54-02,+00 11:12:10.123457
- C. +25-00,+00-650,+00 11:12:10.123457
- D. +00-300,+00-650,+00 11:12:10.123457
Answer: A
NEW QUESTION 35
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update EMPLOYEES table as follows:
* Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
* Set department_id for these employees to the department_id corresponding to London (location_id
* 2100).
* Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
* Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is outcome?
- A. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- B. It executes successfully and gives the desired update.
- C. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
- D. It executes successfully but does not give the desired update.
Answer: D
NEW QUESTION 36
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query.
- A. ORDER BY "Last name"
- B. ORDER BY "CUST_NO"
- C. ORDER BY 2, cust_id
- D. ORDER BY CUST_NO
- E. ORDER BY 2, 1
Answer: A,C,E
Explanation:
Explanation
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION 37
Which three statements are true regarding the data types?
- A. The BLOBdata type column is used to store binary data in an operating system file.
- B. Only one LONGcolumn can be used per table.
- C. A TIMESTAMPdata type column stores only time values with fractional seconds.
- D. The value for a CHARdata type column is blank-padded to the maximum defined column width.
- E. The minimum column width that can be specified for a VARCHAR2data type column is one.
Answer: B,D,E
NEW QUESTION 38
Which statement is true regarding the default behavior of the ORDER BY clause?
- A. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
- B. In a character sort, the values are case-sensitive.
- C. NULLs are not included in the sort operation.
- D. Numeric values are displayed in descending order if they have decimal positions.
Answer: B
NEW QUESTION 39
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- A. Both execute successfully but do not give the required result
- B. Only the second query gives the correct result
- C. Only the first query gives the correct result
- D. Both execute successfully and give the same result
Answer: C
NEW QUESTION 40
You execute this query:
What is the result?
- A. It executes successfully but does not return any result.
- B. It returns the date for the last Monday of the current month.
- C. It returns the date for the first Monday of the next month.
- D. It generates an error.
Answer: C
NEW QUESTION 41
Which two are true about savepoints?
- A. A rollback to savepoint command issued before the start of a transaction results In an error.
- B. After issuing a savepoint, you cannot roll back the complete transaction.
- C. After issuing a savepoint, you can roll back to the savepoint name within the current transaction.
- D. You can commit updates done between two savepoints without committing other updates In the current transaction.
- E. They make uncommitted updates visible to other sessions owned by the? same user.
- F. They make uncommitted updates visible to sessions owned by other users.
Answer: C,E
NEW QUESTION 42
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
- A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
- B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
- C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
- D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iUSING (product_id) AND quantity_on_hand < 5;
Answer: A,C
NEW QUESTION 43
Examine the description of the CUSTOMERS table:
You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
SELECT cust_last_nare, cust_credit_limit FROM customers
Which two WHERE conditions give the required result?
- A. WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%') AND ROUND(cust_credit_limit) < 1000;
- B. WHERE (UPPER(cust_last_name) LIKE 'A%' OR UPPER(cust_last_name) LIKE 'B%') AND ROUND(cust_credit_limit) < 1000;
- C. WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:
- D. WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;
- E. WHERE (INITCAP(cust_last_name) LIKE 'A%' OR ITITCAP(cust_last_name) LIKE 'B%') AND cust_credit_limit < 1000
Answer: B,E
NEW QUESTION 44
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?
- A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
- B. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- C. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
- D. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 45
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)
You executed this UPDATE statement:
Which statement is true regarding the execution?
- A. It would execute and restrict modifications to the columns specified in the SELECT statement.
- B. It would not execute because a SELECT statement cannot be used in place of a table name.
- C. It would not execute because two tables cannot be referenced in a single UPDATE statement.
- D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
Answer: A
NEW QUESTION 46
Which two statements are true about conditional INSERT ALL? (Choose two.)
- A. The total number of rows inserted is always equal to the number of rows returned by the subquery.
- B. It cannot have an ELSEclause.
- C. Each WHENcondition is tested for each row returned by the subquery.
- D. A single WHEN condition can be used for multiple INTOclauses.
- E. Each row returned by the subquery can be inserted into only a single target table.
Answer: C,D
NEW QUESTION 47
Examine this SQL statement:
Which two are true?
- A. All existing rows in the EMPLOYEES table are deleted.
- B. The DELETE statement executes successfully even if the subquery selects multiple rows.
- C. The subquery is executed before the DELETE statement is executed.
- D. The subquery is not a correlated subquery.
- E. The subquery is executed for every row in the employees table.
Answer: C,E
NEW QUESTION 48
Sales data of a company is stored in two tables, SALES1and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1table, which are not present in the SALES2table.
Which set operator generates the required output?
- A. PLUS
- B. SUBTRACT
- C. UNION
- D. MINUS
- E. INTERSECT
Answer: D
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
NEW QUESTION 49
Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; CONSTRAINT_NAME CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL
ENABLED
ORDER_CUSTOMER_ID_NN
C
"CUSTOMER_ID" IS NOT NULL
ENABLED
ORDER_MODE_LOV
C
order _mode in ('direct', 'online')
ENABLED
ORDER TOTAL MIN
C
order total >= 0
ENABLED
ORDER PK
P
ENABLED
ORDERS CUSTOMER ID
R
CUSTOMERS ID
SET NULL
ENABLED
ORDERS SALES REP
R
EMP EMP ID
SET NULL
ENABLED
Which two statements are true about the output? (Choose two.)
- A. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
- B. In the second column, 'c' indicates a check constraint.
- C. The STATUS column indicates whether the table is currently in use.
- D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: B,D
NEW QUESTION 50
......
Oracle 1z1-071 Real 2021 Braindumps Mock Exam Dumps: https://www.dumpsquestion.com/1z1-071-exam-dumps-collection.html
Latest 1z1-071 Exam Dumps Recently Updated 305 Questions: https://drive.google.com/open?id=1siHLYWHFJRjm90EsKHWrPmmSPx7_Sage