DB2考试范围整理

1、DB2考试范围整理填空20空，每空2分。选择30题，每题2分。出题一定是给定范围中的原句。填空只在第二章。填空题填中文或填错字母都无分。Unit 2. DB2 SQL Workshop2.1 Instruction（命令）Page17 Notes:A relational database is a collection of tables.（一个关系型数据库是一些表的集合）A table is a set of columns and rows. All data is stored in tables.（一个表是一组列和行，所有的数据存储在表中。）Every table has a nam

2、e.（每个表都有一个名字）In a well-designed database, each table contains data about a specific subject (entity);for instance, one table contains employee data, another table department data, and a third one data about projects.（在一个设计良好的数据库,每个表包含数据关于某个特定主题(实体);例如,一个表包含员工数据,另一个表包含部门数据和第三个表包含项目数据。）The database ma

3、nagement system manages the data.（数据库管理系统管理数据。）If someone requires information about an employee, he/she must talk to the data management system in a language which the system understands: SQL Structured Query Language.（如果有人需要雇员信息,他/她必须用一种语言和数据管理系统交流，这种语言系统能够理解即:SQL -结构化查询语言。）Page19 Notes:DDL is mos

4、tly used by database administrators to define DB2 UDB objects such as tables, views and indices（DDL（数据定义语言）大多数情况下被数据库管理员用来定义DB2 UDB对象，如表、视图和索引）DML is used by all database users to retrieve information or to maintain data（DML（数据操作语言）被用作所有数据库用户检索信息或维护数据）DCL is also used by database administrators to a

5、llow or disallow activities in DB2UDB by granting or revoking privileges（DCL（数据控制语言）也被数据库管理员用来允许或不允许活动,在DB2 UDB它使用GRANT和REVOKE语句对用户或用户组授予或回收数据库对象的权限。）Page20 Notes:Every table has a name.（每个表都有一个名字。）Every column has a name. （每一列都有一个名字。）Column names must be unique within a table. （列名必须在一个表中是唯一的。）A tab

6、le consists of a set of rows and columns. （一个表包含一组行和列。）Rows are normally stored unordered. （行存储通常是无序的。）For each row, a column has one entry (field).（ 对于每一行,一列有一个条目(又称字段)。）All values of a column have the same data type.（一个列中的所有值具有相同的数据类型。）Page21 Notes:Every table must have a unique name. （每个表必须有一个唯一的

7、名称。）Every table name consists of at least two parts referred to as simple table name and its qualifier (tables used in a distributed processing environment may have three part names).（每一个表的名字包括至少两个部分称为简单的表名和其限定词(表用于分布式处理环境可能有三个部件名称)。）The qualifier precedes the simple table name and is separated from

8、 it by a period.（这个限定符前面简单的表名和它分开一段。）The simple table name must be unique within a qualifier.（ 简单的表名必须在一个限定符中是唯一的。）Page22 Notes:DECIMAL(m,n)Decimal number with max.31 digits（十进制数最大位数为31位）m = total number of digits（m =数字的总位数）n = number of decimal digits（n =数字的小数位数）Page23 Notes:Alphanumeric data may c

9、ontain any character or printable special character.（字符型数据可以包含任何字符或打印的特殊字符。）Page24 Notes:USA = US Standard（美国标准）EUR = European Standard（欧洲标准）ISO = International Standards Organization (Default)（国际标准，默认的）JIS = Japanese Industrial Standard（日本工业标准）Page26 Notes:For each column, at a minimum, the followi

10、ng must be defined: （对于每一列,至少下列的必须定义）Column name（列名）Datatype (and length) （数据类型(包括长度)）NULL characteristic（空特点）Page27 Notes:When a table is created, if a null characteristic is not provided the column is marked as "nullable".（当一个表被创建,如果没有提供一个空特点给这一列，则列标记为“空值”。）A column that is specified as

11、NOT NULL must always contain a value for every row in the table.（一列指定为非空时，表中的每一行必须给一个值。）NOT NULL WITH DEFAULT columns are automatically supplied a value when the inserting user or application does not supply its value.（对于默认非空的列，当用户或应用程序没有提供它的值时系统会自动提供一个值插入。）2.2 Simple SQL Queries（简单的SQL查询）Page31 Not

12、es:Each SELECT statement must have a SELECT and a FROM clause. The other clauses are optional.（每个SELECT语句必须有一个SELECT字句和一个FROM字句。其他的字句是可选的。）Page32 Notes:The output of every SELECT statement is called the result table . （ 每一个SELECT语句的输出叫做结果表。）Page33 Notes:The above statement displays all columns of th

13、e DEPARTMENT table but limits the result table to the first x rows specified in the FETCH FIRST clause.（上面的语句显示部门表的所有列，但在FETCH FIRST子句中限制结果表中指定了前x行。）The FETCH FIRST clause, when used with an ORDER BY clause is useful in producing 'top 10' type reports. More on ORDER BY coming up.（FETCH FIRST

14、子句,与ORDER BY子句一起使用时被用作产生前10名的类型的报告。ORDER BY子句可以接多个列名。）Page34 Notes:Items in the select list must be separated by commas.（选择列表中的项目必须使用逗号将其分隔开。）Page35 Notes:Resulting rows are sequenced according to columns listed in the ORDER BY clause.（行排序序结果是根据在ORDER BY子句的列。）The keyword ASC means sort in ascending

15、sequence. This is the default sort sequence and the keyword ASC may be omitted.（关键字ASC意味着升序。这是默认的排序顺序并且关键字ASC可以省略。）DESC means sort in descending sequence.（关键字DESC意味着降序。）Page36 Notes:If multiple columns are specified in the ORDER BY clause, their left to right sequence within the ORDER BY determines

16、their sorting priority.（如果ORDER BY子句中指定了多个列,他们在ORDER BY中从左到右顺序确定了它们的排序优先级。）ASC or DESC may be specified for each column.（ASC或DESC可能指定为每一列。）A selected column can be identified by the column name or by an integer,referencing the columns position within the selection list.（一个选定的列可以被列名或者一个整数,引用列在选择列表中的位

17、置。）Page37 Notes:In queries with a SELECT,FROM, WHERE and ORDER BY clause the ORDER BY must be coded as the last clause in the SELECT statement. （在SELECT,FROM, WHERE和 ORDER BY都出现子句中，ORDER BY子句的顺序必须为SELECT语句的最后一个子句。）Page38 Notes:DISTINCT ensures that the result contains unique rows. DISTINCT removes d

18、uplicate rows.（DISTINCT关键字确保结果包含唯一的行。DISTINCT关键字的作用是删除重复的行。）The keyword DISTINCT must immediately follow the word SELECT. It is effective at the row level.（关键字DISTINCT必须紧跟SELECT关键字之后。它是有效性是行级。）Page39 Notes:DISTINCT eliminates all rows that contain duplicate data in the set of columns specified in th

19、e SELECT clause.（DISTINCT消除一组在SELECT子句指定的列中重复数据的所有行。）Page40 Notes:A WHERE clause can have one or more predicates, tests, or conditions.（WHERE子句可以有一个或多个谓词,测试,或条件。）A predicate in the WHERE clause specifies a condition that is true, false, or unknown for a given row or group of rows. The values specifi

20、ed in a predicate must be compatible with the data type of the column or expression to which it is compared.（在WHERE子句中的一个谓词，对于一个给定的行或一组行指定了一个条件判断是真的,假的,或未知的。谓词中指定的值必须兼容数据类型的列或表达式，它是需要比较的。）For example, if you compare a column which is defined as alphanumeric, the value to which it is to be compared m

21、ust be enclosed in single quotes.（例如,如果你比较被定义为字符型的列,它的值必须括在单引号内才能比较。）The comparison of alphanumeric columns is case sensitive（字符型的列的比较是大小写敏感的。）The SELECT list need not include the columns used in the WHERE clause.（SELECT列表中不需要包含WHERE子句中使用的列。）Page41 Notes:For numeric columns, for example, INTEGER, SM

22、ALLINT or DECIMAL, the values to be compared must not be enclosed in single quotes.（对于数字列,例如,整数,短整型或十进制值相比较,不得括在单引号。）Page42 Notes:The symbols between the column named SALARY and the value 20000 are interpreted to mean the words to the right of the predicate.（列名为SALARY和值20000之间的符号的解释在谓词右边。）Within man

23、y query editors, two hyphens side by side (no space between them) represents a comment marker. Text to the right of the comment marker is ignored.（在许多查询编辑器,两个并排的连字符 (他们之间没有空格)代表一个注释标记。正确的文本注释标记将被忽略。）Page43 Notes:When predicates are combined via AND, for a row to qualify, both predicates must be true

24、. For predicates combined via OR, rows qualify if at least one predicate is true.（当谓词通过AND组合形成复合条件，则两个谓词的条件必须为真。当谓词通过OR组合形成复合条件，者行资格则至少一个谓词为真。）Page44 Notes:When AND isused between predicates, for a row to qualify, both conditions must be met.（当用AND谓词连接时,两个条件必须都得到满足。）In the above example, only rows f

25、or analysts with an education level of 16 are displayed.（在上面的示例中,只有分析师和教育水平为16的行显示。）Page46 Notes:If you combine multiple conditions by AND and OR, AND takes precedence over OR.（如果你通过AND和OR组合多个条件, AND比OR优先级高。）Page47 Notes:If the order of evaluation is not determined by parentheses, AND is applied bef

26、ore OR.（如果运算顺序不取决于括号,那么AND比OR先运算。）Page48 Notes:The IN predicate compares a value with a set of values.The values may be numeric, or alphanumeric, a date, or a time.An alphanumeric value, a date, or a time must be enclosed within single quotes.（IN谓词用于比较一个值与一组值。值可能是数字或字符,日期,或时间。字符值,日期,或时间必须用单引号括起来。）Ex

27、ample:（例子）SELECT * FROM DEPARTMENT WHERE DEPTNO IN ('B01', 'C01', 'D01')Page49 Notes:The clauseWHERE EDLEVEL BETWEEN 12 AND 15is equivalent toWHERE EDLEVEL >= 12 AND EDLEVEL <= 15上面的WHERE字句和下面的WHERE字句等价Page51 Notes:WHERE LASTNAME LIKE G%The above SELECT searches for nam

28、es beginning with the letter 'G'.（上述SELECT子句搜索开头字母“G”的名字。）Page53 Notes:I need a listing of all employee last names whose second character is the letter C（ 我需要一个清单的所有员工姓名的第二个字是字母“C”）WHERE LASTNAME LIKE _G%2.3 Retrieving Data from Multiple Tables（从多个表中检索数据）Page58 Notes:Correlation names are de

29、fined in theFROM clause of any query.（相关的名字定义在FROM子句的查询。）Page60 Notes:When the JOIN keyword is used in the FROM clause, the join predicates must be specified in an ON clause.（当JOIN关键字用于FROM子句时,连接条件必须被指定在ON子句中。）Row conditions (local predicates) must be written in a WHERE clause which must follow the

30、ON clause.（行条件(局部条件)必须写在一个WHERE子句,必须跟在ON子句之后。）Page62 Notes:Notes that we could also have used the second type of syntax.（指出,我们也可以使用第二种类型的语法）SELECT DEPTNAME,MGRNO,LASTNAMEFROM DEPARTMENT,JOIN EMPLOYEE ON MGRNO=EMPNOWHERE DEPTNO=D21Page67 Notes:Table qualifiers (correlation names) are required because

31、 a table is referenced twice within the FROM clause of the query.（表修饰符(关联名称，别名)是必需的,因为一个表在查询的FROM子句中被引用两次。）Page69 Notes:Once again,table qualifiers (correlation names) are required because a table is referenced twice within the FROM clause of the query. （再次说明，表修饰符(关联名称)是必需的,因为一个表在查询的FROM子句中被引用两次。）2.

32、4 Scalar Functions and Arithmetic（标量函数和数学运算）Page72 Notes:Because the SELECT clause is executed after the WHERE clause the new column name may only be used in the ORDER BY clause.（因为SELECT子句是在WHERE子句执行后才新列名称只执行，新列名仅能能用在ORDER BY子句中。）Page73 Notes:COALESCE allows multiple parameters:（COALESCE允许多个参数:）COA

33、LESCE(expression_1, expression_2, expression_n, 'constant').（COALESCE (表达式1,表达式2,表达式n,“常数”)。）The COALESCE function takes a variable number of parameters and returns the value of the first parameter that is not null. The result is null if the values of all parameters are null.（COALESCE函数接受参数的

34、数目是可变的并且第一个参数的返回值不能为空。当所有参数的值是空时结果是空。）Page76 Notes:The DECIMAL scalar function returns a decimal representation of a numeric value or a decimal representation of a numeric looking character value.（DECIMAL标量函数返回一个十进制数表示的一个数值或十进制表示的数字看起来字符值。）Page77 Notes:If you need a rounded result, add 5 to the firs

35、t decimal position to be truncated and apply the DECIMAL function to this expression.（如果你需要一个大约的结果,添加5到第一个十进制的位置被截断,并应用这个表达式的十进制函数。）Page78 Notes:Arithmetic expressions can be used in the SELECT list and in the WHERE clause.（算术表达式可用于SELECT列表和在WHERE子句中。）Page85 Notes:The resulting columns normally do n

36、ot have column headings. The AS clause is used to name the columns.（结果列通常没有列标题。AS子句用于命名这个列。）Page88 Notes:SUBSTR(COURSINF,6,15) returns a substring of length 15 beginning at position 6,that is, the course title in the above example（在上面的例子中，SUBSTR(COURSINF,6、15)返回的开始位置6子字符串长度15的字符串，即课程标题）Page89 Notes:

37、SUBSTR(PROJNO,1,2) extracts the first two characters. The function can be interpreted to read: Within the PROJNO column, start with the first character and return two characters.（SUBSTR(PROJNO,1,2)提取前两个字符。该函数可以被解释为阅读:在PROJNO列,从第一个字符开始并返回两个字符。）2.5 Column Functions and Grouping（列函数和分组）Page92 Notes:The

38、re are two types of SQL functions:（有两种类型的SQL函数:）Column functions (also known as summary functions)（列函数(也称为汇总函数)）Scalar functions（标量函数）The format for writing both types of functions is the same, the name of the function followed by parentheses. Inside the parentheses is the argument to which the func

39、tion is applied.（编写这两种类型的函数的格式是相同的,函数的名字后跟括号。括号内提供函数的参数。）Column functions produce a summary row for a set of rows.（列函数对一组行产生一个汇总。）Scalar functions produce a value for each row within the set.（标量函数在设置为每一行产生一个值。）Page95 Notes:Except for COUNT(*)and COUNT_BIG(*), expressions (for example, SALARY + COMM)

40、 may be used as an argument for column functions.（除了COUNT(*)和COUNT_BIG(*),表达式(例如, SALARY + COMM)可能被使用作为列函数的一个参数。）Page96 Notes:If a null is part of row level arithmetic the result is null.（如果空值作为行级运算的一部分，结果是空）Nulls are ignored in column level arithmetic.（列级运算，空值忽略。）Consider using the COALESCE/VALUE s

41、calar function to replace nulls with known values.（考虑使用COALESCE/VALUE标量函数来用已知值代替空值。）Page97 Notes:The result is derived from the set of rows for which the WHERE clause predicate is true.（结果由一组行组成,这些行WHERE子句谓词为真。）Page98 Notes:With GROUP BY, a column function results in a single value for each group. A

42、 group is made up of all the candidate rows (rows satisfying the WHERE clause)having the same value for all the columns in the GROUP BY (in this example,having the same value for WORKDEPT). （使用GROUP BY子句，列函数返回每一个组一个值。一个组是由所有候选行(行满足WHERE子句) GROUP BY子句指定列值相同的分组 (在这个例子中, WORKDEPT有相同的值)）A name defined b

43、y an AS clause cannot be used in GROUP BY.（一个在AS子句中定义的不能用于GROUP BY。）GROUP BY does not guarantee asorted result table . If an ordered result table is required an appropriate ORDER BY clause must be specified.（GROUP BY不保证排序结果表。如果一个命令需要一个排序的结果表必须在ORDER BY子句中指定。）Page102 Notes:So far we built groups. Wit

44、h HAVING, we now can select groups satisfying certain conditions. Conditions for groups must be specified in the having clause.（到目前为止我们建立了分组。通过HAVING子句,我们现在可以选择组满足一定条件。组中的条件中必须由having子句指定。）HAVING may specify any column function on any column in a table being queried.This column need not be in the SE

45、LECT list.（HAVING子句能指定任何列函数在表查询的任意一列。这列不需要出现在SELECT列表中。）HAVING is used to qualify or disqualify a group from further processing.（HAVING子句是用来限制或取消一组进行更多处理。）Page107 Notes:Through the FROM clause DB2 UDB locates the table.（通过FROM 子句从DB2 UDB定位表。）The WHERE clause extracts into the conceptual intermediate

46、 table rows that will be processed further.（WHERE子句提取到概念中间表行,将做进一步的处理。）The GROUP BY clause sorts the rows in the intermediate table into subsets.（GROUP BY子句排序在中间表的行成子集。）2.7 Using Subqueries（使用子查询）Page124 Notes:As a consequence, the second SELECT is executed by the outer(main) query. The inner SELECT

47、 is referred to as the subquery .（因此,第二个SELECT是执行外(主要)查询。内部的SELECT被称为子查询。）Page131 Notes:Subqueries（子查询）·Must be on the right side of search condition when used in WHERE cause or in HAVING cause. （当用在WHERE子句和HAVING子句中时必须写在搜索条件的右边）·Must be enclosed in parentheses（参数必须用括号括起来）·Can return

48、single or multiple values（可以返回一个或多个值）·Number of values subquery can return must be compatible with operator in outer SELECT（子查询返回的值的数目必须与主查询兼容）·The Subquery must return the same number of items as in the list to which is compared（当用作比较是子查询必须返回相同的项目个数）2.8 Maintaining Data（维护数据）Page135 Notes

49、:Page140 Notes:DELETE FROM TESTEMPWHERE EMPNO = 000111Page141 Notes:DROP TABLE TESTEMPVIEW PERSINFO is automatically dropped（视图PERSINFO自动删除）Unit 1. Overview of DB2 9 on Linux, UNIX and Windows（Linux、UNIX 和 Windows下DB2版本9概述）Page3 Notes:DB2 Workgroup Server Edition (WSE) provides full DB2 database ser

50、ver capacity for your departmental LANs running Windows NT, Windows 2000, Windows XP, Windows .NET, AIX, HP-UX, Linux, and Solaris operating environments.（DB2 Workgroup服务器版(WSE)提供完整的DB2数据库服务器容量为你的部门局域网运行Windows NT、Windows 2000、Windows XP,Windows.NET,AIX, HP-UX、Linux和Solaris操作环境。）Page4 Notes:DB2 Ente

51、rprise Server Edition (ESE) meets the database server needs of midsize to large businesses.（DB2 Enterprise Server Edition(ESE)满足数据库服务器需要的中型到大型企业。）Page5 Notes:Accessing DB2Command Line Processor（命令行处理器）Administration Tools（管理工具）Applications（应用）Command Line Processor (CLP) CLP is used to dy namically

52、execute SQL requests, XQuery requests, DB2 commands, or all of them.（命令行处理器(CLP)CLP用于动态地执行SQL请求,XQuery请求,DB2命令,或者他们所有的。）Page9 Notes:The DB2 Runtime Client (RTCL) is a lightweight client that provides the functionality required for an application to access DB2 servers and DB2 Connect servers.（DB2运行时客

53、户端(RTCL)是一种轻量级客户机提供了所需的功能应用程序访问DB2服务器和DB2 Connect服务器。）Unit 3. Command Line Processor (CLP) and GUI usage（命令行处理器和图形用户界面的用法）3.1 Command Line Processor (CLP) （命令行处理器）Page145 Notes:Through the Command Line Processor you can issue: （通过命令行处理器可以发出:） SQL statements （SQL语句） XQuery statements (with the prefix

54、 XQUERY) （XQuery语句(前缀XQuery)） DB2 commands （DB2命令） OS system commands（OS系统命令）Page146 Notes:The Online Command Reference is invoked by: （在线命令引用通过以下方式调用:）1. db2 ? List of all DB2 commands （列出所有DB2命令）2. db2 ? command Information about specific commands （命令的相关信息）3. db2 ? SQL nnnn Information about a spe

55、cific SQLCODE generated by database manager. SQLCODE must be four or five digits in length. （信息是关于特定的一个数据库管理器生成的SQLCODE。必须是四个或五个SQLCODE位数长度。）4. db2 ? nnnnn Information about a specific SQLSTATE generated by database manager. SQLSTATE must be five digits in length.（信息是关于特定的一个数据库管理器生成的SQLSTATE。SQLSTAT

56、E应是5位数的长度。）Page147 Notes:Using the CLPNon-interactive mode（非交互模式）db2connect to musicdbdb2“select * from syscat.tables”|more(double quotes may be required)Interactive mode（交互模式）db2db2=> connect to musicdbdb2=> select * from syscat.tables3.2 Getting started with DB2 GUI tools（开始使用DB2图形用户界面的工具）Pa

57、ge153 Notes:Unit 4. The DB2 environment（DB2环境）Page176 Notes:The database manager configuration file is created when an instance of DB2 is created, and affects the system resources allocated to DB2 for an individual instance.（当一个DB2实例被创建时，数据库管理器配置文件同时被创建,并影响系统资源分配给为一个独立的DB2实例。）A database configuratio

58、n file for an individual database is created when the database is created.（每个独立数据库的数据库配置文件，数据库配置文件是在创建数据库时生成的。）Page178 Notes:Each node can have several instances. （每个节点可以有多个实例。）Each instance can manage several databases. However, a given database belongs to only one instance. （每个实例可以管理几个数据库。然而,一个给定的数据库只能属于一个实例。）A database is a collection of tables. （数据库是一组表。）The tables a