Monday, April 07, 2008

OCP/OCA: Oracle Certified Professional Exam Sample Questions

what will the following query return? SELECT REPLACE(RTRIM('Anticipation','on'), 'ti','shun') from DUAL;
Anticipashun
Anshuncipashun
Anshuncipashunon
Anticipashunon
In oracle, what do trigonometric functions operate on?
Degrees
Radians
Gradients
The default is radians, but degrees or gradients can be specified
If it is 5 minutes past noon on 15 jan 2000, what will the following statement return? SELECT ROUND(SYSDATE) ROUND(SYSDATE,'Y') FROM DUAL;
155
15
0
16
Which statement about nested functions is most correct?
Single-row nested functions can be nested in either single-row or group functions
Group functions can be nested in other group functions
Group functions cab be nested in single-row functions
A, B and C
A and B only
Why will the following query raise an exception? SELECT DEPT_NO, AVG(DISTINCT SALARY), COUNT(JO
JOB_COUNT FRIM EMP WHERE MGR LIKE 'J%' OR ABS(SALARY)>10 HAVING COUNT (JO
>5 ORDER BY 2 DESC;
A HAVING clause cannot contain a group function
The GROUP BY clause is missing
Abs() is not an oracle function
The query will not raise an exception
Why does the following SELECT statement fail? SELECT colorname Colour, MAX(cost) From itemdetail Where upper(colorname) like '%WHITE%' Group by colour Having count(*) > 20;
A GROUP BY clause cannot contain a coloumn alias
The condition COUNT (*) > 20 should be in the WHERE clause
The GROUP BY clause must contain the group functions used in the SELECT list
The HAVING Clause can only contain the group functions used in the SELECT list
What will the following query report? SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
The number of employees in each department, including those without a deptnno
The number of employees in each department, ecept those without a deptno
The total number of employees, including those without a deptno
The total number of employees, except those without a deptno
Which assertion about the following quires is true> SELECT COUNT(DISTICT mgr), MAX(DISTINCT salary) from emp; SELECT COUNT (ALL mgr), MAX(ALL salary) FROM emp;
They will always return the same numbers in columns 1 and 2
They may return different numbers in column 1 but will always return the same number in column 2
They may return different numbers in column 1 and may return different numbers in column 2
They will always return the same number in column 1 but may return different numbers in column 2
What is the limit on the number of values a subquery using the IN operator can return to the parent query?
1
32,764
unlimited
0
When using multiple tables to query information, in which clause do you specify the table names?
HAVING
GROUP BY
WHERE
FROM
The contents of the CONTESTANTS table are listed as follows: NAME AGE COUNTRY ---------------- -------------- --------------- BERTRAND 24 FRANCE GONZALEZ 29 SPAIN HEINRICH 22 GERMANY TAN 39 CHINA SVENSKY
RUSSIA SOO 21 You issue the following query against this table: SELECT NAME FROM CONTESTANT WHERE (COUNTRY, AGE) IN ( SELECT COUNTRY, MIN(AGE) FROM CONTESTANT GROUP BY COUNTRY); What is the result?
SOO
HEINRICH
BERTRAND
GONZALEZ
To delete any constraint from the table we have to use command
Drop Constraint
Delet
Alter *
Truncate
All the operators are used in single row subquery except one
Between and
<>
=
in
All the commands executes in iSQLplus except one
Column
Compute
define
Accept
Ascript file which will be executed automatically in iSQLPlus is
afiedtbuf
loginsql
both a and b
none of the above
A command in iSQL plus is used to give the status of old and new value of variable
set feedback
set verify
set confirm
none of the above
All commands are used to save the changes of the transaction except one
Commit
exitting from sqlplus
DDL command
savepoint
none of the above
A Clause which is used in joining two tables other than equality operator is
join
on
in
using
A Clause which is the pseudocolumn used to know the current value of the sequence
nextval
current_val
currval
none of the above
A Query which is used in top-N analysis is
subquery
correlated subquery
inline query
outer query
An operator is used to get and display the redundant records
Union all
Distinct
Union
Intersect
All the datatypes with respect to Oracle 9i is true except one
DATE
TIMESTAMP
TIMSTAMP with TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
None of the above
You are creating some tables in your database as part of the logical data model Which of the following constraints have an index associated with them that is generated automatically by Oracle?
Unique
Foreign-key
Check
NOT NULL
You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view You issue the DROP TABLE CASCADE CONSTRAINTS statement Which of the following objects will still remain after the statement is issued?
A The triggers B The indexes C The foreign keys in the other tables D The view
In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method would be used?
A Setting preferences in the appropriate menu option B Creating an appropriate LOGINSQL file C Issuing the ALTER USER statement D Issuing the ALTER TABLE statement
The EMP_SALARY table has two columns, EMP_USER and SALARY EMP_USER is set to be the same as the Oracle username To support user MARTHA, the salary administrator, you create a view with the following statement: CREATE VIEW EMP_SAL_VW AS SELECT EMP_USER, SALARY FROM EMP_SALARY WHERE EMP_USER <> 'MARTHA'; MARTHA is supposed to be able to view and update anyone in the company's salary except her own through this view Which of the following clauses do you need to add to your view creation statement in order to implement this functionality?

WITH ADMIN OPTION
WITH GRANT OPTION
WITH SECURITY OPTION
WITH CHECK OPTION
You are developing PL/SQL code to manipulate and store data in an Oracle table All of the following numeric datatypes in PL/SQL can be stored in an Oracle database, except one Which is it?
CHAR
RAW
DATE
INTEGER
You are performing some conversion operations in your PL/SQL programs To convert a date value into a text string, you would use which of the following conversion functions?
CONVERT
TO_CHAR
TO_NUMBER
TO_DATE
You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken Tests given in various locations throughout the country are stored in this table A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location Which of the following SQL statements would be useful for identifying the students who have done so?
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT BJERSEY_NUM, BPOSITION, BNAME FROM BASEBALL_TEAM B WHERE BNAME = USER; What will happen when user JONES attempts to SELECT a listing for user SMITH?
The SELECT will receive an error
The SELECT will succeed
The SELECT will receive NO ROWS SELECTED
The SELECT will add data only to BASEBALL_TEAM
You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
51
95
30
75
What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
A VARCHAR2 value
An error; you can't do this with dates
-36493 if the NLS_DATE_FORMAT is set to the default value
What is the purpose of the SUBSTR string function?
To insert a capital letter for each new word in the string
To return a specified substring from the string
To return the number of characters in the string
To substitute a non-null string for any null values returned
Evaluate this command: SELECT iisotope, gcalibration FROM chart_n i, gamma_calibrations g WHERE ienergy = genergy; What type of join is the command?
Equijoin
Nonequijoin
Self-join
The statement is not a join query
In a SELECT statement, which character is used to pass in a value at runtime?
\
%
&
_ (underscore)
Which single-row function could you use to return a specific portion of a character string?
INSTR
SUBSTR
LPAD
LEAST
What will the following statement return? SELECT LAST_NAME, FIRST_NAME, START_DATE FROM EMPLOYEES WHERE HIRE_date< Trunc(sysdate) 5;
Employees hired in the past 5 years
Employess hired in the past 5 days
Employees hired more thatn 5 years ago
Employees hired more than 5 days ago
Which function(s) accept arguments of any datatype? Select all that apply
SUBSTR
NVL
ROUND
DECODE
SIGN
What will be returned from SIGN(ABS(NVL(-32,0)))?
1
32
1
0
NULL
Which functions could you use to strip leading characters from a character string Select two
LTRIM
SUBSTR
RTRIM
INSTR
MOD

No comments: