1Z0-071 | The Secret Of Oracle 1Z0-071 Actual Test

NEW QUESTION 1
Which statement is true about Data Manipulation Language (DML)?

• A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.
• B. Each DML statement forms a transaction by default.
• C. A transaction can consist of one or more DML statements.
• D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

Answer: C

NEW QUESTION 2
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

• A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
• B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
• C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
• D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
• E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

Answer: BE

Explanation:
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html

NEW QUESTION 3
Examine the structure of the SALES table. (Choose two.)

Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

• A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
• B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
• C. It will not be created because of the invalid WHERE clause.
• D. It is created with no rows.
• E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Answer: BD

NEW QUESTION 4
View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

• A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
• B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
• C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
• D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Answer: CD

NEW QUESTION 5
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)

The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them. The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?

• A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details dON (p.based_on = d.task_id);
• C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
• D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details dON (p.based_on = d.task_id);

Answer: D

NEW QUESTION 6
View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

• A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
• B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
• C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
• D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer: D

NEW QUESTION 7
Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

• A. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
• B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
• C. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM programs;
• D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’) FROMprograms

Answer: AD

NEW QUESTION 8
View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)

Which two statements are true regarding data type conversion in query expressions?

• A. inv_date = '15-february-2008' :uses implicit conversion
• B. inv_amt = '0255982' : requires explicit conversion
• C. inv_date > '01-02-2008' : uses implicit conversion
• D. CONCAT(inv_amt, inv_date) : requires explicit conversion
• E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: AE

NEW QUESTION 9
View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE, (SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?

• A. The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
• B. The statement will fail because a subquery cannot be used in a VALUES clause.
• C. The statement will execute and a new row will be inserted in the SALES table.
• D. The statement will fail because the VALUES clause is not required with the subquery.

Answer: C

NEW QUESTION 10
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation:
References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 11
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator: SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p sales s ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

• A. FULL OUTER JOIN
• B. JOIN
• C. LEFT OUETR JOIN
• D. RIGHT OUTER JOIN

Answer: AC

NEW QUESTION 12
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

• A. By default, EM express is available for a database after database creation.
• B. You can use EM express to manage multiple databases running on the same server.
• C. You can perform basic administrative tasks for pluggable databases by using the EM express interface.
• D. You cannot start up or shut down a database Instance by using EM express.
• E. You can create and configure pluggable databases by using EM express.

Answer: A

NEW QUESTION 13
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 get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. 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.

Answer: B

NEW QUESTION 14
Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
• B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
• C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 15
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?

• A. SYSDATE cannot be used with the CHECK constraint.
• B. The BETWEEN clause cannot be used for the CHECK constraint.
• C. The CHECK constraint cannot be placed on columns having the DATE data type.
• D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: A

NEW QUESTION 16
......