SQL Certificate Study Material

SQL Certificate Questions Bank

SQL Certificate Mock Exams

SQL Certificate - Mock Exams

SQL Certificate Useful Resources

Selected Reading

SQL - Conditional Expressions

Conditional Expressions Questions

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

They are given to columns for sorting purposes.

They are given to columns for a structured representation in a table.

They are given to columns to constrain the nature of the data it can store.

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?

There is a pmit to use the Nested functions only 5 times in Oracle DB.

They are evaluated from the outermost level to the innermost level.

They are evaluated from the innermost level to the outermost level.

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 simppfy working with columns that potentially contain null values?

Nested functions

General functions

Conditional functions

None of the above

Answer: B. The general functions pke 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?

VARCHAR2

NUMBER

DATE

All Datatypes

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

5. What is true about the COALESCE function?

It accepts minimum 2 and maximum 5 input parameters

It always returns the first NULL value among the input parameters

It can accept unpmited number of input parameters

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?

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 imppcitly 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)

There is nothing wrong

The parameter original is not required

The parameter ifnull is not required

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;

1234

1000

NULL

ORA-00909:invapd 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;

A white space i.e.

1234

NULL value

ORA-01722: invapd 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 imppcitly 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. Imppcit 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;

abc

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)

SELECT first_name, last_name, commission_pct*salary FROM employees WHERE department_id  = 100;

SELECT first_name, last_name, NVL(commission_pct*salary,0) monthly_commission FROM employees WHERE department_id  = 100;

SELECT first_name, last_name, NVL(commission_pct,0)*salary monthly_commission FROM employees WHERE department_id  = 100;

SELECT first_name, last_name, commission_pct*salary FROM employees;

Answer: B, C.

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

Imppcit conversions

Exppcit conversions

External conversions

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 imppcit conversion in Oracle DB?

Oracle DB imppcitly converts one data type to the expected data type

The user has to convert the data type to the expected data type

Oracle DB doesn t convert any data type

Imppcit conversion can be controlled by the user

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

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

Oracle DB converts one data type to the other and displays to the user exppcitly

Oracle DB prompts the user to convert one data type to the other and then converts the data type

The user uses conversion functions suppped by Oracle DB to convert data types

The data type is never converted exppcitly in Oracle DB

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

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

Imppcit and Exppcit conversions

Imppcit conversion

Exppcit conversion

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 vapd imppcit conversion performed by Oracle?

NUMBER TO VARCHAR2

NUMBER TO DATE

CHAR TO DATE

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 appped to the following query?

SELECT first_name, salary
FROM employees
WHERE hire_date >  01-JAN-13 ;

No conversion happens and this query results in an ORA error

Exppcit conversion

Imppcit conversion

Both Imppcit and exppcit conversion

Answer: C. The string (VARCHAR2 or CHAR) is converted imppcitly 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?

NUMBER TO CHAR

DATE TO VARCHAR2

CHAR to DATE

NUMBER TO DATE

Answer: A, B. DATE and NUMBER values can easily be converted to their character equivalents. Imppcit 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 imppcit conversion of CHAR to NUMBER in Oracle to work?

Nothing in particular is mandatory for this type of conversion

It is mandatory that the character string represents a vapd number

No such conversion is supported by Oracle

CHAR to NUMBER has to be converted exppcitly only

Answer: B. Character data must represent a vapd number to be considered for imppcit conversion.

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

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

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 exppcitly for a conversion of a NUMBER to a CHAR?

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

Such conversion is not possible

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

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

TO_CHAR

Use TO_DATE and then convert the date to a NUMBER

TO_NUMBER

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 exppcitly for a conversion of a DATE to a CHAR?

TO_CHAR

TO_DATE

TO_NUMBER

Such conversion is not possible

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

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

TO_CHAR

TO_DATE

TO_NUMBER

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])

It converts a VARCHAR2 to a CHAR

It converts a number/date to a VARCHAR2 string with the format model [format]

It converts a VARCHAR2 to a NUMBER or a DATE

[NLSPARAMETERS] is mandatory in the statement

Answer: B. The TO_CHAR function returns an item of data type VARCHAR2. When appped 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])

Decimal character

Group separator

Currency symbol

All of the above

Answer: D.

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

It throws an ORA error

The [nlsparameters] parameter is mandatory and it can t be omitted.

It will use the default parameter values for the session.

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])

The nlsparameters parameter specifies the language in which the month and day names are returned.

The nlsparameters parameter is omitted on the execution of the above statement.

The nlsparameters parameter will return a NULL whether specified or not

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])

It converts any string to a number in the format specified in [format]

It converts only a NUMBER to the desired format as mentioned in [format]

It converts a string with digits to a number in the format specified in [format]

The result of this function is always a character

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

TO_DATE(char, [format],[nlsparameters])

It converts any string to a DATE in the format specified in [format]

It converts only a DATE to another DATE in the desired format as mentioned in [format]

It converts a string with DATE to a number in the format specified in [format]

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])

It will return a DATE value with the format DD-MON-YY

It will return a DATE value with the format DD-MON-RR

It will return a character value

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 )

The fmt can be written in double quotes as well as single quotes.

Case of the fmt doesn t matter in this function

Fmt can include any character or NUMBER

The fmt has to be enclosed in single quotes and has to be a vapd date format.

Answer: D.

33. What will the following statement on execution yield?

SELECT TO_CHAR ( 01-JAN-13   DD-MON-YY ) FROM dual;

01-JAN-13

01-01-2013

An ORA error

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])

The fmt can be written in double quotes as well as single quotes.

Case of the fmt doesn t matter in this function

The [fmt] parameter has an fm element which removes spaces and suppresses leading zeroes.

Fmt can include any character or NUMBER

Answer: C.

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

First Move

Filter Mode

Fill Mode

First Mode

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

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

First Expression

Fill Expression

First Extra

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;

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?

This modifier suppresses blank padding in the subsequent character elements such as MONTH

This modifier suppresses leading zeroes for subsequent number of elements such as MI

This modifier has no effect on the date format

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?

The result of the character element is left padded with blanks to a variable length

The result of the character element is right padded with blanks to a fixed length

The leading zeroes are not returned in the result of the character element

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?

Right

Left

Centre

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 January2013

1 January 2013

1 Jan 2013

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;

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;

It will return an ORA error because of the use of double quotes in the Date format

1st January 2013

First of JANUARY 2013 12:00:00 AM

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?

TO_DATE

TO_CHAR

Answer: D.

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

It is case sensitive

It ignores the spaces in the character argument when matching with the Date format model mentioned

The punctuations and quoted text in the character argument do not necessarily match the format model

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;

It will execute successfully

It will give the result January 21, 2013

It creates an ORA error

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?

It can be used with TO_CHAR

It can be used with both TO_CHAR and TO_DATE

It can be used with only TO_DATE

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;

1st of January

1st

1 ST

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;

1st of January

1st

1ST

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;

Tuesday

TUESDAY

TUESDAY s Meeting

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;

ORA error

01-JAN-2013

01-JANUARY-13

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;

ORA error

01-JAN-2013

01-JANUARY-13

01-JAN-13

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

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

SELECT TO_DATE( 01/JAN/13 , fxDD-MON-YY ) FROM dual;

01-JAN-2013

ORA error

01-JAN-13

01-JANUARY-13

Answer: B. With the format exact modifier, the input pteral 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;

01-JAN-2013

ORA error

01-JAN-13

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;

11-JAN-13

11-01-13

11-JAN-2013

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 ;

ORA error

01-JAN-2013

1-JAN-13

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;

01-JAN-13

01-01-2013

01-January-13

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 pst 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 pst is to be generated on 01-JAN-2013 )

WHERE TO_DATE (hire_date, DD-MON-YY ) < 01-JAN-1990

WHERE TO_DATE (hire_date, DD-MON-YYYY ) < 01-JAN-1990

WHERE TO_DATE (hire_date, DD-MON-YY ) < 01-JAN-90

WHERE TO_DATE (hire_date, DD-MON-RR ) < 01-JAN-1990

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?

SELECT lower(last_name) FROM employees;

SELECT upper (last_name) FROM employees;

SELECT concat (first_name, last_name) FROM employees;

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?

It can take only 2 arguments

All the arguments in the COALESCE function can be of different data types

All the arguments in the COALESCE function should be of the same data type

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?

TO_CHAR

COALESCE

NVL

CASE

Answer: D. The CASE expression faciptates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression psts the conditional search item once, and equapty to the search item is tested by each comparison expression. The searched CASE expression psts 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;

01-jan-2013

01-jan-13

tue-jan-13

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-jan-2013

01-jan-13

TUESDAY -jan-13

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 traipng or leading spaces.

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

SELECT TO_CHAR(TO_DATE( 19-JUN-13 ), qth ) FROM dual;

1st

2nd

3rd

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 pst 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?

SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date, w ) >2;

SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date, w ) between 1 and 2;

SELECT first_name, last_name, department_id  FROM employees WHERE TO_CHAR(hire_date, w ) <3;

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?

SELECT count(employee_id  ) FROM employees WHERE TO_CHAR(hire_date,  q ) > 1;

SELECT count(employee_id  ) FROM employees Where TO_CHAR(hire_date,  q ) = 3;

SELECT employee_id   FROM employees Where TO_CHAR(hire_date,  q ) = 3;

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?

SELECT TO_CHAR (hire_date,  fmDay,"the "ddth "of " Month, Yysp. ) FROM employees;

SELECT TO_CHAR (hire_date,  Day,"the "ddth "of " Mon, Yyyy. ) FROM employees;

SELECT TO_CHAR (hire_date,  fmDAY,"the "ddth "of " Month, Ysp. ) FROM employees;

SELECT TO_CHAR (hire_date,  fmDay,"the "ddth "of " MONTH, Rsp. ) FROM employees;

Answer: A. The sp identifier spells the year in simple engpsh 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?

TO_DATE( 2011 , YYYY )

TO_CHAR (TO_DATE ( 2011 , YYYY ), fmMM/DD/YYYY )

TO_CHAR( 2011 , DD-MON-YYYY )

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;

3000

$3000

NULL

ORA error

Answer: D. The query throws error of "ORA-01722: invapd 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;

$3000.67

3000

3000.67

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;

$3,000,000.67

3000,000.67

3000.67

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;

ORA error

456.23

456

None of the above

Answer: B.

73. What is true about the nested functions?

Nesting imppes the use of output from one function as an input to another.

Nesting can be appped up to 3 levels of nesting.

Nesting are appped to Multiple row functions to any level of depth

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;

NULL

None of the above

Answer: C. The NULLIF function tests two terms for equapty. 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-JAN-13

01-JAN-2013

NULL

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?

0:1

1:2

2: any number

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?

COALESCE(expr1,expr2) = NVL(expr1,expr2)

COALESCE(expr1,expr2) = NVL2(expr1,expr2,expr3)

COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

All of the above

Answer: A, C.

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

NVL(original,ifnotnull)

NVL2(original,ifnull,ifnotnull)

NVL(original,NULL)

NVL(original,ifnull) and NVL2(original,ifnotnull,ifnull)

Answer: D.

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

CASE

DECODE

Both A and B

None of the above

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

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

DECODE(expr1,comp1,iftrue1,comp2,[iftrue2])

Comp2 is not optional

If expr1 is equal to comp1 then comp2 is returned

If expr1 is equal to comp1 then iftrue1 is returned

None of the above

Answer: C. The DECODE function implements if-then-else conditional logic by testing its first two terms for equapty 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?

All parameters must be VARCHAR2

No expressions can be parameters to the DECODE function

All parameters must be NUMBER

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 equapty 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;

NULL

Expr3

ORA error

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

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

SELECT DECODE ( elephant , rat , pon , tiger , cat , squirrel , elephant , koala , rat , And it continues ) FROM dual;

elephant

rat

koala

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?

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

CASEÖEND IF;

IFÖEND IF;

CASEÖ;

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?

NULL

Either 0 or NULL

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?

The CASE searches or compares only the first level and exists without checking other levels of WHENÖTHEN.

The CASE statement will search in all the levels of WHENÖTHEN until it finds a match.

Both A and B

None of the above

Answer: B.

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

VARCHAR2

DATE

NUMBER

Column values, pterals and expressions

Answer: D.

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

SELECT

None of these options

WHERE

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;

It will create an ORA error as more than one WHENÖTHEN statements cannot be written in the CASE statement.

It will display the department IDs as 100,101,102

It will only display Accounts where ever the department ID 100 appears and ignore the remaining commands.

None of the above

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

Unpmited

1000

216

255

Answer: D.

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

SELECT NVL2(
       NULLIF ( BMW , AUDI ),
        HYUNDAI ,
        FERRARI 
       )
       FROM dual;

BMW

FERRARI

NULL

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;

1st January, 2013

1st of Jan, 2013

01st of January, 2013

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;

First January Two Thousand Thirteen

First JAN Two Thousand Thirteen

One January Two Thousand Thirteen

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;

First January Two Thousand Thirteen

One January Two Thousand Thirteen

ORA error

01-JAN-2013 zeroeth

Answer: D. Spelpng 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?

TO_CHAR

TO_NUMBER

Both A and B

CASE

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;

10000

NULL

ORA error

Answer: B. The DECODE function implements if-then-else conditional logic by testing its first two terms for equapty 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?

SELECT TO_CHAR(sysdate,  DD-MON-YY HH24:MI:SS.FF ) FROM dual;

SELECT TO_CHAR(sysdate,  DD-MON-YY HH24:MI:SS ) FROM dual;

SELECT TO_CHAR(sysdate,  DD-MON-YY HH24:MI:SS.FF3 ) FROM dual;

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?

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?

SELECT TO_CHAR(hire_date,  DDD ) FROM employees WHERE last_name =  Patrick  AND first_name =  John ;

SELECT TO_CHAR(hire_date,  YYYY ) FROM employees WHERE last_name =  Patrick  AND first_name =  John ;

SELECT TO_CHAR(hire_date,  DD-MON-YYYY ) FROM employees WHERE last_name =  Patrick  AND first_name =  John ;

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?

TO_CHAR(sysdate, HH24:MI:SS )

TO_CHAR(sysdate, HH24:MI:SS.FF )

TO_CHAR(sysdate, HH24:MI:SSSS )

TO_CHAR(sysdate, HH24:MI:SS.FF3 )

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

Conditional Expressions Questions

友情链接