| 1. | Examine the following statement:
SELECT empno_seq.CURRVAL
FROM SYS.dual;
Which value is displayed? |
| a. | | Current value of the EMPNO_SEQ sequence |
| b. | | Current value of the EMPNO_SEQ cursor |
| c. | | Values of the EMPNO_SEQ column |
| d. | | Current value of the EMPNO_SEQ index
|
|
|
|
|
|
|
| 2. | If a database is queried with the following command:
SELECT object_name
FROM all_objects
WHERE object_type = 'TABLE';
Which values are displayed? |
| a. | | Only the names of all the tables that can be accessed |
| b. | | Only the names of all the objects that can be accessed |
| c. | | Only the names of the objects that are owned |
| d. | | Only the names of the tables that are owned
|
|
|
|
|
|
|
| 3. | Which of the following commands cause a transaction to end? |
| a. | | DELETE |
| b. | | ALTER |
| c. | | INSERT |
| d. | | UPDATE |
| e. | | ROLLBACK
|
|
|
|
|
|
|
| 4. | Which statements about data types are true? |
| a. | | The BLOB data type stores character data up to four gigabytes |
| b. | | The TIMESTAMP data type is an extension of the VARCHAR2 data type |
| c. | | The CHAR data type should be used for fixed-length character data |
| d. | | The INTERVAL YEAR TO MONTH data type allows time to be stored as an interval of years and months
|
|
|
|
|
|
|
| 5. | Evaluate the following SELECT statement:
SELECT emp_id, name
FROM emp
WHERE emp_id NOT IN (SELECT emp_id
FROM emp
WHERE dept_id = 30
AND job = 'SALESMAN');
What would happen if the inner query returned a NULL value? |
| a. | | No rows would be selected from the EMPLOYEE table |
| b. | | A syntax error would be returned |
| c. | | All the EMPLOYEE_ID and NAME values in the EMPLOYEE table would be displayed |
| d. | | Only the rows with EMPLOYEE_ID values equal to NULL would be included in the results
|
|
|
|
|
|
|
| 6. | In which clauses of a SELECT statement can substitution variables be used? |
| a. | | The SELECT and FROM clauses, but NOT the WHERE clause |
| b. | | The SELECT, FROM, WHERE, and GROUP BY clauses, but NOT the ORDER BY clause |
| c. | | The SELECT, WHERE, GROUP BY, and ORDER BY clauses, but NOT the FROM clause |
| d. | | The SELECT and FROM clauses, but NOT the WHERE clause |
| e. | | The SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses
|
|
|
|
|
|
|
| 7. | Which operators can be used in an outer join condition? |
| a. | | AND |
| b. | | IN |
| c. | | OR |
| d. | | =
|
|
|
|
|
|
|
| 8. | What is the correct use of the Trunc command on a date? |
| a. | | TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual; |
| b. | | Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual; |
| c. | | Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL; |
| d. | | SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
|
|
|
|
|
|
|
| 9. | Which type of join should be written to perform an outer join of tables A and B that returns all rows from B? |
| a. | | Any outer join |
| b. | | A left outer join |
| c. | | A cross join |
| d. | | A right outer join |
| e. | | An inner join
|
|
|
|
|
|
|
| 10. | Which Data Dictionary view holds information about the column in a view? |
| a. | | USER_VIEWS |
| b. | | USER_VIEW_COLUMNS |
| c. | | USER_TAB_COLUMNS |
| d. | | USER_ALL_COLUMNS
|
|
|
|
|
|
|
| 11. | Which of the following SELECT statements will get the result 'ELCOME' from the string 'WELCOME'? |
| a. | | SELECT SUBSTR ('WELCOME',1) FROM dual; |
| b. | | SELECT INITCAP(TRIM('WELCOME', 1,1) FROM dual; |
| c. | | SELECT LOWER (SUBSTR ('WELCOME', 2,1) FROM dual; |
| d. | | SELECT LOWER (SUBSTR('WELCOME', 2,1) FROM dual; |
| e. | | SELECT LOWER (TRIM ('W' FROM 'WELCOME')) FROM dual;
|
|
|
|
|
|
|
| 12. | Which script displays '01-MAR-03' when the JOIN_DATE value is '03-MAR-03'? |
| a. | | SELECT ROUND(join_date, 'YEAR')
FROMEmp |
| b. | | SELECT ROUND(join_date, 'DAY')
FROMEmp |
| c. | | SELECT ROUND(join_date, 'MONTH')
FROMEmp |
| d. | | SELECT ROUND(TO_CHAR(join_date, 'YYYY'))
FROMEmp
|
|
|
|
|
|
|
|
|
| 13. | Evaluate the following statement:
TRUNCATE TABLE product;
Which of the following users can successfully issue this statement? |
| a. | | The owner of the INVENTORY table |
| b. | | Any user with access to the PUBLIC schema |
| c. | | Any user with the DELETE ANY TABLE system privilege |
| d. | | Any member of the CONNECT and RESOURCE roles
|
|
|
|
|
|
|
| 14. | What does SQL stand for? |
| a. | | Strong Question Language |
| b. | | Structured Question Language |
| c. | | Structured Query Language |
| d. | | Structured Quiz Language
|
|
|
|
|
|
|
|
|
| 15. | SQL was formerly known as SEQUEL, what does the 'E' stands for ? |
| a. | | Enquiry |
| b. | | Element |
| c. | | English |
| d. | | Exclusive
|
|
|
|
|
|
|
|
|
| 16. | What will be the result of the following Query ?
SELECT NULLIF(4,4) from dual
|
| a. | | 4 |
| b. | | NULL |
| c. | | 1 |
| d. | | NONE
|
|
|
|
|
|
|
| 17. | WHAT IS OUTPUT OF FOLLOWING QUERY
SELECT ROWNUM,ENAME,SAL FROM EMP WHERE ROWNUM<=2 |
| a. | | It show only the 2nd record. |
| b. | | It show only the record 1 and 2 |
| c. | | It shows no row rows selected. |
| d. | | None of these
|
|
|
|
|
|
|
|
|
| 18. | in pl/sql without using any loop or cursor can i retrieve whole record.
if yes how |
| a. | | no can't |
| b. | | bulk collect |
| c. | | procedure |
| d. | | function
|
|
|
|
|
|
|
| 19. | Can you stop the transaction if your exception is message type. how?
(for explanation sent a mail) |
| a. | | Yes by the help of pragma exception_init |
| b. | | No,you use raise_application_error |
| c. | | It possible in cusor |
| d. | | None of the above
|
|
|
|
|
|
|
|
|
What others think about Oracle OCP Practice Test - Oracle SQL PL/SQL OCP |
| By: Wise on Jul 15, 2011 |
| | I'm so glad to find this site. |
|
 |
| By: shyam on Jun 30, 2011 |
| | its very intresting questions. |
|
|
| By: Radha on May 31, 2011 |
| | Very Intersting and confusion question |
|
|
| By: Mr. Oracle on Feb 3, 2011 |
| | This is not PL/SQL practice test, but SQL practice.. |
|
|
| By: santosh on Jan 24, 2011 |
| | Nice questions collections but should improve test quality. |
|
|
| By: saranya on Dec 21, 2010 |
|
|
| By: RUCHIT on Dec 15, 2010 |
| | good coolection of questions. |
|
|
| By: Sara on Oct 30, 2010 |
| | it is real good way to test our pl/sql knowledge |
|
|
| By: JINA on Aug 27, 2010 |
|
|
| By: kalai on Jul 22, 2010 |
| | this is good test ur learning |
|
|
| By: Sanjay Verma on Jul 9, 2010 |
|
|
| By: vicky on May 17, 2010 |
|
|
| By: subhash on Apr 10, 2010 |
|
|
| By: sunny on Nov 7, 2009 |
|
|
| By: op on Nov 5, 2009 |
|
|
| By: SRINIVAS on Oct 14, 2009 |
|
|
| By: bbb on Sep 29, 2009 |
| | 2 polish playing chess 2 more join and say lets play doubles |
|
|
| By: Preeti on Sep 10, 2009 |
| | The questions are tricky and test your depth about the subject. |
|
|
| By: tushar on Jul 8, 2009 |
|
|
| By: test taker on Jul 4, 2009 |
|
|
| By: Manish on Jun 29, 2009 |
|
|
| By: Kirti Patel on Jun 17, 2009 |
| | Its Nice practice test........ |
|
|
| By: pace on Jun 4, 2009 |
|
|
| By: Rakesh on May 15, 2009 |
|
|
| By: Fhighlander on May 14, 2009 |
| | I think it's a good enough test for you keep studying in order to achieve your goal. |
|
|
| By: ram on Apr 17, 2009 |
|
|
| By: Digvijay on Feb 26, 2009 |
| | Very Nice Practice Test.
Everybody should be take test. |
|
|
| By: Digvijay Singh on Feb 26, 2009 |
| | Ouiz is very good and practice test |
|
|
| By: Vikas on Dec 30, 2008 |
|
|
| By: mano on Dec 22, 2008 |
|
|
| By: Bala on Nov 30, 2008 |
|
|
| By: Madhava Sharma on Nov 20, 2008 |
|
|
| By: Marie on Nov 19, 2008 |
| | 45: does the first answer list the wrong table? If so, then that one should be checked.
44: none of these will return the desired date (in another year).
42: None of these will return 'ELCOME';. the 4th answer would return 'elcome'. |
|
|
| By: aditi on Nov 3, 2008 |
|
|
