SQL – Conditional Expressions


SQL – Conditional Expressions Questions

”;


Previous

Next

1. What is true about data types in Oracle DB?

  1. They are given to columns for sorting purposes.
  2. They are given to columns for a structured representation in a table.
  3. They are given to columns to constrain the nature of the data it can store.
  4. They are not mandatory.

Answer: C. Data types define the nature of data which a column can store in a table. A column can store only one type of data. The primary data types available in Oracle are NUMBER, VARCHAR2, and DATE.

2. What is true about nested functions?

  1. There is a limit to use the Nested functions only 5 times in Oracle DB.
  2. They are evaluated from the outermost level to the innermost level.
  3. They are evaluated from the innermost level to the outermost level.
  4. All the functions in a Nested expression return the same data type.

Answer: C. Single row functions can group functions can be nested in a SELECT query in which the innermost function is the first one to be executed. The result of the execution of innermost function serves as the input for the outer function.

3. Which of the following functions simplify working with columns that potentially contain null values?

  1. Nested functions
  2. General functions
  3. Conditional functions
  4. None of the above

Answer: B. The general functions like NVL, NVL2, NULLIF, and COALESCE are used to pacify the effect of NULL while displaying the query results. They bypass the NULL values by assigning an alternative value.

4. Which of the following data types are appropriate for general functions?

  1. VARCHAR2
  2. NUMBER
  3. DATE
  4. All Datatypes

Answer: D. General functions are usually compatible with all primary data types like NUMBER, VARCHAR2 and DATE.

5. What is true about the COALESCE function?

  1. It accepts minimum 2 and maximum 5 input parameters
  2. It always returns the first NULL value among the input parameters
  3. It can accept unlimited number of input parameters
  4. It returns the first non-null parameter else it returns a null.

Answer: C, D. 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))

6. How many input parameters are mandatory in NVL function?

  1. 0
  2. 1
  3. 2
  4. 3

Answer: C. The NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

7. What is wrong in the following statement?

NVL (ifnull, original)
  1. There is nothing wrong
  2. The parameter original is not required
  3. The parameter ”ifnull” is not required
  4. The correct statement is NVL (original,ifnull)

Answer: D. The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned.

8. What will be the output of the following query?

SELECT NVL(1234) FROM dual;
  1. 1234
  2. 1000
  3. NULL
  4. ORA-00909:invalid number of arguments error

Answer: D. he NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null.

9. What will be output of the following query?

SELECT NVL(1234,'' '') FROM dual;
  1. A white space i.e. ” ”
  2. 1234
  3. NULL value
  4. ORA-01722: invalid number

Answer: D. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. The 1234 should be in single quotes. Implicit conversion of data type doesn”t happen in this case.

10. What will be outcome of the following query?

SELECT NVL(SUBSTR(''abc'',-4),''SUBSTR didn''t work'') FROM dual;
  1. abc
  2. bc
  3. c
  4. SUBSTR didn”t work

Answer: D.

11. You need to extract a report which gives the first name, last name and the commission percentage earned by all the employees in department 100. The report should not have any columns which are empty. All the columns should have at least a ”0” if there is no value for them. Which of the following queries will fulfill this requirement? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. SELECT first_name, last_name, commission_pct*salary FROM employees WHERE department_id  = 100;
  2. SELECT first_name, last_name, NVL(commission_pct*salary,0) monthly_commission FROM employees WHERE department_id  = 100;
  3. SELECT first_name, last_name, NVL(commission_pct,0)*salary monthly_commission FROM employees WHERE department_id  = 100;
  4. SELECT first_name, last_name, commission_pct*salary FROM employees;

Answer: B, C.

12. What are the types of Data conversions in Oracle DB?

  1. Implicit conversions
  2. Explicit conversions
  3. External conversions
  4. Physical conversions

Answer: A, B. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

13. What happens during an implicit conversion in Oracle DB?

  1. Oracle DB implicitly converts one data type to the expected data type
  2. The user has to convert the data type to the expected data type
  3. Oracle DB doesn”t convert any data type
  4. Implicit conversion can be controlled by the user

Answer: A. If Oracle database implicitly converts a value to a compatible data type, it is known as Implicit conversion.

14. What happens during an explicit conversion in Oracle DB?

  1. Oracle DB converts one data type to the other and displays to the user explicitly
  2. Oracle DB prompts the user to convert one data type to the other and then converts the data type
  3. The user uses conversion functions supplied by Oracle DB to convert data types
  4. The data type is never converted explicitly in Oracle DB

Answer: C. When the programmer has to programmatically convert a value using one of the conversion functions, it is known as explicit conversion.

15. Which of the following conversion methods is recommended for the reliability of SQL statements in Oracle DB?

  1. Implicit and Explicit conversions
  2. Implicit conversion
  3. Explicit conversion
  4. None of the above

Answer: C. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

16. Which of the following is a valid implicit conversion performed by Oracle?

  1. NUMBER TO VARCHAR2
  2. NUMBER TO DATE
  3. CHAR TO DATE
  4. DATE TO VARCHAR2

Answer: A, D.

17. Examine the structure of the EMPLOYEES table as given. 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Which conversion method is applied to the following query?

SELECT first_name, salary
FROM employees
WHERE hire_date > ''01-JAN-13'';
  1. No conversion happens and this query results in an ORA error
  2. Explicit conversion
  3. Implicit conversion
  4. Both Implicit and explicit conversion

Answer: C. The string (VARCHAR2 or CHAR) is converted implicitly to a DATE by Oracle giving the required output as selected.

18. Which of the following is supported with respect to expression evaluation is supported by Oracle DB?

  1. NUMBER TO CHAR
  2. DATE TO VARCHAR2
  3. CHAR to DATE
  4. NUMBER TO DATE

Answer: A, B. DATE and NUMBER values can easily be converted to their character equivalents. Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY].

19. What is mandatory for and implicit conversion of CHAR to NUMBER in Oracle to work?

  1. Nothing in particular is mandatory for this type of conversion
  2. It is mandatory that the character string represents a valid number
  3. No such conversion is supported by Oracle
  4. CHAR to NUMBER has to be converted explicitly only

Answer: B. Character data must represent a valid number to be considered for implicit conversion.

20. Which of the following expressions can be used explicitly for a conversion of a CHAR to a NUMBER?

  1. TO_CHAR
  2. Use TO_DATE and then convert the date to a NUMBER
  3. TO_NUMBER
  4. Such conversion is not possible

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

21. Which of the following expressions can be used explicitly for a conversion of a NUMBER to a CHAR?

  1. TO_CHAR
  2. Use TO_DATE and then convert the date to a NUMBER
  3. TO_NUMBER
  4. Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

22. Which of the following expressions can be used explicitly for a conversion of a CHAR to a DATE?

  1. TO_CHAR
  2. Use TO_DATE and then convert the date to a NUMBER
  3. TO_NUMBER
  4. TO_DATE

Answer: D. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE. When strings with only a subset of the date time elements are converted, Oracle provides default values to construct a complete date. Components of character strings are associated with different date time elements using a format model or mask.

23. Which of the following expressions can be used explicitly for a conversion of a DATE to a CHAR?

  1. TO_CHAR
  2. TO_DATE
  3. TO_NUMBER
  4. Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

24. Which of the following are the functions for explicit conversion provided by Oracle to convert one data type to the other?

  1. TO_CHAR
  2. TO_DATE
  3. TO_NUMBER
  4. All of the above

Answer: D. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

25. Interpret the working of the below function.

TO_CHAR(number/date, [format], [nlsparameters])
  1. It converts a VARCHAR2 to a CHAR
  2. It converts a number/date to a VARCHAR2 string with the format model [format]
  3. It converts a VARCHAR2 to a NUMBER or a DATE
  4. [NLSPARAMETERS] is mandatory in the statement

Answer: B. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

26. What does the [NLSPARAMETERS] clause in the following statement specify?

TO_CHAR(number/date, [format], [nlsparameters])
  1. Decimal character
  2. Group separator
  3. Currency symbol
  4. All of the above

Answer: D.

27. What value will the TO_CHAR (number/date, [format], [nlsparameters]) use if the [nlsparameters] parameter is omitted?

  1. It throws an ORA error
  2. The [nlsparameters] parameter is mandatory and it can”t be omitted.
  3. It will use the default parameter values for the session.
  4. It will use the default parameter values set during the database design.

Answer: C. By default, the TO_CHAR function considers the NLS settings of the current active session.

28. What is true about the following statement?

TO_CHAR(number/date, [format], [nlsparameters])
  1. The nlsparameters parameter specifies the language in which the month and day names are returned.
  2. The nlsparameters parameter is omitted on the execution of the above statement.
  3. The nlsparameters parameter will return a NULL whether specified or not
  4. The nlsparameters parameter will return the default language of the DB on every execution

Answer: A.

29. What is true regarding the following statement in Oracle DB?

TO_NUMBER(char, [format],[nlsparameters])
  1. It converts any string to a number in the format specified in [format]
  2. It converts only a NUMBER to the desired format as mentioned in [format]
  3. It converts a string with digits to a number in the format specified in [format]
  4. The result of this function is always a character

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

30. What is true regarding the following statement in Oracle DB?

TO_DATE(char, [format],[nlsparameters])
  1. It converts any string to a DATE in the format specified in [format]
  2. It converts only a DATE to another DATE in the desired format as mentioned in [format]
  3. It converts a string with DATE to a number in the format specified in [format]
  4. It converts a string with DATE to a DATE in the format specified in [format]

Answer: C. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE.

31. What will be the result if the [format] parameter in the following statement is omitted?

TO_DATE(char, [format],[nlsparameters])
  1. It will return a DATE value with the format DD-MON-YY
  2. It will return a DATE value with the format DD-MON-RR
  3. It will return a character value
  4. It will return a NUMBER value

Answer: A.

32. Which of the following is true about the following statement in Oracle DB?

TO_CHAR(date, ''frmt'')
  1. The fmt can be written in double quotes as well as single quotes.
  2. Case of the fmt doesn”t matter in this function
  3. Fmt can include any character or NUMBER
  4. The fmt has to be enclosed in single quotes and has to be a valid date format.

Answer: D.

33. What will the following statement on execution yield?

SELECT TO_CHAR (''01-JAN-13'' ''DD-MON-YY'') FROM dual;
  1. 01-JAN-13
  2. 01-01-2013
  3. An ORA error
  4. 1-JAN-13

Answer: C. The parameters ”01-JAN-13” and format model should be separated by a “,”.

34. What is true about the [fmt] parameter in the following statement?

 TO_DATE ([date as string],[format])
  1. The fmt can be written in double quotes as well as single quotes.
  2. Case of the fmt doesn”t matter in this function
  3. The [fmt] parameter has an ”fm” element which removes spaces and suppresses leading zeroes.
  4. Fmt can include any character or NUMBER

Answer: C.

35. What is the abbreviation for the FM modifier in Oracle DB?

  1. First Move
  2. Filter Mode
  3. Fill Mode
  4. First Mode

Answer: C. The format model ”fm” stands for Fill Mode.

36. What is the abbreviation for the FX modifier in Oracle DB?

  1. First Expression
  2. Fill Expression
  3. First Extra
  4. Format Exact

Answer: D. The format model ”fm” stands for Format Exact.

37. How many maximum places for display will Oracle DB allocate to the Month element in the following statement?

SELECT TO_CHAR (sysdate, ''fmMonth'') FROM dual;
  1. 5
  2. 6
  3. 7
  4. 9

Answer: D. The longest word for Month is ”September” and hence Oracle pads according to 9 places for the display of the Month parameter.

38. Which of the following is true about the FM modifier in Oracle DB?

  1. This modifier suppresses blank padding in the subsequent character elements such as MONTH
  2. This modifier suppresses leading zeroes for subsequent number of elements such as MI
  3. This modifier has no effect on the date format
  4. This modifier is mandatory for all the date formats used with the function TO_CHAR

Answer: A, B.

39. What happens when the FM modifier is not used in the DATE format model in Oracle DB?

  1. The result of the character element is left padded with blanks to a variable length
  2. The result of the character element is right padded with blanks to a fixed length
  3. The leading zeroes are not returned in the result of the character element
  4. The length of the return value is fixed if the FM modifier is used

Answer: B.

40. How is a number result justified in the output buffer in a number format element of a TO_CHAR function when the FM modifier is used?

  1. Right
  2. Left
  3. Centre
  4. None of the above

Answer: B. The FM modifier suppresses blanks added to the left of the number.

41. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13''), ''fmDD Month YYYY'') FROM dual;
  1. 1 January2013
  2. 1 January 2013
  3. 1 Jan 2013
  4. 1 January 13

Answer: B. The TO_CHAR formats the input date as per the given format model.

42. How many spaces will be added to the ”DD” of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDD Month YYYY'') FROM dual;
  1. 0
  2. 1
  3. 2
  4. 3

Answer: A. The FM modifier removes all the padded spaces from the Date format..

43. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDdspth "of" Month YYYY fmHH:MI:SS AM'') FROM dual;
  1. It will return an ORA error because of the use of double quotes in the Date format
  2. 1st January 2013
  3. First of JANUARY 2013 12:00:00 AM
  4. First of January 2013 12:00:00 AM

Answer: D. The TO_CHAR formats the input date ”01-JAN-13” as per the given format.

44. Which of the following specifies the exact match for the character argument and the date format model of a TO_DATE function?

  1. TO_DATE
  2. TO_CHAR
  3. FM
  4. FX

Answer: D.

45. What is true about the FX modifier in the Oracle DB?

  1. It is case sensitive
  2. It ignores the spaces in the character argument when matching with the Date format model mentioned
  3. The punctuations and quoted text in the character argument do not necessarily match the format model
  4. None of the above

Answer: D.

46. What will be the outcome of the following query?

SELECT TO_DATE (''January   21, 2013'' , ''fxMonth DD, YYYY'') FROM dual;
  1. It will execute successfully
  2. It will give the result January 21, 2013
  3. It creates an ORA error
  4. It will give the result JANUARY 21, 2013

Answer: C. The character argument should match exactly with the format model if FX is used. Here the extra spaces after January are mismatching.

47. What is true about the FX modifier in Oracle DB?

  1. It can be used with TO_CHAR
  2. It can be used with both TO_CHAR and TO_DATE
  3. It can be used with only TO_DATE
  4. None of the above

Answer: C. The FX format modifier can only be used with the TO_DATE function.

48. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''DDTH'') FROM dual;
  1. 1st of January
  2. 1st
  3. 1 ST
  4. 01ST

Answer: D.

49. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDDTH'') FROM dual;
  1. 1st of January
  2. 1st
  3. 1ST
  4. 01ST

Answer: C.

50. Assuming the SYSDATE is 01-JAN-13 and falls on Tuesday, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDay'')||''''''s Meeting'' FROM dual;
  1. Tuesday
  2. TUESDAY
  3. TUESDAY”s Meeting
  4. Tuesday”s Meeting

Answer: D.

51. What will be the outcome of the following query?

SELECT TO_DATE(''01 / JAN / 13'',''DD-MON-YY'') FROM dual;
  1. ORA error
  2. 01-JAN-2013
  3. 01-JANUARY-13
  4. 01-JAN-13

Answer: D.

52. What will be the outcome of the following query?

SELECT TO_DATE(''01 ## JAN / 13'',''DD-MON-YY'') FROM dual;
  1. ORA error
  2. 01-JAN-2013
  3. 01-JANUARY-13
  4. 01-JAN-13

Answer: A. Use a single delimiter between the dates.

53. What will be the outcome of the following query?

SELECT TO_DATE(''01/JAN/13'',''fxDD-MON-YY'') FROM dual;
  1. 01-JAN-2013
  2. ORA error
  3. 01-JAN-13
  4. 01-JANUARY-13

Answer: B. With the format exact modifier, the input literal must match the format string.

54. What will be the outcome of the following query?

SELECT TO_DATE(''01-JAN-13'',''fxDD-MON-YY'') FROM dual;
  1. 01-JAN-2013
  2. ORA error
  3. 01-JAN-13
  4. 01-JANUARY-13

Answer: C.

55. What will be the outcome of the following query?

SELECT TO_DATE (''11-JAN-2013'',''fxDD-MON-YYYY'') FROM dual;
  1. 11-JAN-13
  2. 11-01-13
  3. 11-JAN-2013
  4. ORA error

Answer: C.

56. An employee Allen was hired on 1-JAN -13. What will be the outcome of the following query? (Assume that the NLS parameter for the session is set to DD-MON-YY)

SELECT TO_DATE(hire_date, ''fxfmDD-MON-YY'') FROM employees WHERE first_name=''ALLEN'';
  1. ORA error
  2. 01-JAN-2013
  3. 1-JAN-13
  4. 1-JAN-2013

Answer: C.

57. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE (''01-JAN-2013''), ''DD-Month-RR'') FROM dual;
  1. 01-JAN-13
  2. 01-01-2013
  3. 01-January-13
  4. 01-January -13

Answer: D. The Month modifier is padded up to 9 places with spaces.

Examine the structure of the EMPLOYEES table as given and answer the questions 58 and 59 that follow. 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

58. You need to list out the first and the last names for all the employees who were hired before the year 1990. Which of the following WHERE statements will give you the required results? (Assume that this list is to be generated on ”01-JAN-2013”)

  1. WHERE TO_DATE (hire_date, ”DD-MON-YY”)
  2. WHERE TO_DATE (hire_date, ”DD-MON-YYYY”)
  3. WHERE TO_DATE (hire_date, ”DD-MON-YY”)
  4. WHERE TO_DATE (hire_date, ”DD-MON-RR”)

Answer: D. Using the RR format will consider the year portion of the date between 1950 and 1999.

59. Which of the following is an example of a nested function?

  1. SELECT lower(last_name) FROM employees;
  2. SELECT upper (last_name) FROM employees;
  3. SELECT concat (first_name, last_name) FROM employees;
  4. SELECT upper (concat(SUBSTR(first_name,1,6),''_UK'')) FROM employees;

Answer: D. More than one functions in a function is known as nesting of functions.

60. What is true about the COALESCE function in Oracle DB?

  1. It can take only 2 arguments
  2. All the arguments in the COALESCE function can be of different data types
  3. All the arguments in the COALESCE function should be of the same data type
  4. None of the above

Answer: C. 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.

61. Which of the following functions is used for conditional expressions?

  1. TO_CHAR
  2. COALESCE
  3. NVL
  4. CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE
expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

62. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''dy-mon-yyyy'') FROM dual;
  1. 01-jan-2013
  2. 01-jan-13
  3. tue-jan-13
  4. tue-jan-2013

Answer: D. The format model ”dy” spells the first three letters of the day from the input date. ”DY” will give ìTUEî and not ìtueî as in the query given above.

63. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''fmDAY-mon-yyyy'') FROM dual;
  1. 1-jan-2013
  2. 01-jan-13
  3. TUESDAY -jan-13
  4. TUESDAY-jan-2013

Answer: D. fmDAY (for all capital letters) or fmday (for all small letters) format model will spell the day of the input date without any trailing or leading spaces.

64. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''19-JUN-13''),''qth'') FROM dual;
  1. 1st
  2. 2nd
  3. 3rd
  4. 4th

Answer: B. The format model ”q” gives the quarter in which the given date falls. In the given query, APR-JUN is the 2nd quarter.

Examine the structure of the EMPLOYEES table as given and answer the questions 65 to 67 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

65. Some employees joined company ABC in the second week of the current year i.e. 2013. You need to list out the first names, last names and the department IDs for all these employees. Which of the following queries will give you the required result?

  1. SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date,''w'') >2;
  2. SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date,''w'') between 1 and 2;
  3. SELECT first_name, last_name, department_id  FROM employees WHERE TO_CHAR(hire_date,''w'') 
  4. SELECT first_name, last_name, department_id  FROM employees WHERE TO_CHAR(sysdate,''ww'') =2;

Answer: D. The format model ”ww” gives the week of the year.

66. The management of a company ”ABC” wants to find out how many employees were hired in the 3rd quarter of the year 2012. Which of the following queries will give the required result?

  1. SELECT count(employee_id  ) FROM employees WHERE TO_CHAR(hire_date, ''q'') > 1;
  2. SELECT count(employee_id  ) FROM employees Where TO_CHAR(hire_date, ''q'') = 3;
  3. SELECT employee_id   FROM employees Where TO_CHAR(hire_date, ''q'') = 3;
  4. SELECT count(employee_id  ) FROM employees Where TO_CHAR(hire_date, ''q'') between 0 and 3;

Answer: B. The format model ”q” gives the quarter of a year.

67. A certificate of achievement has to be printed and presented to all those employees who joined the organization before the year 2008 and are still a part of the organization. The printing of the first name, last name and the dates will happen by using placeholders fetched from a query. The Certificate should contain all the digits spelled out.
Example: Tuesday, the 1st of January, Two Thousand and eight. The final text of the Certificate should be in the following form:
This is to certify that first_name last_name who joined the organization on Tuesday, the 1st of January, Two Thousand and eight has successfully completed 5 glorious years in the company.
Which of the following queries will be helpful in printing the dates as in the required format?

  1. SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " Month, Yysp.'') FROM employees;
  2. SELECT TO_CHAR (hire_date, ''Day,"the "ddth "of " Mon, Yyyy.'') FROM employees;
  3. SELECT TO_CHAR (hire_date, ''fmDAY,"the "ddth "of " Month, Ysp.'') FROM employees;
  4. SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " MONTH, Rsp.'') FROM employees;

Answer: A. The ”sp” identifier spells the year in simple english language.

68. A report has to be generated which creates an audit history table for all the employees from an available paper source. The paper source only has data for the year 2011 when the employees were hired. This data only has the year of the hire date. You need to put the date in the audit-history table as 1st of January of that particular year (without leading zeroes and spaces). Which of the following clauses will achieve this requirement?

  1. TO_DATE(”2011”,”YYYY”)
  2. TO_CHAR (TO_DATE (”2011”,”YYYY”),”fmMM/DD/YYYY”)
  3. TO_CHAR(”2011”,”DD-MON-YYYY”)
  4. TO_DATE (”01-01-2011”,”DD-MM-YYYY”)

Answer: B.

69. What will be the outcome of the following query?

SELECT TO_NUMBER (''$3000'') FROM dual;
  1. 3000
  2. $3000
  3. NULL
  4. ORA error

Answer: D. The query throws error of “ORA-01722: invalid number” because the given string cannot be recognized in numbers.

70. What will be the outcome of the following query?

SELECT TO_NUMBER(''$3,000.67'',''$999,999.99'') FROM dual;
  1. $3000.67
  2. 3000
  3. 3000.67
  4. ORA error as the input string has lesser characters than the format model mentioned.

Answer: C. The appropriate format model helps the TO_NUMBER to convert given string in numbers.

71. What will be the outcome of the following query?

SELECT TO_NUMBER(''$3,000,000.67'',''$999,999.99'') FROM dual;
  1. $3,000,000.67
  2. 3000,000.67
  3. 3000.67
  4. ORA error as the format model has lesser characters than the input string. It should be the same.

Answer: D.

72. What will the following query yield?

SELECT TO_NUMBER(''456.23'',''999.99'') FROM dual;
  1. ORA error
  2. 456.23
  3. 456
  4. None of the above

Answer: B.

73. What is true about the nested functions?

  1. Nesting implies the use of output from one function as an input to another.
  2. Nesting can be applied up to 3 levels of nesting.
  3. Nesting are applied to Multiple row functions to any level of depth
  4. None of the above

Answer: A. The output from a function execution is used as input for its preceding function.

74. What will be the result of the following query?

SELECT NULLIF(1,2-1) FROM dual;
  1. 0
  2. 1
  3. NULL
  4. None of the above

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. Here 1 and the expression “2-1” are considered equal by oracle and hence NULL is returned.

75. What will be the outcome of the following query?

SELECT NULLIF(''01-JAN-2013'',''01-JAN-13'') FROM dual;
  1. 1-JAN-13
  2. 01-JAN-2013
  3. NULL
  4. ORA error

Answer: B. Since the lengths for both the dates is different, the first parameter is returned.

76. What is the ratio of mandatory parameters to optional parameters in the COALESCE function in Oracle DB?

  1. 0:1
  2. 1:2
  3. 2: any number
  4. None of the above

Answer: C. The COALESCE function takes two mandatory parameters and any number of optional parameters. OALESCE 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)).

77. Which of the following equations are true?

  1. COALESCE(expr1,expr2) = NVL(expr1,expr2)
  2. COALESCE(expr1,expr2) = NVL2(expr1,expr2,expr3)
  3. COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))
  4. All of the above

Answer: A, C.

78. Which of the following is the correct syntax of NVL2?

  1. NVL(original,ifnotnull)
  2. NVL2(original,ifnull,ifnotnull)
  3. NVL(original,NULL)
  4. NVL(original,ifnull) and NVL2(original,ifnotnull,ifnull)

Answer: D.

79. Which of the following functions is an ANSI standard keyword inherited in Oracle?

  1. CASE
  2. DECODE
  3. Both A and B
  4. None of the above

Answer: A. CASE is an ANSI SQL compliant and not Oracle specific.

80. What is true about the DECODE statement in Oracle DB?

DECODE(expr1,comp1,iftrue1,comp2,[iftrue2])
  1. Comp2 is not optional
  2. If expr1 is equal to comp1 then comp2 is returned
  3. If expr1 is equal to comp1 then iftrue1 is returned
  4. None of the above

Answer: C. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more.

81. What is true about the parameters in the DECODE function?

  1. All parameters must be VARCHAR2
  2. No expressions can be parameters to the DECODE function
  3. All parameters must be NUMBER
  4. The return data type is the same as that of the first matching comparison item.

Answer: D. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.

82. What will be the outcome of the following query?

SELECT DECODE (null,null,''expr3'') FROM dual;
  1. NULL
  2. 0
  3. Expr3
  4. ORA error

Answer: C. DECODE considers two NULL values to be equivalent. One of the anomalies of NULL in Oracle.

83. What will be the outcome of the following query?

SELECT DECODE (''elephant'',''rat'',''lion'',''tiger'',''cat'',''squirrel'',''elephant'',''koala'',''rat'',''And it continues'') FROM dual;
  1. elephant
  2. rat
  3. koala
  4. And it continues

Answer: D. The DECODE function takes at least three mandatory parameters, but can take many more.

84. What is the number of minimum mandatory parameters for the CASE expression in Oracle DB?

  1. 0
  2. 1
  3. 2
  4. 3

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE
expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression. It takes atleast 3 mandatory parameters but it can take more also.

85. Which of the following keyword combinations is used to enclose a CASE statement in Oracle DB?

  1. CASEÖEND IF;
  2. IFÖEND IF;
  3. CASEÖ;
  4. CASEÖEND;

Answer: D.

86. Which of the following values is returned in case of a false value if the ELSE block in the CASE statement is undefined?

  1. 0
  2. NULL
  3. Either 0 or NULL
  4. None of the above

Answer: B.

87. Which of the following options is true if more than one WHEN..THEN levels exist in a CASE statement?

  1. The CASE searches or compares only the first level and exists without checking other levels of WHENÖTHEN.
  2. The CASE statement will search in all the levels of WHENÖTHEN until it finds a match.
  3. Both A and B
  4. None of the above

Answer: B.

88. What data types can be the search, comparison and result parameters in the CASE statement?

  1. VARCHAR2
  2. DATE
  3. NUMBER
  4. Column values, literals and expressions

Answer: D.

89. The CASE statement cannot be used in which of the following parts of an Oracle SQL query?

  1. SELECT
  2. None of these options
  3. WHERE
  4. ORDER BY

Answer: B.

90. Examine the structure of the EMPLOYEES table as given. 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following query in Oracle DB?

SELECT first_name, salary,
CASE department_id WHEN 100
THEN ''Accounts''
WHEN 101
THEN ''Human Resources''
WHEN 102
THEN ''Sales''
ELSE ''Unknown''
 END
 FROM employees;
  1. It will create an ORA error as more than one WHENÖTHEN statements cannot be written in the CASE statement.
  2. It will display the department IDs as 100,101,102
  3. It will only display ”Accounts” where ever the department ID 100 appears and ignore the remaining commands.
  4. None of the above

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE
expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

91. What is the maximum number of WHENÖTHEN levels a CASE statement in Oracle DB can have?

  1. Unlimited
  2. 1000
  3. 216
  4. 255

Answer: D.

92. What will be the outcome of the following query?

SELECT NVL2(
       NULLIF (''BMW'',''AUDI''),
       ''HYUNDAI'',
       ''FERRARI''
       )
       FROM dual;
  1. BMW
  2. FERRARI
  3. NULL
  4. HYUNDAI

Answer: D. The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. If the first term is not null, the second parameter is returned, else the third parameter is returned.

93. Assuming the SYSDATE is 01-JAN-13 , what will the following query yield?

SELECT TO_CHAR (sysdate, ''fmddth" of" Month YYYY'') FROM dual;
  1. 1st January, 2013
  2. 1st of Jan, 2013
  3. 01st of January, 2013
  4. 1st of January 2013

Answer: D. The ìthî format model gives the day of the date as ìstî or ìthî.

94. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''MmSP Month Yyyysp'') FROM dual;
  1. First January Two Thousand Thirteen
  2. First JAN Two Thousand Thirteen
  3. One January Two Thousand Thirteen
  4. None of the above

Answer: C.

95. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''DD-MON-YYYY hh24SpTh'') FROM dual;
  1. First January Two Thousand Thirteen
  2. One January Two Thousand Thirteen
  3. ORA error
  4. 01-JAN-2013 zeroeth

Answer: D. Spelling out the timestamp component can be done using ”SpTh” format modifier.

96. Which of these functions do the work similar to if-then-else logic in SQL statements?

  1. TO_CHAR
  2. TO_NUMBER
  3. Both A and B
  4. CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE
expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

97. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following SQL query?

SELECT DECODE (salary,10000) FROM employees;
  1. 10000
  2. NULL
  3. 0
  4. ORA error

Answer: B. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. If the default value in the DECODE function is omitted, a NULL is returned.

98. You need to display the time of the Oracle DB session up to 3 decimal places of the fractional seconds. Which of the following queries will give the required output?

  1. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF'') FROM dual;
  2. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS'') FROM dual;
  3. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF3'') FROM dual;
  4. SELECT TO_CHAR(sysdate, ''DD-MON-YY'') FROM dual;

Answer: C. The FF [1..9] extension to the HH:MI:SS format yields fractional seconds up to 1..9 digits in the fractional seconds.

99. Which of the following punctuation marks can be used with Dates and Times in Oracle DB?

  1. #
  2. @
  3. ,
  4. :

Answer: C, D.

100. Examine the structure of the EMPLOYEES table as given. 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to find the day of the year when the employee Jaimie Patrick was hired in the company ”ABC”. Which of the following queries will give the required output?

  1. SELECT TO_CHAR(hire_date, ''DDD'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John''; 
  2. SELECT TO_CHAR(hire_date, ''YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John''; 
  3. SELECT TO_CHAR(hire_date, ''DD-MON-YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John'';
  4. SELECT TO_CHAR(hire_date, ''DD-MON-RR'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John'';

Answer: A. The format model ”DDD” returns the day of the year on which the given date falls.

101. A report is required to be generated which gives the timings for all the batch runs that started on midnight 1st June, 2013. These timings should be in the precision of seconds after midnight. Which of the following clauses will fulfill the requirement?

  1. TO_CHAR(sysdate,”HH24:MI:SS”)
  2. TO_CHAR(sysdate,”HH24:MI:SS.FF”)
  3. TO_CHAR(sysdate,”HH24:MI:SSSS”)
  4. TO_CHAR(sysdate,”HH24:MI:SS.FF3”)

Answer: C. the ”SSSS” format model gives the seconds after midnight.



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *