[Jan-2022] Pass Oracle 1z0-071 Exam in First Attempt Guaranteed!
Full 1z0-071 Practice Test and 305 unique questions with explanations waiting just for you, get it now!
NEW QUESTION 47
View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
- A. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL; - B. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); - C. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL; - D. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
Answer: C
NEW QUESTION 48
Examine the structure proposed for the TRANSACTIONStable:
Which two statements are true regarding the creation and storage of data in the above table structure?
(Choose two.)
- A. The TRANS_VALIDITYcolumn would give an error.
- B. The CUST_CREDIT_LIMITcolumn would be able to store decimal values.
- C. The TRANS_VALIDITYcolumn would have a maximum size of one character.
- D. The TRANS_DATEcolumn would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- E. The CUST_STATUScolumn would store exactly one character.
- F. The CUST_STATUScolumn would give an error.
Answer: A,E
NEW QUESTION 49
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 product_id, quantity_on_hand, supplier_idFROM
product_informationNATURAL JOIN inventories AND quantity_on_hand < 5; - B. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)USING (product_id) 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 iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
Answer: C,D
NEW QUESTION 50
Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
- A. Creating an external table creates a directory object.
- B. Creating an external table creates a dump file that can be used only by an external table in the same database.
- C. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
- D. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
- E. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
Answer: C,E
NEW QUESTION 51
You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed.
The variables used in your query are never undefined in your session?
Which query can be used?
- A. Option D
- B. Option B
- C. Option A
- D. Option E
- E. Option C
Answer: A
NEW QUESTION 52
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It shows COST_REMARK for all the promos in the promo category 'TV'.
- B. It shows COST_REMARK for all the promos in the table.
- C. It produces an error because subqueries cannot be used with the CASE expression.
- D. It produces an error because the subquery gives an error.
Answer: B
NEW QUESTION 53
Which CREATE TABLE statement is valid?
- A. Option D
- B. Option B
- C. Option A
- D. Option C
Answer: A
NEW QUESTION 54
Examine the description of the EMPLOYYES table:
Which two statements will run successfully?
- A. SELECT 'The first-name is ' ' ' | | first_name | | ' ' ' FROM employees;
- B. SELECT 'The first-name is \ ' ' | | first_name | | ' \ ' ' FROM employees;
- C. SELECT 'The first-name is ' ' ' | | first_name | | ' ' ' ' FROM employees;
- D. SELECT 'The first-name is \ ' ' | | first_name | | ' ' FROM employees;
- E. SELECT 'The first-name is ' | | first_name | | ' ' FROM employees;
Answer: C,E
NEW QUESTION 55
Which two queries execute successfully?
- A. select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;
- B. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
- C. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
- D. SELECT SYSDATE "INTERRVAL '1' DAY FROM DUAL;
- E. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;
Answer: C,E
NEW QUESTION 56
Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
- A. All existing rows in the EMPLOYEES table are deleted,
- B. The subquery is executed before the DELETE statement is executed.
- C. The DELETE statement executes successfully even if the subquery selects multiple rows.
- 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 57
Evaluate the following SQL commands:
The command to create a table fails. Identify the reason for the SQL statement failure.
- A. You cannot use ORD_NOand ITEM_NOcolumns as a composite primary key because ORD_NOis also the FOREIGN KEY.
- B. You cannot use the BETWEENclause in the condition of a CHECKconstraint.
- C. You cannot use the NEXTVALsequence value as a DEFAULTvalue for a column.
- D. You cannot use SYSDATEin the condition of a CHECKconstraint.
Answer: D
NEW QUESTION 58
In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- A. fourth normal form
- B. second normal form
- C. third normal form
- D. first normal form
Answer: B
Explanation:
Explanation
https://blog.udemy.com/database-normal-forms/
NEW QUESTION 59
Which three statements are true regarding subqueries?
- A. Main query and subquery can get data from different tables.
- B. Main query and subquery must get data from the same tables.
- C. Subqueries can contain GROUP BY and ORDER BY clauses.
- D. Multiple columns or expressions can be compared between the main query and subquery.
- E. Only one column or expression can be compared between the main query and subquery.
- F. Subqueries can contain ORDER BY but not the GROUP BY clause.
Answer: A,C,D
Explanation:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
NEW QUESTION 60
View the Exhibit and examine the structure of the PRODUCTStable
Which two tasks would require subqueries? (Choose two.)
- A. display the number of products whose PROD_LIST_PRICEis more than the average PROD_LIST_PRICE
- B. display suppliers whose PROD_LIST_PRICEis less than 1000
- C. display the minimum PROD_LIST_PRICEfor each product status
- D. display all products whose PROD_MIN_PRICEis more than the average PROD_LIST_PRICEof all products, and whose status is orderable
- E. display the total number of products supplied by supplier 102and have a product status as 'OBSOLETE'
Answer: A,D
NEW QUESTION 61
The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).
Evaluate this SQL statement:
SELECT TO_CHAR(unit_price,'$9,999') FROM PRODUCT_INFORMATION;
Which two statements are true about the output?
- A. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
- B. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
- C. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####
- D. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
- E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
Answer: A,C
NEW QUESTION 62
Which three statements are true?
- A. The statement will execute successfully and a new row will be inserted into the SALES table.
- B. A product can have a different unit price at different times.
- C. A customer can exist in many countries.
- D. The SALES table has five foreign keys.
- E. The statement will fail because subquery may not be I contained in a values clause.
- F. The statement will fail if a row already exists in the SALES table for product 23.
Answer: A,B,D
NEW QUESTION 63
......
Prepare for your Oracle certification with the updated Exams4Collection 1z0-071 exam questions: https://drive.google.com/open?id=1oaD0mIiV40RqIqfV0kywRD3l1pMfMg8Y
Get Latest 1z0-071 Dumps Exam Questions in here: https://www.exams4collection.com/1z0-071-latest-braindumps.html
