Oracle SQL Experts: Oracle Simple View FAQs with Examples

A simple view is a view based primarily on one base table, without features such as GROUP BY, aggregate functions, DISTINCT, or set operators. Simple views are especially important because they are often updatable.

1. What is a simple view?

A simple view is a view created from a single table using a relatively straightforward SELECT.

CREATE OR REPLACE VIEW emp_simple_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees;

Now:

SELECT *
FROM emp_simple_view;

Conceptually:

EMPLOYEES
    |
    ↓
EMP_SIMPLE_VIEW
    |
    ↓
SELECT

The view normally doesn't store a separate copy of the employee rows.

2. Why is it called a simple view?

It is called "simple" because the view definition is relatively uncomplicated.

Simple View
    ↓
One base table
    ↓
No GROUP BY
No aggregate functions
No DISTINCT
No UNION
No complex calculations

Example:

CREATE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

3. What is the syntax for creating a simple view?

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1,
       column2,
       ...
FROM table_name
WHERE condition;

Example:

CREATE OR REPLACE VIEW high_salary_emp AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary > 100000;

4. Does a simple view store data?

No, not as a separate copy of the data.

For example:

CREATE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

The data remains in:

EMPLOYEES
   ↓
  data
   ↓
EMP_VIEW
   ↓
query definition

5. Can a simple view contain a WHERE clause?

Yes.

CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE status = 'ACTIVE';

Query:

SELECT *
FROM active_employees;

Only rows satisfying:

status = 'ACTIVE'

are displayed.

6. Can we update a simple view?

Yes, provided the view is updatable.

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

You can potentially do:

UPDATE emp_view
SET salary = salary + 1000
WHERE employee_id = 101;

The underlying table is actually updated:

UPDATE VIEW
     ↓
EMPLOYEES
     ↓
Row updated

7. Can we insert through a simple view?

Yes, if the view is insertable and the required underlying columns are satisfied.

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees;

Then:

INSERT INTO emp_view
(
    employee_id,
    first_name,
    salary,
    department_id
)
VALUES
(
    101,
    'John',
    50000,
    10
);

The row is inserted into EMPLOYEES.

8. Can we delete through a simple view?

Yes, if the view is deletable.

DELETE FROM emp_view
WHERE employee_id = 101;

The corresponding row is deleted from:

EMPLOYEES

9. Why are simple views often updatable?

Because there is generally a clear one-to-one relationship between a row in the view and a row in the underlying table.

CREATE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

A view row:

101 | John | 50000

corresponds directly to an underlying employee row. Therefore Oracle can determine which base-table row should be modified.

10. Can a simple view contain a WHERE condition and still be updated?

Yes.

CREATE VIEW sales_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10;

You may be able to update:

UPDATE sales_employees
SET salary = salary + 1000
WHERE employee_id = 101;

The underlying EMPLOYEES row is updated.

But there is an important issue when changing the columns used by the view condition.

11. What is WITH CHECK OPTION in a simple view?

WITH CHECK OPTION prevents DML through the view from producing rows that no longer satisfy the view's WHERE condition.

CREATE OR REPLACE VIEW sales_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Now this is rejected:

UPDATE sales_employees
SET department_id = 20
WHERE employee_id = 101;

Why?

Because after the update:

department_id = 20

The row would no longer satisfy:

department_id = 10

Remember:

WITH CHECK OPTION
        ↓
"You can modify the row,
but it must remain visible through the view."

12. What happens without WITH CHECK OPTION?

Consider:

CREATE VIEW sales_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10;

You may perform:

UPDATE sales_employees
SET department_id = 20
WHERE employee_id = 101;

The underlying row can be changed to department 20.

After the update:

SELECT *
FROM sales_employees
WHERE employee_id = 101;

may return no row because the employee no longer satisfies:

department_id = 10

This is why WITH CHECK OPTION is useful.

13. Can a simple view contain calculated columns?

This needs care.

CREATE VIEW emp_salary_view AS
SELECT employee_id,
       first_name,
       salary,
       salary * 12 AS annual_salary
FROM employees;

This is still based on one table, but ANNUAL_SALARY is a derived expression.

The derived column itself isn't directly updatable:

UPDATE emp_salary_view
SET annual_salary = 1000000;

is not a normal direct update of a base-table column.

So, for interview purposes, the safest definition of a simple updatable view is:

A view based on a single table that exposes directly mappable base-table columns and doesn't contain constructs that prevent direct modification.

14. Can a simple view contain DISTINCT?

Technically, a view can be created using DISTINCT, but it is not considered a simple updatable view in the usual Oracle terminology.

CREATE VIEW dept_view AS
SELECT DISTINCT department_id
FROM employees;

This is not a straightforward row-for-row representation of EMPLOYEES.

Therefore:

DISTINCT
   ↓
Generally not directly updatable

15. Can a simple view contain GROUP BY?

A view can contain GROUP BY, but then it is generally considered a complex view, not a simple view.

CREATE VIEW dept_summary AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

This is a complex/aggregate view.

16. Can a simple view contain aggregate functions?

A view can contain them, but it is no longer a simple updatable view.

CREATE VIEW salary_summary AS
SELECT department_id,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

The AVG() value isn't stored as an individual base-table column.

Therefore, Oracle cannot simply determine which employee row should be updated if you attempt:

UPDATE salary_summary
SET avg_salary = 70000;

17. Can a simple view contain a JOIN?

A view can contain a JOIN, but that generally makes it a complex view.

CREATE VIEW emp_dept_view AS
SELECT e.employee_id,
       e.first_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

This is not the typical definition of a simple view because it uses multiple tables.

18. Simple View vs Complex View

Feature	Simple View	Complex View
One base table	✅	Not necessarily
Multiple tables	❌ Typically	✅
JOIN	❌ Typically	✅
GROUP BY	❌	✅
Aggregate functions	❌	✅
DISTINCT	❌	✅
Set operators	❌	✅
Often updatable	✅	Often ❌

Easy memory trick:

Simple View
    ↓
One table
    ↓
Direct columns
    ↓
Usually easy to update


Complex View
    ↓
JOIN / GROUP BY / DISTINCT / aggregates
    ↓
Usually not directly updatable

19. Can we create a read-only simple view?

Yes.

CREATE OR REPLACE VIEW emp_readonly AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

You can query it:

SELECT *
FROM emp_readonly;

But DML through the view is prohibited.

UPDATE emp_readonly
SET salary = 60000
WHERE employee_id = 101;

This fails because the view is read-only.

20. WITH CHECK OPTION vs WITH READ ONLY

Feature	WITH CHECK OPTION	WITH READ ONLY
SELECT	✅	✅
INSERT	Potentially	❌
UPDATE	Potentially	❌
DELETE	Potentially	❌
Enforces view WHERE condition	✅	N/A
Allows DML	✅	❌

Remember:

CHECK OPTION
→ DML allowed, but view condition must remain true

READ ONLY
→ No DML through the view

21. Can a simple view hide columns?

Yes. This is one of the most useful applications.

Suppose:

EMPLOYEES
----------------
EMPLOYEE_ID
FIRST_NAME
SALARY
SSN
BANK_ACCOUNT

Create:

CREATE OR REPLACE VIEW employee_public AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

Now users accessing the view don't see:

SSN
BANK_ACCOUNT

You can grant access to the view:

GRANT SELECT
ON employee_public
TO reporting_user;

This is useful for security and controlled data exposure.

22. Can we rename columns in a simple view?

Yes.

CREATE OR REPLACE VIEW employee_info AS
SELECT employee_id AS emp_id,
       first_name AS employee_name,
       salary AS monthly_salary
FROM employees;

Now:

SELECT *
FROM employee_info;

returns:

EMP_ID
EMPLOYEE_NAME
MONTHLY_SALARY

23. Can we explicitly specify view column names?

Yes.

CREATE OR REPLACE VIEW employee_info
(
    emp_id,
    employee_name,
    monthly_salary
)
AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

The view columns become:

EMP_ID
EMPLOYEE_NAME
MONTHLY_SALARY

24. What happens if we insert into a simple view that doesn't contain all base-table columns?

It depends on the underlying table.

Suppose:

CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    salary NUMBER,
    department_id NUMBER NOT NULL
);

And the view is:

CREATE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

Now:

INSERT INTO emp_view
VALUES (101, 'John', 50000);

The insert may fail because DEPARTMENT_ID is required and isn't supplied through the view.

So:

A view being simple/updatable doesn't mean every possible INSERT will succeed. Underlying constraints still apply.

25. Can a simple view be used for security?

Yes.

CREATE OR REPLACE VIEW employee_public AS
SELECT employee_id,
       first_name,
       department_id
FROM employees;

Grant:

GRANT SELECT
ON employee_public
TO app_user;

The application can use:

SELECT *
FROM employee_public;

instead of having direct access to all columns in EMPLOYEES.

26. How do I find simple views in my schema?

Oracle's data dictionary doesn't provide a simple IS_SIMPLE_VIEW flag that you should rely on for all cases.

You can inspect views using:

SELECT view_name,
       text
FROM user_views
ORDER BY view_name;

For example:

SELECT view_name
FROM user_views;

Then inspect the definitions to determine whether they contain joins, aggregation, set operations, etc.

27. What is USER_UPDATABLE_COLUMNS?

Oracle provides dictionary information about whether columns in views are updatable.

Example:

SELECT owner,
       table_name,
       column_name,
       insertable,
       updatable,
       deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_VIEW';

This can help determine whether DML is allowed for columns exposed by a view.

Example result conceptually:

COLUMN_NAME      INSERTABLE  UPDATABLE  DELETABLE
----------------------------------------------------
EMPLOYEE_ID      YES         YES        YES
FIRST_NAME       YES         YES        YES
SALARY           YES         YES        YES

This is particularly useful when analyzing view updatability.

28. What is the difference between a simple view and a table?

Feature	Table	Simple View
Physically stores rows	✅	❌
Based on another object	Not necessarily	✅
Stores query definition	❌	✅
Can have indexes	✅	❌
Can hide columns	Through privileges	✅
Can be queried	✅	✅
Can be updated	✅	Often ✅

29. What is the difference between a simple view and a materialized view?

Feature	Simple View	Materialized View
Stores query definition	✅	✅
Stores query result	❌	✅
Refresh required	❌	✅
FAST refresh	❌	✅
Physical rows	❌	✅
Can have indexes on result	❌	✅
Primary purpose	Abstraction/security	Performance/reporting

Think:

SIMPLE VIEW
Query → Base table → Result


MATERIALIZED VIEW
Query → Stored result
          ↑
        Refresh

30. What happens when the underlying table changes?

Because a normal view doesn't contain a separate copy of the data, querying the view normally reflects the current underlying table data.

Example:

CREATE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

Then:

UPDATE employees
SET salary = 75000
WHERE employee_id = 101;

Now:

SELECT *
FROM emp_view
WHERE employee_id = 101;

will reflect the updated salary.

There is no separate view refresh operation for a normal view.

31. Does a simple view need to be refreshed?

No.

This is an important distinction:

Normal View
    ↓
No refresh


Materialized View
    ↓
Refresh required to synchronize stored results

For example:

SELECT *
FROM emp_view;

always queries the underlying data according to the view definition.

32. Can we create a simple view using CREATE OR REPLACE?

Yes.

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees;

This replaces the existing view definition if the view already exists.

33. Does replacing a simple view delete table data?

No.

For:

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

Oracle changes the view definition. It doesn't delete rows from:

EMPLOYEES

34. How do I drop a simple view?

Use:

DROP VIEW emp_view;

This removes:

EMP_VIEW

but does not remove:

EMPLOYEES
or its rows.

35. Can a simple view be used in another query?

Absolutely.

SELECT employee_id,
       first_name
FROM emp_view
WHERE salary > 50000
ORDER BY salary DESC;

The view behaves like a table from the perspective of the query.

36. Can we create a view based on a view?

Yes.

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees;

Then:

CREATE OR REPLACE VIEW high_paid_emp AS
SELECT employee_id,
       first_name,
       salary
FROM emp_view
WHERE salary > 100000;

Conceptually:

EMPLOYEES
    ↓
EMP_VIEW
    ↓
HIGH_PAID_EMP

Although possible, avoid unnecessarily deep chains of views because they can make dependency and performance analysis more complicated.

37. Can a simple view contain a subquery?

A view can contain a subquery, but once the definition becomes more complex, it may no longer fit the practical definition of a simple updatable view.

For example:

CREATE VIEW high_paid_emp AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

This is a valid view, but don't classify every one-table view as automatically updatable merely because it references one table.

38. Can we use ORDER BY in a simple view?

The safest practice is to put ordering in the query that reads the view:

SELECT *
FROM emp_view
ORDER BY salary DESC;

Don't rely on a view to guarantee presentation order.

39. What are the most common simple-view interview questions?

Question 1: What is a simple view?

A view generally based on a single table without constructs such as joins, grouping, aggregation, DISTINCT, or set operators that make direct row modification difficult.

Question 2: Is a simple view always updatable?

❌ No. A simple view is often updatable, but actual updatability depends on its definition and the DML operation.

Question 3: Can we update a simple view?

✅ Often yes.

UPDATE emp_view
SET salary = salary + 1000
WHERE employee_id = 101;

Question 4: Can a simple view hide columns?

✅ Yes.

CREATE VIEW emp_public AS
SELECT employee_id,
       first_name
FROM employees;

Question 5: Can a simple view have a WHERE clause?

✅ Yes.

Question 6: Does a simple view store data?

❌ No, not as a separate result set.

Question 7: Does a simple view need refresh?

❌ No.

Question 8: Can a simple view have WITH CHECK OPTION?

✅ Yes.

Question 9: What is WITH READ ONLY?

It prevents DML through the view.

Question 10: What is the difference between a simple and complex view?

Simple
→ usually one table
→ straightforward columns
→ often updatable

Complex
→ joins / aggregation / GROUP BY / DISTINCT / set operations
→ often not directly updatable

40. Simple View Complete Example

Let's put everything together.

Base table

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    salary        NUMBER,
    department_id NUMBER,
    status        VARCHAR2(20)
);

Create simple view

CREATE OR REPLACE VIEW active_emp_view AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

Query

SELECT *
FROM active_emp_view;

Update

UPDATE active_emp_view
SET salary = salary + 1000
WHERE employee_id = 101;

Attempt to move row outside the view

Whether a column can be modified through a view depends on whether that column is exposed by the view. Since STATUS isn't exposed here, this particular statement cannot directly modify it through the view.

Instead, attempting to change an exposed view predicate column illustrates the CHECK OPTION behavior:

CREATE OR REPLACE VIEW dept10_emp AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

Then:

UPDATE dept10_emp
SET department_id = 20
WHERE employee_id = 101;

is rejected because the resulting row would violate the view condition.

41. Simple View Cheat Sheet

CREATE VIEW
    ↓
Create view

CREATE OR REPLACE VIEW
    ↓
Replace definition

SELECT FROM view
    ↓
Read view

UPDATE view
    ↓
Possible if updatable

INSERT INTO view
    ↓
Possible if insertable

DELETE FROM view
    ↓
Possible if deletable

WITH CHECK OPTION
    ↓
DML must satisfy view condition

WITH READ ONLY
    ↓
No DML through view

DROP VIEW
    ↓
Remove view only

The Key Concept to Remember

SIMPLE VIEW
       ↓
Usually one table
       ↓
Direct base columns
       ↓
No GROUP BY / aggregate
       ↓
No DISTINCT / UNION
       ↓
Often directly updatable

Interview One-Liner: A simple view in Oracle is a view based on a single table with a straightforward column mapping, generally without joins, aggregation, GROUP BY, DISTINCT, or set operators, and it is often directly updatable.

Home

Oracle Simple View FAQs with Examples

1. What is a simple view?

2. Why is it called a simple view?

3. What is the syntax for creating a simple view?

4. Does a simple view store data?

5. Can a simple view contain a WHERE clause?

6. Can we update a simple view?

7. Can we insert through a simple view?

8. Can we delete through a simple view?

9. Why are simple views often updatable?

10. Can a simple view contain a WHERE condition and still be updated?

11. What is WITH CHECK OPTION in a simple view?

12. What happens without WITH CHECK OPTION?

13. Can a simple view contain calculated columns?

14. Can a simple view contain DISTINCT?

15. Can a simple view contain GROUP BY?

16. Can a simple view contain aggregate functions?

17. Can a simple view contain a JOIN?

18. Simple View vs Complex View

19. Can we create a read-only simple view?

20. WITH CHECK OPTION vs WITH READ ONLY

21. Can a simple view hide columns?

22. Can we rename columns in a simple view?

23. Can we explicitly specify view column names?

24. What happens if we insert into a simple view that doesn't contain all base-table columns?

25. Can a simple view be used for security?

26. How do I find simple views in my schema?

27. What is USER_UPDATABLE_COLUMNS?

28. What is the difference between a simple view and a table?

29. What is the difference between a simple view and a materialized view?

30. What happens when the underlying table changes?

31. Does a simple view need to be refreshed?

32. Can we create a simple view using CREATE OR REPLACE?

33. Does replacing a simple view delete table data?

34. How do I drop a simple view?

35. Can a simple view be used in another query?

36. Can we create a view based on a view?

37. Can a simple view contain a subquery?

38. Can we use ORDER BY in a simple view?

39. What are the most common simple-view interview questions?

40. Simple View Complete Example

41. Simple View Cheat Sheet

The Key Concept to Remember

No comments:

Post a Comment