Online Access Free 1z0-071 Practice Test

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
(Choose two.)

• A.If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
• B.Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
• C.Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into and no explicit value is given for ORD_NO.
  ORD_ITEMS
• D.Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
• E.Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times.

Examine these statements and results:

How many rows are retrieved by the last query?

• A.14
• B.0
• C.42
• D.28

Which statements are true? (Choose all that apply.)

• A.The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
• B.Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
• C.The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
• D.The data dictionary is created and maintained by the database administrator.
• E.The usernames of all the users including the database administrators are stored in the data dictionary.
• F.Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?

• A.It would be automatically enabled and immediate.
• B.It would remain disabled and can be enabled only by dropping the foreign key constraint and re- creating it.
• C.It would remain disabled and has to be enabled manually using the ALTER TABLE command.
• D.It would be automatically enabled and deferred.

Examine the business rule:
Each student can take up 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.STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
• B.PROJECT_ID must be the primary key in the projects entity and foreign key in the STUDENTS entity.
• C.The ERD must have a M:M relationship between the students and projects entities that must be resolved into 1:M relationships.
• D.The ERD must have a1:M relationship between the students and projects entitles.
• E.An associative table must be created with a composite key of STUDENT_ID andPROJECT_ID; which is the foreign key linked to theSTUDENTSandPROJECTSentities.