Sql Certificate 简明教程

SQL - Using DDL Statements Questions

1.What is the full form of DDL in Oracle DB?

*答案：B. *DDL 是 SQL 中的一种类别，代表数据定义语言。其他 SQL 类型为 DML、DCL 和 TCL。

*Answer: B. *DDL is one of the categories of SQL which stands for Data Definition Language. Other SQL types are DML, DCL, and TCL.

2.DDL statements are used for which of the following Oracle database objects?

*答案：A。*DDL包含用来创建表、存储的子程序和数据库模式中包的CREATE、ALTER和ANALYZE等命令。

*Answer: A. *DDL contains commands like CREATE, ALTER and ANALYZE which are used to CREATE TABLEs, view stored subprograms and packages in a database schema.

3.What is the basic unit of storage in Oracle Database that contains data?

*答案：D。*表是Oracle数据库中数据的基本物理存储单元。

*Answer: D. *Table is the basic unit of physical storage of data in Oracle database.

4.Which of the below options best define a View?

*答案：B。*视图是查询，它表现为一个用来格式化存储在一个或多个表中的数据的窗口。视图不包含任何物理数据，而只包含在运行时创建的查询。

*Answer: B. *View is a query which behaves like a window to format the data contained in one or more tables. Views do not contain any physical data but just a query which are created during runtime.

5. Which of the following are database objects?

*答案：D。*物理存储在数据库模式中的对象是数据库对象。

*Answer: D. *Objects which are physically stored in database schema are database objects.

6. Which of the following database objects generate numeric values?

*答案：D。*序列用来生成以确定值开头的唯一值，并且按指定因子递增。可以创建序列来生成一系列整数。序列生成的值可以存储在任何表中。使用CREATE SEQUENCE命令创建序列。

*Answer: D. *Sequence are used to generate unique values starting with a definite value and incremented by a specified factor. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

7.Which of the following database objects gives an alternative name to an object?

*答案：A。*同义词为数据库对象提供了永久别名。公共同义词对任何数据库用户可用。专用同义词仅对创建它的用户可用。同义词通过使用CREATE SYNONYM命令创建。同义词通过使用DROP SYNONYM命令删除。只有具有DBA权限的用户可以删除公共同义词。

*Answer: A. *A synonym provides a permanent alias for a database object. A public synonym is available to any database user. A private synonym is available only to the user who created it. A synonym is created by using the CREATE SYNONYM command. A synonym is deleted by using the DROP SYNONYM command. Only a user with DBA privileges can drop a public synonym.

8.Which of the following database objects improves the performance of some queries?

*回答：D. *

*Answer: D. *

9. When a table can be created?

*答案：C。*可以创建索引来加速查询过程。当存在索引时，DML操作总是更慢。Oracle 11g自动为PRIMARY KEY和UNIQUE约束创建索引。显式索引通过CREATE INDEX命令创建。如果查询条件或排序操作基于用于创建索引的列或表达式，Oracle 11g可以自动使用索引。

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

10. What is true about a table?

回答：A、C。

*Answer: A, C. *

11. A table named 123_A is created for storing the number of employees in an organization. What is wrong in the name of the table?

*答案：A。*根据对象命名约定，表名必须以字母开头。

*Answer: A. *As per the object naming conventions, table name must start with an alphabet.

12. What is the range of number of letters a table name can have?

*答案：C。*表名不能超过30个字符。

*Answer: C. *A table name cannot exceed more than 30 characters.

13 Which of the following characters can be used to name a table?

*答案：D。*根据 Oracle 中的标准命名约定，对象的名称可以在任何情况下包含字母。第一位必须是字母，而其他部分可以是字母和数字的混合。

*Answer: D. *As per the standard naming convention in Oracle, object’s name can contain alphabets in any case. Mandatorily, first place is for letters while the rest can be mix of letters and digits.

14. Which of the following special characters can be used to name a table?

*答案：B、C、D。*在为表命名时，除 (#、$、_) 以外，不允许使用其他特殊字符。不建议在表名中使用特殊字符。

*Answer: B, C, D. *No other special character, except (#, $, _), are allowed while naming a table. Use of special characters in the table name is discouraged.

15. What is true about the name of a table?

*答案：D。*根据命名空间，表名不能与任何其他模式对象相同。共享相同命名空间的模式对象包括表、视图、序列、私有同义词、存储过程、存储函数、包、物化视图和用户自定义类型。

*Answer: D. *By virtue of namespace, a table name cannot be same as any other schema objects. Schema objects which share the same namespace include tables, views, sequences, private synonyms, stored procedures, stored functions, packages, materialized views, and user-defined types.

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

*答案：A、C。*你不能用与 Oracle Server 保留字相同的名称来创建表。

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

17. You create a table using quoted identifiers ' '. How will you refer this table?

*答案：B。*如果创建的表名称带有引号标识符，则必须使用双引号表示。不建议使用引号标识符。引号标识符区分大小写。

*Answer: B. *If the table is created with the name having a quoted identifier, it must be addressed using double quotes. Using quoted identifiers is not recommended. Quoted identifiers are case-sensitive

18. You create a table named EMPLOYEES. What among the following is possible?

*答案：D。*在 Oracle 中，不带引号的对象名称不区分大小写。

*Answer: D. *Unquoted objects names are not case-senstive in Oracle.

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

*答案：A、B。*用户必须具有 CREATE TABLE 权限，并且必须有足够的空间来将初始范围分配给表段。

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

20. What is the syntax for creating a table?

答案：A。

*Answer: A. *

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

*答案：D。*表必须至少有一列、其数据类型规范以及精度（如果需要）。

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

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

回答：B。

*Answer: B. *

23. What is true about a schema?

答案：D。数据库中的用户空间称为模式。模式包含用户拥有或访问的对象。每个用户只能拥有自己的单个模式。

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

24. What among the following is true about tables?

答案：B。在定义列时可以使用关键字 DEFAULT 为列指定默认值。

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

25. Which of the following can be used with the DEFAULT option while creating a table?

答案：D。列的默认值可以是文字，也可以使用 SQL 函数派生。

*Answer: D. *The default value for a column can either be a literal or a derivative using SQL function.

26. Which of the following command is used to see the structure of a table?

答案：C。DESCRIBE 是一个 SQL*Plus 命令，用于列出表的结构。

*Answer: C. *DESCRIBE is a SQL*Plus command to list the structure of the table.

27.What is the limit of CHECK constraints on a column?

答案：A。Oracle 对列上的检查约束没有限制。

*Answer: A. *Oracle imposes no limit on the check constraints on a column.

28. Which of the following commands will drop table employees? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

回答：B。

*Answer: B. *

29. What is true about a namespace?*

答案：D。命名空间定义了一组对象类型，在其中所有名称必须通过模式和名称唯一标识。不同命名空间中的对象可以共享相同的名称。

*Answer: D. *A namespace defines a group of object types,within which all names must be uniquely identified-by schema and name.Objects in different namespaces can share the same name.

30. Which of the following object types share the same namespace?*

*回答：D. *

*Answer: D. *

31. What among the following is true about a table and an index?*

答案：A。由于索引和约束共用同一个命名空间，所以表和索引可以具有相同的名称。

*Answer: A. *As the index and constraints share the same namespace, a table and an index can have the same name.

32. What is true about creating a table?*

答案：A。为了构建表的结构，每个列都必须具备行为属性，如数据类型和精度。

*Answer: A. *Each column must possess behavioral attributes like data types and precision in order to build the structure of the table.

33. Suppose you create a table as shown below:*

CREATE TABLE employees
(emp_id NUMBER(4),
last_name VARCHAR2 (20)
);

Oracle 将为 LAST_NAME 列分配多少空间？

How much space will Oracle allocate to the LAST_NAME column?

答案：A。

*Answer: A. *

34. What is the range of size that a VARCHAR2 data type can take?*

*الإجابة: ج. *حتى إصدار Oracle 11g الإصدار 2، يمكن لنوع بيانات السلسلة VARCHAR2 أن يحتوي بحد أقصى على 4000 بايت.

*Answer: C. *Until Oracle 11g Release 2, string data type VARCHAR2 can maximum contain 4000 bytes.

35.What is the range of size that a CHAR data type can take?

*الإجابة: ب. *حتى إصدار Oracle 11g الإصدار 2، يمكن لنوع البيانات السلسلة CHAR أن يحتوي بحد أقصى على 2000 بايت.

*Answer: B. *Until Oracle 11g Release 2, string data type CHAR can maximum contain 2000 bytes.

36. What is true about the CHAR data type?*

*الإجابة: ب. *يوفر CHAR تخزينًا ثابت الطول لقيمة بينما يكون VARCHAR2 مرنًا. إذا تم إدراج بيانات بطول أقل من دقة CHAR في عمود CHAR، فسيتم إضافة الطول المتبقي إلى قيمة العمود.

*Answer: B. *CHAR provides a fixed length storage to a value while VARCHAR2 is flexible. If the data of length less than CHAR precision is inserted in a CHAR column, the remaining length will be padded to the column value.

37. Which of the following is a data type for variable length binary data?*

回答：C。

*Answer: C. *

38. What is the precision allowed for the NUMBER data type?*

*الإجابة: د. *حتى إصدار Oracle 11g الإصدار 2، كان للنوع الأساسي للبيانات NUMBER أقصى دقة وهي 38 رقمًا.

*Answer: D. *Until Oracle 11g Release 2, primary data type NUMBER had the maximum precision of 38 digits.

39. What is the scale allowed for the NUMBER data type?*

回答：C。

*Answer: C. *

40. Which of the following are the data types for date and time data?*

*回答：D. *

*Answer: D. *

41. Which of the following data types are for large objects?*

*الإجابة: أ، ب. *أنواع بيانات LOB في SQL هي BLOB وCLOB وBFILE.

*Answer: A, B. *LOB data types in SQL are BLOB, CLOB, and BFILE.

42. What will happen if the inserted value is of a smaller length as defined for a VARCHAR2 data type column?*

*الإجابة: ب. *تحتوي VARCHAR2 على بيانات أحرف ذات طول متغير.

*Answer: B. *VARCHAR2 contains variable length character data.

43. What does NUMBER (8, 2) in oracle mean?*

*الإجابة: أ. *يشير p إلى الدقة، والعدد الإجمالي للأرقام على يسار ويمين موضع العلامة العشرية، بحد أقصى 38 رقمًا؛ تشير s، أو المقياس، إلى عدد المواضع على يمين العلامة العشرية. مثال: يمكن لـ NUMBER (7، 2) تخزين قيمة رقمية تصل إلى 99999.99. إذا لم يتم تحديد الدقة أو المقياس، فإن العمود يكون افتراضيًا بدقة 38 رقمًا.

*Answer: A. *The p indicates precision,the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal.Example: NUMBER(7, 2) can store a numeric value up to 99999.99. If precision or scale isn’t specified, the column defaults to a precision of 38 digits.

44. Which of the following queries will create a table with no rows in it?*

*الإجابة: ج. *يمكن استخدام عملية المسار المباشر CTAS (CREATE TABLE .. AS SELECT ..) لنسخ بنية جدول موجود دون نسخ البيانات.

*Answer: C. *The direct path operation CTAS (CREATE TABLE .. AS SELECT..) can be used to copy the structure of an existing table without copying the data.

45. Which of the following statements would add a column to a table already created?*

الإجابة: جيم. * يسمح أمر ALTER TABLE للمستخدم بإضافة عمود جديد إلى جدول. تطبق القواعد نفسها لإنشاء عمود في جدول جديد على إضافة عمود إلى جدول موجود. يجب تعريف العمود الجديد باسم عمود ونوع بيانات (وعرض، إذا كان ذلك ممكنًا). يمكن أيضًا تعيين قيمة افتراضية. والاختلاف هو أن العمود الجديد يُضاف في نهاية الجدول الموجود ـ سيكون العمود الأخير.

*Answer: C. *The ALTER TABLE command allows a user to add a new column to a table.The same rules for creating a column in a new table apply to adding a column to an existing table.The new column must be defined by a column name and datatype (and width, if applicable).A default value can also be assigned. The difference is that the new column is added at the end of the existing table-it will be the last column.

46. Which of the following statements will modify the data type of an already existing column?*

الإجابة: أليف. * يُستخدم ALTER TABLE..MODIFY لتعديل تعريف العمود في الجدول. التغييرات المسموح بها هي زيادة دقة العمود أو تغيير نوع البيانات ضمن عائلة نوع البيانات أو تغيير القيمة الافتراضية للعمود.

*Answer: A. *The ALTER TABLE..MODIFY is used to modify column definition in a table. The admissible changes are increasing column precision, change datatype within a datatype family, or change the default value of the column.

47. Which of the following statements will remove a column from the table?*

الإجابة: جيم. * يمكن استخدام ALTER TABLE..DROP COLUMN لإسقاط عمود من الجدول.

*Answer: C. *The ALTER TABLE..DROP COLUMN can be used to drop a column from the table.

48. Which of the following will rename the column emp_id to empno?*

الإجابة: أليف. * يمكن استخدام ALTER TABLE..RENAME لإعادة تسمية عمود موجود في الجدول.

*Answer: A. *The ALTER TABLE..RENAME can be used to rename an existing column in teh table.

49. You need to mark the table employees as read only. Which of the following statements will you execute to get the required result?*

الإجابة: باء. * يمكن وضع علامة على الجدول للقراءة فقط لجعله سلبيًا أمام عبارات DML وDDL. تم تقديم ميزة القراءة فقط في Oracle 11g.

*Answer: B. *A table can be marked read only to make it passive against the DML and DDL statements. The read only feature was introduced in Oracle 11g.

50. What among the following is true about DDL statements?*

الإجابة: باء. * يتم الالتزام تلقائيًا بأوامر DDL فقط إذا تم تنفيذها بنجاح دون أخطاء. إذا فشل أمر DDL، لا تزال المعاملة الجارية نشطة في الجلسة ولم يتم الالتزام بها في قاعدة البيانات.

*Answer: B. *DDL commands are auto commit only if they are successfully executed without errors. If DDL command fails, the ongoing transaction is still active in the session and not committed into the database.

51. What happens if there is an active transaction against a table on which a DDL is issued?*

回答：B。

*Answer: B. *

52. Which of the following commands will remove unused columns in an SQL statement?*

الإجابة: جيم. * تُسقط معاملة SET UNUSED الأعمدة غير المستخدمة فقط من جدول وهي أسرع.

*Answer: C. *The SET UNUSED command drops only the un-used columns from a table and is faster

53. What happens when a table which is marked Read Only is attempted for drop?*

الإجابة: جيم. * يؤثر أمر DROP على تعريف قاموس بيانات الجداول التي ليست للقراءة فقط وبالتالي يمكن الإسقاط.

*Answer: C. *The DROP command affects the data dictionary definition of the tables which are not Read Only and hence dropping is possible

Consider the following statement and answer the questions 54 and 55 that follow:

CREATE TABLE departments
(dept_id NUMBER (2),
 dept_name VARCHAR2(14),
 create_date DATE DEFAULT SYSDATE);

54. What will happen if the DEFAULT clause specification is removed from the statement?

回答：C。

*Answer: C. *

55.What is true about the above statement?

答案：A、B、C。

*Answer: A, B, C. *

56. Up to which limit can a BLOB data type column hold values?

*答案：C。*按照 Oracle 11g，BLOB 中容纳的数据最大值为 4GB。

*Answer: C. *As per Oracle 11g, the maximum size of data accomodated in a BLOB can be 4GB.

57.What is the difference between CLOB and BLOB data types? (Choose the most appropriate answer)

*答案：C。*CLOB 是一个字符大对象，用于存储类似于 PDF、文档和文本文件之类的字符文件，而 BLOB 是一个二进制 LOB，用于存储媒体文件。

*Answer: C. *CLOB is a character large object which is used to store character files like PDF, docs and text files while BLOB is a binary LOB used to store media files.

58.What among the following is a ROWID?

*答案：B。*它是一种 base-64 系统，表示其表中行的唯一地址。

*Answer: B. *It is a base-64 system representing the unique address of a row in its table.

59.What is the data type used for storing Binary data stored in an external file (up to 4 GB)?

*答案：D。*BFILE 是一种外部 LOB 类型，用于引用外部媒体文件。内部 LOB 类型是 BLOB 和 CLOB，用于二进制大文件和存储在数据库中的字符大文件。

*Answer: D. *BFILE is an external LOB type which is used to refer external media files. Internal LOB types are BLOB and CLOB which are used for binary large files and character large files stored in the database.

60. What is true about a table created with a sub-query?

*答案：C。*CTAS 方法用于创建表，不会复制 LONG 列。

*Answer: C. *The CTAS method to create a table doesn’t copies the LONG column.

61. Which of the following data types cannot be used with a GROUP BY and an ORDER BY clause?

*答案：D。*LONG 数据类型不能用于 GROUP BY 和 ORDER BY 子句。

*Answer: D. *LONG data types cannot be used in GROUP BY and ORDER BY clause.

62. How many LONG columns can a table contain?

*答案：D。*一张表最多可以包含一列 LONG 类型。

*Answer: D. *A table can contain maximum one column of LONG type.

63.Which of the following data types cannot be constrained in SQL?

*答案：B。*LONG 类型列上无法创建约束。

*Answer: B. *Constraints cannot be created on LONG type columns.

64. Which of the following data types can you use if you want a date with fractional seconds?

*答案：C。*TIMESTAMP 数据类型提供日期值的额外精确信息。它提供分数秒和时区信息。

*Answer: C. *The TIMESTAMP data type provides additional precised information of date values. It provides fractional seconds and time zone information.

65. You need to store an interval of days, hours, minutes and seconds in a column. Which of the data type would help?

回答：C。

*Answer: C. *

66.You need to find how many employees were hired in June, 2011 and June, 2012. Which of the following data types will help?

*回答：D. *

*Answer: D. *

67. What is true about constraints?

*回答：B。*约束是应用于添加到表中的数据的规则。它代表业务规则、策略或过程。违反约束的数据不会添加到表中。约束可以在创建表时作为 CREATE TABLE 命令的一部分包括在内，或者通过 ALTER TABLE 命令将其添加到现有表中。基于复合列（多列）的约束必须使用表级别方法创建。

*Answer: B. *A constraint is a rule applied to data being added to a table. It represents business rules, policies, or procedures.Data violating the constraint isn’t added to the table.A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command. A constraint based on composite columns (more than one column) must be created by using the table-level approach.

68. How are constraints helpful?

*回答：C。*约束是应用于添加到表的规则。它表示业务规则、策略或程序。违反约束的数据不会添加到表中。

*Answer: C. *A constraint is a rule applied to data being added to a table.It represents business rules, policies, or procedures.Data violating the constraint isn’t added to the table.

69.A RAW data type column can store variable-length binary strings up to what value?

回答：C。

*Answer: C. *

70. Which of the following are valid constraints in Oracle?

*回答：C，D。*NOT NULL 约束只能与列级方法一起使用。PRIMARY KEY 约束不允许在指定列中重复或出现 NULL 值。一个表中只允许一个 PRIMARY KEY 约束。FOREIGN KEY 约束要求列项与表中的引用列项匹配，或者为 NULL。UNIQUE 约束类似于 PRIMARY KEY 约束，但允许在指定列中存储 NULL 值。CHECK 约束确保在将数据添加到表中之前，该数据符合给定的条件。

*Answer: C, D. *A NOT NULL constraint can be created only with the column-level approach. A PRIMARY KEY constraint doesn’t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table. A FOREIGN KEY constraint requires that the column entry match a referenced column entry in the table or be NULL. A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column. A CHECK constraint ensures that data meets a given condition before it’s added to the table.

71. Which of the below DML operations consider constraints on a column?

*回答：A，C，D。*所有 DML 操作都遵循表的列上的约束。

*Answer: A, C, D. *All the DML operations obey constraints on the columns of the table.

72. When can a constraint be created?

*回答：C。*可以在创建表期间将约束包括作为 CREATE TABLE 命令的一部分，或使用 ALTER TABLE 命令将约束添加到现有表中。

*Answer: C. * A constraint can be included during table creation as part of the CREATE TABLE command or added to an existing table with the ALTER TABLE command.

73 Where are constraints stored?

回答：C。

*Answer: C. *

74. You create a constraint but do not name it. What will be the default name given to the constraint?

*回答：A。*默认情况下，Oracle 为约束分配一个通用名称 SYS_Cn，其中 n 是一个整数，用于保持约束名称的唯一性。

*Answer: A. *By default, Oracle gives a generic name to the constraints SYS_Cn, where the n is an integer to keep the name of a constraint unique.

75. What is the functional difference between a column-level constraint and a table-level constraint?

*回答：C。*从功能上说，表级约束和列级约束的工作方式相似。复合约束只能在表级别定义。

*Answer: C. *Functionally, the table level constraints and column level constraints work similar. Composite constraints can be defined at table level only.

76. What is true about column-level constraints?

*答案：C。*列级别约束是随着列规范而定义的。

*Answer: C. *Column level constraints are defined along with the column specification.

77. What is true about NOT NULL constraints in SQL?

*答案：B。*仅能使用列级别的方法创建 NOT NULL 约束。

*Answer: B. *A NOT NULL constraint can be created only with the column-level approach.

Consider the following statement and answer the questions 78 and 79 that follow:

CREATE TABLE employees (
emp_id NUMBER (6)  CONSTRAINT emp_emp_id_PK PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
hire_date DATE
);

78.Which type of constraint is created in the above statement?

*答案：A。*列级别约束是随着列定义而创建的。

*Answer: A. *A column level constraint is created along with the column definition.

79. What modification can be made to the above statement to give it a table level constraint?

回答：B。

*Answer: B. *

80. What is true about PRIMARY KEY constraint?

*答案：A。*主键约束不允许指定列中出现重复或 NULL 值。在一个表中仅允许有一个主键约束。

*Answer: A. *A PRIMARY KEY constraint doesn’t allow duplicate or NULL values in the designated column. Only one PRIMARY KEY constraint is allowed in a table.

81. What among the following is true regarding a UNIQUE KEY constraint?

*答案：B。*唯一约束与主键约束类似，但唯一约束允许在指定列中存储 NULL 值。

*Answer: B. *A UNIQUE constraint is similar to a PRIMARY KEY constraint, except it allows storing NULL values in the specified column.

Consider the following statement and answer the questions 82 and 83 that follow:

CREATE TABLE employees (
emp_id NUMBER (6)
first_name VARCHAR2(20),
last_name VARCHAR2(20),
job VARCHAR2(20),
hire_date DATE
CONSTRAINT emp_job_UK UNIQUE (job));

82. Which of the below statements interpret the above CREATE TABLE script?

*答案：A、C。*在 JOB 列上的唯一约束将限制重复值，但允许出现空值。

*Answer: A, C. *A UNIQUE constraint on the JOB column will restrict duplicate value but allows nulls.

83. If the constraint emp_job_UK is modified as emp_job_PK PRIMARY KEY (job), what will be outcome?

答案：A。

*Answer: A. *

84. What is true about the UNIQUE key constraint?

*答案：A。*当对表施加唯一约束时，Oracle 会在内部在列上创建唯一键索引，以限制值的重复。

*Answer: A. *When a unique constraint is imposed on a table, Oracle internally creates a unique key index on the column to restrict the duplication of values.

85. Which of the following is true about indexes?

回答：C、D。

*Answer: C, D. *

86.Which of the following CREATE TABLE statements is valid?

*答案：A、C、D。*所有 CREATE TABLE 脚本都是有效的。

*Answer: A, C, D. *All the CREATE TABLE scripts are valid.

87. How many PRIMARY KEY constraints can a table have?

*答案：D。*一个表只能有一个主键。

*Answer: D. *A table can have one and only one primary key.

88. You want to put a CHECK constraint on the EMP_ID such that it should be equal to the current value of a Sequence through which it is getting its values. Which of the following statements will help you achieve this?

*答案：D。*你无法在 CHECK 约束中使用伪列 CURRVAL、NEXTVAL、LEVEL 和 ROWNUM。

*Answer: D. *You cannot use CURRVAL, NEXTVAL, LEVEL and ROWNUM pseudo columns in the CHECK constraint

89. Which of the following commands will help in converting the foreign key values to NULL?

回答：B。

*Answer: B. *

90. You need to add a constraint to the EMPLOYEES table which restricts the addition of those employees who have salaries less than 10000. Which of the following commands will give you the required results?

答案：A。

*Answer: A. *

91. You need to add a constraint to the EMPLOYEES table which imposes a restriction that the HIRE_DATE for all the employees should be equal to SYSDATE-7. Which of the following statements will give you the required results?

*答案：D。*你无法在 CHECK 约束中使用 SYSDATE、UID、USER 和 USERENV 函数。

*Answer: D. *You cannot use SYSDATE, UID, USER and USERENV functions in the CHECK constraint.

Consider the following query and answer the questions 92 to 94 that follow:

CREATE TABLE EMPLOYEES
(emp_id NUMBER (2),
first_name VARCHAR(20),
last_name VARCHAR(20),
dept_id NUMBER (10),
hire_date DATE DEFAULT SYSDATE
CONSTRAINT emp_emp_id_PK PRIMARY KEY (emp_id, hire_date)
CONSTRAINT emp_dept_FK FOREIGN KEY (dept_id)
REFERENCES departments (dept_id)
);

92. Which of the below statements interpret the CREATE TABLE script?

*答案：C。*我们在定义外键约束以实现参照完整性时使用关键字 FOREIGN KEY 和 REFERENCES。

*Answer: C. *The keywords FOREIGN KEY and REFERENCES are used when we define a FOREIGN KEY constraint for referential integrity.

93. You need to delete all the dependent rows in DEPARTMENTS table when you delete the EMPLOYEES table. Which of the following command will solve the purpose? (Consider the table structures as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

*答案：B。*如果 ON DELETE CASCADE 存在于约束定义中，且从父表中删除了一条记录，则子表中任何对应的记录也会被自动删除。

*Answer: B. *If ON DELETE CASCADE is included in the constraint definition and a record is deleted from the parent table,any corresponding records in the child table are also deleted automatically.

94. The EMPLOYEES table as shown below, has 5 employees who work in department 10. An executive from admin department issues the below query.

DELETE FROM departments
WHERE dept_id = 10;

此查询的结果是什么？（假设表结构如所示）

What will be the outcome of this query? (Assume the table structures as shown)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

*答案：A。*DEPARTMENTS 中的 DEPT_ID 是 EMPLOYEES 表中的外键，并且部门 10 中有员工，因此除非找到子记录，否则无法从父表中删除值。

*Answer: A. *The DEPT_ID from DEPARTMENTS is the foreign key in the table EMPLOYEES and there are employees in department 10 ,hence a value cannot be deleted from the parent table unless the child record is found.