Skip to main content

18. ADDING CHECK CONSTRAINT WHILE INSERTING.

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_SEQEMP_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 throughsubstitutionvariable.

The INSERT statement should fail if the user supplies a value other than20 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 CHECKOPTION) 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
Labels:

Comments

Post a Comment

Popular posts from this blog

Question 8: Aggregate Functions

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two) A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date) D. AVG(start_date, end_date) E. MIN(start_date) F. MAXIMUM(start_date) Answer: C & E Explanation: It is possible to apply COUNT() and MIN() functions on the column with DATE data type. Incorrect Answers A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column. D: Function AVG() cannot be used with DATE data type column, and function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Post a Comment
Read more

21.Oracle - The self documenting dictionary

Oracle - The self documenting dictionary Oracle's Data dictionary is itself self documenting. we can query DICTIONARY AND DICT_COLUMNS views for descriptions of the data dictionary views and their columns. The following query gives the descriptions of all of the data dictionary views. SELECT table_name, comments FROM dictionary ORDER BY table_name; It gives the large amount of output. By using where class we can focus a smaller set of views as like the following query. SELECT table_name, comments FROM dictionary WHERE table_name LIKE '%TABLE%' ORDER BY table_name It returns all views containing the word 'TABLE' Like wise We can query the DICT_COLUMNS view also. It gives the descriptions for the columns for all views. Following query retrieves descriptions for the columns in ALL_TAB_COLUMNS. SELECT column_name, comments FROM dict_columns WHERE table_name = 'ALL_TAB_COLUMNS'; This is a sample one. We can learn lot about oracle through data d...
1 comment
Read more