Q1. - (Topic 1)
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. The number of columns and data types must be identical for all SELECT statements in the query
C. The names of columns in all SELECT statements must be identical
D. Reversing the order of the intersected tables the result
Answer: B
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and
removing duplicates.
The columns in the queries that make up a compound query can have different names, but
the output result set will use the names of the columns in the first query.
Q2. - (Topic 2)
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string
Answer: A,C,D
Q3. - (Topic 1)
User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.
Which SQL statement can Mary use to accomplish that task?
A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;
B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);
C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;
D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;
E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;
F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;
Answer: C
Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;
Q4. - (Topic 2)
Examine the structure of the EMP_DEPT_VU view:
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Answer: E
Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error
Q5. - (Topic 1)
Examine the description of the EMP_DETAILS table given below: Exhibit:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause
B. You cannot add a new column to the table with LONG as the data type
C. An EMP_IMAGE column cannot be included in the ORDER BY clause
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
Answer: B,C
Explanation:
LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.
There can only be one LONG column in a table.
Guidelines
A LONG column is not copied when a table is created using a subquery.
A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
Only one LONG column can be used per table.
No constraints can be defined on a LONG column.
You might want to use a CLOB column rather than a LONG column.
Q6. - (Topic 1)
Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation: Explanation:
The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command.
This command returns a description of tablename, including all columns in that table, the
datatype for each column and an indication of whether the column permits storage of NULL
values.
Incorrect Answer:
A INSERT is not a SQL*PLUS command
B UPDATE is not a SQL*PLUS command
C SELECT is not a SQL*PLUS command
E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
Q7. - (Topic 2)
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
A. An error is returned.
B. You are prompted to enter a new value.
C. A report is produced that matches the first report produced.
D. You are asked whether you want a new value or if you want to run the report based on the previous value.
Answer: C
Explanation:
use the double-ampersand if you want to reuse the variable value without prompting the user each time.
Incorrect Answer: Ais not an error
B&& will not prompt user for second time D&& will not ask the user for new value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13
Q8. - (Topic 2)
Which four are valid Oracle constraint types? (Choose four.)
A. CASCADE
B. UNIQUE
C. NONUNIQUE
D. CHECK
E. PRIMARY KEY
F. CONSTANT
G. NOT NULL
Answer: B,D,E,G
Explanation:
Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect Answer: AIs not Oracle constraint CIs not Oracle constraint FIs not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3
Q9. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
Answer: C,E
Q10. - (Topic 2)
The EMPLOYEES table has these columns:
LAST NAMEVARCHAR2(35) SALARYNUMBER(8,2) HIRE_DATEDATE
Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:
ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);
What is true about your 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 at 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.
Answer: B
Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Existing
rows will not be affected.
Incorrect Answers
A:Column definitions can be altered to add DEFAULT values.
C:Column definitions can be altered to add DEFAULT values. It works for columns with a
NUMBER data type also.
D:A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224 Chapter 5: Creating Oracle Database Objects