Sql Certificate 简明教程

SQL Certificate Mock Exams

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

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

SELECT ROUND(144.23,-1) FROM dual;

2.In which of the following cases, parenthesis should be specified?

2.In which of the following cases, parenthesis should be specified?

3. Which of the following are DML commands in Oracle Database?

3. Which of the following are DML commands in Oracle Database?

4. Write a query to display employee details (Name, Department, Salary and Job) from EMP table.

4. Write a query to display employee details (Name, Department, Salary and Job) from EMP table.

5.What among the following are different types of Views?

5.What among the following are different types of Views?

6.What is true about the SET operators?

6.What is true about the SET operators?

7.Which of the following multi-row operators can be used with a sub-query?

7.Which of the following multi-row operators can be used with a sub-query?

8. When a table can be created?

8. When a table can be created?

9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

10. What among the following is true about a View?

10. What among the following is true about a View?

11. Predict the output when below statement is executed in SQL Plus?*

11. Predict the output when below statement is executed in SQL Plus?*

DESC emp

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

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

SELECT 100+NULL+999 FROM dual;

13. With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error?

13. With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error?

14.Which of the following syntax models is used in extensively in the software systems worldwide?

14.Which of the following syntax models is used in extensively in the software systems worldwide?

  • 15.What is true about co-related sub-queries?*

  • 16. You issue an UPDATE statement as follows:*

UPDATE employees
SET employee_id   = NULL;
WHERE job_id  = 'CLERK';

上述语句的结果会是什么?(此处通过设置约束,将 EMPLOYEE_ID 列标记为必需)

What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint)

17.What is true with respect to the query given above?

17.What is true with respect to the query given above?

18. Consider the following query.

18. Consider the following query.

SELECT e.job_id , e.first_name, d.department_id
FROM departments D JOIN employees e JOIN BONUS b
USING (job_id );

此查询导致错误。出错的原因是什么?

This query results in an error. What is the reason of the error?

19. Predict the output of the below query

19. Predict the output of the below query

SELECT 50 || 0001
FROM dual

20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

  • 21. What will be the outcome of the following query?*

SELECT *
FROM employees
WHERE salary BETWEEN (SELECT max(salary)
			FROM employees
			WHERE department_id  = 100)
AND (SELECT min(salary) FROM employees where department_id  = 100);

此查询返回错误。造成此错误的原因是什么?

This query returns an error. What is the reason for the error?

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

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

23.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?

23.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?

24. Which of the following can create a view even if the base table(s) does not exist?

24. Which of the following can create a view even if the base table(s) does not exist?

25. Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?

25. Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?

26. What among the following are the pre-requisites for creating a table?

26. What among the following are the pre-requisites for creating a table?

27. What is the syntax for creating a table?

27. What is the syntax for creating a table?

28.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?

28.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?

29. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?

29. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?

30.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?

30.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?

31.What will be displayed in the result of this query?

31.What will be displayed in the result of this query?

32. Which of the following commands ensures that no DML operations can be performed on a view?

32. Which of the following commands ensures that no DML operations can be performed on a view?

33. What is true about the NOFORCE option in CREATE VIEW statement?

33. What is true about the NOFORCE option in CREATE VIEW statement?

34. What is true about the OR REPLACE keyword?

34. What is true about the OR REPLACE keyword?

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

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

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

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

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

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

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

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

39. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

39. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (*) FROM t_count;

40. Pick the element which you must specify while creating a table.

40. Pick the element which you must specify while creating a table.

41. What can be said about the statement given above?

41. What can be said about the statement given above?

42. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

42. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (num) FROM t_count;

43. You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?

43. You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?

44. Which of the following is NOT a GROUP BY extensions in SQL?

44. Which of the following is NOT a GROUP BY extensions in SQL?

45. What will happen if the above statement is modified as below?

45. What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;

46. What among the following is true about the DELETE statement?

46. What among the following is true about the DELETE statement?

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

47. 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';

48.当我们对表执行 DELETE 语句时,以下哪项会出现?(选择最恰当的答案)

*48.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer) *

49.What is true about the query given above?

49.What is true about the query given above?

50.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

50.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

51.What is the default sorting order of the results when UNION ALL operator is used?

51.What is the default sorting order of the results when UNION ALL operator is used?

52. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

52. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;

53.What is the maximum level up to which Sub-queries can be nested?

53.What is the maximum level up to which Sub-queries can be nested?

54. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

54. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (DISTINCT num) FROM t_count;

55. Here are few statements about VARIANCE function in SQL.

55. Here are few statements about VARIANCE function in SQL.

  1. The function accepts multiple numeric inputs and returns variance of all the values

ii. 该函数接受一个数值列,并返回包括 NULL 值在内的所有列数值的方差

ii. The function accepts a number column and returns variance of all column values including NULLs

iii. 该函数接受一个数值列,并返回排除 NULL 值在内的所有列数值的方差

iii. The function accepts a number column and returns variance of all column values excluding NULLs

Chose the correct combination from the below options.

Chose the correct combination from the below options.

56. Which clause is used to filter the query output based on aggregated results using a group by function?

56. Which clause is used to filter the query output based on aggregated results using a group by function?

57. A user named "Kevin" wants to access a table which is owned by another user named "Jonathan". Which of the following will work for Kevin?

57. A user named "Kevin" wants to access a table which is owned by another user named "Jonathan". Which of the following will work for Kevin?

58.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

58.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

59. Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

59. Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

60.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

60.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

61. What is true about a schema?

61. What is true about a schema?

62. In which order the values will get inserted with respect to the above INSERT statement?

62. In which order the values will get inserted with respect to the above INSERT statement?

63. What among the following is true about tables?

63. What among the following is true about tables?

65. Which of the below SQL query will display employee names, department, and annual salary?

65. Which of the below SQL query will display employee names, department, and annual salary?

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

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

  • 67. Which of the following SELECT statements lists the highest retail price of all books in the Family category?*

68. Which of the following functions can be used to include NULL values in calculations?

68. Which of the following functions can be used to include NULL values in calculations?

69.Which statements best describes the inference drawn from the questions 34 and 35?

69.Which statements best describes the inference drawn from the questions 34 and 35?

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

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

SELECT length('hi') FROM dual;

Answer:

Answer(1): A. ROUND 函数将根据指定精度 -1 四舍五入 144.23 的值并返回 140。

Answer(1): 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.

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): A. *使用括号将在 INTERSECT 与其他运算符一起使用时明确更改计算顺序。

*Answer(2): A. *Using parenthesis will explicitly change the order of evaluation when INTERSECT is used with other operators.

*答案 (3): A、D. *严格来说,SELECT 是一个 DML 命令,因为它是在表中操作数据的强制性子句之一。

*Answer(3): A, D. *On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

*答案 (4): A. *从表中选择需要的,每个表逗号分隔。

*Answer(4): A.*Select the required from the tables each separated by a comma.

*答案 (5): C. *简单视图和复杂视图是两种视图。简单视图基于仅引用一个表且不包括分组函数、表达式或 GROUP BY 子句的子查询。复杂视图基于从一个或更多表中检索或派生数据的子查询,并且可以包含函数或分组数据。

*Answer(5): C. *Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn’t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

*答案 (6): C. *使用 SET 运算符时,所有组合都应具有相同数量的列。组成复合查询的查询中的对应列必须属于相同的数据类型组。

*Answer(6): C. *All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.

*答案 (7): D. *多行子查询返回多行结果。可与多行子查询搭配使用的运算符包括 IN、ALL、ANY 和 EXISTS。

*Answer:(7) D. *Multiple-row subqueries return more than one row of results.Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

*答案 (8): C. *可以创建索引以加快查询过程。存在索引时,DML 操作总是更慢。Oracle 11g 会自动为 PRIMARY Key 和 UNIQUE 约束创建索引。使用 CREATE INDEX 命令创建显式索引。如果查询条件或排序操作基于创建索引所用的列或表达式,Oracle 11g 可以自动使用索引。

*Answer(8): C. *An index can be created to speed up the query process. DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY KEY and UNIQUE constraints automatically. An explicit index is created with the CREATE INDEX command. An index can be used by Oracle 11g automatically if a query criterion or sort operation is based on a column or an expression used to create the index.

*答案 (9): A. *使用 SELECT 子句是将行插入表的常用技术。可减少为每列手动键入值的工作。

*Answer(9): A. *Using the SELECT clause is the most common technique for inserting rows into tables. It reduces the effort of manually keying in values for each column.

*答案 (10): A. *视图定义可以使用子查询。

*Answer(10): A. *View definition can make use of sub-queries.

*答案 (11): C. *DESCRIBE 用于显示表结构以及表列、其数据类型和空值。

*Answer(11): C. *DESCRIBE is used to show the table structure along with table columns, their data type and nullity

*答案 (12): C. *任何带 NULL 的算术运算都将产生一个 NULL。

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

*答案 (13): C、D. *

*Answer()13: C, D. *

*答案 (14): C. *ANSI SQL: 1999 语法虽然不像传统 Oracle 语法用得多,但它仍然是 Oracle SQL 中可能使用的语法之一。

*Answer(14): C. *The ANSI SQL: 1999 syntax though not used as much as the traditional Oracle syntax, it still is one of the syntaxes that may be used in Oracle SQL

*答案 (15): B. *相关子查询引用外部查询中的列,并针对外部查询中的每一行执行子查询,而无相关子查询先执行子查询,并将值传递到外部查询。

*Answer(15): B. *Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

*答案 (16): D. *在更新列值时,必须遵守列上的约束。在给定的 UPDATE 语句中,将引发错误,因为 EMPLOYEE_ID 列是 EMPLOYEES 表中的主键,这意味着它不能为 NULL。

*Answer(16): D. *The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.

*答案 (17): D. *WHERE 子句可以省略,相关条件可以容纳在 JOIN.ON 子句中,如给定查询所示。

*Answer(17): D. *The WHERE clause can be omitted and the relevant conditions can be accommodated in the JOIN..ON clause itself as shown in the given query

*答案 (18): A. *在没有每个 JOIN 之间的 ON 子句的情况下,不允许 Table1 JOIN table2 JOIN table3。

*Answer(18): A. *Table1 JOIN table2 JOIN table3 is not allowed without the ON clauses for between each JOIN

*答案 (19): C. *Oracle 忽略表达式右侧操作数中的前导零。

*Answer(19): C. *The leading zeroes in the right operand of expression are ignored by Oracle.

*答案 (20): A、C. *你不能创建一个与 Oracle Server 保留字同名的表。

*Answer(20): A, C. *You cannot create a table with the name same as an Oracle Server reserved word.

*解答 (21):C。*BETWEEN 运算符可以在子查询中使用,但不能与子查询一起使用。

*Answer(21): C. *The BETWEEN operator can be used within a sub-query but not with a sub-query.

Answer(22): D. 函数可以执行计算、进行大小写转换和类型转换。

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

*解答 (23):A、C。*联接可以是内部联接,其中仅返回在所有表中都有匹配记录的记录,也可以是外部联接,其中无论联接中是否存在匹配记录都可以返回记录。当需要在结果中包含记录,而联接表中没有相应记录时,会创建外部联接。这些记录与 NULL 记录进行匹配,以便将它们包含在输出中。

*Answer(23): A, C. *A join can be an inner join,in which the only records returned have a matching record in all tables,or an outer join, in which records can be returned regardless of whether there’s a matching record in the join.An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they’re included in the output.

*解答 (24):B。*如果您在 CREATE 子句中包含 FORCE 关键字,Oracle 11g 会创建视图,而不考虑缺少任何引用表的情况。NOFORCE 是 CREATE VIEW 命令的默认模式,这意味着所有表和列必须有效,否则视图将不会创建。

*Answer(24): B. *Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn’t created.

解答 (25):D。

*Answer(25): D. *

*解答 (26):A、B。*用户必须拥有 CREATE TABLE 权限,并且必须有足够的空间将初始范围分配给表段。

*Answer(26): A, B. *A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.

解答 (27):A。

*Answer(27): A. *

*解答 (28):C。*FULL OUTER JOIN 返回来自两个表的未匹配行。完整外部联接包含来自两个表的所有记录,即使在另一张表中找不到相应记录也是如此。

*Answer(28): C. *The FULL OUTER JOIN returns the non-matched rows from both the tables. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

*解答 (29):B。*在 SQL 中,连接运算符由两个竖线 (||) 表示。

*Answer(29): B.*In SQL, concatenation operator is represented by two vertical bars (||).

*解答 (30):C。*括号可用于对特定查询进行分组以显式更改优先级。在执行期间,括号优先于其他 SET 运算符。

*Answer(30): C. *Parenthesis can be used to group the specific queries in order to change the precedence explicitly. Parentheses are preferred over other SET operators during execution.

*解答 (31):A。*UNION 返回两个查询的组合行,对其进行排序并删除重复项。

*Answer(31): A. *UNION Returns the combined rows from two queries, sorting them and removing duplicates.

*解答 (32):C。*WITH READ ONLY 选项防止对视图执行任何 DML 操作。当确保用户只能查询数据,而不进行任何更改时,通常会使用此选项。

*Answer(32): C. *The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it’s important that users can only query data, not make any changes to it.

*解答 (33):B、C。*NOFORCE 是 CREATE VIEW 命令的默认模式,这意味着所有表和列必须有效,否则视图将不会创建。

*Answer(33): B, C. *NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn’t created.

*解答 (34):B。*OR REPLACE 选项通知 Oracle 11g 可能已经存在同名视图;如果存在,则视图的先前版本应替换为新命令中定义的版本。

*Answer(34): B. *The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view’s previous version should be replaced with the one defined in the new command.

*解答 (35):A。*基本上有两种类型的函数 - 单行和多行函数。

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

*解答 (36):B。*字符、日期、转换、常规、数字是单行函数的类型。

*Answer(36): B. * Character, Date, Conversion, General, Number are the types of Single row functions.

*解答 (37):B。*多行函数始终针对一组行工作,并为每组行返回一个值。

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

*解答 (38):C。*组函数与多行函数和聚合函数相同。

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

Answer(39): A. *The COUNT( ) 统计行数,包括重复项和 NULL。使用 DISTINCT 和 ALL 关键字来限制重复项和 NULL 值。

Answer(39): A. *The COUNT() counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.

*答案(40):D。*表至少必须具有一个列、其数据类型规范和精度(如果需要)。

*Answer(40): D. *A table must have atleasr one column, its data type specification, and precision (if required).

*答案(41):C。*指定别名是提高代码和视图查询可读性的好习惯。

*Answer(41): C. *Specifying alias name is good practice to improve the readability of the code and the view queries.

*答案(42):C。*COUNT(column)会忽略 NULL 值,但会计算重复项。

*Answer(42): C. *COUNT (column) ignores the NULL values but counts the duplicates.

*答案(43):C。*NATURAL JOIN 子句会隐式匹配所有同名列。要添加其他条件,可以使用 WHERE 子句。

*Answer(43): C. *The NATURAL JOIN clause implicitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.

*答案(44):A。*GROUPING SETS 操作可用于通过一次查询执行多个 GROUP BY 聚合。

*Answer(44): A. *GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.

*答案(45):B。*列别名的顺序并不重要,因为它们不携带任何行为属性。

*Answer(45): B. * The sequence of the column alias not matters much as they don’t carry any behavioral attribute.

*答案(46):B。*WHERE 子句谓词在 DELETE 语句中是可选的。如果省略 WHERE 子句,表中的所有行都将被删除。

*Answer(46): B. *The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.

*答案(47):B。*假设 employees 表中的姓氏是正确的大写形式,则条件 WHERE last_name = 'smith' 将不会得到满足,因此不会显示任何结果。

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

*答案(48):C。*作为活动事务或新事务的一部分,表中的行将被删除。

*Answer(48): C. *As a part of the active or a new transaction, the rows in the table will be deleted.

*答案(49):D。*复合查询是由使用不同表的多个查询组成的查询。

*Answer(49): D. *A compound query is one query made up of several queries using different tables.

答案(50):D。

*Answer(50): D. *

*答案(51):B。*默认情况下,复合查询会按从左到右升序的方式对所有列中的行进行排序。唯一例外是 UNION ALL,其中不会对行进行排序。只有在复合查询的末尾才允许使用 ORDER BY 子句。

*Answer(51): B. *A compound query will by default return rows sorted across all the columns,from left to right in ascending order.The only exception is UNION ALL, where the rows will not be sorted. The only place where an ORDER BY clause is permitted is at the end of the compound query.

*答案(52):C。*COUNT(ALL column) 会忽略 NULL 值,但会计算重复项。

*Answer(52): C. *COUNT(ALL column) ignores the NULL values but counts the duplicates.

答案(53):A。

*Answer(53): A. *

*答案(54):B。*COUNT (DISTINCT column) 计算非空值的个数。

*Answer(54): B. *COUNT (DISTINCT column) counts the distinct not null values.

*答案(55):C。*VARIANCE 函数接受单一数字参数,作为列名,并考虑 NULL 值返回所有列值的方差。

*Answer(55): C. *The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs.

*答案(56):D。*HAVING 子句用于限制组结果。使用 HAVING 子句可指定要显示的组,从而根据聚合信息进一步限制组。HAVING 子句可以位于 GROUP BY 子句之前,但建议先放置 GROUP BY 子句,因为这样更符合逻辑。在将 HAVING 子句应用于 SELECT 列表中的组之前,先形成组并计算组函数。

*Answer(56): D. *HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

答案(57):B。

*Answer(57): B. *

答案(58):C。' > ALL' 大于子查询返回的最大值。'< ALL' 小于子查询返回的最小值。'< ANY' 小于子查询返回的最大值。'> ANY' 大于子查询返回的最小值。'= ANY' 等于子查询返回的任何值(与 IN 相同)。'[NOT] EXISTS' 行必须与子查询中的值匹配。

*Answer(58): C. *'> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery.

*答案 (59):C. *视图 DEPT_SUM_VU 仍然是一个复杂视图,因为它使用了 DISTINCT 关键字。因此,它不支持 DML 操作。

*Answer(59): C. *The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

*答案 (60):C. *Oracle 不隐式地转换数据类型。

*Answer(60): C. *Oracle does not convert data types implicitly.

*答案 (61):D. *数据库中的用户空间称为模式。模式包含由该用户拥有或访问的对象。每个用户可以拥有自己的单个模式。

*Answer(61): D. *The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.

*答案 (62):B. *如果在 INSERT 子句中提到了这些列,那么 VALUES 关键字应按相同顺序包含这些值

*Answer(62): B. *If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order

*答案 (63):B. *使用 DEFAULT 关键字在定义列时,可以指定其默认值。

*Answer(63): B. *A default value can be specified for a column during the definition using the keyword DEFAULT.

*答案 (65):C. *在 SELECT 语句中使用数字表达式执行基本算术计算。

*Answer(65): C. *Use numeric expressions in SELECT statement to perform basic arithmetic calculations.

Answer(66): A. SUBSTR(string, x, y) 函数接受三个参数,并返回一个字符串,该字符串由从源字符串提取的指定数量的字符组成,从指定开始位置 (x) 开始。当位置为正时,该函数从 string 的开头开始计数以找到第一个字符。当位置为负时,该函数从 string 的末尾向前计数。

Answer(66): 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.

*答案 (67):A. *由于 category FAMILY 必须在分组之前进行限制,因此必须使用 WHERE 子句(而不是 HAVING 子句)对表行进行筛选。

*Answer(67): A. *Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.

*答案 (68):B. *NVL 是一个通用函数,用于向 NULL 值提供备用值。它确实可以在使用 AVG、STDDEV 和 VARIANCE 组函数进行算术计算时有所不同。

*Answer(68): B. *NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

*答案 (69):C. *由于作业代码和部门的组合是唯一的,因此不会获得重复项。

*Answer(69): C. *As the combination of the job codes and departments is unique, there are no duplicates obtained.

*答案 (70):A. *LENGTH 函数仅给出字符串的长度。

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