Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?
A. INTERSECT
B. UNION ALL
C. MINUS
D. UNION
Answer: C
Q2. - (Topic 2)
Which best describes an inline view?
A. a schema object
B. a sub query that can contain an ORDER BY clause
C. another name for a view that contains group functions
D. a sub query that is part of the FROM clause of another query
Answer: D
Explanation:
a sub query that is part of the FROM clause of another query
Incorrect Answer:
Ais not a schema object
Bsub query can contain GROUP BY clause as well.
Cdoes not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21
Q3. - (Topic 1)
Which is the valid CREATE [TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER(4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
Answer: A
Explanation:
Schema Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an
object with a quoted identifier or a nonquoted identifier.
A quoted identifier begins and ends with double quotation marks ("). If you name a schema
object using a quoted identifier, then you must use the double quotation marks whenever
you refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
indicated:
Names must be from 1 to 30 bytes long with these exceptions:
Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to
30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one
byte. For example, suppose you identify a column like this:
"schema"."table"."column"
Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES"
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.
Q4. - (Topic 2)
You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema.
C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible by everyone.
F. Create a view only of it is based on tables that you created.
Answer: C
Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
privilege.
Incorrect Answers
A:You can create a view in your own schema only.
B:You can create a view in your own schema only, not in any schema.
D:There is no sequence view in Oracle.
E:You cannot create a view that is accessible by everyone. You will need specially grant
SELECT privileges on this view for everyone.
F:You can create a view in your own schema, but not only for tables in your schema. You
can use object from other users schemas if you have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301
Chapter 7: Creating Other Database Objects in Oracle
Q5. - (Topic 2)
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Lex De Haan Renske Ladwig Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the
first name as follows:
CUST NAME
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)
A.
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')
"CUST NAME" FROM customers
WHERE INSTR(cust_name, ' ',1,2)<>0;
B.
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')
"CUST NAME" FROM customers
WHERE INSTR(cust_name, ' ',-1,2)<>0;
C.
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',-1,-2)<>0;
D.
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',1,2)<>0 ;
Answer: A,B
Q6. - (Topic 2)
Which object privileges can be granted on a view?
A. none
B. DELETE, INSERT,SELECT
C. ALTER, DELETE, INSERT, SELECT
D. DELETE, INSERT, SELECT, UPDATE
Answer: D
Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12
Q7. - (Topic 1)
You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL; B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;
Answer: C
Explanation:
Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines
.
The format model must be enclosed with single quotation marks and is case-sensitive.
.
The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
.
The names of days and months in the output are automatically padded with blanks.
.
To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)
Q8. - (Topic 1)
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
Answer: B
Explanation:
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
Q9. - (Topic 1)
Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)
A. Combining more than two columns or expressions into a single column in the output
B. Displaying a date in a nondefault format
C. Substituting a character string in a text expression with a specified string
D. Finding the number of characters in an expression
Answer: B,C,D
Q10. - (Topic 2)
You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement?
DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. The data in the EMPLOYEES table is deleted but not the structure.
D. You get an error because the statement is not syntactically correct.
Answer: C
Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHEREcondition]; Incorrect Answer: AStatement will not cause error BDelete statement will not delete the table structure DStatement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19
Q11. - (Topic 1)
View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format: CATEGORIES 5MP Digital Photo Camera's category is Photo Y Box's category is Electronics Envoy Ambassador's category is Hardware Which two queries would give the required output? (Choose two.)
A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
C. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;
Answer: C,D
Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows: q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself
contains a single quotation mark, you can use the quote (q) operator and select your own
quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following
character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.
Q12. - (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
Q13. - (Topic 2)
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.
D. It produces an error because subqueries cannot be used with the CASE expression.
Answer: A
Q14. - (Topic 1)
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table
B. Synonyms are used to reference only those tables that are owned by another user
C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
D. A public synonym and a private synonym can exist with the same name for the same table
Answer: D
Q15. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Evaluate the following query: Exhibit:
The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
D. The alias NAME should not be enclosed within double quotation marks
Answer: A