Sql Certificate 简明教程

SQL - Conditional Expressions Questions

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

*回答：C。*数据类型定义列可以在表中存储的数据的性质。列只能存储一种类型的数据。Oracle 中可用的主数据类型为 NUMBER、VARCHAR2 和 DATE。

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

【答案：C】单行函数分组函数可以嵌套在一个 SELECT 查询中，其中最内部的函数首先执行。最内部函数执行的结果用作外部函数的输入。

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

【答案：B】NVL、NVL2、NULLIF 和 COALESCE 等一般函数用于在显示查询结果时缓解 NULL 的影响。它们通过指定备用值来绕过 NULL 值。

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

【答案：D】一般函数通常与所有主要数据类型兼容，如 NUMBER、VARCHAR2 和 DATE。

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

【答案：C、D】COALESCE 函数采用两个必需参数和任意数量的可选参数。语法为 COALESCE(expr1, expr2,Ö,exprn)，其中 expr1 在不为 null 时返回，否则 expr2 在不为 null 时返回，依此类推。COALESCE 是 NVL 函数的一般形式，如下两个方程式所示：COALESCE(expr1,expr2) = NVL(expr1,expr2)，COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

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

【答案：C】NVL 函数采用两个必需参数。其语法为 NVL(original, ifnull)，其中 original 表示要测试的项，ifnull 表示 original 项计算为 null 时返回的结果。original 和 ifnull 参数的数据类型始终必须兼容。它们要么必须属于同一种类型，要么必须能够将 ifnull 隐式转换为 original 参数的类型。NVL 函数返回与 original 参数具有相同数据类型的值。

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

【答案：D】NVL 函数评估任何数据类型的列或表达式是否为 null。如果该项为 null，则返回备用的非 null 值；否则，返回初始项。

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

【答案：D】he NVL 函数采用两个必需参数。其语法为 NVL(original, ifnull)，其中 original 表示要测试的项，ifnull 表示 original 项计算为 null 时返回的结果。

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

【答案：D】original 和 ifnull 参数的数据类型始终必须兼容。它们要么必须属于同一种类型，要么必须能够将 ifnull 隐式转换为 original 参数的类型。NVL 函数返回与 original 参数具有相同数据类型的值。1234 应置于单引号中。在这种情况下不会发生数据类型的隐式转换。

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

*回答：D. *

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

答案：B、C。

*Answer: B, C. *

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

【答案：A、B】TO_CHAR、TO_NUMBER 和 TO_DATE 是三种最广泛使用的数据转换函数，将在后面详细讨论。TO_CHAR 函数将数字和日期信息转换为字符，而 TO_NUMBER 和 TO_DATE 分别将字符数据转换为数字和日期。

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

*答案：A. *如果 Oracle 数据库将值隐式转换为兼容的数据类型，则称为隐式转换。

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

*答案：C. *当程序员必须使用转换函数之一以编程方式转换值时，称为显式转换。

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

*答案：C. *TO_CHAR、TO_NUMBER 和 TO_DATE 是三种最广泛使用的转换函数，并且进行了详细讨论。TO_CHAR 函数将数字和日期信息转换为字符，而 TO_NUMBER 和 TO_DATE 分别将字符数据转换为数字和日期。

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

*答案：A，D. *

*Answer: A, D. *

17. 检查 EMPLOYEES 表的结构（如给定）。

*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';

*答案：C. *字符串（VARCHAR2 或 CHAR）由 Oracle 隐式转换为 DATE，提供所选数据的所需输出。

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

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

*答案：B. *字符数据必须表示有效数字才能考虑进行隐式转换。

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

*答案：C. *TO_NUMBER 函数返回 NUMBER 类型的项。转换为数字的字符字符串必须适当地格式化，以便通过适当的格式掩码转换或去除任何非数字部分。

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

*答案：A. *TO_CHAR 函数返回VARCHAR2 数据类型的项。当应用于 NUMBER 类型的项时，有几种可用格式化选项。

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

*答案：D。*TO_DATE 函数返回 DATE 类型的项目。转换时间字符串可以包含日期时间元素的所有或仅一部分来组成 DATE。当含有仅部分日期时间元素的字符串转换时，Oracle 提供默认值来构造一个完整日期。字符字符串的组件使用格式模型或掩码与不同的日期时间元素相关联。

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

*答案：A. *TO_CHAR 函数返回VARCHAR2 数据类型的项。当应用于 NUMBER 类型的项时，有几种可用格式化选项。

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

*答案：D。*TO_CHAR、TO_NUMBER 和 TO_DATE 是使用最广泛的三种转换函数，并在详细内容中进行了讨论。TO_CHAR 函数将数字和日期信息转换为字符，而 TO_NUMBER 和 TO_DATE 函数分别将字符数据转换为数字和日期。

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

*答案：B。*TO_CHAR 函数返回VARCHAR2 数据类型的项目。当应用于 NUMBER 类型的项目时，有几个可用格式化选项。

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

*回答：D. *

*Answer: D. *

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

*答案：C。*默认情况下，TO_CHAR 函数考虑当前活动会话的 NLS 设置。

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

答案：A。

*Answer: A. *

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

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

*答案：C. *TO_NUMBER 函数返回 NUMBER 类型的项。转换为数字的字符字符串必须适当地格式化，以便通过适当的格式掩码转换或去除任何非数字部分。

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

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

*答案：C。*TO_DATE 函数返回 DATE 类型的项目。转换时间字符串可以包含日期时间元素的所有或仅一部分来组成 DATE。

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

答案：A。

*Answer: A. *

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

TO_CHAR(date, 'frmt')

*回答：D. *

*Answer: D. *

33. What will the following statement on execution yield?

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

*答案：C。*参数“01-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])

回答：C。

*Answer: C. *

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

*回答：C。*格式模型“fm”表示填充模式。

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

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

*回答：D。*格式模型“fm”表示精确格式。

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

回答：D。“月”的最长单词为“九月”，因此，Oracle 将根据 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?

回答：A、B。

*Answer: A, B. *

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

回答：B。

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

*回答：B。*FM 修改器抑制添加到数字左侧的空格。

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

*回答：B。*TO_CHAR 根据给定的格式模型对输入日期进行格式化。

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

*回答：A。*FM 修改器移除了日期格式中填充的所有空格。

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

*回答：D。*TO_CHAR 根据给定的格式对输入日期“01-JAN-13”进行格式化。

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

*回答：D. *

*Answer: D. *

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

*回答：D. *

*Answer: D. *

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

SELECT TO_DATE ('January   21, 2013' , 'fxMonth DD, YYYY') FROM dual;

*答案：C. *如果使用 FX，则字符参数应与格式模型完全匹配。此处，January 后的额外空格不匹配。

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

*答案：C. *FX 格式修改器只能与 TO_DATE 函数一起使用。

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

*回答：D. *

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

回答：C。

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

*回答：D. *

*Answer: D. *

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

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

*回答：D. *

*Answer: D. *

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

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

*答案：A. *在日期之间使用单个分隔符。

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

*答案：B. *使用格式确切修饰符时，输入文本必须与格式字符串匹配。

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

回答：C。

*Answer: C. *

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

SELECT TO_DATE ('11-JAN-2013','fxDD-MON-YYYY') FROM dual;

回答：C。

*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';

回答：C。

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

*答案：D. *月份修饰符前面补齐 9 个空格。

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

检查 EMPLOYEES 表的结构，然后回答后面的问题 58 和 59。

*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. 你需要列出 1990 年以前受雇的所有员工的姓氏和名字。以下哪个 WHERE 语句会给你所需的结果？（假设此列表应在“01-JAN-2013”当天生成）

*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') *

答案：D。*使用 RR 格式将考虑 1950 年至 1999 年之间的日期部分。

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

答案：D。*函数中存在多个函数称为函数嵌套。

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

答案：C。*COALESCE 函数采用两个必需参数和任意数量的可选参数。语法为 COALESCE(expr1, expr2, Ö, exprn)，其中 expr1 如果不为 null 则返回，否则 expr2 不为 null，依此类推。

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

答案：D。*CASE 表达式促进了 if-then-else 条件逻辑。CASE 表达式有两种变体。简单的 CASE 表达式一次列出条件搜索项，并且每个比较表达式都会测试与该搜索项的相等性。搜索的 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;

答案：D。*格式模型“dy”从输入日期中拼出前三个字母。正如上面给出的查询中，“DY”将给出“TUE”，而不是“tue”。

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

答案：D。*fmDAY（所有大写字母）或 fmday（所有小写字母）格式模型将拼出输入日期中的日期，不带任何尾随或前导空格。

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

答案：B。*格式模型“q”提供给定日期所在季度。在给定的查询中，4 月至 6 月是第二季度。

*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.

检查所给的 EMPLOYEES 表格的结构，并回答以下 65 至 67 个问题。*

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

答案：D。*格式模型“ww”提供今年的第几周。

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

答案：B。*格式模型“q”提供今年的第几个季度。

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

*答案：A. *“sp”标识符以简单的英语拼写年份。

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

回答：B。

*Answer: B. *

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

SELECT TO_NUMBER ('$3000') FROM dual;

*答案：D. *查询抛出“ORA-01722: 无效数字”错误，因为给定的字符串不能被识别为数字。

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

*答案：C. *适当的格式模型帮助 TO_NUMBER 将给定字符串转换为数字。

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

*回答：D. *

*Answer: D. *

72. What will the following query yield?

SELECT TO_NUMBER('456.23','999.99') FROM dual;

回答：B。

*Answer: B. *

73. What is true about the nested functions?

*答案：A. *函数执行的输出用作其前一个函数的输入。

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

*答案：C. *NULLIF 函数测试两个术语的相等性。如果它们相等，函数返回 null，否则它返回测试的两个术语中的第一个。这里 1 和表达式“2-1”被 Oracle 视为相等，因此返回 NULL。

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

*答案：B. *由于两个日期的长度不同，因此返回第一个参数。

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

*答案：C. *COALESCE 函数接受两个强制参数和任意数量的可选参数。COALESCE 是 NVL 函数的一般形式，如下面两个等式所示：COALESCE(expr1,expr2) = NVL(expr1,expr2)， COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))。

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

回答：A、C。

*Answer: A, C. *

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

*回答：D. *

*Answer: D. *

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

回答：A. *CASE 是符合 ANSI SQL 标准的，不属于 Oracle 特定。

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

回答：C. *DECODE 函数通过测试其前两个条件是否相等来实现 if-then-else 条件逻辑，如果相等，则返回第三项，如果不想等，还可以返回其他项。DECODE 函数至少需要三个强制参数，但可以需要更多。

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

回答：D. *DECODE 函数通过测试其前两个条件是否相等来实现 if-then-else 条件逻辑，如果相等，则返回第三项，如果不想等，还可以返回其他项。

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

回答：C. *DECODE 认为两个 NULL 值是等价的。这是 Oracle 中 NULL 的一个异常现象。

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

回答：D. *DECODE 函数至少需要三个强制参数，但可以需要更多。

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

回答：D. *CASE 表达式促进了 if-then-else 条件逻辑。CASE 表达式有两种变体。简单的 CASE 表达式只列出一次条件搜索项，并且由每个比较表达式测试与搜索项的相等性。已搜索的 CASE 表达式为每个比较表达式列出单独的条件。它至少需要 3 个强制参数，但也可以接受更多。

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

*回答：D. *

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

回答：B。

*Answer: B. *

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

回答：B。

*Answer: B. *

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

*回答：D. *

*Answer: D. *

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

回答：B。

*Answer: B. *

90. 检查给定的 EMPLOYEES 表的结构。

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

在 Oracle DB 中执行以下查询会有什么结果？

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;

答案：D。*CASE 表达式促进了 if-then-else 条件逻辑。CASE 表达式有两种变体。简单的 CASE 表达式一次列出条件搜索项，并且每个比较表达式都会测试与该搜索项的相等性。搜索的 CASE 表达式为每个比较表达式列出独立条件。

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

*回答：D. *

*Answer: D. *

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

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

*答案：D. *NVL2 函数对 NVL 进行了增强，但目的非常相似。它评估任何数据类型的列或表达式的值是否为 null。如果第一个单元格不为 null，则返回第二个参数，否则返回第三个参数。

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

*答案：D. *“ith” 格式模型按 “st” 或 “th” 给出日期中的某一天。

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

回答：C。

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

*答案：D. *可以使用 “SpTh” 格式修改符拼写时间戳组件。

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

答案：D。*CASE 表达式促进了 if-then-else 条件逻辑。CASE 表达式有两种变体。简单的 CASE 表达式一次列出条件搜索项，并且每个比较表达式都会测试与该搜索项的相等性。搜索的 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)

以下 SQL 查询的结果是什么？

What will be the outcome of the following SQL query?

SELECT DECODE (salary,10000) FROM employees;

*答案：B. *DECODE 函数通过测试其前两个单元格是否相等来实现 if-then-else 条件逻辑，如果它们相等则返回第三个单元格，如果它们不相等则可选地返回另一个单元格。DECODE 函数至少需要三个必需的参数，但可以采用更多参数。如果 DECODE 函数中省略了默认值，则返回值为 NULL。

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

*答案：C. *HH:MI:SS 格式的 FF [1..9] 扩展会在小数秒中产生多达 1..9 位的小数秒。

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

回答：C、D。

*Answer: C, D. *

100. 检查给定的 EMPLOYEES 表结构。

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

你需要找出员工 Jaimie Patrick 在该公司 “ABC” 中入职的那一年中的哪一天。以下哪个查询会给出一个所需输出？

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?

*答案：A. *格式模型 “DDD” 返回给定日期属于的那一年中的哪一天。

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

*答案：C. *格式模型 “SSSS” 给出午夜后的秒数。

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