| 1. | Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table? |
| a. | | CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno); |
| b. | | CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)); |
| c. | | CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno)); |
| d. | | CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
|
|
|
|
|
|
|
| 2. | Which of the following constitute the correct guidelines for naming database tables? |
| a. | | Must begin with either a number or letter |
| b. | | Must be 1-30 characters long |
| c. | | Should not be an Oracle Server reserved word |
| d. | | Must contain only A-Z, a-z, 0-9, _,*, and # |
| e. | | Must contain only A-Z, a-z, 0-9, _, $, and # |
| f. | | Must begin with a letter
|
|
|
|
|
|
|
| 3. | Which condition would allow a user to grant SELECT privileges on the ITEM table to everyone using the PUBLIC keyword? |
| a. | | The user owns the ITEM table |
| b. | | The user has SELECT privileges on the ITEM table |
| c. | | The user has been granted the SELECT privilege with the PUBLIC OPTION |
| d. | | The user has been granted the PUBLIC privilege
|
|
|
|
|
|
|
| 4. | Which of the following privileges is an object privilege? |
| a. | | INDEX |
| b. | | CREATE SESSION |
| c. | | DROP USER |
| d. | | BACKUP ANY TABLE
|
|
|
|
|
|
|
| 5. | An ELEMENT table is being created. The atomic weights of elements have varying decimal places. For example, values could be 4, 6.45, or 5.5678.
Which data type would be most appropriate for the atomic weight values? |
| a. | | LONG |
| b. | | RAW |
| c. | | NUMBER(p,s) |
| d. | | NUMBER
|
|
|
|
|
|
|
| 6. | Which statement is true concerning a new user that has only been granted the CREATE SESSION privilege? |
| a. | | The user can alter their password |
| b. | | The user can select from tables they have created |
| c. | | The user can create a table |
| d. | | The user can grant privileges on objects that they own
|
|
|
|
|
|
|
| 7. | Which script should be executed to create user JOY and give him the ability to connect to the database and the ability to create tables, sequences, and procedures in his schema? |
| a. | | CREATE Joy
IDENTIFIED BY Joy18;
GRANT CREATE CONNECT, TABLE, SEQUENCE, PROCEDURE
TO Joy |
| b. | | CREATE USER Joy
IDENTIFIED BY Joy18;
GRANT CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO Joy |
| c. | | CREATE OR REPLACE USER Joy
IDENTIFIED BY Joy18
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE |
| d. | | CREATE USER Joy
IDENTIFIED BY Joy18;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO Joy
|
|
|
|
|
|
|
| 8. | When could an index decrease the speed of a query? |
| a. | | The column is used in a WHERE clause |
| b. | | The column contains a wide range of values |
| c. | | The table is small |
| d. | | The column contains a large number of null values
|
|
|
|
|
|
|
| 9. | Which SELECT statement will display the next value of the ITEM_ID_SEQ sequence by actually retrieving the value from the sequence? |
| a. | | SELECT NEXTVAL(item_id_seq)
FROM SYS.DUAL; |
| b. | | SELECT item_id_seq.NEXTVAL
FROM SYS.DUAL; |
| c. | | SELECT NEXTVAL(item_id_seq)
FROM item; |
| d. | | SELECT item_id_seq NEXTVAL
FROM item;
|
|
|
|
|
|
|
| 10. | The EMPLOYEES table has these columns:
LAST_NAME VARCHAR2 (35)
SALARY NUMBER (8,2)
HIRE_DATE DATE
Management wants to add a default value to the SALARY column. It was planned to alter the table by using the following SQL statement:
ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);
Which is true about the ALTER statement? |
| a. | | Column definitions cannot be altered to add DEFAULT values |
| b. | | A change to the DEFAULT value affects only subsequent insertions to the table |
| c. | | Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type |
| d. | | All the rows that have a NULL value for the SALARY column will be updated with the value 5000
|
|
|
|
|
|
|
| 11. | Which data dictionary table should be queried to view the object privileges granted to the user on specific columns? |
| a. | | USER_TAB_PRIVS_MADE |
| b. | | USER_TAB_PRIVS |
| c. | | USER_COL_PRIVS_MADE |
| d. | | USER_COL_PRIVS
|
|
|
|
|
|
|
| 12. | The following command was issued:
SELECT emp_id_seq.CURRVAL
FROM SYS.dual;
Which value is displayed? |
| a. | | Values of the EMP_ID_SEQ column |
| b. | | Current value of the EMP_ID_SEQ sequence |
| c. | | Current value of the EMP_ID_SEQ cursor |
| d. | | Current value of the EMP_ID_SEQ sequence
|
|
|
|
|
|
|
What others think about Free Oracle Test OCP Practice Test - Oracle 9i SQL OCP |
| By: kcube on Nov 10, 2009 |
|
 |
| By: Vasudevan on Sep 2, 2009 |
|
|
| By: Ravi on Jun 3, 2009 |
|
|
| By: sneha mishra on Mar 24, 2009 |
| | good and innovative questions. |
|
|
| By: genie on Mar 12, 2009 |
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
| By: genie on Mar 12, 2009 |
|
|
