| 1. | Which SQL SELECT statement performs a projection, a selection, and join when executed? |
| a. | | SELECT id_number,seller_id
FROM ITEM
ORDER BY seller_id, id_number; |
| b. | | SELECT id_number,seller_id
FROM ITEM
WHERE seller_id = 'F10032'; |
| c. | | SELECT seller_id, city
FROM SALLER
AND seller_id = 'F10032'
ORDER BY city; |
| d. | | SELECT p.id_number, s.seller_id, seller.city
FROM ITEM p, SALLER s
WHERE p.seller_id = m.seller_id
AND m.seller_id='F10032';
|
|
|
|
|
|
|
| 2. | Which component is a literal in the following select statement?
Select 'Emp name :'||ename from emp where deptno=20;
|
| a. | | ename. |
| b. | | 20. |
| c. | | emp name. |
| d. | | ||.
|
|
|
|
|
|
|
| 3. | Which of the following is an iSQL*PLUS command? |
| a. | | INSERT |
| b. | | UPDATE |
| c. | | SELECT |
| d. | | DESCRIBE |
| e. | | DELETE
|
|
|
|
|
|
|
| 4. | Which of the following components are required to run iSQL*PLUS on PC? |
| a. | | SQL*PLUS installed on the PC |
| b. | | HTTP Server |
| c. | | Oracle net on PC |
| d. | | iSQL*PLUS Server
|
|
|
|
|
|
|
| 5. | Which of the following SQL statement returns a numeric value? |
| a. | | SELECT ADD_MONTHS(MAX (hire_date), 6) FROM EMP; |
| b. | | SELECT ROUND(hire_date)FROM EMP; |
| c. | | SELECT sysdate-hire_date FROM EMP; |
| d. | | SELECT TO_NUMBER(hire_date + 7)FROM EMP;
|
|
|
|
|
|
|
| 6. | _______ operator can be used with a multiple row SubQuery. |
| a. | | = |
| b. | | LIKE |
| c. | | BETWEEN |
| d. | | NOT IN |
| e. | | Is
|
|
|
|
|
|
|
| 7. | The names of the employees who have been with the company for more than five years is to be listed. Which of the following SQL statements will display the required results? |
| a. | | SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5 |
| b. | | SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5 |
| c. | | SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5 |
| d. | | SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5
|
|
|
|
|
|
|
| 8. | Examine the structures of the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE
------------------
EMP_ID NUMBER NOT NULL PK
NAME VARCHAR(30) NOT NULL
FNAME VARCHAR(25) NOT NULL
DEPT_NO NUMBER
TITLE VARCHAR2(25)
DEPARTMENT
------------------------
DEPT_ID NUMBER NOT NULL PK
DEPT_NAME VARCHAR2(25)
A list of departments has to be created including the department name that have more than three administrative assistants. Which SELECT statement will produce the desired result? |
| a. | | SELECT d.dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3; |
| b. | | SELECT dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
AND COUNT(*) > 3; |
| c. | | SELECT dept_name
FROM employee NATURAL JOIN department
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3; |
| d. | | SELECT dept_name
FROM employee
GROUP BY dept_no
HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3;
|
|
|
|
|
|
|
| 9. | The PRODUCT table contains the following columns:
PRODUCT_ID NUMBER(9)
DESCRIPTION VARCHAR2(20)
COST NUMBER(5,2)
MANUFACTURER_ID VARCHAR2(10)
The product costs needs to be displayed with the following results:
1. The cost displayed for each product is increased by 20 percent.
2. The product manufacturer id must be 5001, 5020, or 5050.
3. Twenty percent of the original cost is less than $4.
Which statement should be used? |
| a. | | SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4
AND manufacturer_id = ('5001','5020','5050'); |
| b. | | SELECT description, cost * .20
FROM product
WHERE cost * .20 < 4.00
AND manufacturer_id BETWEEN '5001' AND '5050'; |
| c. | | SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00
AND manufacturer_id IN ('5001', '5020', '5050'); |
| d. | | SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00
AND manufacturer_id ANY('5001', '5020', '5050');
|
|
|
|
|
|
|
| 10. | Consider the following SELECT statement:
SELECT emp_id, name
FROM employee
WHERE emp_id NOT IN (SELECT emp_id
FROM employee
WHERE depart_id = 30
AND job = 'CLERK');
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 EMP_ID and NAME values in the EMPLOYEE table would be displayed |
| d. | | Only the rows with EMP_ID values equal to NULL would be included in the results
|
|
|
|
|
|
|
| 11. | In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement? |
| a. | | Immediately after the SELECT clause |
| b. | | Before the WHERE clause |
| c. | | After the ORDER BY clause |
| d. | | After the WHERE clause
|
|
|
|
|
|
|
| 12. | A database was queried with the following command:
SELECT name, salary, dept_id FROM employee WHERE salary >
(SELECT AVG(salary) FROM employee WHERE dept_no =
(SELECT dept_no FROM employee WHERE last_name =
(SELECT last_name FROM employee WHERE salary > 50000)));
Which SELECT clause is evaluated first?
|
| a. | | SELECT dept_no |
| b. | | SELECT last_name |
| c. | | SELECT name, salary, dept_id |
| d. | | SELECT AVG(salary)
|
|
|
|
|
|
|
| 13. | find the name of the employee from emp table whose fname second charcter begins with 'e' |
| a. | | select * from emp where fname like'_e%' |
| b. | | select * from emp where fname like'e%' |
| c. | | select * from emp where fname like'_e' |
| d. | | select * from emp where fname like'ae%'
|
|
|
|
|
|
|
What others think about Oracle OCP Practice Test - Oracle 9i SQL OCP I |
| By: tsicgoetrt on Sep 19, 2010 |
| | Keep QuizMoz a free site for all. |
|
 |
| By: dips on Sep 7, 2010 |
|
|
| By: sri on Feb 10, 2010 |
| | thank you for creating this site! |
|
|
| By: jahangir on Dec 24, 2009 |
| | This site is very good for practice in oracle.
I appreciate the owner of this site. |
|
|
| By: anu on Sep 6, 2009 |
|
|
| By: Rudr on Aug 14, 2009 |
|
|
| By: Sudeep on Aug 9, 2009 |
| | It's a very good test to evaluate ur knowledge |
|
|
| By: Ayush on Aug 4, 2009 |
|
|
| By: suresh kumar R on May 22, 2009 |
| | A good test which builds our confidence.... |
|
|
| By: Mike Masembe on May 12, 2009 |
|
|
| By: varun on Feb 26, 2009 |
| | a very good test....superb...!!!! |
|
|
| By: TOM on Feb 23, 2009 |
|
|
| By: piyush on Jan 2, 2009 |
|
|
| By: Gfh on Dec 29, 2008 |
| | I feel Q.No. 58 answer is wrong. |
|
|
| By: rao on Dec 20, 2008 |
|
|
| By: PRAVEEN on Nov 16, 2008 |
| | very useful thing for training |
|
|
| By: Parag Shukla on Oct 7, 2008 |
| | It is really a very good practice. and i enjoy with it and i really very thankful to the content collector. |
|
|
| By: JAI on Oct 3, 2008 |
| | IT IS GREAT TO SEE SUCH TYPE OF SITE FOR THE BEGINEERS WHO WILL ENJOY AND STUDY THE OCP EXAMS AND THANKS TO THE CONTENT WRITERS |
|
