Sql Certificate 简明教程

SQL - Using Single-Row 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。

Answer: A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 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;

What is the outcome of this query?

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

*Answer: C. *The SYSDATE function will take the current time of the database which it is connecting to remotely. You must perform basic arithmetic operation to adjust the time zone.

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 函数返回从给定起始位置和结束位置开始的字符串的一组字符。

Answer: A. The INSTR function returns the position of a given character in the required string. The SUBSTR function returns set of characters from the string from a given starting and end position.

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 标识的列中非空且唯一的值的数目。

Answer: A. The 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() returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr. COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by 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 的列值。

Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs. Similarly, COUNT(*) is used to count the column values including NULLs.

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

SELECT 100+NULL+999 FROM dual;

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

*Answer: C. *Any arithmetic operation with NULL results in a NULL.

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

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

*Answer: D. *Single row functions can take more than one argument and the return type can be different from the data type of the inputs.

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

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

*Answer: A, D. * The format model $99G999D99 formats given number into numeric, group separator, and decimals. Other format elements can be leading zeroes, decimal position, comma position, local currency, scientific notation, and sign.

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，则返回第三个参数。

*Answer: C. * The NVL2 function takes minimum three arguments. The NVL2 function checks the first expression. If it is not null, the NVL2 function returns the second argument. If the first argument is null, the third argument is returned.

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

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

*Answer: D. *The types of single-row functions like character, numeric, date, conversion and miscellaneous as well as programmer-written can be incorporated in Oracle DB.

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

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

Answer: D. Single row function can be used in SELECT statement, WHERE clause and ORDER BY clause.

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

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

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

14. 查看 EMPLOYEES 表的结构，如下所示。

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

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

*Answer: C. *The NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

15. What will the outcome of the following query?

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

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

*Answer: B. *The NVL will treat NULL as a value and returns the alternate argument i.e. 1 as the result.

16. 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 employee_id , NVL(salary, 0) FROM employees WHERE first_name like 'P%' ORDER BY first_name;

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

*Answer: B. *NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

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 进行隐式类型转换。在所有其他情况下，两个参数都必须为相同的数据类型。

*Answer: C. * If arg1 is of VARCHAR2 data type, Oracle does implicit type conversion for arg2 id arg2 is of NUMBER datatype. In all other cases, both the arguments must be of same datatype.

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，则返回第三个表达式。

*Answer: B. * The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

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 参数的数据类型相同。

*Answer: D. *The data types of the arg2 and arg3 parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert arg3 to the type of the arg2 parameter. The data type returned by the NVL2 function is the same as that of the arg2 parameter.

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，则返回第三个表达式。

*Answer: C. *The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

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

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

*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. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(arg1,arg2), where the arguments arg1 and arg2 are compared. If they are identical, then NULL is returned. If they differ, the arg1 is returned.

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 架构创建一个报告，显示自入职以来工作发生变化的员工。您执行下面给出的查询。

You need to create a report from the HR schema displaying employees who have changed jobs since they were hired. You execute the query given below.

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;

What will be the outcome of the query given above?

Answer: D.

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

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

Answer: D. Functions can perform calculations, perform case conversions and type conversions.

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

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

*Answer: B. * Single row functions always return one result per row and they operate on single rows only; hence the name ‘Single Row' is given to them.

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

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

*Answer: A. * There are basically two types of functions - Single row and Multiple row functions.

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

答案： B 和 D。 因为字符和 NULLIF 是单行函数，其余为数据类型。

*Answer: B and D. * As Character and NULLIF are single row function and rest are the datatypes.

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

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

*Answer: B. * Multiple Row functions always work on a group of rows and return one value per group of rows.

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

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

*Answer: C. *Group functions are same as Multi row functions and aggregate functions.

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

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

*Answer: A. * Single row functions can be nested up to multiple levels.

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

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

*Answer: D. *Single row functions can accept one or more arguments depending upon the objective they serve.

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

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

*Answer: C. * A user-supplied constant, variable value, column value and expression are the types of arguments of a single row function.

33. What is true about Character functions?

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

*Answer: C. *The character function INSTR accepts a string value but returns numeric position of a character in the string.

34. What is true about Number functions?

*回答：D. *

*Answer: 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 除外，它返回一个数字。

*Answer: C. * All the DATE data type functions return DATE as return values except MONTHS_BETWEEN which returns a number.

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

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

*Answer: C. * Conversion functions convert a value from one data type to another. The NVL function replaces a null value with an alternate value.

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

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

*Answer: C. * The CONCAT, SUBSTR and REPLACE are Character-manipulation Character functions while INITCAP, LOWER and UPPER are case conversion character functions.

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

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

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

*Answer: C. *The LOWER function converts a string to lower case characters.

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

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

答案：D。 大小写转换字符可以嵌套在 SELECT 查询中。

*Answer: D. *Case conversion characters can be nested in the SELECT queries.

检查 EMPLOYEES 表的结构，并回答随后的问题 40 至 42。

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

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 函数连接两个字符串，但中间没有空格。

*Answer: A. *The CONCAT function joins two strings without any space in between.

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。

*Answer: 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' 将不成立，因此不会显示任何结果。

*Answer: B. * Provided the last names in the employees table are in a proper case, the condition WHERE last_name = 'smith' will not be satistified and hence no results will be displayed.

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

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

Answer: B. The CONCAT function accepts only two arguments of NUMBER or VARCHAR2 datatypes.

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

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

Answer: A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

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

SELECT length('hi') FROM dual;

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

*Answer: A. * the LENGTH function simply gives the length of the string.

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

回答：C。

*Answer: 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 函数一起使用。

*Answer: B. * Substitution variables can be used with the UPPER and LOWER functions.

48. What is false about the table DUAL in Oracle database?*

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

*Answer: C. * The DUAL table has one column named DUMMY and one row which has a value 'X'.

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

SELECT sysdate+4/12 FROM dual;

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

*Answer: B. * Arithmetic operations can be performed on dates in the Oracle DB.

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

SELECT lower (100+100) FROM dual;

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

*Answer: D. *Arithmetic expressions can be specified within case conversion functions.

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 函数可以接受日期类型输入，并生成与作用于字符串时相同的结果。

*Answer: C. * The functions UPPER and LOWER can accept date type inputs and will yield the same result as they do on Strings.

52. What is the result of the following query?

SELECT INITCAP (24/6) FROM dual;

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

*Answer: A. *Arithmetic expressions can be specified within case conversion functions.

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”开头的所有员工。下列哪一个查询将产生所需的结果？

You need to display the last name of all employees which starts with the letter 'A'. Which of the following queries will yield the required result?

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 函数接受所有类型的参数。

*Answer: D. * The CONCAT function accepts arguments of all types.

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

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

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

*Answer: A. *The CONCAT function can be nested with self or other character function.

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”。以下哪个查询会提供所需输出？

You need to generate a report which shows the first name, last name and the salary for all the employees in the department 100. The report should show the results in the form 'Andy Smith earns 50000'. Which of the following queries will give the required output?

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

*Answer: A. *The CONCAT function can be nested with self or other character function.

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

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

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

*Answer: A. *The LENGTH functions counts blank spaces, tabs and special characters too.

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 子句中。

*Answer: B. *The LENGTH function can be used in WHERE clause.

59. How does the function LPAD works on strings?

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

*Answer: D. * The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

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 个参数，则填充将由空格进行。

*Answer: C. * LPAD and RPAD take maximum of 3 arguments. If there are 2 arguments given, the padding happens by spaces.

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 ( 中。

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

63. What is true regarding the TRIM function?

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

*Answer: B. *The TRIM function literally trims off leading or trailing (or both) character strings from a given source string. TRIM function when followed by TRAILING or LEADING keywords, can remove characters from one or both sides of a string.

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

Which of the following queries will give the required result?

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

*Answer: B. *The LTRIM and RTRIM functions can be used in combination with each other.

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

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

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

*Answer: C. *INSTR function returns a 0 when the search string is absent in the given string.

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 个 '$' 的出现。

*Answer: B. *INSTR function search for the 4th occurrence of '$' starting from the 3rd position.

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。

*Answer: D. * SUBSTR function will search 3 places starting from the end of string and will give 2 characters in the forward direction giving #9.

检查下面给出的 EMPLOYEES 表的结构，然后回答后面的第 68 和 69 题。

*Examine the structure of the EMPLOYEES table as given below and answer the questions 68 and 69 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)

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 函数在字符串的左侧或右侧添加填充字符串，直到它在填充后达到指定的长度。

*Answer: B. *The LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

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) 开始。当位置为正时，函数从字符串开头计数以查找第一个字符。当位置为负时，函数从字符串末尾向后计数。

*Answer: B. *The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

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 开始的位置。

*Answer: D. * The query will give a NULL as the position 10 to start with in the SYSDATE doesn’t exist.

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

Answer: 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'。

*Answer: C. *The REPLACE function searches for '8' in 9998 and replaces it with '88'.

73. 检查此处给出的 EMPLOYEES 表结构。

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

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

You need to retrieve the first name, last name (separated by a space) and the formal names of employees where the combined length of the first name and last name exceeds 15 characters. A formal name is formed by the first letter of the First Name and the first 14 characters of the last name. Which of the following queries will fulfill this requirement?

*回答：D. *

*Answer: D. *

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

SELECT round(148.50) FROM dual;

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

*Answer: D. * if the decimal precision is absent, the default degree of rounding is 0 and the source is rounded to the nearest whole number.

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

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

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

*Answer: B. *The date is truncated to the first day of the month. Similarly, it can be done for year also.

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

SELECT trunc(1902.92,-3) FROM dual;

回答：B。

*Answer: B. *

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

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

*Answer: C. *The MOD function is used to get the remainder of a division operation.

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

*答案：B. *无论除数为正还是负，MOD 函数都提供相同的答案。

*Answer: B. * The MOD function gives the same answer for a positive divisor as well as a negative divisor.

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”是奇数。

*Answer: C. *The MOD function can be used to check whether a given number is even or odd. If MOD (num,2) returns zero, the number 'num' is an even. If MOD (num,2) returns 1, the number 'num' is odd.

80. 检查 EMPLOYEES 表如下所示的结构。

*80. Examine the structure of the EMPLOYEES table as given below. *

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 开头。以下哪条查询将满足该要求？

You need to allocate the first 12 employees to one of the four teams in a round-robin manner. The employee IDs start with a 100. Which of the following queries will fulfill the requirement?

回答：B。

*Answer: B. *

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

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

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

*Answer: B. *Calling the SUBSTR function with just the first two parameters results in the function extracting a string from a start position to the end of the given source string.

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

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

*Answer: C. * For SQL*PLUS the default date format is 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' 的值为必须为整数，且可以为负数。

*Answer: B. *The ADD_MONTHS(date, x) function adds 'x' number of calendar months to the given date. The value of 'x' must be an integer and can be negative.

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 的值可以是一个数字，代表一个星期几，也可以是一个字符串。

*Answer: C. *The NEXT_DAY(date,'day') finds the date of the next specified day of the week ('day') following date. The value of char may be a number representing a day or a character string.

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

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

*Answer: C. * If there is only one parameter present, then the rounding happens to the nearest whole number

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 之间，那么返回当前世纪。

*Answer: C. * If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned.

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 之间，那么返回上个世纪。

*Answer: A. * If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned.

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 之间，那么默认返回当前世纪。

*Answer: A. * if the two digits of the current and specified years lie between 50 and 99, the current century is returned by default.

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 之间，那么返回下个世纪。

*Answer: C. * if the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned.

90. How many parameters does the SYSDATE function take?

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

*Answer: D. *The SYSDATE is a pseudo column in Oracle.

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

Answer: D.

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

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

*Answer: B. *Subtraction of two dates results in number of days.

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

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

*Answer: A. *Subtraction of a number from a date value results in date.

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

答案：A。

*Answer: 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 返回一个负数。

*Answer: C. * If the first parameter is less than the second parameter, the MONTHS_BETWEEN returns a negative number.

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

*回答：D. *

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

*Answer: 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，结果为负。结果的小数部分表示该月份的一部分。

*Answer: D. *The MONTHS_BETWEEN(date1, date2) finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

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。

*Answer: B. * the fractional part of 2.5 will be ignored and 2 months will be added to 31-dec-2012 which is 31-feb-2013 but as it is not a valid date, the result is 28-feb-2009.

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') 或最后一个指定星期几的日期。字符值可以是表示某天的数字或字符字符串。

*Answer: B. *The NEXT_DAY(date,'day') and LAST_DAY (date,'day') functions find the date of the next or last specified day of the week ('day') following date. The value of char may be a number representing a day or a character string.