Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
See the Exhibit and Examine the structure of the CUSTOMERS table:
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;
Answer: D
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
–
NVL(commission_pct,0)
–
NVL(hire_date,'01-JAN-97')
–
NVL(job_id,'No Job Yet')
Q2. - (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
Q3. - (Topic 2)
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Explanation:
To reuse the variable without prompting the user each time you can use && substitution
variable.
Incorrect Answers
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries
Q4. - (Topic 2)
Examine the structure of the CUSTOMERS table:
CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)
A. self-join
B. subquery
C. full outer-join with self-join
D. left outer-join with self-join
E. right outer-join with self-join
Answer: A,B
Q5. - (Topic 1)
The PART_CODE column in the SPARES table contains the following list of values:
Which statement is true regarding the outcome of the above query?
A. It produces an error.
B. It displays all values.
C. It displays only the values A%_WQ123 and AB_WQ123 .
D. It displays only the values A%_WQ123 and A%BWQ123 .
E. It displays only the values A%BWQ123 and AB_WQ123.
Answer: D
Explanation:
Combining Wildcard Characters
The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character.
ESCAPE Identifier
When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\'
Q6. - (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
Q7. - (Topic 1)
The SQL statements executed in a user session as follows: Exhibit:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
A. Both the DELETE statements and the UPDATE statement are rolled back
B. The rollback generates an error
C. Only the DELETE statements are rolled back
D. Only the seconds DELETE statement is rolled back
E. No SQL statements are rolled back
Answer: B,E
Q8. - (Topic 1)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C,D
Explanation: the EMP_ID_SEQ sequence is not affected by modification to the
EMPLOYEES table. Any other column of NUMBER data type in your schema can use the
EMP_ID_SEQ sequence.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_ID_SEQ sequence will be dropped automatically when you drop the
EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4
Q9. - (Topic 1)
Which two statements about sub queries are true? (Choose two.)
A. A sub query should retrieve only one row.
B. A sub query can retrieve zero or more rows.
C. A sub query can be used only in SQL query statements.
D. Sub queries CANNOT be nested by more than two levels.
E. A sub query CANNOT be used in an SQL query statement that uses group functions.
F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.
Answer: B,F
Explanation: Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.
Incorrect Answer: Asub query can retrieve zero or more rows Csub query is not SQL query statement Dsub query can be nested Egroup function can be use with sub query
Q10. - (Topic 1)
The CUSTOMERS table has the following structure: Exhibit:
You need to write a query that does the following task:
*
Display the first name and tax amount of the customers. Tax is 5% of their credit limit
*
Only those customers whose income level has a value should be considered
*
Customers whose tax amount is null should not be considered
Which statement accomplishes all the required tasks?
A.
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL;
B.
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
C.
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL;
D.
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,tax_amount) IS NOT NULL;
Answer: B
Q11. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)
A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY (2*prod_min_price)DESC, prod_name;
C. ORDER BY prod_name, (2*prod_min_price)DESC;
D. ORDER BY prod_name DESC, prod_list_price DESC;
E. ORDER BY prod_list_price DESC, prod_name DESC;
Answer: A,E
Explanation:
Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.
Q12. - (Topic 1)
Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list.
B. It returns the highest NOT NULL value in the list for all rows.
C. It requires that all expressions in the list must be of the same data type.
D. It requires that at least one of the expressions in the list must have a NOT NULL value.
Answer: C
Explanation:
The COALESCE Function The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.
Q13. - (Topic 2)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which DELETE statement is valid?
A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey');
D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');
Answer: C
Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table
[ WHERE condition ];
Incorrect Answers :
A. '=' is use in the statement and sub query will return more than one row.
Error Ora-01427: single-row sub query returns more than one row.
B. Incorrect DELETE statement
D. Incorrect DELETE statement
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data,
p. 8-19
Q14. - (Topic 1)
Which statements are correct regarding indexes? (Choose all that apply.)
A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
D. When a table is dropped, the corresponding indexes are automatically dropped
Answer: A,B,D
Q15. - (Topic 1)
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?
A.
UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
B.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
C.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
D.
UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;
Answer: A
Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing
multiple subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ] ;