[Sep-2021] Valid Way To Pass Oracle Exam Dumps with 1z0-071 Exam Study Guide
All 1z0-071 Dumps and Oracle Database SQL Training Courses Help candidates to study and pass the Exams hassle-free!
NEW QUESTION 161
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
- A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
- B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 162
Which three statements are true regarding group functions? (Choose three.)
- A. They can be used with a SQL statement that has a GROUP BY clause.
- B. They can be used only on one column in the SELECT clause.
- C. They can be used together with the single-row functions in the SELECT clause.
- D. They can be used on columns or expressions.
- E. They can be passed as an argument to another group function.
Answer: C,D,E
Explanation:
Explanation
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html
NEW QUESTION 163
Which two statements are true regarding constraints? (Choose two.)
- A. A constraint can be disabled even if the constraint column contains data.
- B. A column with the UNIQUE constraint can contain NULL.
- C. A foreign key cannot contain NULL values.
- D. A constraint is enforced only for the INSERT operation on a table.
- E. All the constraints can be defined at the column level as well as the table level
Answer: A,B
NEW QUESTION 164
View and Exhibit and examine the structure and data in the INVOICE table.
Which two statements are true regarding data type conversion in query expressions? (Choose two.)
- A. inv_amt = '0255982' : requires explicit conversion
- B. CONCAT(inv_amt, inv_date) : requires explicit conversion
- C. inv_no BETWEEN '101' AND '110' : uses implicit conversion
- D. inv_date = '15-february-2008' :uses implicit conversion
- E. inv_date > '01-02-2008' : uses implicit conversion
Answer: C,D
NEW QUESTION 165
View the Exhibit and examine the description for the PRODUCTS and SALEStable.
PROD_IDis a primary key in the PRODUCTStable and foreign key in the SALEStable with ON DELETE CASCADEoption. The SALEStable contains data for the last three years. You want to remove all the rows from the PRODUCTStable for which no sale was done for the last three years.
Which is the valid DELETEstatement?
DELETE
- A. FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE - 3*365 >= time_id);
DELETE - B. FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id - 3*365 );
DELETE - C. FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE - 3*365 ); - D. FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id - 3*365 = SYSDATE );
DELETE
Answer: A
NEW QUESTION 166
Which two are true about dropping columns from a table?
- A. A column drop is implicitly committed.
- B. A column can be removed only if it contains no data.
- C. A column must be set as unused before it is dropped from a table.
- D. A primary key column cannot be dropped.
- E. A column that is referenced by another column in any other table cannot be dropped.
- F. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
Answer: A,E,F
NEW QUESTION 167
Which task can be performed by using a single Data Manipulation Language (DML) statement?
- A. adding a column with a default value while inserting a row into a table
- B. removing all data only from a single column on which a primary key constraint is defined
- C. adding a column constraint while inserting a row into a table
- D. removing all data only from a single column on which a unique constraint is defined
Answer: D
NEW QUESTION 168
You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT 'SEATTLE' NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?
- A. It succeeds and an index is crated for CITIZEN_ID.
- B. It fails because the condition for the CANAMES constraint is not valid.
- C. It succeeds and CITY can contain only 'SEATTLE' or null for all rows.
- D. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
Answer: D
NEW QUESTION 169
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 time_id - 3*365 = SYSDATE );
- B. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);
- C. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
- D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );
Answer: B
NEW QUESTION 170
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.
The PROD_IDcolumn is the foreign key in the SALEStables, which references the PRODUCTStable.
Similarly, the CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERSand TIMEStables, respectively.
Evaluate the following CREATE TABLEcommand:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
- A. The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
- B. The NEW_SALEStable would get created and all the FOREIGNKEYconstraints defined on the specified columns would be passed to the new table.
- C. The NEW_SALEStable would not get created because the column names in the CREATETABLE command and the SELECTclause do not match.
- D. The NEW_SALEStable would get created and all the NOTNULLconstraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 171
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees;
- B. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
- C. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp
- D. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
Answer: C
NEW QUESTION 172
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
Evaluate the following MERGEstatement:
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_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
Answer: B
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION 173
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)
- A. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in all columns would be returned in descending order.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- E. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
Answer: D,E
NEW QUESTION 174
Examine the structure of the EMPLOYEES table.
There is a parent-child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?
- A. SELECT e.last_name, m.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
- B. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;
- C. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
- D. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
Answer: B
NEW QUESTION 175
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)WHERE 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) AND 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 176
......
Real Exam Questions & Answers - Oracle 1z0-071 Dump is Ready: https://drive.google.com/open?id=1EAmdE65R02INlrQBSPLSAmGQl0rtVkqb
Get Latest [Sep-2021] Conduct effective penetration tests using PracticeVCE 1z0-071