Free 1Z0-071 Exam Dumps

Question 11

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.

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

Question 12

Which two statements are true regarding constraints? (Choose two.)

A. A constraint is enforced only for an INSERT operation on a table.
B. A foreign key cannot contain NULL values.
C. The column with a UNIQUE constraint can store NULLS.
D. You can have more than one column in a table as part of a primary key.

Correct Answer:CD

Question 13

Which statement is true regarding the UNION operator?

A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.

Correct Answer:B

Question 14

Examine the structure of the INVOICE table. NameNull?Type
-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?

A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Correct Answer:AC

Question 15

Examine the structure of the SALES table. (Choose two.)
1Z0-071 dumps exhibit
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.

Correct Answer:BD