http://https://www.newpassleader.com/Oracle/1z0-071-exam-preparation-materials.html (162 Q&As Dumps, 30%OFF Special Discount: 30free )

NEW QUESTION NO: 8
View the exhibit and examine the ORDERStable.
ORDERS
Null? Type
Name
ORDER ID NOT NULL NUMBER(4)
ORDATE DATE DATE
CUSTOMER ID NUMBER(3)
ORDER TOTAL NUMBER(7,2)
The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?
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_nn NOT NULL (customer_id);
D. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
Answer: C

NEW QUESTION NO: 9
View the exhibit and examine the description of the PRODUCT_INFORMATIONtable.

Which SQL statement would retrieve from the table the number of products having LIST_PRICEas NULL?
A. SELECT COUNT (list_price)
FROM product_information
WHERE list_price is NULL
B. SELECT COUNT (DISTINCT list_price)
FROM product_information
WHERE list_price is NULL
C. SELECT COUNT (list_price)
FROM product_information
WHERE list_price i= NULL
D. SELECT COUNT (NVL(list_price, 0))
FROM product_information
WHERE list_price is NULL
Answer: D

NEW QUESTION NO: 10
Evaluate the following SELECTstatement 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_CONDITI R_CONSTRAINT_NAME DELETE_RULE STATUS ON
ORDER_DATE_NN C "ORDER_DATE" IS ENABLED
NOT NULL
ORDER_CUSTOMER_ID_ C "CUSTOMER_ID" IS ENABLED
NN NOT NULL
ORDER_MODE_LOV C order _mode in ENABLED
( 'direct',
' online')
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. In the second column, 'c'indicates a check constraint.
B. The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.
C. The STATUScolumn indicates whether the table is currently in use.
D. The column DELETE_RULEdecides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: A,D

NEW QUESTION NO: 11
View the Exhibit and examine the structure of ORDERSand ORDER_ITEMStables.
ORDER_IDis the primary key in the ORDERStable. It is also the foreign key in the ORDER_ITEMStable wherein it is created with the ON DELETE CASCADEoption.
Which DELETEstatement would execute successfully?

A. DELETE orders o, order_items I
WHERE o.order_id = i.order_id;
B. DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items);
C. DELETE order_id
FROM orders
WHERE order_total < 1000;
D. DELETE orders
WHERE order_total < 1000;
Answer: B

NEW QUESTION NO: 12
Which three statements are true regarding group functions? (Choose three.)
A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
C. They can be used only with a SQL statement that has the GROUPBYclause.
D. They can be used on only one column in the SELECTclause of a SQL statement.
E. They can be used along with the single-row function in the SELECTclause of a SQL statement.
Answer: A,B,E
Explanation/Reference:
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

NEW QUESTION NO: 13
View the exhibit and examine the structure of the EMPLOYEEStable.

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAMEof the managers and the second column would have LAST_NAMEof the employees.
Which SQL statement would you execute?
A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100
B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id = 100;
C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100;
D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id = 100;
Answer: C

NEW QUESTION NO: 14
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 generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
B. It executes successfully but does not give the correct result.
C. It executes successfully and gives the correct result.
D. It generates an error because a subquery cannot have a join condition in a UPDATEstatement.
Answer: B

NEW QUESTION NO: 15
Which three statements are true regarding subqueries?
A. Multiple columns or expressions can be compared between the main query and subquery.
B. Subqueries can contain ORDER BYbut not the GROUP BYclause.
C. Main query and subquery can get data from different tables.
D. Subqueries can contain GROUP BY and ORDER BYclauses.
E. Main query and subquery must get data from the same tables.
F. Only one column or expression can be compared between the main query and subquery.
Answer: A,C,D
Explanation/Reference:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

NEW QUESTION NO: 16
View the exhibits and examine the structures of the COSTSand PROMOTIONStables.


Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
( SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promos with the highest cost in the same time interval.
B. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
C. It displays prod IDs in the promo with the lowest cost.
D. It displays prod IDs in the promos with the lowest cost in the same time interval.
Answer: B

NEW QUESTION NO: 17
View the Exhibit and examine the details of PRODUCT_INFORMATIONtable.
PRODUCT_NAME CATEGORY_ID SUPPLIER_ID
Inkjet C/8/HQ 12 102094
Inkjet C/4 12 102090
LaserPro 600/6/BW 12 102087
LaserPro 1200/8/BW 12 102099
Inkjet B/6 12 102096
Industrial 700/ID 12 102086
Industrial 600/DQ 12 102088
Compact 400/LQ 12 102087
Compact 400/DQ 12 102088
HD 12GB /R 13 102090
HD 10GB /I 13 102071
HD 12GB @7200 /SE 13 102057
HD 18.2GB @10000 /E 13 102078
HD 18.2GB @10000 /I 13 102050
HD 18GB /SE 13 102083
HD 6GB /I 13 102072
HD 8.2GB@5400 13 102093
You have the requirement to display PRODUCT_NAMEfrom the table where the CATEGORY_IDcolumn has values 12or 13, and the SUPPLIER_IDcolumn has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
A. It would execute but the output would return no rows.
B. It would execute and the output would display the desired result.
C. It would not execute because the same column has been used in both sides of the ANDlogical operator to form the condition.
D. It would not execute because the entire WHEREclause condition is not enclosed within the parentheses.
Answer: A

NEW QUESTION NO: 18
You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > '&hiredate'
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?
A. only 'mgr_id'
B. only hiredate'
C. both the substitution variables ''hiredate' and 'mgr_id'.
D. none, because no input required
Answer: A