1z0-071 Exam Dumps - Try Best 1z0-071 Exam Questions from Training Expert Actualtests4sure
Practice Examples and Dumps & Tips for 2024 Latest 1z0-071 Valid Tests Dumps
NEW QUESTION # 177
View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.
You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT prder_id, product_id, unit_price*quantity "Total Price"
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?
- A. The statement would execute and provide the desired result.
- B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
- C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
- D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
Answer: C
NEW QUESTION # 178
Evaluate the following statement.
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
- A. They are evaluated by the first WHENclause. If the condition is false, then the row would be evaluated by the subsequent WHENclauses.
- B. They are evaluated by all the three WHENclauses regardless of the results of the evaluation of any other WHENclause.
- C. They are evaluated by the first WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHENclauses.
- D. The insert statement would give an error because the ELSEclause is not present for support in case none of WHENclauses are true.
Answer: B
Explanation:
Explanation/Reference:
References:
http://psoug.org/definition/WHEN.htm
NEW QUESTION # 179
The SQL statements executed in a user session are as follows:
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
- A. Only the DELETE statements are rolled back.
- B. No SQL statements are rolled back.
- C. Both the DELETE statements and the UPDATE statement are rolled back.
- D. The rollback generates an error.
- E. Only the second DELETE statement is rolled back.
Answer: B,D
NEW QUESTION # 180
View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
- A. Statement 1 would return only one row of output.
- B. Statement 2 would return multiple rows of output.
- C. Statement 1 would not return give the same output.
- D. Both the statements would give the same output.
- E. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
Answer: A,B,E
NEW QUESTION # 181
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 would be dropped provided it does not contain any data.
- B. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
- C. The FIRST_NAME column would be dropped provided at least one column remains in the table.
- D. 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.
Answer: C
NEW QUESTION # 182
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. WITH GRANT OPTION should be added to the statement.
- C. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.
- D. ALL should be replaced with a list of specific privileges.
Answer: C
Explanation:
Explanation
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION # 183
Evaluate this ALTER TABLE statement: (Choose the best answer.)
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?
- A. ROLLBACK can be used to restore the ORDER_DATE column.
- B. The DESCRIBE command would still display the ORDER_DATE column.
- C. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
- D. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
Answer: D
NEW QUESTION # 184
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_DATE column would allow storage of dates only in the dd-mon-yyyy format.
- C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
- D. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
Answer: A,D
NEW QUESTION # 185
Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
- A. DROP TABLE products;
- B. CREATE INDEX price_idx ON products (price);
- C. TRUNCATE TABLE products;
- D. ALTER TABLE products DROP UNUSED COLUMNS;
- E. ALTER TABLE products SET UNUSED (expiry_date);
- F. ALTER TABLE products DROP COLUMN expiry_date;
Answer: A,B,D
NEW QUESTION # 186
View the exhibit and examine the descriptions of the DEPT and LOCATIONS 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 dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
- B. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
- C. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
- D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
Answer: D
NEW QUESTION # 187
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 would be dropped provided it does not contain any data.
- B. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
- C. The FIRST_NAME column would be dropped provided at least one column remains in the table.
- D. 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.
Answer: C
NEW QUESTION # 188
Which three statements are true about the Oracle join and ANSI join syntax?
- A. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
- B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
- C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
- D. The Oracle join syntax supports natural joins.
- E. The SQL:1999 compliant ANSI join syntax supports natural joins.
- F. The Oracle join syntax only supports right outer joins.
- G. The Oracle join syntax supports creation of a Cartesian product of two tables.
Answer: C,D,E
NEW QUESTION # 189
Which two are true about external tables that use the ORACLE_DATAPUMPaccess driver? (Choose two.)
- A. Creating an external table creates a dump file that can be used only by an external table in the same database.
- B. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
- C. Creating an external table creates a directory object.
- D. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
- E. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
Answer: C,E
Explanation:
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
You must create the directory object before you create the external table.
Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
https://docs.oracle.com/cd/E11882_01/server.112/e22490/et_concepts.htm#SUTIL011
NEW QUESTION # 190
View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.
CUSTOMERS need to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?
- A. The CUSTOMER_ID column cannot be updated.
- B. The WHERE clause cannot be used with INSERT.
- C. The INTO clause is misplaced in the command.
- D. CUSTOMER_VU cannot be used as a data source.
Answer: A
NEW QUESTION # 191
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- B. It is created with no rows.
- C. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- D. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- E. It will not be created because of the invalid WHERE clause.
Answer: B,C
Explanation:
Explanation/Reference:
NEW QUESTION # 192
You execute the following commands:
For which substitution variables are you prompted for the input?
- A. Only 'hiredate'
- B. Both the substitution variables 'hiredate' and 'mgr_id\
- C. Only 'mgr_id'
- D. None, because no input required
Answer: B
NEW QUESTION # 193
Examine the description of the transactions table:
Which two SQL statements execute successfully?
- A. SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;
- B. SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM transactions;
- C. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;
- D. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;
- E. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;
Answer: B,C
NEW QUESTION # 194
View the Exhibit and examine the description of the tables.
You execute this SQL statement:
Which three statements are true?
- A. A customer can exist in many countries.
- B. The statement will fail because a subquery may not be contained in a VALUES clause.
- C. The statement will fail if a row already exists in the SALES table for product 23.
- D. The statement will execute successfully and a new row will be inserted into the SALES table.
- E. A product can have a different unit price at different times.
- F. The SALES table has five foreign keys.
Answer: A,D,F
NEW QUESTION # 195
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
- A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET'); - B. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET'); - C. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET'); - D. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)
FROM promotions) AND
promo_category= 'INTERNET';
Answer: A
NEW QUESTION # 196
View the exhibit and examine the structure and data in the invoice table. (Choose two.) Which two SQL statements would execute successfully?
- A. SELECT AVG(inv_date) FROM invoice
- B. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
- C. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice
- D. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
Answer: C,D
NEW QUESTION # 197
View the Exhibit and examine the data in the PRODUCTS table.
You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
Which statement is true?
- A. It executes successfully and returns the required result.
- B. It executes successfully but returns no result.
- C. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
- D. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
Answer: B
NEW QUESTION # 198
Which two statements are true?
- A. CASE is a function and DECODE is not.
- B. DECODE is a function and CASE is not.
- C. All conditions evaluated using CASE can also be evaluated using DECODE.
- D. Neither CASE nor DECODE is a function.
- E. AIl conditions evaluated using DECODE can also be evaluated using CASE.
- F. Both CASE and DECODE are functions.
Answer: B,E
NEW QUESTION # 199
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- B. It is created with no rows.
- C. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- D. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- E. It will not be created because of the invalid WHERE clause.
Answer: B,C
NEW QUESTION # 200
......
Latest 100% Passing Guarantee - Brilliant 1z0-071 Exam Questions PDF: https://surepass.actualtests4sure.com/1z0-071-practice-quiz.html
