| 1. | Which SELECT statement should be used if unique combinations of the POSITION and MANAGER values from the EMP table should be displayed? |
| a. | | SELECT DISTINCT position, manager
FROM emp; |
| b. | | SELECT position, DISTINCT manager
FROM emp; |
| c. | | SELECT position, manager
FROM emp; |
| d. | | SELECT position, manager DISTINCT
FROM employee;
|
|
|
|
|
|
|
| 2. | Which character function should be used to return a specified portion of a character string? |
| a. | | SUBSTR |
| b. | | CONCAT |
| c. | | LENGTH |
| d. | | INITCAP
|
|
|
|
|
|
|
| 3. | Which datatype is assigned to a SQL*Plus variable when created with a DEFINE command? |
| a. | | CHAR |
| b. | | DATE |
| c. | | NUMBER |
| d. | | VARCHAR2
|
|
|
|
|
|
|
| 4. | Which SELECT statement is an equijoin query between two tables? |
| a. | | SELECT location.location_name, employee.salary
FROM location, employee
WHERE location.id = employee.location_no; |
| b. | | SELECT location.location_name, employee.salary
FROM location, employee
WHERE employee.salary BETWEEN location.avg_salary AND location.max_salary; |
| c. | | SELECT location.location_name, employee info.last_name
FROM employee location, employee employee info
WHERE employee info.id >= location.manager_id; |
| d. | | SELECT location.location_name, employee.salary
FROM location, employee
WHERE location.id = employee.region_no(+);
|
|
|
|
|
|
|
| 5. | Examine the structures of the EMP and DEPT tables:
EMP
------------------
EMP_ID NUMBER NOT NULL PK
NAME VARCHAR(30) NOT NULL
FNAME VARCHAR(25) NOT NULL
DEPT_NO NUMBER
TITLE VARCHAR2(25)
DEPT
------------------------
DEPT_ID NUMBER NOT NULL PK
DEPT_NAME VARCHAR2(25)
A list of departments is to be produced, including the department name, that have more than three administrative assistants. Which SELECT statement will produce the desired result? |
| a. | | SELECT dept_name
FROM emp JOIN dept
ON employee.dept_id = department.dept_id
WHERE UPPER(title) = 'ADMINISTRATIVE ASSISTANT'
GROUP BY dept_name
HAVING emp_id > 3; |
| b. | | SELECT dept_name
FROM emp
GROUP BY dept_no
HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3; |
| c. | | SELECT dept_name
FROM emp NATURAL JOIN dept
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3; |
| d. | | SELECT dept_name
FROM emp e JOIN dept d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
AND COUNT(*) > 3; |
| e. | | SELECT d.dept_name
FROM emp e JOIN dept d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;
|
|
|
|
|
|
|
| 6. | Consider the following command:
DROP TABLE ITEM;
An explicit COMMIT has NOT been issued. What can be done to reverse this action and restore the data in the INVENTORY table? |
| a. | | Shut down the database |
| b. | | Terminate your current session |
| c. | | The action cannot be reversed |
| d. | | Issue a ROLLBACK
|
|
|
|
|
|
|
| 7. | A view was created that contained groups of data, does NOT allow DML operations, and does not contain a subquery. Which type of view did was created? |
| a. | | Simple |
| b. | | Inline |
| c. | | Complex |
| d. | | Explicit
|
|
|
|
|
|
|
| 8. | The STUDENT table contains the following columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(50)
A SELECT statement is being written to retrieve the names of students that do NOT have an address.
SELECT last_name||', '||first_name "Student Name"
FROM student
Which WHERE clause should be used to complete this statement? |
| a. | | WHERE address = NULL; |
| b. | | WHERE address IS NULL; |
| c. | | WHERE address!= NULL; |
| d. | | WHERE address IS NOT NULL;
|
|
|
|
|
|
|
| 9. | A database was queried with the following SQL statement:
SELECT CONCAT(LOWER(SUBSTR(Stud_name, 1, 3)), subject_id) "Subject Description"
FROM Student;
In which order are the functions evaluated? |
| a. | | CONCAT, LOWER, SUBSTR |
| b. | | All three will be evaluated simultaneously |
| c. | | LOWER, SUBSTR, CONCAT |
| d. | | SUBSTR, LOWER, CONCAT
|
|
|
|
|
|
|
| 10. | What kinds of commands can be entered at the command prompt in SQL*Plus? |
| a. | | PL/SQL Blocks |
| b. | | SQL*PLUS commands |
| c. | | Security commands |
| d. | | SQL commands
|
|
|
|
|
|
|
| 11. | The STUDENT_GRADES table has the following columns:
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4, 3)
Which of the following statements finds the highest Grade Point Average (GPA) per semester? |
| a. | | SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL; |
| b. | | SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; |
| c. | | SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end; |
| d. | | SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; |
| e. | | SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
|
|
|
|
|
|
|
| 12. | Which ALTER TABLE statement should be used to add a PRIMARY KEY constraint on the MANUFACTURER_ID column of the PRODUCT table? |
| a. | | ALTER TABLE product
ADD PRIMARY KEY (manufacturer_id); |
| b. | | ALTER TABLE product
MODIFY CONSTRAINT PRIMARY KEY manufacturer_id; |
| c. | | ALTER TABLE product
MODIFY manufacturer_id CONSTRAINT PRIMARY KEY; |
| d. | | ALTER TABLE product
ADD CONSTRAINT manufacturer_id PRIMARY KEY;
|
|
|
|
|
|
|
What others think about Oracle OCP Practice Test - Oracle SQL PL/SQL OCP Test Questions |
| By: BENSLIMENE on Jun 24, 2009 |
| | as i'm beginning preparing exams, it's a good practice for me |
|
 |
| By: Santosh Mahajan on Jun 17, 2009 |
|
|
| By: Hari on Jun 12, 2009 |
| | nice questions for practice |
|
|
| By: Ali Tarchichi on Apr 21, 2009 |
|
|
| By: Ali Tarchichi on Apr 21, 2009 |
| | i need to know how can develop an application of oracle |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
| By: varun on Feb 26, 2009 |
| | very good for practice...!!! |
|
|
