Which INSERT statement meets the above requirements?

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?

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 IN (20,50));

B.
INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);

C.
INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);

D.
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);

E.
INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);



Leave a Reply 4

Your email address will not be published. Required fields are marked *


liz

liz

Why c is correct?
I read the document said “declare with check option in where clause will receive ora error”

This is an illegal example:
INSERT INTO (SELECT department_id, department_name, location_id
FROM departments WHERE location_id < 2000 WITH CHECK OPTION)
VALUES (9999, 'Entertainment', 2500);
*
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

dames

dames

In your example check option restricts location_id to less than 2000. No wonder 2500 raises check violation error.

pht

pht

yes lizzzy u are right,
i also think like that way,
i think we should be friends
what do you think.

xy

xy

c caused this error: ORA-00947