Sql Certificate 简明教程

SQL - Manipulating Data Questions

1.What does ACID mean with respect to relational database?

回答：C. 所有 Oracle 事务都符合数据库事务的基本属性，即 ACID 属性。原子性规定，一个事务的所有任务都执行，或没有一项被执行。不存在部分事务。一致性意味着事务将数据库从一个一致状态带到另一个一致状态。隔离意味着在事务提交之前，其它事务看不到该事务的影响。持久性意味着已提交事务所做的变更是永久的。在事务完成后，数据库通过其恢复机制确保该事务的变更不会丢失。

*Answer: C. *All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. Atomicity states that all tasks of a transaction are performed or none of them are. There are no partial transactions. Consistency implies the transaction takes the database from one consistent state to another consistent state. Isolation means the effect of a transaction is not visible to other transactions until the transaction is committed. Durability means that changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

2. What does the word DML stands for in Oracle SQL?

回答：C. DML 代表数据操作语言。

*Answer: C. *DML stands for Data Manipulation Language.

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

回答：A，D. 从严格意义来说，SELECT 是一个 DML 命令，因为它是在表中操作数据时一项强制性的条款。

*Answer: 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.Which of the following DML commands can be considered to be a hybrid of INSERT and UPDATE in a single statement?

回答：D. 在 Oracle 中，MERGE 可以使用一个语句执行 INSERT 和 UPDATE 动作。

*Answer: D. *MERGE can perform INSERT and UPDATE actions in a single statement in Oracle.

5. What all operations can MERGE statement perform in SQL?

*答案：A，B。*在某些情况下，除了插入和更新，MERGE 还可以执行 DELETE 操作。

*Answer: A, B. *In some conditions MERGE can perform the DELETE operation too, along with INSERT and UPDATE.

6.Which of following commands is a DDL (Data Definition Language) command but is often considered along with DML commands?

*答案：C。*TRUNCATE 是一条 DDL 命令。它从表中删除记录，没有任何条件。它不是任何正在进行的事务的一部分，在 TRUNCATE 执行后，会话中未提交的事务将被提交。

*Answer: C. *TRUNCATE is a DDL command. It removes the records from the table without any condition. It is not the part of any ongoing transaction and an uncommitted transaction in the session is committed after TRUNCATE is executed.

7.Which of the following commands manipulate data basically?

*答案：B，C。*UPDATE 是一条 DML 语句，用于修改表中的列值。TRUNCATE 通过无条件地从表中删除数据来操作数据。

*Answer: B, C. *UPDATE is a DML statement to modify a column value in a table. TRUNCATE manipulates the data by removing them unconditionally from a table.

8. Which of the following commands is used to populate table rows with data?

*答案：B。*INSERT 命令用于向表中插入行。

*Answer: B. *INSERT command is used to insert rows in a table.

9. What is true about the INSERT statement? (Choose the most appropriate answer)

*答案：C。*INSERT 语句一次可以将一行或一组行插入到一张表中。

*Answer: C. *The INSERT statement is capable of inserting a row or set of rows in a single table at a time.

10.What is true about the insertion of rows in tables?

*答案：C。*约束是对列施加的业务规则，以确保进入列中的数据的行为。这些约束在 INSERT 过程中对数据进行验证。

*Answer: C. *Constraints are business rules imposed on the columns so as to ensure the behavior of the data coming in the column. These constraints are validated for the data during the INSERT process.

11. What is true about the INSERT statement in Oracle SQL? (Choose the most appropriate answer)

*答案：D。*如果 insert 语句中包含的任何数据违反了约束，Oracle 就会引发异常。

*Answer: D. *Oracle raises exception if any of the data contained in the insert statement violates the constraint.

考虑 EMPLOYEES 表及其结构中的以下数据集，并回答问题 12、13 和 14：

*Consider the following data set from the EMPLOYEES table along with its structure and answer the questions 12, 13 and 14: *

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)

EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

12. Examine the structure of the EMPLOYEES table. You issue the following command:*

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (5100, 'BRUCE', 'CLERK');

假设 EMPLOYEE_ID 列上有一个重复值检查约束，上述语句的结果是什么？

Assuming that there is a duplicate value check constraint on the EMPLOYEE_ID column, what will be the outcome of the above statement?

*答案：C。*由于表中已经存在值“5100，BRUCE，CLERK”的行，因此无法向使用相同数据集的 insert 语句中插入行。

*Answer: C. *As the row with values "5100, BRUCE, CLERK" already exists in the table, the insert statement with same data set is not possible.

13.You issue the following command to the data set shown above:*

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (51003,'BRUCE','CLERK');

这个语句的输出是什么？

What will be the output of this statement?

*回答：A. *由于 FIRST_NAME 和 job_id 列没有任何约束，INSERT 将在不发生任何错误的情况下起作用

*Answer: A. *As there is no constraint on the columns FIRST_NAME and job_id, the INSERT will work without any error

14. You issue the following command to the data set shown above:

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id ) VALUES (51003,'BRUCE', NULL);

这个语句的输出是什么？

What will be the output of this statement?

*回答：D. *由于 FIRST_NAME 和 JOB_ID 列没有任何 NOT NULL 约束，NULL 值将被插入。

*Answer: D. *As there is no NOT NULL constraint on the columns FIRST_NAME and JOB_ID , the NULL value will get inserted.

15. What among the following can be said regarding inserting of rows in tables?*

*回答：B. *INSERT 语句可以使用替代变量在运行时提示用户键入值。它提供了一种将数据插入表的交互方式

*Answer: B. *An INSERT statement can make use of substitution variable to prompt the user to key in values during the runtime. It provides an interactive way of inserting data into tables

16.Which of the following can be used to insert rows in tables?

*回答：D. *INSERT 语句可以使用显式 INSERT、INSERT-SELECT 或子查询方法将数据插入表中。

*Answer: D. *INSERT statement can make use of explicit INSERT, INSERT-SELECT or a sub-query method to insert data into tables.

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

*回答：A. *使用 SELECT 子句是将行插入表中最常用的技术。它减少了为每列手动键入值的工作量。

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

18.Which of the following commands is used to change the rows that already exist in a table?

*回答：C. *UPDATE 是一条 DML 语句，用于修改表中的列值。

*Answer: C. *UPDATE is a DML statement which is used to modify the column values in a table.

19.What is true about the UPDATE command?

*回答：C. *UPDATE 可以根据 WHERE 子句条件一次更新一行或多行。

*Answer: C. *An UPDATE can update multiple rows in one or more rows at a time based on the WHERE clause conditions.

20.Which of the following clauses decides how many rows are to be updated?

*回答：B. *UPDATE 语句使用 WHERE 子句来捕获需要更新的行集。

*Answer: B. *UPDATE statement makes use of WHERE clause to capture the set of rows which needs to be updated.

21.What among the following is true about the UPDATE statement? (Choose the most appropriate answer)

*回答：A、C. *UPDATE 语句仅影响一个表的行，而不是多个表。

*Answer: A, C. *An UPDATE statement affects rows of only one table and not multiple tables.

*阅读 EMPLOYEES 表中的以下数据集及其结构。回答后面的 22 至 24 个问题。 *

*Consider the following data set from the EMPLOYEES table and its structure. Answer questions 22 to 24 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)

EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

22. You need to change the JOB_ID for Bruce (Employee Id 7389) to 'ACCOUNTANT'. Which of the following statements will you fire?

*回答：A。*选项 B 失败，因为它会将所有职员的职务代码更改为 ACCOUNTANT。选项 C 错误，因为它会将表中所有员工的职务代码更新为 ACCOUNTANT。

*Answer: A. *Option B fails because it modifies the job code of all clerks to ACCOUNTANT. Option C is wrong because it update job code to ACCOUNTANT for all the employees in the table.

Answer the following questions 23 and 24 based on the below actions -

您向 EMPLOYEES 表发出以下查询，其数据组如上所示。

You issue the following query to the EMPLOYEES table with the data set as shown above.

UPDATE employees
Set job_id  = NULL
Where employee_id   = 51000;

数据组如下所示：（假设在 EMPLOYEE_ID 字段上存在重复值约束）

The data set will be as shown below: (Assume that there is a duplicate value constraint on the EMPLOYEE_ID column)

EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

23. Suppose you fire an UPDATE statement to update Bruce’s JOB_ID to 'SALESMAN' (with respect to the data set shown above). What will be the outcome of the query?*

*回答：B。*UPDATE 将向该 NULL 值添加新值，从而将 NULL 更改为新值

*Answer: B. *The UPDATE will add the new value to the NULL value changing the NULL to the new value

24. You issue an UPDATE statement to update the employee id 7389 to 7900. You query the employee by its id '7389' before committing the transaction. What will be the outcome?*

*回答：B。*会话中的查询与正在进行的事务一致。如果在不同的会话中执行此相同的查询，将会显示 ID 为 7389 的员工记录，因为第一个会话中的活动事务尚未提交。

*Answer: B. *A query in a session is consistent with the ongoing transactions. If the same query would have been executed in a different session, it would have shown the employee record with id 7389 because the active transaction in the first session is not yet committed.

25. What among the following is a typical use of an UPDATE statement? (Select the most appropriate answer)

*回答：A。*虽然 UPDATE 语句可以修改所有行中的所有字段值，但通常用于选择一行并更新一列或多列。

*Answer: A. *Although, the UPDATE statement can modify all column values in all rows, but typically it is used to select a row and update one or more columns.

26. Which of the following practices appropriately describe for selecting which row set to update using the UPDATE statement?

回答：C。

*Answer: C. *

27. Which of the following columns in a table are not usually updated?

*回答：C。*作为一种通用做法，作为其他表中外键引用的主键字段不应更新。虽然可以通过延迟通常不建议执行的约束来更新它们。

*Answer: C. *As a common practice, the primary key columns which serve as foreign key reference in other tables, should not be updated. Though they can be updated by deferring the constraints which is usually not recommended.

考虑 EMPLOYEES 表的以下数据集和结构并回答以下的 28 和 29 问题：

*Consider the following data set and structure of the EMPLOYEES table and answer the questions 28 and 29 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)

EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE             NULL
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

28. You issue an UPDATE statement as follows:

UPDATE employees
SET job_id  = NULL;

上述语句的结果是什么？

What will be the outcome of the above statement?

*回答：C。*没有 WHERE 子句的 UPDATE 语句将更新表的所有行。

*Answer: C. *An UPDATE statement without a WHERE clause will update all the rows of the table.

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

*回答：D。*更新字段值时必须遵守该字段上的约束。在给定的 UPDATE 语句中，将引发错误，因为 EMPLOYEE_ID 字段是 EMPLOYEES 表中的主键，这意味着它不能为 NULL。

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

30. Which of the following commands can be used to remove existing records from a table?

*答：D. *DELETE 用于移除表中的记录，可以根据条件选择性地移除。作为 DML 语句，它是一笔交易的一部分。

*Answer: D. *DELETE is used to remove the records from the table which can be optionally based upon a condition. Being a DML statement, it is the part of a transaction.

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

*答：B. *DELETE 语句中的 WHERE 子句谓词是可选的。如果省略 WHERE 子句，则表中的所有行都将被删除。

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

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

*答：C. *作为活动交易或新交易的一部分，表中的行将被删除。

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

33.Consider the following data set from the EMPLOYEES table and its structure:

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)

 EMPLOYEE_ID FIRST_NAME   JOB_ID
------------------- ------------------ --------
5100 	             BRUCE
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

你必须删除 employee_id 为 51001 的行的 JOB_ID 列中的数据。以下哪些查询是正确的？

You need to delete the data from the JOB_ID column in the row with employee_id 51001. Which of the following queries will be correct?

*答：D. *你不能通过 DELETE 语句针对特定行删除特定列值。整个行将根据给定条件被删除。列中不需要的值可以更新为 NULL。选项“A”接近但根据问题的上下文不正确。

*Answer: D. *You cannot delete a particular column value for a particular row with the DELETE statement. The entire row gets deleted based on the conditions given. Unwanted values in a column can be updated to NULL. Option 'A' is near but not correct in the context of the question.

34. What is the difference between the UPSERT and MERGE statements?

*答：D. *UPSERT 是一条过时的语句，MERGE 取而代之并具有新功能。

*Answer: D. *UPSERT is an obsolete statement and MERGE took over with new capabilities.

35. What is the difference between the MERGE command and the commands INSERT, UPDATE and DELETE?

*答：C. *MERGE 语句可以将表上的所有三个操作嵌入到单条语句中，而 INSERT、UPDATE 和 DELETE 一次执行一个操作。

*Answer: C. *The MERGE statement can embed all three operations on a table in a single statement while INSERT, UPDATE and DELETE perform one operation at a time.

36. Which of the following objects can be the data source in a MERGE statement?

*答：C. *MERGE 既可以与表配合使用，也可以作为子查询配合使用。

*Answer: C. *MERGE works well with a table or a subquery.

37. What among the following is a TRUNCATE statement equivalent to? (Choose the most suitable answer)

*答：C. * TRUNCATE 通过一条命令删除所有行。

*Answer: C. * TRUNCATE deletes all the rows in one command.

38.Which of the following situations indicate that a DML operation has taken place?

*答：A. *当表中的现有行插入、修改或从表中移除时，则通过 DML 语句完成。

*Answer: A. *When existing rows in a table are inserted, modified or removed from a table, it is done through a DML statement.

39.Which of the following best defines a transaction?

*答案：C。*数据库事务包括一条或多条 DML 语句，构成数据中的一个一致更改，或一条 DDL 语句或一条 DCL 命令 (GRANT 或 REVOKE)。它以第一条 DML 语句开始，以 DCL 或 DDL 或 TCL (COMMIT 或 ROLLBACK) 命令结束。请注意，DDL 和 DCL 命令具有自动提交功能。

*Answer: C. *A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL statement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or TCL (COMMIT or ROLLBACK) command. Note that DDL and DCL commands hold auto commit feature.

40. What does a collection of DML statements that form a logical unit work known as?

*回答：D. *

*Answer: D. *

41.What happens when a DML statement in an active transaction encounters an error on execution?

*答案：A。*如果活动事务中的任何 DML 语句遇到错误，整个事务将最终回滚。

*Answer: A. *If any of the DML statement in an active transaction encounters error, the whole transaction ends up in a rollback.

42.What is true about the keyword VALUES in INSERT statements?

*答案：D。*VALUES 关键字仅在 INSERT 语句中明确指定列值时使用。

*Answer: D. *The VALUES keyword is used only when the column values are explicitly specified in the INSERT statement.

请考虑以下语句和表结构。回答以下第 43 至 45 题：

*Consider the following statement and the table structure. Answer the questions 43 to 45 that follow: *

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)

INSERT INTO departments (department_id , department_name , manager_id, location_id )
VALUES (100, 'Human Resources', 121, 1000);

43. How many rows will be inserted by the above statement?

*答案：D。*当使用 VALUES 关键字时，它一次仅插入一行。

*Answer: D. *When the keyword VALUES is used, it inserts only one row at a time.

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

*答案：B。*如果在 INSERT 子句中提到了列，则 VALUES 关键字应按相同顺序包含值

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

45. Suppose the above given statement is modified as below:

INSERT INTO departments VALUES (100, 'Human Resources', 121, 1000);

此修改的结果是什么？假设 DEPARTMENTS 表有四列，即 department_id、DEPARTMENT_NAME、MANAGER_ID 和 LOCATION_ID。

What will be the outcome of this modification? Assume that the DEPARTMENTS table has four columns namely, department_id ,DEPARTMENT_NAME ,MANAGER_ID and LOCATION_ID .

*答案：A。*在 INSERT 语句中包括列名称是可选的，前提是值必须符合表中列的计数和顺序。

*Answer: A. *Including the column names in the INSERT statement is optional provided the values must comply with the count and sequence of the columns in the table.

46. What will be the outcome of the below INSERT statement? (Consider the table structure)

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)

INSERT INTO EMPLOYEES (employee_id , hire_date) VALUES (210,"21-JUN-2013");

*答案：C。*日期文字格式有误。它应放在单引号内，而不是双引号内。

*Answer: C. *The date literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

47.What will be the outcome of the below INSERT statement? (Consider the given table structure)

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)

INSERT INTO EMPLOYEES (employee_id , first_name) VALUES (210,"Bryan");

*答案：C。*字符串文字格式有误。它应放在单引号内，而不是双引号内。

*Answer: C. *The string literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

48. Suppose you need to insert the name O’Callaghan as the last name of the employees table. Which of the following queries will give you the required results? (Consider the given table structure)

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)

回答：C。

*Answer: C. *

49. What will be the outcome of the below INSERT statement? (Consider the given table structure)

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)

INSERT INTO EMPLOYEES (employee_id , first_name) VALUES ("210",'Bryan');

*答案：A. *数字值不应该用引号括起来。

*Answer: A. *Number values should not be enclosed within quotes.

50. What will be the outcome of the below INSERT statement? (Consider the given table structure)

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)

INSERT INTO departments VALUES (200,'Accounts', NULL, NULL);

*答案：C. *NULL 可用于 VALUES 子句来交替填充列值。

*Answer: C. *NULLs can be used in the VALUES clause to fill up the column values alternatively.

51. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the table structure given)

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)

INSERT INTO departments VALUES (NULL, 'Accounts', NULL);

*答案：A. *NULL 值不能插入非 NULL 列。

*Answer: A. *NULL values cannot be inserted into non null columns.

52. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the given table structure)

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)

INSERT INTO departments VALUES (200, 34, NULL);

*答案：B. *值的数据类型与表中列的数据类型不匹配。

*Answer: B. *Data type of the value mismatches with the data type of the column in the table.

53. Which of the following commands is used to save the changed data in a table permanently?

*答案：B. *TCL 命令 COMMIT 用于通过使表中的所有挂起数据更改永久化来结束会话中的当前活动事务。

*Answer: B. *The TCL command COMMIT is used to end the current active transaction in a session by making all the pending data changes permanent in the tables.

54. Which of the following commands allows undoing the changed data?

*答案：A. *TCL 命令 ROLLBACK 用于通过丢弃所有挂起的数据更改来结束会话中的当前活动事务。

*Answer: A. *The TCL command ROLLBACK is used to end the current active transaction in a session by discarding all the pending data changes.

55. Which of the following commands allows enabling markers in an active transaction?

*答案：C. *SAVEPOINT 标记事务中一个点，将事务分为更小的部分。

*Answer: C. *SAVEPOINT marks a point in a transaction which divides the transaction into smaller sections.

56. Which of the following commands prevents other users from making changes to a table?

回答：C。

*Answer: C. *

57. What is true about an INSERT statement which tries to insert values into a virtual column? (Choose the most appropriate answer)

*答案：A. *虚拟列是一个总是根据列规范中定义的派生表达式自动生成的列。用户不能显式地插入其值。

*Answer: A. *A Virtual column is a column which is always auto generated based on the derivation expression defined in the column specification. Its value cannot be explicitly inserted by the user.

58.Which of the following commands allows the user to insert multiple rows with a single statement?

*答案：B。*可以使用 INSERT ALL 执行批量插入操作。

*Answer: B. *Bulk insert operations can be carried out using INSERT ALL.

59. Which of the following is the syntax for inserting rows through a sub-query?

答案：A。

*Answer: A. *

考虑 EMPLOYEES 表的以下表格，并回答以下第 60 至 63 题：

*Consider the following exhibit of the EMPLOYEES table and answer the questions 60 to 63 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)

60. Which of the following queries will execute successfully?

答案：A。

*Answer: A. *

61.Due to structural reorganization in the organization, you are asked to update department IDs for all the employees to NULL before the final decision is made public. Only those records should be updated which have the JOB_ID as NULL. Which of the following queries will work?

*答案：C。*使用 IS NULL 运算符检查列的空值。

*Answer: C. *Use IS NULL operator to check column value for nullity.

62.You need to add a basic employee data into EMPLOYEES table. The basic data contains the last name as 'Bond' and department ID as 300. Which of the following statements will give the correct results?

*答案：B、C。*子查询在 INSERT 语句中起作用，前提是它们返回与所在列相匹配或兼容的数据类型标量值。

*Answer: B, C. *Sub queries do work in INSERT statements provided they return a scalar value of data type matching or compatible to the column for which they are used.

63。您触发以下查询：

*63. You fire the following query: *

DELETE FROM EMPLOYEES;

假设在任何会话中，EMPLOYEES 表上都没有处于活动状态的事务，以下哪条语句为真？

Assuming that there are no active transactions on the EMPLOYEES table in any sessions, which of the following statements is true?

*答案：B。*作为一条 DML 语句，DELETE 操作造成的数据变更，只有在会话中发出 COMMIT 后才变为永久变更。

*Answer: B. *Being a DML statement, the data changes due to DELETE operation are made permanent only after COMMIT is issued in the session.

64。考虑 COUNTRY 表的结构，如下所示：

*64.Consider the structure of the COUNTRY table as shown: *

SQL> desc countries
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 COUNTRY_ID		 NOT NULL CHAR(2)
 COUNTRY_NAME			  VARCHAR2(40)
 REGION_ID			  NUMBER

您在会话中发出以下语句。

You issue the following statements in a session.

INSERT INTO COUNTRIES (1, 'Whales')
/
INSERT INTO COUNTRIES (2, 'England')
/
SAVEPOINT A;
UPDATE COUNTRIES
SET country_id= 100 where country_id= 1
/
SAVEPOINT B;
DELETE FROM COUNTRIES where country_id= 2
/
COMMIT
/
DELETE FROM COUNTRIES where country_id= 100
/

当为用户会话发出 ROLLBACK TO SAVEPOINT 命令后，会发生什么？

What will happen when a ROLLBACK TO SAVEPOINT command is issued for the user session?

*答案：A、C。*由于存在两个保存点 - A 和 B，并且 ROLLBACK 命令没有指定实际保存点标记，因此 Oracle 会引发错误。

*Answer: A, C. *Since there are two savepoints - A and B, and the ROLLBACK command does specifies the actual savepoint mark, Oracle throws error.

65.If a user issues a DML command and exits the SQL Developer abruptly without a COMMIT or ROLLBACK, what will be the outcome? (Assume the session is not auto commit)

*答案：B。*当系统故障中断了事务时，整个事务将自动回滚。

*Answer: B. *When transaction is interrupted by a system failure, the entire transaction is automatically rolled back.

66. Which of the following commands / statements would end a transaction?

*答案：A、D。*除了 TCL 命令（即 COMMIT 或 ROLLBACK）以外，DDL 命令和 DCL 命令具有自动提交功能。如果在同个会话中执行 DDL 语句，将提交活动的事务。

*Answer: A, D. *Apart from TCL commands i.e. COMMIT or ROLLBACK, the DDL commands and DCL commands possess auto commit feature. The active transaction will be committed if the DDL statement is executed in the same session.

67. 交易何时完成？

*67.When does a transaction complete? *

答案：D. *如果在会话中执行 TCL、DCL 或 DDL 命令，则交易完成。

*Answer: D. *Transaction completes if a TCL, DCL or a DDL command is executed in the session.

68. Examine the given table structures and consider the following query:

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)

INSERT INTO EMPLOYEES (department_id ) VALUES
(select department_id  FROM departments);

上述查询的结果会怎样？

What will be the outcome of the above query?

答案：C. *VALUES 关键字使用错误。必须在有要插入表中的列数据时才使用该关键字。

*Answer: C. *Wrong usage of VALUES keyword. It must be used only when you have column data in hand, which has to be inserted in the table.

69. 检查给定的表结构，并考虑以下查询：

*69.Examine the given table structure and consider the following query: *

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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

UPDATE (select employee_id , job_id  from employees)
SET hire_date = '01-JAN-13'
WHERE employee_id  = (select employee_id  FROM job_history);

哪一项是对给定查询的正确描述？

Which of the following is true regarding the given query?

回答：C。

*Answer: C. *

70.What happens when a transaction is committed?

答案：D. *提交交易会将待定数据更改永久保存到数据库中。

*Answer: D. *Committing a transaction saves the pending data changes permanently into the database.

71. Which of the following reasons will the best one on the usage of string?*

答案：C、B、D. *意外引用不存在的对象/列可能是其他原因。

*Answer: C, B, D. *References to non-existing objects / columns, Space issues might be other reasons.

72. What happens when an INSERT statement tries to insert records in an old table? *

回答：C。

*Answer: C. *

73. A user named 'Jonathan Adams' is able to SELECT columns from the EMPLOYEES table but he is unable to insert records into EMPLOYEES. What can be the reason?*

答案：C. *用户可以根据其职责享受表访问权限。一个用户可能只有对一张表的读取访问权限，而另一个用户则可以享有读取和写入访问权限。

*Answer: C. *Users can enjoy table access based on their responsibilities. One can have only read access on a table while other can enjoy read and write access.

74. Suppose 1 million rows are to be inserted into the AUDIT table. An INSERT transaction runs successfully for the first 1000 rows and an ORA error is thrown 'Constraint violated'. What will happen to the values inserted in the first 1000 rows?*

答案：C. *如果交易期间有任何 DML 语句遇到错误，则整个交易将回滚。

*Answer: C. *If any of the DML statement during the transaction encounters error(s), the complete transaction will be rolled back.

检查表结构并考虑以下查询，并回答后续的 75、76 和 77 题：

*Examine the table structure and consider the following query and answer the questions 75, 76 and 77 that follow: *

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)

INSERT INTO departments values (15, NULL);

75. What will be the outcome of this statement?

答案：C. *DEPARTMENTS 表包含四列，但 INSERT 语句只给两列提供了值，且也没有提及这些列。因此，会抛出 ORA 错误。

*Answer: C. *The DEPARTMENTS table contains four columns but the INSERT statement supplies value for two columns only without mentioning the columns too. Hence, the ORA error is thrown.

76. What is true about the above INSERT statement?

*答案：A. *如果 INSERT 语句中未指定列，则 Oracle 将按顺序和位置将每个值映射到表中的列。

*Answer: A. *If the columns are not specified in the INSERT statement, Oracle sequentially and positionally maps each value to the column in the table.

77. With respect to the statement given above, what will happen if the table is altered to add a new column?

*答案：B. *由于前面未指定列，因此问题仍然存在。列-值映射中的不匹配将引发 ORA 错误。

*Answer: B. *Since the columns were not specified earlier, the problem will still exist. Mismatch in the column-value mapping would throw an ORA error.

检查下面给出的表结构，并考虑以下查询，然后回答后面的 78 和 79 题：

*Examine the table structure given below and consider the following queries and answer the questions 78 and 79 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)

INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, 'ADAMS','21-DEC-12');

INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, upper('ADAMS'),to_date('21-DEC-12','DD-MON-YY'));

78. Which of the above two queries is better?

*答案：C. *查询 2 更好，因为它将日期值作为日期插入，而不是作为字符串插入。尽管 Oracle 会将指定为日期的字符串文本隐式转换为日期，但不推荐这样做。

*Answer: C. *Query-2 is better because it inserts date value as a date and not as a string. Though Oracle will perform implicit conversion of string literal specified as a date, but not recommended.

79. Which of the following queries is equivalent of the query 2 given above?*

*答案：A、C、D. *算术运算/函数可用于插入值，如上所示。

*Answer: A, C, D. *Arithmetic operations /functions can be used to insert values as shown above.

80. You need to copy the data from one table to another table. Which of the following methods can be used?

*答案：B. *INSERT-AS-SELECT (IAS) 直接路径操作是最常用的从一个表复制数据到另一个表的方法。

*Answer: B. *The direct path operations INSERT-AS-SELECT (IAS) is the most commonly used method to copy data from one table to another.

81.Which of the following statements will copy data from the JOB_HISTORY table to the JOB_HISTORY_ARCHIVE table? (Consider the table structure as given)*

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

*答案：C. *选项“C”正确显示了 IAS（INSERT-AS-SELECT）方法的用法。

*Answer: C. *The option 'C' correctly shows the usage of IAS (INSERT-AS-SELECT) method.

Examine the given table structure. Consider the following query and answer the questions 82 and 83 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)

INSERT ALL
WHEN job_id   = 'SA_REP' then
INTO employees (employee_id , department_id , salary, hire_date)
VALUES (employee_id , 10, salary, hire_date)
WHEN job_id  <> 'SA_REP' then
INTO employees (employee_id , department_id  , salary, hire_date)
VALUES (employee_id , 20, salary, hire_date)
SELECT employee_id , department_id , job_id, salary, commission_pct , hire_date
FROM employees
WHERE hire_date > sysdate - 30;

82. Interpret the output of the above INSERT statement.

*答案：B、C. *INSERT ALL 可以对目标表进行条件插入。

*Answer: B, C. *INSERT ALL can make conditional inserts into the target tables.

83. Which employees' data will be inserted in the department 20?*

*答案：B. *根据 INSERT ALL 语句，job_id 并非“销售代表”的员工详细信息。

*Answer: B. *As per the INSERT ALL statement, the details of employees whose job_id is not 'Sales Representative'.

84. What will be the outcome of the below query? (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)

INSERT INTO employees (employee_id , salary) VALUES (&employee_id , &salary);
COMMIT;

*答案：C. *代换变量与 DML 语句配合使用效果很好。

*Answer: C. *Substitution variables work well with the DML statements.

85. Evaluate the following SQL statements that are executed in a user session in the specified order:*

CREATE SEQUENCE id_seq;
SELECT id_seq.nextval
FROM dual;

INSERT INTO employees (employee_id ,first_name,job_id )
VALUES (ord_seq.CURRVAL, 'Steyn','Trainee');

UPDATE employees
SET employee_id = id_seq.NEXTVAL
WHERE first_name = 'Steyn'
AND job_id ='Trainee';

上述语句的结果是什么？

What would be the outcome of the above statements?

回答：B。

*Answer: B. *

86. What is the restriction on the sub-query used in the UPDATE statement?*

*答案：B. 在 UPDATE 语句中使用时，子查询不应返回多行

*Answer: B. *The sub-query should not return multiple rows when being used in an UPDATE statement

检查给定的表结构并考虑以下查询，然后回答随后的问题 87 和 88：

*Examine the given table structure and consider the query given below and answer the questions 87 and 88 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)

UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id =7382);

87. What will be the outcome of the above query?*

*答案：B. *查询结果可用于更新表中的列值。

*Answer: B. *Query results can be used to update the column values in a table.

88. Suppose if the employee 7382 doesn’t exist in the EMPLOYEES table. What will be the outcome of the query?*

*答案：B. *UPDATE 语句不会引发任何异常，语法错误除外。

*Answer: B. *UPDATE statements do not raise any exception except for syntactical errors.

检查给定的表结构并考虑以下查询，然后回答随后的问题 89 和 90：

*Examine the given table structure and consider the query given below and answer the questions 89 and 90 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)

UPDATE employees
set salary = (select salary from employees where last_name = 'Adams');

89. What will be the outcome of the query?*

*答案：C. *子查询可能返回多行，从而导致错误。

*Answer: C. *The sub-query might return more than one row causing an error.

90. What changes in the above query will make sure there are no errors caused?*

答案：A。

*Answer: A. *

检查给定的表结构并考虑以下查询，然后回答随后的问题 91 和 92：

*Examine the given table structure and consider the following query and answer the questions 91 and 92 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)

UPDATE employees
SET salary = (select max (salary) from employees where last_name = 'Adams');

91. What will be the outcome of the query given above?*

*答案：B. *算术函数 MAX 或 MIN 可与子查询一起使用，以获取标量值并避免错误。

*Answer: B. *Arithmetic functions MAX or a MIN can be used with sub-queries to get scalar values and avoid errors.

92. Assume that the sub-query above is replaced with the following:*

 SELECT distinct salary from employees where last_name = 'Adam';

给定主查询的结果是什么？

What will be the outcome of the main query given above?

*答案：C. * 给出了错误，因为具有姓氏“亚当”的人有很多，因此会有许多不同的薪水。

*Answer: C. * it gives an error because as there are many with the last name as 'Adam' there will many distinct salaries.

检查给定的表结构，并考虑以下查询，然后回答后面的第 93 和 94 题：

*Examine the given table structure and consider the following query and answer the questions 93 and 94 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)

UPDATE employees
SET salary = 50000;
WHERE job_id  in (select job_id  from job_history where department_id  = 10);

93. What will the above statement do? (Choose the most appropriate answer)

回答：C。

*Answer: C. *

94. What will happen if the WHERE clause given above is replaced with the following?*

 WHERE job_id = (select job_id from job_history where department_id  = 10);

*答：C。*等号会导致错误。

*Answer: C. *The equal sign will raise the error.

检查给定的表结构并考虑以下语句。回答后面的第 95 至 97 题。

*Examine the given table structure and consider the following statement. Answer the questions 95 to 97 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)

DELETE FROM employees where last_name = 'A%';
COMMIT;

95. What will be the outcome of the query given above?*

*答：A。*DELETE 语句可以具有 WHERE 子句谓词。基于条件，记录将从表中移除。

*Answer: A. *DELETE statement can have WHERE clause predicate. Based on conditions, the records will be removed from the table.

96. Consider the following statement:*

DELETE FROM employees where employee_id  IS NULL and job_id = NULL;
COMMIT;

假设 employee_id 列有 NOT NULL 约束，上述查询的结果是什么？

Assuming there is a NOT NULL constraint on the column employee_id , what will be the outcome of the above query?

*答：B。*可以将多个谓词应用于 DML 语句 UPDATE 和 DELETE。

*Answer: B. *Multiple predicates can be applied to the DML statements UPDATE and DELETE.

97. Consider the following query:*

DELETE FROM employees where department_id  = &deptID;
COMMIT;

执行上述语句时会发生什么？

What will happen when the above statement is executed?

*答：B。*代换变量可与 DML 语句一起使用。

*Answer: B. *Substitution variables can be used with DML statements.

98. All parts of a transaction should complete or none of them. Which property of ACID rule complies with the given statement?*

*答：A。*ACID 指数据库事务的基本属性：原子性、一致性、隔离性和持久性。原子性意味着一整套动作要么完成，要么中止。一致性意味着事务将资源从一个一致状态转移到另一个一致状态。隔离性意味着在事务提交之前，事务的效果对于其他事务不可见。持久性意味着已提交事务所做的更改是永久的，并且必须能够在系统故障中存活。

*Answer: A. *ACID refers to the basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability. Atomicity implies that entire sequence of actions must be either completed or aborted. Consistency implies that the transaction takes the resources from one consistent state to another. Isolation implies that a transaction’s effect is not visible to other transactions until the transaction is committed. Durability implies that the changes made by the committed transaction are permanent and must survive system failure.

99. What does the principle of Durability in the ACID property state?*

回答：C。

*Answer: C. *

100. An incomplete transaction should be invisible to all the other users. Which of the properties of the ACID state this?*

答：A。“I”代表隔离性。

*Answer: A. * "I" stands for Isolation.

101. What does the principle of consistency states?*

答案：A。 ACID 属性中的"C"代表一致性

*Answer: A. * the "C" in ACID property stands for Consistency

102. What among the following best describes a Transaction?*

*回答：D. *

*Answer: D. *

103. A user named "Jonathan" inserts data in the table EMPLOYEES. When will the other users be able to see the new data?*

答案：C。 活动事务必须在同一会话中提交。

*Answer: C. *The active transaction must be committed in the same session.

104. What can be said about the nesting of transactions?*

回答：C。

*Answer: C. *

105. Which of the following reasons will terminate a transaction?*

答案：D。 DDL 会自动提交并且将会结束正在进行的活动事务。

*Answer: D. *DDL is auto commit and will end the ongoing active transaction.