Which three statements are true about views in an Oracle database? (Choose three.)
Which three statements are true about views in an Oracle database? (Choose three.)
Views can be altered or replaced without the need to re-grant privileges, not specifically updated as stated in option A, which makes it incorrect. Data Manipulation Language (DML) operations like insert, update, and delete cannot be performed on views that have a GROUP BY clause specified in their defining query, which is correctly captured by options E and F. The WITH CHECK clause ensures that only rows meeting specific conditions are inserted or updated, preventing changes that would produce rows not included in the subquery, which validates option G.
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.
hmm... make sense
Answer is EFG A is wrong because it should be views can be altered*** without the need to re-grant privileges on the view. Not update G is correct, certain rows cannot be updated or inserted . for example. you try to update the department_id to 100 from 20 but the view was created with a where department id_= 20 or insert a deparment_id other than 20 . it will cause a ORA-01402 error
Answer is EFG A is wrong because it should be views can be replaced*** without the need to re-grant privileges on the view. Not update G is correct, certain rows cannot be updated or inserted . for example. you try to update the department_id to 100 from 20 but the view was created with a where department id_= 20 or insert a deparment_id other than 20 . it will cause a ORA-01402 error
answered G was not finished, certain rows cannot be updated or inserted, where? the correct form of the answer is "The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view." which makes G wrong
This question is a nightmare... there are 4 possible answers 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. Source: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm#SQLRF01504 E & F-> 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. Source: https://docs.oracle.com/database/121/ADMIN/views.htm#ADMIN11781 G -> 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. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause. Source: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm#SQLRF01504 I'd go with answer A, E, F and hope for the best!
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 :)
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
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.
For A, it should be ALTERED without needs of re-grant, not UPDATED.
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.
D is correct
I think, A,F,G are correct
A is wrong: Source: https://www.oracletutorial.com/oracle-view/oracle-with-check-option/
Sorry, A is correct
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.
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
I think A,E,F,G all are correct
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
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
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
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
Selected Answer: EFG