Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit
Using the PROMOTIONS table, you need to display the names of all promos done after
January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 1 DESC;
B. SELECT promo_name,promo_begin_date "START DATE"
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY "START DATE" DESC;
C. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 2 DESC;
D. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY promo_name DESC;
Answer: B,C
Q2. - (Topic 1)
You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
A.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)='a'
B.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>'' AND SUBSTR(cust_first_name, -2, 1)='a'
C.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a'
D.
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a'
Answer: A
Explanation:
The SUBSTR(string, start position, number of characters) function accepts three
parameters and returns a string consisting of the number of characters extracted from the
source string, beginning at the specified start position:
substr('http://www.domain.com',12,6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the
first character.
When position is negative, then the function counts backward from the end of string.
substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined
by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses
Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position],[nth occurrence of search item])
function returns a number that represents the position in the source string, beginning from
the given start position, where the nth occurrence of the search item begins:
instr('http://www.domain.com','.',1,2) = 18
Q3. - (Topic 1)
View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.
What would be the outcome if all the parentheses are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B
Q4. - (Topic 2)
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C
Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13
Q5. - (Topic 2)
The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. No privileges.
B. Only the SELECT privilege.
C. Only the CONNECT privilege.
D. All the privileges of a default user.
Answer: A
Explanation:
There are no privileges for the user Scott at this point. They are not added themselves to
the user immediately after creation. The DBA needs to grant all privileges explicitly.
Incorrect Answers
B:There are no privileges for the user Scott at this point. SELECT privilege needs to be
added to the user Scott.
C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be
added to the user Scott.
D:There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle
Q6. - (Topic 1)
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;
Answer: A,B
Explanation:
Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C
Q7. - (Topic 2)
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\'
B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'
C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";
D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_'
Answer: A
Explanation: ESCAPE identifier to search for the _ symbol
Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13
Q8. - (Topic 1)
Which statement is true regarding the UNION operator?
A. The number of columns selected in all SELECT statements need to be the same
B. Names of all columns must be identical across all SELECT statements
C. By default, the output is not sorted
D. NULL values are not ignored during duplicate checking
Answer: A
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q9. - (Topic 1)
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
Answer: D
Explanation:
Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):
Q10. - (Topic 1)
You need to create a table for a banking application. One of the columns in the table has the following requirements:
You want a column in the table to store the duration of the credit period
The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion
The maximum period of the credit provision in the application is 30 days
the interest has to be calculated for the number of days an individual has taken a credit for
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TO MONTH
B. NUMBER
C. TIMESTAMP
D. DATE
E. INTERVAL DAY TO SECOND
Answer: E
Q11. - (Topic 2)
Which two statements are true regarding tables? (Choose two.)
A. A table name can be of any length.
B. A table can have any number of columns.
C. A column that has a DEFAULT value cannot store null values.
D. A table and a view can have the same name in the same schema.
E. A table and a synonym can have the same name in the same schema.
F. The same table name can be used in different schemas in the same database.
Answer: E,F
Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.
Q12. - (Topic 2)
Top N analysis requires _____ and _____. (Choose two.)
A. the use of rowid
B. a GROUP BY clause
C. an ORDER BY clause
D. only an inline view
E. an inline view and an outer query
Answer: C,E
Explanation:
The correct statement for Top-N Analysis SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list]
FROM table
ORDER BY Top-N_coloumn)
WHERE ROWNUM <= N;
Incorrect Answer:
AROWID is not require
BGROUP BY clause is not require
DMust have inline view and outer query.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-23
Q13. - (Topic 1)
You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
Answer: D
Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect Answer: Ainvalid syntax Binvalid syntax Cinvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14
Q14. - (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
Q15. - (Topic 2)
In which four clauses can a sub query be used? (Choose four.)
A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the VALUES clause of an INSERT statement
Answer: A,B,D,E
Explanation:
A: a sub query is valid on the INTO clause of an ISERT Statement
B: a sub query can be used in the FROM clause of a SELECT statement
D: a sub query can be used in the WHERE clause of a SELECT statement,
E: a sub query can be used in the SET clauses of an UPDATE statement,
Incorrect Answer:
Csub query cannot be used
F: is incorrect.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5