Q: 111 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SAL NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
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:
EMPLOYEE_ID: Next value from the sequence EMP_ID_SEQ
EMP_NAME and JOB_ID: As specified by the user during run time, through
substitution variables
SAL: 2000
MGR_ID: No value
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable. The INSERT statement should
fail if the user supplies a value other than 20 or 50.
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);
Answer: D
问下，这个答案为什么选D呢？
insert into (select ...)？这也可以啊？还有value('&jobid')这是啥语法？
------解决方案--------------------------------------------------------
只有D的语法正确且满足需求。
这里注意with check option的用法。
insert into (select ...)等价于insert into t(col1,col2)
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
学习了！
------解决方案--------------------------------------------------------
The INSERT statement should fail if the user supplies a value other than 20 or 50. 如果没加WITH CHECK OPTION，那么不是20和50的数也会被插入，加勒WITH CHECK OPTION就不会允许对这个子查询不包含的记录做任何改变
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
&jobid 中 &是引用变量
EMP_NAME and JOB_ID: As specified by the user during run time, through
题目的要求是在执行的时候由用户输入，所以，需要使用变量。