条件表达式问题

1. Oracle DB中的数据类型是什么?

  1. 它们被赋予列以进行排序.

  2. 它们被赋予表中结构化表示的列.

  3. 它们被赋予列以限制它可以存储的数据的性质.

  4. 它们不是强制性的.

答案:C.数据类型定义列可以存储在表中的数据的性质.列只能存储一种类型的数据. Oracle中可用的主要数据类型是NUMBER,VARCHAR2和DATE.

2.嵌套函数的真实情况是什么?

  1. 仅限使用嵌套函数5 Oracle DB中的时间.

  2. 从最外层到最里层评估它们.

  3. 从最里层到最外层评估它们.

  4. 嵌套表达式中的所有函数都返回相同的数据类型.

答案:C .单行函数可以将组函数嵌套在SELECT查询中,其中最里面的函数是第一个要执行的函数.执行最内层函数的结果用作外部函数的输入.

3.以下哪个函数简化了对可能包含空值的列的处理?

  1. 嵌套函数

  2. 常规函数

  3. 条件函数

  4. 以上都不是

答案:B. NVL,NVL2,NULLIF和COALESCE等常规函数用于在显示查询结果时平息NULL的影响.它们通过指定替代值来绕过NULL值.

4.以下哪种数据类型适用于一般功能?

  1. VARCHAR2

  2. NUMBER

  3. DATE

  4. 所有数据类型

答案:D.一般功能通常与所有主要数据类型兼容,例如NUMBER,VARCHAR2和DATE.

5. COALESCE函数的真实情况是什么?

  1. 它接受最小2和最多5个输入参数

  2. 它总是返回输入参数中的第一个NULL值

  3. 它可以接受无限数量的输入参数

  4. 它返回第一个非null参数,否则返回null.

答案:C,D. COALESCE函数有两个必需参数和任意数量的可选参数.语法是COALESCE(expr1,expr2,Ö,exprn),其中如果expr1不为null则返回expr1,否则返回expr2(如果它不为null),依此类推. COALESCE是NVL函数的一般形式,如以下两个等式所示:COALESCE(expr1,expr2)= NVL(expr1,expr2),COALESCE(expr1,expr2,expr3)= NVL(expr1,NVL(expr2,expr3) )

6. NVL函数中必须有多少输入参数?

  1. 0

  2. 1

  3. 2

  4. 3

答案:C. NVL功能需要两个必需参数.它的语法是NVL(original,ifnull),其中original表示正在测试的术语,ifnull是原始术语计算为null时返回的结果. original和ifnull参数的数据类型必须始终兼容.它们必须是相同的类型,或者必须可以隐式地将ifnull转换为原始参数的类型. NVL函数返回一个与原始参数具有相同数据类型的值.

7.以下语句出了什么问题?

 NVL(ifnull,original)

  1. 没有错误

  2. 不需要参数原始

  3. 不需要参数'ifnull'

  4. 正确的陈述是NVL(原文,ifnull)

答案:D. NVL函数评估任何数据类型的列或表达式是否为null或不.如果该术语为null,则返回替代非空值;否则,返回初始术语.

8.以下查询的输出是什么?

 SELECT NVL(1234)FROM dual;

  1. 1234

  2. 1000

  3. NULL

  4. ORA-00909:无效参数数量错误

答案:D.他的NVL函数有两个必需参数.它的语法是NVL(original,ifnull),其中original表示正在测试的术语,ifnull是原始术语计算为null时返回的结果.

9.将输出以下查询的内容?

 SELECT NVL(1234,'')FROM dual;

  1. 一个空格,即''

  2. 1234

  3. NULL值

  4. ORA-01722:无效数字

答案:D.原始参数和ifnull参数的数据类型必须始终兼容.它们必须是相同的类型,或者必须可以隐式地将ifnull转换为原始参数的类型. NVL函数返回与原始参数具有相同数据类型的值. 1234应该是单引号.在这种情况下,不会发生数据类型的隐式转换.

10.以下查询的结果是什么?

 SELECT NVL(SUBSTR('abc', -  4),'SUBSTR无效')FROM dual;

  1. abc

  2. bc

  3. c

  4. SUBSTR不起作用

答案:D.

11.您需要提取一份报告,该报告提供部门100中所有员工所获得的名字,姓氏和佣金百分比.报告中不应包含任何列为空的列.如果没有值,则所有列都应至少为'0'.以下哪些查询将满足此要求? (考虑给定的表结构)

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.  SEL ECT first_name,last_name,commission_pct * salary FROM employees;

答案:B,C.

12. Oracle DB中的数据转换类型有哪些?

  1. 隐含转化

  2. 明确转化

  3. 外部转化

  4. 物理转化

答案:A,B. TO_CHAR,TO_NUMBER和TO_DATE是三种使用最广泛的转换函数,详细讨论. TO_CHAR函数将数字和日期信息转换为字符,而TO_NUMBER和TO_DATE分别将字符数据转换为数字和日期.

13.在Oracle DB中隐式转换期间会发生什么?

  1. Oracle DB隐式地将一种数据类型转换为预期的数据类型

  2. 用户必须将数据类型转换为预期的数据类型

  3. Oracle DB不转换任何数据类型

  4. 隐式转换可以由用户控制

答案:A.如果是Oracle数据库隐式地将值转换为兼容的数据类型,它被称为隐式转换.

14.在Oracle DB中显式转换期间会发生什么?

  1. Oracle DB将一种数据类型转换为其他并显式向用户显示

  2. Oracle数据库提示用户将一种数据类型转换为另一种数据类型然后转换数据类型

  3. 用户使用Oracle DB提供的转换函数来转换数据类型

  4. 数据类型永远不会在Oracle DB中显式转换

答案:C.当程序员必须使用其中一个转换函数以编程方式转换值时,它被称为显式转换.

15 .对于Oracle DB中SQL语句的可靠性,建议使用以下哪种转换方法?

  1. 隐式和显式转化

  2. 隐式转化

  3. 显式转化

  4. 以上都不是

答案:C. TO_CHAR,TO_NUMBER和TO_DATE是三种使用最广泛的转换函数,将详细讨论. TO_CHAR函数将数字和日期信息转换为字符,而TO_NUMBER和TO_DATE分别将字符数据转换为数字和日期.

16.以下哪项是由Oracle执行的有效隐式转换?

  1. NUMBER TO VARCHAR2

  2. NUMBER TO DATE

  3. CHAR TO DATE

  4. DATE TO VARCHAR2

答案:A,D.

17.检查给定的EMPLOYEES表的结构.

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,salary 
 FROM employees 
 WHERE hire_date> '01 -JAN-13';

  1. 没有转换,此查询导致ORA错误

  2. 明确转换

  3. 隐式转换

  4. 隐式转换和显式转换

答案:C. Oracle将字符串(VARCHAR2或CHAR)隐式转换为DATE,并将所需的输出选中.

18. Oracle DB支持以下哪种表达式评估?

  1. NUMBER致CHAR

  2. DATE TO VARCHAR2

  3. CHAR to DATE

  4. NUMBER TO DATE

答案:A,B. DATE和NUMBER值可以很容易地转换为它们的等价字符.当字符串符合以下日期模式时,可以进行隐含的字符到日期转换:[D | DD] separator1 [MON | MONTH] separator2 [R | RR | YY | YYYY].

19.在Oracle中,CHAR到NUMBER的强制性和隐式转换是什么工作?

  1. 什么都没有特别是此类转换必须的

  2. 字符串必须表示有效数字

  3. Oracle不支持此类转换

  4. CHAR到NUMBER必须只显式转换

答案:B.字符数据必须表示要考虑进行隐式转换的有效数字.

20.以下哪个表达式可以明确用于将CHAR转换为NUMBER?

  1. TO_CHAR

  2. 使用TO_DATE然后将日期转换为NUMBER

  3. TO_NUMBER

  4. 此类转换不是可能

答案:C. TO_NUMBER函数返回NUMBER类型的项目.转换为数字的字符串必须进行适当的格式化,以便使用适当的格式掩码翻译或删除任何非数字组件.

21.以下哪个表达式可以明确用于将NUMBER转换为CHAR?

  1. TO_CHAR

  2. 使用TO_DATE然后将日期转换为NUMBER

  3. TO_NUMBER

  4. 此类转换不是可能

答案:A. TO_CHAR函数返回数据类型为VARCHAR2的项.应用于NUMBER类型的项目时,可以使用多种格式选项.

22.以下哪个表达式可以明确用于将CHAR转换为DATE?

  1. TO_CHAR

  2. 使用TO_DATE然后将日期转换为NUMBER

  3. TO_NUMBER

  4. TO_DATE

答案:D. TO_DATE函数返回DATE类型的项目.转换为日期的字符串可以包含包含DATE的日期时间元素的全部或仅一部分.当转换仅包含日期时间元素子集的字符串时,Oracle会提供默认值来构造完整日期.字符串的组件使用格式模型或掩码与不同的日期时间元素相关联.

23.以下哪个表达式可以明确用于将DATE转换为CHAR?

  1. TO_CHAR

  2. TO_DATE

  3. TO_NUMBER

  4. 无法进行此类转换

答案:A. TO_CHAR函数返回数据类型为VARCHAR2的项.应用于NUMBER类型的项目时,可以使用多种格式选项.

24.以下哪项是Oracle提供的将一种数据类型转换为另一种数据类型的显式转换函数?

  1. TO_CHAR

  2. TO_DATE

  3. TO_NUMBER

  4. 以上所有

答案:D. TO_CHAR,TO_NUMBER和TO_DATE是三种使用最广泛的转换函数,将详细讨论. TO_CHAR函数将数字和日期信息转换为字符,而TO_NUMBER和TO_DATE分别将字符数据转换为数字和日期.

25.解释下面函数的工作原理.

 TO_CHAR(数字/日期,[格式],[nlsparameters])

  1. 它将VARCHAR2转换为CHAR

  2. 它将数字/日期转换为VARCHAR2格式为[格式]的字符串

  3. 它将VARCHAR2转换为NUMBER或DATE

  4. [NLSPARAMETERS]在声明中是强制性的

答案:B. TO_CHAR函数返回数据类型为VARCHAR2的项.应用于NUMBER类型的项目时,可以使用多种格式选项.

26.以下语句中的[NLSPARAMETERS]子句指定了什么?

 TO_CHAR(数字/日期,[格式],[nlsparameters])

  1. 小数字符

  2. 组分隔符

  3. 货币符号

  4. 以上所有

答案:D.

27.如果省略[nlsparameters]参数,TO_CHAR(数字/日期,[格式],[nlsparameters])会使用什么值?

  1. 抛出ORA错误

  2. [nlsparameters]参数是必需的,不能省略.

  3. 它将使用会话的默认参数值.

  4. 它将使用数据库设计期间设置的默认参数值.

答案:C.默认情况下,TO_CHAR函数会考虑当前活动会话的NLS设置.

28.以下陈述的真实情况是什么?

 TO_CHAR(数字/日期,[格式],[nlsparameters])

  1. nlsparameters参数指定返回月份和日期名称的语言.

  2. 执行上述语句时省略了nlsparameters参数.

  3. 无论是否指定,nlsparameters参数都将返回NULL

  4. nlsparameters参数将在每次执行时返回DB的默认语言

答案:A.

29.关于Oracle DB中的以下语句有什么用?

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

  1. 它将任何字符串转换为[格式]中指定格式的数字

  2. 它只将NUMBER转换为所需格式,如[格式]中所述

  3. 它将带有数字的字符串转换为[格式]中指定格式的数字

  4. 此函数的结果始终是一个字符

答案:C. TO_NUMBER函数返回一个项目输入NUMBER.转换为数字的字符串必须进行适当的格式化,以便使用适当的格式掩码翻译或删除任何非数字组件.

30.关于Oracle DB中的以下语句有什么用?

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

  1. 它以[格式]中指定的格式将任何字符串转换为DATE

  2. 它按照[格式]中提到的所需格式将DATE转换为另一个DATE.

  3. 它将带有DATE的字符串转换为[格式]中指定格式的数字

  4. 它将DATE的字符串转换为[格式]中指定格式的DATE

答案:C TO_DATE函数返回DATE类型的项.转换为日期的字符串可以包含包含DATE的日期时间元素的全部或仅一部分.

31.如果省略以下语句中的[format]参数,结果会是什么?

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

  1. 它将返回格式为DD-MON-YY

  2. $的DATE值b $ b

  3. 它将返回DATE值,格式为DD-MON-RR

  4. 它将返回一个字符值

  5. 它将返回NUMBER值

答案:A.

32.关于Oracle DB中的以下语句,以下哪项是正确的?

 TO_CHAR(date,'frmt')

  1. fmt可以用双引号和单引号编写.

  2. fmt的情况与此功能无关

  3. Fmt可以包含任何字符或NUMBER

  4. fmt必须用单引号括起来,并且必须是有效的日期格式.

答案:D.

33.以下关于执行的声明会产生什么?

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

  1. 01-JAN-13

  2. 01-01 -2013

  3. ORA错误

  4. 1-JAN-13

答案:C.参数'01 -JAN-13'和格式模型应用","分隔.

34.以下语句中的[fmt]参数是什么?

 TO_DATE([date as string],[format])

  1. fmt可以用双引号和单引号编写.

  2. fmt的情况在此函数中无关紧要

  3. [fmt]参数有一个'fm'元素,用于删除空格并抑制前导零.

  4. Fmt可以包含任何字符或NUMBER

答案:C.

35. Oracle DB中FM修饰符的缩写是什么?

  1. First Move

  2. 过滤模式

  3. 填充模式

  4. 第一模式

答案:C.格式模型'fm'代表填充模式.

36. Oracle DB中FX修饰符的缩写是什么?

  1. First Expression

  2. 填写表达

  3. First Extra

  4. 格式确切

答案:D.格式模型'fm'代表格式精确.

37. Oracle DB在以下语句中分配给Month元素的最大显示位数是多少?

 SELECT TO_CHAR(sysdate,'fmMonth')FROM dual ;

  1. 5

  2. 6

  3. 7

  4. 9

答案:D. Month的最长单词是'September',因此Oracle填充根据9个位置显示Month参数.

38.关于Oracle DB中的FM修饰符,以下哪项是正确的?

  1. 此修饰符抑制空白在后续字符元素中填充,例如MONTH

  2. 此修饰符抑制后续数量的元素(如MI)的前导零

  3. 此修饰符对日期格式

  4. 此修饰符对于函数TO_CHAR使用的所有日期格式都是必需的

答案:A,B.

39.如果在DB DB中的DATE格式模型中未使用FM修饰符会发生什么?

  1. 字符元素的结果用空格填充到可变长度

  2. 字符元素的结果右边用空格填充到固定长度

  3. 字符元素的结果中不返回前导零

  4. 如果使用FM修饰符,则返回值的长度是固定的

答案:B.

40.当使用FM修饰符时,TO_CHAR函数的数字格式元素中的输出缓冲区中的数字结果是如何对齐的?

  1. 正确

  2. 中心

  3. 以上都不是

答案:B. FM修饰符会抑制添加到数字左侧的空白.

41.以下查询的结果是什么?

 SELECT TO_CHAR(TO_DATE('01 -JAN-13'),'fmDD Month YYYY') FROM dual;

  1. 2013年1月1日

  2. 2013年1月1日

  3. 2013年1月1日

  4. 1月13日

答案:B. TO_CHAR根据给定的格式模型格式化输入日期.

42.将在以下查询的"DD"中添加多少个空格?

 SELECT TO_CHAR(TO_DATE('01 -JAN-13',' DD-MON-YY'),'fmDD月YYYY')FROM dual;

  1. 0

  2. 1

  3. 2

  4. 3

答案:A. FM修饰符从日期格式中删除所有填充空格.

43.以下查询的结果是什么?

 SELECT TO_CHAR(TO_DATE('01 -JAN-13','DD-MON-YY' ),"月份YYYY fmHH:MI:SS AM"的'fmDdspth')来自双重;

  1. 由于在日期格式中使用双引号,它将返回ORA错误

  2. 2013年1月1日

  3. 2013年1月1日12:00 :00 AM

  4. 2013年1月1日上午12:00:00

答案:D. TO_CHAR按照给定的格式格式化输入日期'01 -JAN-13'.

44.以下哪项指定了字符参数和TO_DATE函数的日期格式模型的完全匹配?

  1. TO_DATE

  2. TO_CHAR

  3. FM

  4. FX

答案:D.

45. Oracle DB中FX修饰符的真实情况是什么?

  1. 区分大小写

  2. 当与提到的日期格式模型匹配时忽略字符参数中的空格

  3. 字符参数中的标点符号和引用文本不一定与格式模型

  4. 以上都不是

答案:D.

46.以下查询的结果是什么?

 SELECT TO_DATE('2013年1月21日','fxMonth DD,YYYY')FROM dual;

  1. 它会成功执行

  2. 它会给出结果2013年1月21日

  3. 它会产生ORA错误

  4. 它将给出结果2013年1月21日

答案:C.如果使用FX,字符参数应与格式模型完全匹配. 1月份之后的额外空格不匹配.

47. Oracle DB中的FX修饰符有什么用?

  1. 它可以与TO_CHAR一起使用

  2. 它可以与TO_CHAR和TO_DATE一起使用

  3. 它只能用于TO_DATE

  4. 没有以上

答案:C. FX格式修饰符只能与TO_DATE函数一起使用.

48.假设SYSDATE是01-JAN-13,以下查询的结果是什么?

 SELECT TO_CHAR (SYSDATE, 'DDTH') FROM dual;

  1. 1月1日

  2. 1st

  3. 1 ST

  4. 01ST

答案:D.

49.假设SYSDATE是01-JAN-13,以下查询的结果是什么?

 SELECT TO_CHAR(SYSDATE,'fmDDTH')FROM dual;

  1. 1月1日

  2. 1st

  3. 1ST

  4. 01ST

答案:C.

50.假设SYSDATE是01-JAN-13并且在星期二,那么以下查询的结果是什么?

 SELECT TO_CHAR (SYSDATE, 'fmDay')||'''s Meeting' FROM dual;

  1. 星期二

  2. 星期二

  3. 星期二会议

  4. 星期二会议

答案:D.

51.以下查询的结果是什么?

 SELECT TO_DATE('01/JAN/13','DD-MON-YY')FROM双重;

  1. ORA错误

  2. 01-JAN-2013

  3. 01-JANUARY-13

  4. 01-JAN-13

答案:D.

52.以下查询的结果是什么?

 SELECT TO_DATE('01 ## JAN/13','DD-MON-YY') FROM dual;

  1. ORA错误

  2. 01-JAN -2013

  3. 01-JANUARY-13

  4. 01-JAN-13

答案:A.在日期之间使用一个分隔符.

53.以下查询的结果是什么?

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

  1. 01-JAN-2013

  2. ORA错误

  3. 01-JAN-13

  4. 01-JANUARY-13

答案:B.使用格式精确修饰符,输入文字必须与格式字符串匹配.

54.以下查询的结果是什么?

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

  1. 01-JAN-2013

  2. ORA错误

  3. 01-JAN-13

  4. 01-JANUARY-13

答案:C.

55.以下查询的结果是什么?

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错误

答案:C.

56.一名员工艾伦于1月1日 -  13日被聘用.以下查询的结果是什么? (假设会话的NLS参数设置为DD-MON-YY)

 SELECT TO_DATE(hire_date,'fxfmDD-MON-YY' )FROM employees WHERE first_name ='ALLEN';

  1. ORA错误

  2. 01-JAN-2013

  3. 1-JAN-13

  4. 1-JAN-2013

答案:C.

57.以下查询的结果是什么?

 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

答案:D.月份修饰符最多可填充9个带空格的位置.

检查给定的EMPLOYEES表的结构并回答下面的问题58和59.

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’) <’01-JAN-1990’

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

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

  4. 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?

  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’)<3;
  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.\tA certificate of achievement has to be printed and presented to all those employees who joined the organization before the year 2008 and are stil l 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.