| 1. | Which operator will be evaluated first in the following statement?
select (2+3*4/2-8) from dual: |
| a. | | + |
| b. | | - |
| c. | | / |
| d. | | *
|
|
|
|
|
|
|
| 2. | Which of the following SELECT statements should be used to extract the year from the system date to display it in the format "2001"? |
| a. | | SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual; |
| b. | | SELECT TO_DATE(SYSDATE, 'yyyy') FROM dual; |
| c. | | SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual; |
| d. | | SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual; |
| e. | | SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;
|
|
|
|
|
|
|
| 3. | Examine the SQL statements given below:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them? |
| a. | | The two statements produce identical results |
| b. | | The second statement returns a syntax error |
| c. | | There is no need to specify DESC because the results are sorted in descending order by default |
| d. | | None of the above
|
|
|
|
|
|
|
| 4. | Evaluate the SQL statement given below:
SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;
Which of the following values will be displayed? |
| a. | | 46 and 45.93 |
| b. | | 50 and 45.93 |
| c. | | 50 and 45.9 |
| d. | | 45 and 45.93 |
| e. | | 45.95 and 45.93
|
|
|
|
|
|
|
| 5. | An outer join is used when: |
| a. | | The tables being joined have NOT NULL columns |
| b. | | The tables being joined have only matched data. |
| c. | | The columns being joined have NULL values |
| d. | | The tables being joined have only unmatched data |
| e. | | The tables being joined have both matched and unmatched data
|
|
|
|
|
|
|
| 6. | Which SQL statement accepts user input for the columns to be displayed, table name, and the WHERE condition? |
| a. | | SELECT &1, "&2"FROM &3 WHERE last_name = '&4'; |
| b. | | SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4''; |
| c. | | SELECT &1, &2 FROM &3 WHERE last_name = '&4'; |
| d. | | SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
|
|
|
|
|
|
|
| 7. | Examine the structure of the STUDENTS table given below:
STUDENT_ID NUMBER NOT NULL, Primary Key
STUDENT_NAME VARCHAR2 (30)
COURSE_ID VARCHAR2 (10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE
A report is to be created of ten students who achieved the highest ranking in the course INT_SQL and completed the course in the year 1999.
Which SQL statements accomplishes this task? |
| a. | | SELECT student_id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'ORDER BY marks DESC; |
| b. | | SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks; |
| c. | | SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC); |
| d. | | SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC)WHERE ROWNUM <= 10 ; |
| e. | | SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL';
|
|
|
|
|
|
|
| 8. | The value 12* salary* commission_pct has to be calculated for all the employees in the EMP table. Which of the following statements ensures that a value is displayed in the calculated column for all the employees? |
| a. | | SELECT last_name, 12 * salary* commission_pct FROM emp; |
| b. | | SELECT last_name, 12 * salary* (commission_pct,0) FROM emp; |
| c. | | SELECT last_name, 12 * salary* (nvl(commission_pct,0) FROM emp; |
| d. | | SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;
|
|
|
|
|
|
|
| 9. | The titles of all the books that meet the following criteria has to be displayed:
1. Purchased before Feb 21, 2002
2. Price is less than $500 or greater than $900
The result has to be sorted by the date of purchase, starting with the most recently bought book.
Which of the following statements should be used? |
| a. | | SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21-FEB-2002' ORDER BY purchase_date; |
| b. | | SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '21-FEB-2002' ORDER BY purchase date ASC; |
| c. | | SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC; |
| d. | | SELECT Book_title FROM books WHERE price < 500 OR>900 AND purchase_date<'21-FEB-2002' ORDER BY purchase date DESC;
|
|
|
|
|
|
|
| 10. | The EMPLOYEE table contains the following columns:
EMPLOYEE_ID NUMBER NOT NULL
EMP_LNAME VARCHAR2(20) NOT NULL
EMP_FNAME VARCHAR2(10) NOT NULL
DEPT_ID NUMBER
SALARY NUMBER(9,2)
The user needs to retrieve information on employees that have the same department id and salary as an employee id that they will enter. The query results should include employees that do not have a salary, but not the employee that the user entered.
Which statement will return the desired result? |
| a. | | SELECT *
FROM employee
WHERE (dept_id, NVL(salary, 0)) IN
(SELECT dept_id, NVL(salary, 0)
FROM employee
WHERE employee_id = &&1)
AND employee_id <> &&1; |
| b. | | SELECT *
FROM employee
WHERE (dept_id, salary) IN
(SELECT dept_id, NVL(salary, 0)
FROM employee
WHERE employee_id = &1); |
| c. | | SELECT *
FROM employee
WHERE (department, salary) NOT IN
(SELECT department, salary)
FROM employee
WHERE employee_id = &1); |
| d. | | SELECT *
FROM employee
WHERE (dept_id, salary) IN
(SELECT dept_id, salary)
FROM employee
WHERE employee_id = &1
AND salary IS NULL);
|
|
|
|
|
|
|
| 11. | The EMP table contains the following columns:
EMPNO NUMBER(6)
HIREDATE DATE
Review the following SELECT statement:
SELECT empno, TO_DATE('31-DEC-99') - hiredate
FROM emp
WHERE hiredate BETWEEN '31-DEC-00' AND '31-DEC-01';
What are the results of this statement? |
| a. | | The employee numbers of any employees hired on January 1, 2001 are displayed |
| b. | | Date and numeric values are returned |
| c. | | Only numeric values are returned |
| d. | | The employee numbers of all employees hired before the year 2001 are displayed |
| e. | | Only the employee numbers of all employees are displayed because the BETWEEN operator used in the WHERE clause condition is a number function
|
|
|
|
|
|
|
| 12. | Which character function should be used to return a specified portion of a character string? |
| a. | | CONCAT |
| b. | | LENGTH |
| c. | | SUBSTR |
| d. | | INITCAP
|
|
|
|
|
|
|
| 13. | Which character function should be used to return a specified portion of a character string? |
| a. | | CONCAT |
| b. | | LENGTH |
| c. | | SUBSTR |
| d. | | INITCAP
|
|
|
|
|
|
|
What others think about Free Oracle OCP Practice Test - Oracle 9i SQL OCP |
| By: ismailbegum on Jul 21, 2009 |
|
 |
| By: gopal on May 28, 2009 |
| | hi,
I am planning to appear for the OCP TEST.
The papert is 1Z0-047-oracle Database SQL Expert.
It would be a great help if I could get dumps this paper.
Thanks.
Gopal |
|
|
| By: deepak on May 18, 2009 |
|
|
| By: varun on Apr 8, 2009 |
|
|
| By: Raguraman on Feb 9, 2009 |
| | This pratice test is very useful to access our level in OCP and we prepare accordingly |
|
|
| By: Naveed Ali 923214935853 on Feb 11, 2009 |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
| By: jeet on Feb 8, 2009 |
| | It's a good practice for OCP exam. |
|
|
