Oracle Views FAQs with Examples

An Oracle view is a virtual table based on the result of a SQL query.

A normal view generally does not store the query result as separate data. Instead, Oracle stores the view definition, and when you query the view, Oracle retrieves the underlying data.

Think:

Base Tables
↓
SELECT
↓
VIEW
↓
User queries the VIEW

1. What is a view in Oracle?

A view is a named SQL query that behaves like a table when queried.

Example:

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

Now you can query:

SELECT *
FROM emp_details;

Instead of repeatedly writing:

SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees;

2. Does a view store data?

For a normal view, generally no.

The view stores the SQL definition:

VIEW
↓
SQL query definition
↓
Underlying table data

For example:

CREATE VIEW emp_view AS
SELECT employee_id, salary
FROM employees;

The rows are still stored in:

EMPLOYEES

not separately in:

EMP_VIEW

Important distinction

VIEW
→ virtual table

MATERIALIZED VIEW
→ stores query result physically

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

CREATE [OR REPLACE] VIEW view_name AS
SELECT ...
FROM ...;

Example:

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

Query:

SELECT *
FROM high_salary_emp;

4. Why do we use views?

Common reasons include:

5. Simplify complex SQL

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

Instead of repeatedly writing the join:

SELECT *
FROM emp_dept_details;

6. Security

You can expose only selected columns:

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

Users don't need direct access to every column in EMPLOYEES.

7. Hide implementation details

Users can work with:

EMP_DEPT_DETAILS

without knowing the underlying joins.

8. Can a view contain a WHERE clause?

Yes.

Example:

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

Then:

SELECT *
FROM active_employees;

Only active employees are returned.

9. Can a view contain a JOIN?

Yes.

Example:

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

Query:

SELECT *
FROM employee_department;

10. Can a view contain multiple tables?

Yes.

Example:

CREATE VIEW employee_details AS
SELECT e.employee_id,
       e.first_name,
       d.department_name,
       l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

A view can be based on complex queries involving multiple tables.

11. Can a view contain aggregate functions?

Yes.

For example:

CREATE VIEW dept_salary_summary AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Query:

SELECT *
FROM dept_salary_summary;

Result conceptually:

DEPARTMENT_ID  EMPLOYEE_COUNT  TOTAL_SALARY  AVERAGE_SALARY
-------------  --------------  ------------  --------------
10             5               300000        60000
20             8               600000        75000
30             4               280000        70000

12. Can a view contain GROUP BY?

Yes.

Example:

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

However, views containing grouping and aggregation are generally not directly updatable.

13. Can a view contain ORDER BY?

Oracle has restrictions depending on the query and usage. In general, you should not rely on a view's row order.

For example, rather than assuming:

SELECT *
FROM emp_view;

will return rows in a particular order, use:

SELECT *
FROM emp_view
ORDER BY salary DESC;

Interview point

A view does not guarantee the ordering of rows.

The ORDER BY should normally be specified in the query that consumes the view when ordering matters.

14. What is a simple view?

A simple view is generally based on a single table and doesn't contain features such as aggregation or grouping that prevent straightforward row modification.

Example:

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

This is a typical simple view.

15. What is a complex view?

A complex view generally contains things such as:

  • Multiple tables
  • Joins
  • Aggregate functions
  • GROUP BY
  • DISTINCT
  • Calculated expressions

Example:

CREATE VIEW emp_dept_summary AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id,
         d.department_name;

This is a complex view.

16. What is the difference between simple and complex views?

Feature Simple View Complex View
Usually based on one table ✅ Not necessarily
Multiple tables Usually no ✅
JOIN Usually no ✅
GROUP BY Usually no ✅
Aggregate functions Usually no ✅
DISTINCT Usually no ✅
Often directly updatable ✅ Often ❌

The exact updatability rules depend on the view definition.

17. Can we INSERT into a view?

Sometimes.

Example:

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

If the view is updatable, you may be able to:

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

The insert affects the underlying table:

INSERT INTO VIEW
↓
Underlying EMPLOYEES table

18. Can we UPDATE a view?

Yes, if the view is updatable.

Example:

UPDATE emp_view
SET salary = 60000
WHERE employee_id = 101;

The underlying table is modified.

Conceptually:

UPDATE VIEW
↓
EMPLOYEES
↓
Row updated

19. Can we DELETE from a view?

Yes, if the view is deletable.

Example:

DELETE FROM emp_view
WHERE employee_id = 101;

The corresponding row is deleted from the underlying table.

20. When is a view not directly updatable?

Views containing certain constructs generally aren't directly modifiable.

Common examples include:

  • GROUP BY
  • DISTINCT
  • Aggregate functions
  • UNION
  • UNION ALL
  • Set operators

For example:

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

This isn't a normal row-by-row updatable view.

You cannot simply do:

UPDATE dept_summary
SET employee_count = 10;

because employee_count is derived from an aggregate.

21. What is WITH CHECK OPTION?

WITH CHECK OPTION ensures that DML performed through a view doesn't create rows that fall outside the view's defining condition.

Example:

CREATE VIEW high_salary_emp AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary >= 100000
WITH CHECK OPTION;

Now:

UPDATE high_salary_emp
SET salary = 50000
WHERE employee_id = 101;

Oracle rejects the operation because the resulting row would no longer satisfy:

salary >= 100000

Without WITH CHECK OPTION, an update through an updatable view can potentially move a row outside the view's result set.

22. What is WITH READ ONLY?

WITH READ ONLY prevents DML through the view.

Example:

CREATE VIEW emp_read_only AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

You can:

SELECT *
FROM emp_read_only;

But DML through the view is prohibited:

UPDATE emp_read_only
SET salary = 60000
WHERE employee_id = 101;

The view is intended for querying only.

23. WITH CHECK OPTION vs WITH READ ONLY

Feature WITH CHECK OPTION WITH READ ONLY
SELECT ✅ ✅
INSERT Can be allowed ❌
UPDATE Can be allowed ❌
DELETE Can be allowed ❌
Enforces view condition ✅ N/A
Prevents all DML ❌ ✅

Remember:

CHECK OPTION → DML allowed, but must obey view condition

READ ONLY → No DML through view

24. What is CREATE OR REPLACE VIEW?

It allows you to replace the existing view definition.

Example:

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

If EMP_VIEW already exists, Oracle replaces its definition.

This is commonly used when modifying a view.

25. Does replacing a view delete the underlying table data?

No.

For example:

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

Replacing the view changes the view definition, not the underlying EMPLOYEES data.

26. Can a view reference another view?

Yes.

Example:

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

Then:

CREATE VIEW high_paid_active_employees AS
SELECT employee_id,
       first_name,
       salary
FROM active_employees
WHERE salary > 100000;

So:

EMPLOYEES
↓
ACTIVE_EMPLOYEES
↓
HIGH_PAID_ACTIVE_EMPLOYEES

However, excessive layers of views can make SQL harder to understand and optimize.

27. Can a view reference a materialized view?

Yes.

A view can use a materialized view as one of its underlying objects, provided the necessary privileges and dependencies are satisfied.

Example:

CREATE VIEW sales_report AS
SELECT *
FROM mv_sales_summary;

28. Can a view have calculated columns?

Yes.

Example:

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

Query:

SELECT *
FROM emp_salary_details;

Result:

EMPLOYEE_ID  FIRST_NAME  SALARY  ANNUAL_SALARY
-----------  ----------  ------  -------------
101          John        50000   600000
102          Mary        60000   720000

ANNUAL_SALARY is derived from the underlying data.

29. Can a view hide sensitive columns?

Yes. This is a common security use case.

Suppose:

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

Create:

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

Users can be granted access to the view instead of the base table.

GRANT SELECT ON employee_public TO reporting_user;

This allows controlled exposure of columns.

30. Does creating a view automatically give users access to the underlying tables?

No.

The owner of the view needs appropriate privileges on referenced objects, and other users need privileges on the view.

For example:

GRANT SELECT ON employee_public TO reporting_user;

The user can query:

SELECT *
FROM employee_public;

without necessarily receiving direct SELECT access to EMPLOYEES.

31. What happens if the underlying table is dropped?

The view's dependency on the table is broken.

For example:

DROP TABLE employees;

A view depending on EMPLOYEES becomes invalid.

You can check object status using:

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE object_type = 'VIEW';

32. What happens if a column used by a view is changed?

The view can become invalid depending on the type of change.

For example:

CREATE VIEW emp_view AS
SELECT employee_id,
       salary
FROM employees;

If the underlying structure changes incompatibly, Oracle may invalidate the view.

Check:

SELECT object_name,
       status
FROM user_objects
WHERE object_type = 'VIEW';

33. How can I see all views in my schema?

Use:

SELECT view_name
FROM user_views;

For example:

SELECT view_name
FROM user_views
ORDER BY view_name;

34. How can I see the definition of a view?

Use:

SELECT view_name,
       text
FROM user_views
WHERE view_name = 'EMP_VIEW';

You can also inspect the source through Oracle's dictionary views depending on your privileges and needs.

35. What is USER_VIEWS?

USER_VIEWS contains information about views owned by the current user.

Example:

SELECT view_name
FROM user_views;

Common columns include information about:

VIEW_NAME
TEXT

36. What is the difference between USER_VIEWS, ALL_VIEWS, and DBA_VIEWS?

View Shows
USER_VIEWS Views owned by current user
ALL_VIEWS Views accessible to current user
DBA_VIEWS Views in the database, requiring appropriate privileges

Example:

SELECT view_name
FROM user_views;

37. How do I rename a view?

Oracle supports:

RENAME old_view TO new_view;

Example:

RENAME emp_view TO employee_view;

However, be careful with dependent objects, application code, synonyms, and grants.

38. How do I drop a view?

Use:

DROP VIEW emp_view;

This removes the view definition.

It does not delete data from the underlying table.

Example:

DROP VIEW
↓
VIEW removed

EMPLOYEES
↓
Data remains

39. What is the difference between DROP VIEW and DROP TABLE?

Drop view

DROP VIEW emp_view;

Removes:

VIEW

but leaves:

EMPLOYEES

Drop table

DROP TABLE employees;

Removes the underlying table and its data, subject to Oracle's DDL behavior and dependencies.

40. Can a view contain a subquery?

Yes.

Example:

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

This displays employees earning above the average salary.

41. Can a view use UNION?

Yes.

Example:

CREATE VIEW all_customers AS
SELECT customer_id,
       customer_name
FROM domestic_customers

UNION

SELECT customer_id,
       customer_name
FROM international_customers;

Such a view is generally not directly updatable because of the set operation.

42. Can a view contain DISTINCT?

Yes.

Example:

CREATE VIEW employee_departments AS
SELECT DISTINCT department_id
FROM employees;

Query:

SELECT *
FROM employee_departments;

Because DISTINCT changes the row mapping between the view and base table, such a view is generally not directly updatable.

43. Can we create an index on a normal view?

No.

You cannot normally create an index directly on a conventional view.

For example:

CREATE INDEX idx_emp_view
ON emp_view(salary);

is not how normal views are indexed.

Instead, indexes belong to the underlying tables.

For example:

CREATE INDEX idx_emp_salary
ON employees(salary);

Important distinction

VIEW
↓
No stored rows
↓
No normal index on the view itself

MATERIALIZED VIEW
↓
Stores rows
↓
Can have indexes

44. Does a view improve query performance?

Not automatically.

A normal view is primarily a logical abstraction.

For example:

CREATE VIEW emp_view AS
SELECT *
FROM employees
WHERE department_id = 10;

Then:

SELECT *
FROM emp_view;

doesn't inherently make the query faster just because it uses a view.

Oracle's optimizer can often merge or transform view queries during optimization.

For performance, consider:

  • Appropriate indexes
  • Good SQL
  • Statistics
  • Partitioning
  • Materialized views where appropriate

45. View vs Materialized View

This is a very common interview question.

Feature View Materialized View
Stores query definition ✅ ✅
Stores query result ❌ Generally no ✅
Data physically stored ❌ ✅
Needs refresh ❌ ✅
Can use FAST refresh ❌ ✅
Can improve reporting performance Sometimes Often
Can have indexes on stored result ❌ ✅

Think:

VIEW
Query → underlying tables → result

MATERIALIZED VIEW
Query → stored result
↓
refresh

46. View vs Table

Feature Table View
Stores data ✅ ❌ Normally
Stores query definition ❌ ✅
Can contain joins Data structure doesn't ✅
Can hide columns Via privileges ✅
Can be queried with SELECT ✅ ✅
Can always be updated Depends on constraints Depends on view
Requires refresh ❌ ❌

47. What is an inline view?

An inline view is a subquery in the FROM clause.

Example:

SELECT *
FROM (
    SELECT employee_id,
           first_name,
           salary
    FROM employees
    WHERE salary > 50000
);

The inner query is an inline view.

Important distinction:

CREATE VIEW
↓
Database object

Inline view
↓
Subquery used inside SQL statement

48. What is an updatable view?

An updatable view is a view through which Oracle can perform DML against the underlying table.

Example:

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

Then potentially:

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

The underlying EMPLOYEES row is updated.

49. What is a read-only view?

Example:

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

You can:

SELECT *
FROM emp_readonly;

But not:

DELETE FROM emp_readonly;

or:

UPDATE emp_readonly
SET salary = 50000;

50. What is a view with WITH CHECK OPTION?

Example:

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

Now:

UPDATE sales_emp
SET department_id = 20
WHERE employee_id = 101;

is rejected because the updated row would no longer satisfy:

department_id = 10

Think:

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

51. What is a force view?

Oracle allows:

CREATE FORCE VIEW my_view AS
SELECT *
FROM future_table;

FORCE tells Oracle to create the view even if referenced objects aren't currently valid/available, subject to Oracle's rules.

Example:

CREATE FORCE VIEW emp_future AS
SELECT employee_id
FROM employees_future;

If EMPLOYEES_FUTURE doesn't exist, the view can be created as an invalid object.

Later, after the dependency is created:

CREATE TABLE employees_future (
    employee_id NUMBER
);

the view can potentially be revalidated.

Compare:

CREATE VIEW
↓
Referenced objects must be valid

CREATE FORCE VIEW
↓
Allow creation even when dependencies aren't currently valid

52. What is NO FORCE?

NO FORCE is the default behavior for CREATE VIEW.

Example:

CREATE NO FORCE VIEW emp_view AS
SELECT *
FROM employees;

Oracle requires the referenced objects to be valid enough for the view to be created.

53. What is a common security use case for views?

Suppose a table contains:

EMPLOYEES
----------------
EMPLOYEE_ID
NAME
SALARY
SSN
BANK_ACCOUNT

You want reporting users to see:

EMPLOYEE_ID
NAME
DEPARTMENT

Create:

CREATE VIEW employee_report AS
SELECT employee_id,
       first_name,
       department_id
FROM employees;

Then:

GRANT SELECT
ON employee_report
TO reporting_user;

The user can query:

SELECT *
FROM employee_report;

This gives you a controlled interface over the underlying table.

54. What happens if the underlying table has new columns?

Suppose:

CREATE VIEW emp_view AS
SELECT *
FROM employees;

Later:

ALTER TABLE employees
ADD email VARCHAR2(200);

Do not assume that an existing SELECT * view automatically behaves exactly like a newly created SELECT * query.

A key best practice is:

Explicitly specify columns in production views.

Prefer:

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

instead of relying on:

SELECT *

This makes the view definition clearer and more stable.

55. Can we create a view with column aliases?

Yes.

Example:

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

Query:

SELECT *
FROM employee_info;

Result columns:

EMP_ID
EMPLOYEE_NAME
MONTHLY_SALARY

56. Can we explicitly specify view column names?

Yes.

Example:

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

Now the view columns are:

EMP_ID
EMPLOYEE_NAME
MONTHLY_SALARY

57. Can a view have constraints?

A normal view doesn't have table-style storage constraints such as primary keys in the same sense as a base table.

However, Oracle supports certain view constraints for query optimization and integrity-related metadata in appropriate contexts.

For most interview purposes, remember:

Base table → actual data + table constraints

View → logical query result

58. Can we create a synonym for a view?

Yes.

Example:

CREATE SYNONYM emp
FOR employee_view;

Then:

SELECT *
FROM emp;

The synonym points to the view.

59. Can a view be used in a stored procedure?

Yes.

Example:

CREATE OR REPLACE PROCEDURE show_employees
IS
BEGIN
    FOR r IN (
        SELECT employee_id,
               first_name
        FROM emp_view
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            r.employee_id || ' ' || r.first_name
        );
    END LOOP;
END;
/

A view can be used anywhere an appropriate table/query source can be used.

60. Can a view be used in a trigger?

Yes.

For example, a trigger can query a suitable view, subject to the usual trigger restrictions.

However, be careful with dependencies and mutating-table situations when the view ultimately references the table currently being modified.

61. Can a view be used with another view in a JOIN?

Yes.

Example:

SELECT e.employee_id,
       e.first_name,
       d.department_name
FROM employee_view e
JOIN department_view d
ON e.department_id = d.department_id;

Oracle's optimizer can often transform the resulting query.

62. What happens when a view becomes INVALID?

Check:

SELECT object_name,
       status
FROM user_objects
WHERE object_type = 'VIEW';

If:

STATUS
------
INVALID

the underlying dependency has changed or become unavailable.

Oracle may recompile the view when appropriate, or you can explicitly compile it:

ALTER VIEW emp_view COMPILE;

Then check again:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

63. How do I compile an invalid view?

Use:

ALTER VIEW emp_view COMPILE;

Then:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

64. What are common Oracle View interview traps?

Trap 1

Does a normal view store data?

❌ Generally no.

Trap 2

Can every view be updated?

❌ No.
Updatability depends on the view definition.

Trap 3

Can a view have a JOIN?

✅ Yes.

Trap 4

Can a view have GROUP BY?

✅ Yes.
But such a view is generally not directly updatable.

Trap 5

Can a view have an index?

❌ A normal view doesn't have indexes like a table.
A materialized view can have indexes because it stores data.

Trap 6

Does a view improve performance automatically?

❌ No.
A view is primarily a logical abstraction.

Trap 7

Does dropping a view delete the base-table data?

❌ No.

Trap 8

What does WITH CHECK OPTION do?

It ensures DML through an updatable view doesn't create a row that violates the view's defining condition.

Trap 9

What does WITH READ ONLY do?

Prevents DML through the view.

Trap 10

View vs Materialized View?

VIEW
→ virtual/logical result

MATERIALIZED VIEW
→ physically stored result that can be refreshed

65. Simple real-world example

Suppose you have:

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

Create a view for active employees:

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

Query:

SELECT *
FROM active_employee_view;

Create a more restricted version:

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

Now the view provides:

EMPLOYEES
|
+----------------------+
|                      |
↓                      ↓
ACTIVE_EMPLOYEE_VIEW   ACTIVE_EMPLOYEE_PUBLIC
|                      |
↓                      ↓
Internal users         Restricted users

66. View vs Materialized View vs Table

Feature Table View Materialized View
Stores data ✅ ❌ ✅
Stores query definition ❌ ✅ ✅
Virtual result ❌ ✅ ❌
Refresh required ❌ ❌ ✅
Can use FAST refresh ❌ ❌ ✅
Can have indexes ✅ ❌ ✅
Good for security abstraction Possible ✅ Excellent Possible
Good for precomputed reporting ❌ ❌ ✅

67. Final Oracle View Cheat Sheet

CREATE VIEW
↓
Create virtual table

CREATE OR REPLACE VIEW
↓
Replace view definition

WITH READ ONLY
↓
No DML through view

WITH CHECK OPTION
↓
DML must keep row within view condition

CREATE FORCE VIEW
↓
Allow creation when dependencies aren't currently valid

DROP VIEW
↓
Remove view, not base-table data

ALTER VIEW ... COMPILE
↓
Recompile invalid view

Most important commands

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

-- Query
SELECT *
FROM emp_view;

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

-- Read-only
CREATE VIEW emp_readonly AS
SELECT employee_id, first_name, salary
FROM employees
WITH READ ONLY;

-- Check option
CREATE VIEW high_salary_emp AS
SELECT employee_id, first_name, salary
FROM employees
WHERE salary >= 100000
WITH CHECK OPTION;

-- Compile
ALTER VIEW emp_view COMPILE;

-- Drop
DROP VIEW emp_view;

One-line interview answer

An Oracle view is a named, stored SQL query that presents data from one or more underlying objects as a virtual table, primarily used for abstraction, security, and simplifying complex queries.

Ultimate memory trick

VIEW
↓
Stores query, not normally the result
↓
Acts like a virtual table
↓
Can hide complexity / columns
↓
May be updatable depending on definition

And remember:

VIEW              → virtual result
MATERIALIZED VIEW  → stored result
TABLE              → stored base data
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)