Custom Search

Saturday, November 15, 2008

Part - 5 Oracle Interview

Question: 41. (C)

Management has asked you to calculate the value 12*salary* comossion_pct for all the
employees in the EMP table. The EMP table contains these columns:



Which statement ensures that a value is displayed in the calculated columns for all employees?

A. SELECT last_name, 12*salary*commison_pct
FROM emp;

B. SELECT last_name, 12*salary* (commission_pct,0)
FROM emp;

C. SELECT last_name, 12*salary*(nvl(commission_pct,0))
FROM emp;

D. SELECT last_name, 12*salary*(decode(commission_pct,0))
FROM emp;

Answer: C

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string is returned. The value specified to be returned if the column value is NULL must be the same
datatype as the column specified.

Incorrect Answers

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: It is incorrect syntax in this query: NVL function needs to be used for correct result.
D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it cannot have only two parameters.

Question: 42. (C)

Examine the description of the STUDENTS table:



Which two aggregate functions are valid on the START_DATE column? (Choose two)

A. SUM(start_date)

B. AVG(start_date)

C. COUNT(start_date)

D. AVG(start_date, end_date)

E. MIN(start_date)

F. MAXIMUM(start_date)

Answer: C & E

Explanation:

It is possible to apply COUNT() and MIN() functions on the column with DATE data type.

Incorrect Answers

A: Function SUM() cannot be used with DATE data type column.
B: Function AVG() cannot be used with DATE data type column.
D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.
F: There is no MAXIMUM() function in Oracle, only MAX() function exists.

Question: 43. (C)

The EMPLOYEE tables has these columns:



You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

A. SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;

B. SELECT last_name, (salary * 12) * IFNULL(commission_pct,
0)
FROM EMPLOYEES;

C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0)
FROM EMPLOYEES;

D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;

Answer: D

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string isreturned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.

Incorrect Answers

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT
column.
B: There is no IFNULL() function in Oracle.
C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.

Question: 44. (C)

You would like to display the system date in the format "Monday, 01 June, 2001".
Which SELECT statement should you use?

A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY')
FROM dual;

B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY')
FROM dual;

C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY')
FROM dual;

D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;

E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY')
FROM dual;

Answer: C

Explanation:

This answer is correct: "Day" shows the day spelled out, "DD" shows the two-digit date, "Month" provides the month spelled out, "YYYY" shows the four -digit year. "FMDay" is special format mask to suppresses the extra spaces between the name of the day and the number of the date.

Incorrect Answers

A: This statement will return an error because of inappropriate usage of the TO_DATE() function.
B: Incorrect format mask "DY" is used to show the number of the day.
D: Incorrect format mask "DY" is used to show the name of the day and format mask "DDD" is used to show the number of the day.
E: Incorrect format mask 􀂳DY􀂴 is used to show the name of the day and format mask "DDD" is used to show the number of the day. Also this statement will return an error because of inappropriate usage of the TO_DATE() function.

Question: 45. (C)

Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?

A. 0

B. 1

C. 0.00

D. An error statement

Answer: A

Explanation:

Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the decimal precision of y.

Incorrect Answers

B: Result will be 0, not 1.
C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions (TRUNC and ROUND) return 0 also.
D: There is no error in this statement.

Question: 46. (C)

Examine the description of the MARKS table:



SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2)
ORDER BY total marks;
What is the result of the SELECT statement?

A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.

B. The statement returns an error at the SELECT clause.

C. The statement returns an error at the WHERE clause.

D. The statement returns an error at the ORDER BY clause.

Answer: C

Explanation:

The statement returns an error at the WHERE clause because group function AVG() cannot be used in the WHERE clause. Group functions can be used in SELECT clause and GROUP BY clause. They allow you to perform data operations on several values in a column of data as though the column were one collective group of data.

Incorrect Answers

A: The statement does not execute successfully because an error will be generated.
B: The statement returns an error at the WHERE, not at the SELECT clause.
D: The statement returns an error at the WHERE, not at the ORDER BY clause.

Question: 47. (C)

Which three SELECT statements displays 2000 in the format 􀂳$2,000.00􀂴? (Choose three)

A. SELECT TO_CHAR (2000, '$#,###.##􀂶)
FROM dual;

B. SELECT TO_CHAR (2000, '$0,000.00􀂶)
FROM dual;

C. SELECT TO_CHAR (2000, '$9,999.00􀂶)
FROM dual;

D. SELECT TO_CHAR (2000, '$9,999.99􀂶)
FROM dual;

E. SELECT TO_CHAR (2000, '$2,000.00􀂶)
FROM dual;

F. SELECT TO_CHAR (2000, '$N,NNN.NN􀂶)
FROM dual;

Answer: B, C & D

Explanation:

Only queries in answers B, C and D will show result as in the format 􀂳$2,000.00􀂴.

Incorrect Answers

A: Oracle error "ORA-01481: invalid number format model" will be generated.
E: Oracle error "ORA-01481: invalid number format model" will be generated.
F: Oracle error "ORA-01481: invalid number format model" will be generated.

Question: 48. (C)

Examine the description of the EMPLOYEES table:



Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?

A. SELECT dept_id, MIN(salary(, MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;

B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;

C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

Answer: D

Explanation:

This SELECT statement shows correct result.

Incorrect Answers

A: To provide correct data statement needs also GROUP BY clause.
B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause.
E: You need only grouping by department, not by salary.

Question: 49. (C)

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

F. You cannot group the rows of a table by more than one column while using aggregate
functions.

Answer: C, D

Explanation:

It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.

Incorrect Answers

A: You cannot use aggregate functions in any clause of a SELECT statement. For example, they cannot be used with a WHEN statement.
B: It is not possible to use aggregate functions in the WHERE clause of a SELECT statement.But they can be used with a HAVING clause used after the GROUP BY clause, for example.
E: You don't need to group the whole table as one single group.
F: It is possible to group more than one column while using aggregate functions.\

Question: 50. (C)

Which four statements correctly describe functions that are available in SQL? (Choose four)

A. INSTR returns the numeric position of a named character.

B. NVL2 returns the first non-null expression in the expression list.

C. TRUNCATE rounds the column, expression, or value to n decimal places.

D. DECODE translates an expression after comparing it to each search value.

E. TRIM trims the heading of trailing characters (or both) from a character string.

F. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.

G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.

Answer: A, D, E, G

Explanation:

INSTR returns the numeric position of a named character. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.

Incorrect Answers

B: This statement is not correct. The following is the Syntax for NVL2 function:
NVL2(expr1, expr2, expr3). If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.
C: Command TRUNCATE is used to remove all row data from the table, while leaving the
definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
F: NVL returns second parameter value if first one is NULL.

Add to Google Reader or Homepage

Cheapest predictive dialer for callcenter power dial

Power Dial predictive dialer for callcenter with less investment and more features based in the heart of Hyderabad AP India.Power Dial has setup more then 200 centers and more then 5000 seats supports is provided.
For best quotes, pricing and other details mail me asap at powerdial.hyd@gmail.com

Vivaan Kumar
http://powerdial.blogspot.com/
Cheapest Predictive Dialer power Dial
cheap pd power dial
predictive dialer
predictive dialer for callcenter with less investment power dial
predictive dialer for callcenter with low price
predictive dialer form india
predictive dialer from hyderabad
power dial predictive dialer