OCP-1Z0-051 补充题库 第19题 限制替代变量的输入值

一、原题

Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run,
the INSERT statement should insert a row with the specified values into the EMPLOYEES table.
The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?
A.
INSERT INTO employees
VALUES
  (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
B.
INSERT INTO employees
VALUES
  (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20, 50));
C.
INSERT INTO
  (SELECT * FROM employees WHERE department_id IN (20, 50))
VALUES
  (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D.
INSERT INTO
  (SELECT *
     FROM employees
    WHERE department_id IN (20, 50) WITH CHECK OPTION)
VALUES
  (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E.
INSERT INTO
  (SELECT *
     FROM employees
    WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION)
VALUES
  (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

答案: D

二、题目翻译

下面是EMPLOYEES表的结构:
现在要建一个包含INSERT语句的脚本文件，当脚本文件运行的时候，INSERT语句会向EMPLOYEES表中插入一行指定的数据。
INSERT插入的值应该满足下面的要求:
EMPLOYEE_ID: 是序列中的next value。
EMP_ID_SEQ EMP_NAME和JOB_ID: 运行时通过替代变量实现。
SAL: 2000
MGR_ID:没值。
DEPARTMENT_ID:运行时通过替代变量实现,如果用户插入了20或50以外的其它值，则插入失败。
下面的INSERT语句，哪一个能实现上面的要求?

三、题目解析

D选项正确，因为题目要求department_id只能输入20或50, 替代变量不能限制输入，所以，通过视图的WITH CHECK OPTION来限制输入的值。