Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2)NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in
the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS4 ? 4;
SELECT employee_id, last_name,job_id4 ? 4;
FROM employees4 ? 4;
WHERE job_id LIKE ‘SA_%’4 ? 4;
WITH CHECK OPTION;
Which two statements are true regarding the above view? (Choose two.)
A.
It allows you to insert rows into the EMPLOYEES table .
B.
It allows you to delete details of the existing sales staff from the EMPLOYEES table.
C.
It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.
D.
It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements.
I think B and D are right…since A does not specify that insert will be allowed only for Sales staff and option C is an update that will make the rows disappear from the view which is not allowed.
delete also would make the rows diapper
delete also would make the rows disappear
And in the view definition is missing one column (department_id) that is not null.
From SQL Fundamentals I:
You cannot add data through a view if the view includes:
NOT NULL columns in the base table that are not selected by the view.
PLEASE SOMEBODY EXPLAIN ME THAT WHY THE FOURTH OPTION IS CORRECT
Because in the multitable Insert statement you can combine columns from view and columns from another table/view, so then you can have all the needed columns to insert to employees table.
please advise what does a view allow you to do? What is WITH CHECK OPTION?
Using view you can only select data but also insert and update under certain conditions.
WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery.
See documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#i2065510
A data manipulation language ( DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.
BD
Thank You @Justyna. You have been providing relevant information in almost all the questions. Appreciate it…..
why option c is incorrect and B is correct?