1Z0-071 Free Practice Test

Which two statements are true regarding constraints?

1. A. A table can have only one primary key and one foreign key.
2. B. A table can have only one primary key but multiple foreign keys.
3. C. Only the primary key can be defined at the column and table levels.
4. D. The foreign key and parent table primary key must have the same name.
5. E. Both primary key and foreign key constraints can be defined at both column and table levels.

Correct Answer: BE

When does a transaction complete? (Choose all that apply.)

1. A. When a PL/SQL anonymous block is executed
2. B. When a DELETE statement is executed
3. C. When a data definition language statement is executed
4. D. When a TRUNCATE statement is executed after the pending transaction
5. E. When a ROLLBACK command is executed

Correct Answer: CDE

Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order' FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query: ERROR
ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?

1. A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
2. B. Enclose the literal character string in the SELECT clause within the double quotation marks.
3. C. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
4. D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Correct Answer: A
References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE-DETAILS tables: SQL> CREATE TABLE DEPARTMfiNT_D£TAILS
DEPARTMENT_ID NUMBER PRIMARY KEY , DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS (COURSE ID NUMBER PRIMARY KEY , COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

1. A. SELECT d.departranc_id, c.cours«_id FROM cource_deatils c LEFT OUTER JOIN departmnt_details d ON (c.dapartmsnt_id=d.departtnent_id);
2. B. SELECT d.department_id,
3. C. course_id FROM dapartment_details d RIGHT OUTER JOIN course_dotails c ON (c.depattnient_id=d.department_id) ;
4. D. SELECT d.department i
5. E. ccours_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id);
6. F. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department id).- (DEPARTMENT_ID) ;

Correct Answer: D

Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

1. A. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
2. B. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
3. C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
4. D. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Correct Answer: AB