Exam 1z0-082 topic 1 question 88 discussion

G is tricky answer - "The WITH CHECK clause prevents certain rows from being updated or inserted". Techically, it is correct, but I woulc rather say - "The WITH CHECK clause allow certain rows to be updated or inserted", since only rows which are inline with WITH CHECK constraint can be affected. Speaking about A - "...Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it..." So, it should be A, E and F.

Correct Answer: AEF Wrong Answer: BCD Uncertain: G https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm#SQLRF01504 for me G could also be a valid answer

Selected Answer: EFG

The reason why option A is incorrect is due to the lack of the "OR REPLACE" clause. According to Oracle's official documentation, specifying "OR REPLACE" in the CREATE VIEW statement allows you to recreate an existing view and modify its definition without the need to drop, recreate, or regrant previously granted privileges on the view. For option A to be correct, it should state that by specifying "OR REPLACE," you can update a view without the need to regrant privileges on the view. https://docs.oracle.com/cd/F19136_01/sqlrf/CREATE-VIEW.html

G is correct. CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, salary NUMBER); INSERT INTO employees VALUES (1, 10, 5000); INSERT INTO employees VALUES (2, 10, 6000); INSERT INTO employees VALUES (3, 20, 7000); CREATE VIEW emp_view AS SELECT * FROM employees WHERE department_id = 10 WITH CHECK OPTION; UPDATE emp_view SET department_id = 20 WHERE employee_id = 1; ORA-01402: view WITH CHECK OPTION where-clause violation ORA-06512: at "SYS.DBMS_SQL", line 1721

F is correct. CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, salary NUMBER); INSERT INTO employees VALUES (1, 10, 5000); INSERT INTO employees VALUES (2, 10, 6000); INSERT INTO employees VALUES (3, 20, 7000); INSERT INTO employees VALUES (4, 20, 8000); CREATE VIEW dept_salary AS SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id; DELETE FROM dept_salary WHERE department_id = 10; SQL>ORA-01732: data manipulation operation not legal on this view DELETE FROM dept_salary; SQL>ORA-01732: data manipulation operation not legal on this view

E is correct. CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, salary NUMBER); INSERT INTO employees VALUES (1, 10, 5000); INSERT INTO employees VALUES (2, 10, 6000); INSERT INTO employees VALUES (3, 20, 7000); CREATE VIEW dept_salary AS SELECT department_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id; INSERT INTO dept_salary VALUES (30, 10000); ORA-01733: virtual column not allowed here

Correct answer is EFG A-FALSE :To issue a query or an INSERT, UPDATE, or DELETE statement against a view, you must have the SELECT, READ, INSERT, UPDATE, or DELETE object privilege for the view, respectively, either explicitly or through a role. D-FALSE:With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. E,F-TRUE:If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view. G-TRUE: If a view is defined with WITH CHECK OPTION, a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.

I think A,E,F,G all are correct

I had this question also on the 1Z0-071 exam. AG was the right answers in there but the question is a little bit different

For E, F, and G ; I cannot find any point against them, so probably they are corrects in my opinion. A, because the "updated" word I would say it's false, because we go in the case of DML op. when they are allowed and not on the view. The view must not contain any of the following constructs: A set operator a DISTINCT operator An aggregate or analytic function A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause A collection expression in a SELECT list A subquery in a SELECT list A subquery designated WITH READ ONLY Joins, with some exceptions, as documented in Oracle Database Administrator's Guide.

E -> not always, in this case is a wrong statement. Test case: CREATE TABLE employees ( id NUMBER, name VARCHAR2(100), salary NUMBER, department_id NUMBER ); INSERT INTO employees VALUES(1, 'John Doe', 50000, 1); INSERT INTO employees VALUES(2, 'Jane Doe', 60000, 1); INSERT INTO employees VALUES(3, 'Robert Smith', 70000, 2); CREATE VIEW dept1_employees5 AS SELECT id, count(*) cnt FROM employees group by id; INSERT INTO dept1_employees5 (id) VALUES (4); This will not cause an error. Otherwise DELETE statement will: SQL> delete from dept1_employees5 where id=4; delete from dept1_employees5 where id=4 * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view In my opinion correct answer is AFG

EFG is correct. - E, F: insert/update/delete is prevented when you have GROUP BY in defining query of a view. - G: true, that's what WITH CHECK clause does :)

A is correct: Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it. Remember that removing an object's privileges has a cascade effect, so with the "OR REPLACE" you avoid assigning the privileges again.

A is wrong: Source: https://www.oracletutorial.com/oracle-view/oracle-with-check-option/

I think, A,F,G are correct

D is correct