Sql Certificate 简明教程

SQL - Conversion Functions Questions

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

SELECT ROUND(144.23,-1) FROM dual;

Answer: A. ROUND 函数将根据指定精度 -1 对值 144.23 进行四舍五入，并返回 140。

Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 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)

2. You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command.

SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2;

此次查询的结果是什么？

*答案：C. *SYSDATE 函数将获取它正在远程连接的数据库的当前时间。必须执行基本算术运算以调整时区。

3. You need to display the names of the employees who have the letter 's' in their first name and the letter 't' at the second position in their last name. Which query would give the required output?

Answer: A. INSTR 函数返回给定字符串中给定字符的位置。SUBSTR 函数返回从给定起始位置和结束位置开始的字符串的一组字符。

4. Which of the following statements is true regarding the COUNT function?

Answer: A. COUNT( ) function returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT( ) 返回满足 WHERE 子句中条件的行数。相反，COUNT(expr) 返回由 expr 标识的列中非空值的数目。COUNT(DISTINCT expr) 返回由 expr 标识的列中非空且唯一的值的数目。

5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?

Answer: D. COUNT (ALL column_name) 用于计算除去 NULL 的行数。类似的，COUNT(*) 用于计算包含 NULL 的列值。

6. What will be the outcome of the query given below?

SELECT 100+NULL+999 FROM dual;

*答案：C。*任何包含 NULL 的算数运算都会产生 NULL。

7. Which of the following statements are true regarding the single row functions?

*答案：D。*单行函数可以接受多个参数，并且返回类型可以不同于输入的数据类型。

8. Which of the below queries will format a value 1680 as $16,80.00?

答案：A、D。格式模型 $99G999D99 将给定的数字格式化为数字、组分隔符和小数。其他格式元素可以是前导零、小数点位置、逗号位置、本地货币、科学计数法和符号。

9. Determine the output of the below query.

SELECT RPAD(ROUND('78945.45'),10,'*') FROM dual;

Answer: A. *The LPAD(string, num, char) and RPAD(string, num, char) functions add a character to the left or right of a given string until it reaches the specified length (num) after padding. The ROUND function rounds the value 78945.45 to 78945 and then pads it with ' 直到长度达到 10。

10. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query?

答案：C。NVL2 函数至少需要三个参数。NVL2 函数检查第一个表达式。如果它不是 null，则 NVL2 函数返回第二个参数。如果第一个参数是 null，则返回第三个参数。

11. Which of the following type of single-row functions cannot be incorporated in Oracle DB?

答案：D。字符、数字、日期、转换和不同类型以及程序员编写的单行函数类型可以集成在 Oracle DB 中。

12. Out of the below clauses, where can the single-row functions be used?

Answer: D. 单行函数可以在 SELECT 语句、WHERE 子句和 ORDER BY 子句中使用。

13. What is true regarding the NVL function in Oracle DB?

Answer: B. NVL 函数用备用值替换 null 值。数据类型为日期、字符和数字的列可以使用 NVL 来提供备用值。列及其备用的数据类型必须匹配。

14. 查看 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 last_name, NVL(job_id, 'Unknown')
FROM employees
WHERE last_name LIKE 'A%'
ORDER BY last_name;

答案：C。NVL 函数用备用值替换 null 值。数据类型为日期、字符和数字的列可以使用 NVL 来提供备用值。列及其备用的数据类型必须匹配。

15. What will the outcome of the following query?

SELECT NVL (NULL,'1') FROM dual;

答案：B。NVL 将 NULL 作为值处理，并返回备用参数，即 1 作为结果。

16. 以下查询的结果是什么？（考虑给出的 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 employee_id , NVL(salary, 0) FROM employees WHERE first_name like 'P%' ORDER BY first_name;

*答案：B. *NVL 函数使用替换值替代空值。数据类型为日期、字符和数字的列可以使用 NVL 来提供替换值。列的数据类型及其替换值的数据类型必须匹配。

17. Which of the following statements is true regarding the NVL statement?

SELECT NVL (arg1, arg2) FROM dual;

*答案：C. *如果 arg1 为 VARCHAR2 数据类型，当 arg2 为 NUMBER 数据类型时，Oracle 会为 arg2 id 进行隐式类型转换。在所有其他情况下，两个参数都必须为相同的数据类型。

18. What will be the outcome of the following query? (Consider 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)

SELECT NVL2(job_id,'Regular Employee','New Joinee') FROM employees;

*答案：B. *NVL2 函数检查第一个表达式。如果第一个表达式不为 null，NVL2 函数将返回第二个表达式。如果第一个表达式为 null，则返回第三个表达式。

19. Which of the following is true for the statement given as under.

NVL2 (arg1, arg2, arg3)

*答案：D. *arg2 和 arg3 参数的数据类型必须兼容，不能为 LONG 类型。它们必须为相同类型，或者必须可以将 arg3 转换为 arg2 参数的类型。NVL2 函数返回的数据类型与 arg2 参数的数据类型相同。

20. 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)

下面提到的查询的结果是什么？

SeLECT first_name, salary, NVL2(commission_pct,  salary + (salary * commission_pct), salary) "Income"
FROM employees
WHERE first_name like 'P%'
ORDER BY first_name;

*答案：C. *NVL2 函数检查第一个表达式。如果第一个表达式不为 null，NVL2 函数将返回第二个表达式。如果第一个表达式为 null，则返回第三个表达式。

21. What is true about the NULLIF function in Oracle DB?

*答案：C. *NULLIF 函数测试两个术语是否相等。如果它们相等则函数返回 null，否则返回测试的两个术语中的第一个。NULLIF 函数需要两个任何数据类型的必选参数。语法为 NULLIF(arg1,arg2)，其中比较参数 arg1 和 arg2。如果它们相等，则返回 NULL。如果它们不同，则返回 arg1。

22. Pick the correct answer given after the statement shown as under.

NULLIF (arg1,arg2)

Answer: D.

23. 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)

您需要从 HR 架构创建一个报告，显示自入职以来工作发生变化的员工。您执行下面给出的查询。

SELECT e.last_name, NULLIF(e.job_id, j.job_id,"Old Job ID")
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

以上给出的查询结果是什么？

Answer: B.

24. Which of the following is not a property of functions?

Answer: D. 函数可以执行计算，执行大小写转换和类型转换。

25. What is the most appropriate about single row functions?

*答案： B. * 单行函数始终为每行返回一个结果，并且它们仅针对单行进行操作；因此为其命名为“单行”。

26. What among the following is a type of Oracle SQL functions?

*答案： A. * 函数主要有两种类型 - 单行函数和多行函数。

27. What among the following is a type of single-row function?

回答：B. 单行函数的类型包括字符、日期、转换、常规和数字。

28. What is the most appropriate about Multiple Row Functions?

*答案： B. * 多行函数始终针对一组行工作，并为每组行返回一个值。

29. Which of the following are also called Group functions?

答案： C。 组函数与多行函数和聚合函数相同。

30. Which of the following is true about Single Row Functions?

*答案： A. * 单行函数可嵌套到多层。

31. What is the number of arguments Single Row functions accept?

*答案： D. * 单行函数可根据其服务目标接受一个或多个参数。

32. Which of the following can be an argument for a Single Row Function?

*答案： C. * 用户提供的常量、变量值、列值和表达式是单行函数的参数类型。

33. What is true about Character functions?

*答案： C. * 字符函数 INSTR 接受一个字符串值，但会返回字符串中字符的数字位置。

34. What is true about Number functions?

*回答：D. *

35. Which of the following is an exception to the return value of a DATE type single-row function?

答案：C。 所有 DATE 数据类型函数均返回 DATE 作为返回值，而 MONTHS_BETWEEN 除外，它返回一个数字。

36. Which of the following is not a Conversion type Single Row function?

答案：C。 转换函数将一个值从一种数据类型转换为另一种数据类型。NVL 函数用备用值替换空值。

37. Which of the following is a Case-Conversion Character function?

答案：C。 CONCAT、SUBSTR 和 REPLACE 是字符操作字符函数，而 INITCAP、LOWER 和 UPPER 是大小写转换字符函数。

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

SELECT lower('HI WORLD !!!')  FROM dual;

答案：C。 LOWER 函数将字符串转换为小写字符。

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

SELECT lower(upper(initcap('Hello World') )) FROM dual;

回答：C. 大小写转换字符可以嵌套在 SELECT 查询中。

检查给定的 EMPLOYEES 表的结构，并回答接下来的第 40 到第 42 个问题。*

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)

40. Which of the following queries will give the same result as given in the query given below?

SELECT CONCAT(first_name, last_name) FROM employees;

答案：A。 CONCAT 函数连接两个字符串，但中间没有空格。

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

SELECT 'The job id for '||upper(last_name) ||' is a '||lower(job_id) FROM employees;

答案：A。

42. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?

SELECT employee_id, last_name, department_id  FROM employees WHERE last_name = 'smith';

答案：B。 假设员工表中的姓氏是大写，则条件 WHERE last_name = 'smith' 将不成立，因此不会显示任何结果。

43. What is true about the CONCAT function in Oracle DB?

Answer: B. CONCAT 函数仅接受 NUMBER 或 VARCHAR2 数据类型的两个参数。

44. What is true about the SUBSTR function in Oracle DB?

Answer: A. SUBSTR(string, x, y) 函数接受三个参数，并返回一个字符串，该字符串包含从源字符串中提取的指定数量的字符，从指定的起始位置 (x) 开始。当位置为正时，函数从字符串的开始位置开始计数，以查找第一个字符。当位置为负数时，则该函数从字符串的末尾开始向后计数。

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

SELECT length('hi') FROM dual;

*答案：A. * LENGTH 函数仅给出字符串的长度。

46. What is the difference between LENGTH and INSTR functions in Oracle DB?

回答：C。

47. 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)

以下查询的结果是什么？

SELECT upper(&jobid) FROM employees;

*答案：B. * 替换变量可和 UPPER 和 LOWER 函数一起使用。

[style="arabic"]1. 有关 Oracle 数据库中的表 DUAL，下列哪项是错误的？*

*答案：C. * DUAL 表有一个名为 DUMMY 的列和一个值为“X”的行。

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

SELECT sysdate+4/12 FROM dual;

*答案：B. * 可以对 Oracle DB 中的日期执行算术运算。

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

SELECT lower (100+100) FROM dual;

*答案：D. * 算术表达式可以指定在类型转换函数中。

51. What will be the outcome of the following query if the SYSDATE = 20-MAY-13?

SELECT upper (lower (sysdate)) FROM dual;

*答案：C. * UPPER 和 LOWER 函数可以接受日期类型输入，并生成与作用于字符串时相同的结果。

52. What is the result of the following query?

SELECT INITCAP (24/6) FROM dual;

*答案：A. * 算术表达式可以指定在类型转换函数中。

53. Examine the structure of the EMPLOYEES table as given here.

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)

你需要显示姓氏以字母“A”开头的所有员工。下列哪一个查询将产生所需的结果？

Answer: A, B.

54. Assuming the SYSDATE is 20-FEB-13, What will be the outcome of the following query?

SELECT CONCAT ('Today is :', SYSDATE) FROM dual;

答案：D. CONCAT 函数接受所有类型的参数。

55. What will be the result pattern of the following query?

SELECT CONCAT(first_name, CONCAT (last_name, job_id)) FROM dual;

答案：A. CONCAT 函数可以与自身或其他字符函数嵌套使用。

56. Examine the structure of the EMPLOYEES table as given here.

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)

您需要生成一个报告，其中显示部门 100 中所有员工的姓、名和工资。报告应显示以下形式的结果：“安迪·史密斯收入 50000”。以下哪个查询会提供所需输出？

答案：A. CONCAT 函数可以与自身或其他字符函数嵌套使用。

57. What will the following query show as a result?

SELECT LENGTH('It is a lovely day today!') FROM dual;

答案：A. LENGTH 函数也计算空格、制表符和特殊字符。

58. You need to display the country name from the COUNTRIES table. The length of the country name should be greater than 5 characters. Which of the following queries will give the required output?

答案：B. LENGTH 函数可以用在 WHERE 子句中。

59. How does the function LPAD works on strings?

答案：D. LPAD(string, length after padding, padding string) 和 RPAD(string, length after padding, padding string) 函数在字符串左右两侧添加填充字符串，直到它达到填充后的指定长度。

60. Which of the following options is true regarding LPAD and RPAD functions?

Answer: D.

61. What is the maximum number of input arguments in LPAD and RPAD functions?

答案：C. LPAD 和 RPAD 最多接受 3 个参数。如果给出了 2 个参数，则填充将由空格进行。

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

SELECT lpad (1000 +300.66, 14, '*') FROM dual;

Answer: A. * To make the total length of 14 characters, the return value 1300.66 is padded with 7 asterisks ( ) 在左侧。

63. What is true regarding the TRIM function?

答案：B. TRIM 函数实际上会修剪给定源字符串的前导或尾随（或两者兼有）字符字符串。在 TRIM 函数后跟 TRAILING 或 LEADING 关键字时，可以从字符串的一侧或两侧删除字符。

64. You need to remove the occurrences of the character '.' and the double quotes '"' from the following titles of a book present in the table MAGAZINE.

"HUNTING THOREAU IN NEW HAMPSHIRE" THE ETHNIC NEIGHBORHOOD."

以下哪个查询会给出所需结果？

*答案：B。*LTRIM 和 RTRIM 函数可以结合使用。

65. What will be returned as a result of the following query?

SELECT INSTR('James','x') FROM dual;

*答案：C。*当给定字符串中没有搜索字符串时，INSTR 函数返回 0。

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

SELECT INSTR('1$3$5$7$9$','$',3,4)FROM dual;

*答案：B。*INSTR 函数从第 3 个位置开始搜索第 4 个 '$' 的出现。

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

SELECT INSTR('1#3#5#7#9#', -3,2) FROM dual;

*答案：D。*SUBSTR 函数将从字符串末尾开始搜索 3 个位置，并向前提供 2 个字符，得到 #9。

检查下面给出的 EMPLOYEES 表的结构，并回答接下来的第 68 和 69 个问题。*

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)

68. You need to extract a consistent 15 character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 15 characters long, zeros must be added to the left of the value to yield a 15 character string. Which query will fulfill this requirement?

*答案：B。*LPAD 和 RPAD 函数在字符串的左侧或右侧添加填充字符串，直到它在填充后达到指定的长度。

69. You need to display the last 2 characters from the FIRST_NAME column in the EMPLOYEES table without using the LENGTH function. Which of the following queries can fulfill this requirement?

*答案：B。*SUBSTR(string, x, y) 函数接受三个参数并返回一个字符串，该字符串包含从源字符串中提取的字符数，从指定的起始位置 (x) 开始。当位置为正时，函数从字符串开头计数以查找第一个字符。当位置为负时，函数从字符串末尾向后计数。

70. Assuming the SYSDATE is 13-JUN-13, what will be the outcome of the following query?

SELECT SUBSTR(sysdate,10,7) FROM dual;

*答案：D。*查询将给出一个 NULL，因为 SYSDATE 中不存在从 10 开始的位置。

71. Which of the following is used to replace a specific character in a given string in Oracle DB?

*回答：D. *

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

SELECT replace(9999.00-1,'8',88) FROM dual;

*答案：C。*REPLACE 函数在 9998 中搜索 '8' 并将其替换为 '88'。

73. 检查此处给出的 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)

您需要检索名字、姓氏（空格分隔）和姓氏和名字的长度超过 15 个字符的员工的正式名称。正式名称由名字的首字母和姓氏的前 14 个字符组成。下列哪条查询将满足此要求？

*回答：D. *

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

SELECT round(148.50) FROM dual;

*答案：D. *如果小数精度参数不存在，则舍入的默认度数为 0，并且会将源舍入到最接近的整数。

75. Assuming the sysdate is 10-JUN-13, What will be the outcome of the following query?

SELECT trunc (sysdate,'mon') FROM dual;

*答案：B. *将日期截断为该月的第一天。类似地，也可以针对年份进行此操作。

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

SELECT trunc(1902.92,-3) FROM dual;

回答：B。

77. What is the syntax of the MOD function in Oracle DB?

*答案：C. *MOD 函数用于获取除法运算的余数。

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

回答：B. MOD 函数对正除数和负除数给出相同的答案。

79. Which of the following functions are used to differentiate between even or odd numbers in Oracle DB?

*答案：C. *MOD 函数可用于检查给定数字是奇数还是偶数。如果 MOD(num, 2) 返回零，则数字“num”是偶数。如果 MOD(num, 2) 返回 1，则数字“num”是奇数。

80. 检查 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)

你需要以循环渐进的方式将前 12 位员工分配到四个团队中。员工 ID 以 100 开头。以下哪条查询将满足该要求？

回答：B。

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

SELECT SUBSTR('Life is Calling',1) FROM dual;

*答案：B. *仅调用具有两个参数的 SUBSTR 函数会让该函数从起始位置提取一个字符串到给定源字符串的末尾。

82. What is the default data format for the sysdate in SQL Developer?

*答案：C. *对于 SQL*PLUS，默认日期格式为 DD-MON-RR。

83. Assuming the SYSDATE to be 10-JUN-2013 12:05pm, what value is returned after executing the below query?

SELECT add_months(sysdate,-1) FROM dual;

*答案：B. *ADD_MONTHS(date, x) 函数将“x”个日历月添加到给定日期。'x' 的值为必须为整数，且可以为负数。

84. What value will be returned after executing the following statement? Note that 01-JAN-2013 occurs on a Tuesday.

SELECT next_day('01-JAN-2013','friday') FROM dual;

*答案：C。*NEXT_DAY(日期，“星期”）找到星期后的日期，该日期可以是星期中的任何一天（“星期”）。char 的值可以是一个数字，代表一个星期几，也可以是一个字符串。

85. What is the maximum number of parameters the ROUND function can take?

*答案：C。*如果仅存在一个参数，那么进行到最接近的整数的舍入。

86. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-2004 in the DD-MON-RR format?

*答案：C。*如果当前年份和指定年份的两位数在 0 和 49 之间，那么返回当前世纪。

87. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

*答案：A。*如果当前年份的两位数在 0 和 49 之间，并且指定年份在 50 和 99 之间，那么返回上个世纪。

88. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

*答案：A。*如果当前年份和指定年份的两位数在 50 和 99 之间，那么默认返回当前世纪。

89. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-07 in the DD-MON-RR format?

*答案：C。*如果当前年份的两位数在 50 和 99 之间，并且指定年份在 0 和 49 之间，那么返回下个世纪。

90. How many parameters does the SYSDATE function take?

*答案：D。*SYSDATE 是 Oracle 中的伪列。

91. What is true about the SYSDATE function in Oracle DB?

*回答：D. *

92. What will be the datatype of the result of the following operation?

*答案：B。*两个日期相减得到天数。

93. What will be the datatype of the result of the following operation?

*答案：A。*从日期值中减去一个数字得到日期。

94. What does a difference between two dates represent in Oracle DB?

答案：A。

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

SELECT months_between('21-JUN-13','19-JUN-13') FROM dual;

答案：C. 如果第一个参数小于第二个参数，MONTHS_BETWEEN 返回一个负数。

96. What can be deduced if the result of MONTHS_BETWEEN (start_date,end_date) function is a fraction?

*回答：D. *

97. You are connected to a remote database in Switzerland from India. You need to find the Indian local time from the DB. Which of the following will give the required result?

*回答：D. *

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

SELECT months_between (to_date ('29-feb-2008'), to_date ('29-feb-2008 12:00:00','dd-mon-yyyy hh24:mi:ss'))*31 FROM dual;

答案：D. MONTHS_BETWEEN(date1, date2) 查找日期 date1 和日期 date2 之间的月份数。结果可以是正数或负数。如果日期 date1 晚于日期 date2，结果为正；如果日期 date1 早于日期 date2，结果为负。结果的小数部分表示该月份的一部分。

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

SELECT add_months ('31-dec-2008',2.5) FROM dual;

答案：B. 2.5 的小数部分将被忽略，在 2012-12-31 基础上添加 2 个月，得到 2013-02-31，但由于此日期无效，因此结果为 2013-02-28。

100. You need to identify the date in November when the staff will be paid. Bonuses are paid on the last Friday in November. Which of the following will fulfill the requirement?

答案：B. NEXT_DAY(date,'day') 和 LAST_DAY (date,'day') 函数查找日期后紧接的下一个指定星期几 ('day') 或最后一个指定星期几的日期。字符值可以是表示某天的数字或字符字符串。