A complex view is a view whose definition contains SQL features that make it more than a simple one-table projection. Common examples include joins, aggregate functions, GROUP BY, DISTINCT, set operators, and expressions.
Complex views are commonly used for reporting, data summarization, combining tables, and presenting business-specific data.
1. What is a complex view?
A complex view is generally a view based on:
- Multiple tables
- JOIN
- Aggregate functions such as SUM, AVG, COUNT
- GROUP BY
- DISTINCT
- Set operators such as UNION
- More complicated expressions or subqueries
Example:
CREATE OR REPLACE VIEW emp_dept_view 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;This is a complex view because it combines:
EMPLOYEES
+
DEPARTMENTS
↓
JOIN
↓
EMP_DEPT_VIEW2. What is the difference between a simple view and a complex view?
| Feature | Simple View | Complex View |
|---|---|---|
| Usually one table | ✅ | Not necessarily |
| Multiple tables | ❌ Typically | ✅ |
| JOIN | ❌ Typically | ✅ |
| GROUP BY | ❌ | ✅ |
| Aggregate functions | ❌ | ✅ |
| DISTINCT | ❌ | ✅ |
| Set operators | ❌ | ✅ |
| Reporting use | Sometimes | Very common |
| Directly updatable | Often | Often not |
Easy way to remember:
Simple View
↓
One table
↓
Straightforward data
Complex View
↓
Multiple tables / calculations / aggregation
↓
Reporting or business logic3. Why do we use complex views?
Complex views are useful when you want to hide complicated SQL from users.
Instead of users writing:
SELECT e.employee_id,
e.first_name,
d.department_name,
e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 50000;you can create:
CREATE OR REPLACE VIEW high_paid_emp_dept AS
SELECT e.employee_id,
e.first_name,
d.department_name,
e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 50000;Users can simply run:
SELECT *
FROM high_paid_emp_dept;4. Can a complex view use multiple tables?
Yes. This is one of the most common examples.
CREATE OR REPLACE 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;Now:
SELECT *
FROM emp_dept_details;might produce:
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_NAME
----------- ---------- ------ ---------------
101 JOHN 60000 IT
102 SMITH 70000 HR
103 JANE 80000 SALES5. Can a complex view use GROUP BY?
Yes.
CREATE OR REPLACE 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;Example result:
DEPARTMENT_ID EMPLOYEE_COUNT TOTAL_SALARY AVERAGE_SALARY
------------- -------------- ------------ --------------
10 5 350000 70000
20 8 640000 80000
30 4 300000 75000This is clearly a complex view because it summarizes multiple rows into groups.
6. Can a complex view use aggregate functions?
Yes.
Common aggregate functions include:
COUNT()
SUM()
AVG()
MIN()
MAX()Example:
CREATE OR REPLACE VIEW dept_salary AS
SELECT department_id,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;7. Can a complex view use DISTINCT?
Yes.
CREATE OR REPLACE VIEW employee_departments AS
SELECT DISTINCT department_id
FROM employees;Query:
SELECT *
FROM employee_departments;The DISTINCT removes duplicate department IDs.
Because the result doesn't necessarily correspond one-to-one with individual base-table rows, this type of view is generally not directly updatable.
8. Can a complex view use UNION?
Yes.
CREATE OR REPLACE VIEW all_people AS
SELECT employee_id AS person_id,
first_name AS person_name
FROM employees
UNION
SELECT customer_id AS person_id,
customer_name AS person_name
FROM customers;Now:
SELECT *
FROM all_people;combines data from two sources.
EMPLOYEES
↓
SELECT
↓
UNION
↑
SELECT
↑
CUSTOMERS9. Can a complex view use UNION ALL?
Yes.
CREATE OR REPLACE VIEW all_people AS
SELECT employee_id AS person_id,
first_name AS person_name
FROM employees
UNION ALL
SELECT customer_id AS person_id,
customer_name AS person_name
FROM customers;Difference:
UNION
→ removes duplicates
UNION ALL
→ keeps duplicates10. Can a complex view use calculated columns?
Yes.
CREATE OR REPLACE VIEW employee_salary_details AS
SELECT employee_id,
first_name,
salary,
salary * 12 AS annual_salary,
salary * 12 * 0.10 AS estimated_bonus
FROM employees;Query:
SELECT *
FROM employee_salary_details;Possible output:
EMPLOYEE_ID FIRST_NAME SALARY ANNUAL_SALARY ESTIMATED_BONUS
----------- ---------- ------ ------------- ---------------
101 JOHN 50000 600000 60000The calculated columns are expressions rather than stored base-table columns.
11. Are complex views updatable?
Generally, complex views are not directly updatable.
For example:
CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;Trying:
UPDATE dept_summary
SET average_salary = 70000
WHERE department_id = 10;doesn't make sense as a normal direct update because AVERAGE_SALARY is calculated from multiple employee rows.
Oracle therefore doesn't treat this as a normal directly updatable view.
12. Why are aggregate views generally not updatable?
Consider:
SELECT department_id,
AVG(salary)
FROM employees
GROUP BY department_id;Suppose department 10 has:
Employee A → 50,000
Employee B → 70,000
Employee C → 90,000The view contains:
Department 10 → 70,000 averageIf someone says:
UPDATE view
SET average_salary = 80000;which employee should Oracle change?
Employee A? Employee B? Employee C? All three?
There isn't a unique row mapping. That's why aggregate views are generally not directly updatable.
13. Can a complex view contain a JOIN and still be updatable?
Some join views can be partially updatable, depending on the view definition and which table/columns are being modified.
CREATE OR REPLACE VIEW emp_dept_view 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;The view combines two tables, so you shouldn't assume that every DML operation against it will work.
For interview purposes:
A join view is a complex view, and its DML capabilities depend on Oracle's updatability rules.
14. What is a key-preserved table?
This is an important Oracle interview concept for complex views.
A table is key-preserved when each row from that table can appear at most once in the result of the join.
Example:
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;If DEPARTMENTS.DEPARTMENT_ID is unique/primary key, each employee can match only one department.
Therefore, EMPLOYEES can potentially be key-preserved in the join.
15. Why is key preservation important?
It helps Oracle determine whether an update can be mapped unambiguously to rows in an underlying table.
For example:
UPDATE emp_dept_view
SET salary = salary + 1000
WHERE employee_id = 101;If Oracle can uniquely identify the corresponding EMPLOYEES row, updating that column may be possible.
But attempting to modify columns from a non-key-preserved table can produce errors such as:
ORA-01779:
cannot modify a column which maps to a non key-preserved table16. Can a complex view contain subqueries?
Yes.
CREATE OR REPLACE VIEW above_avg_employees AS
SELECT employee_id,
first_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);Query:
SELECT *
FROM above_avg_employees;This returns employees whose salary is above the company-wide average.
17. Can a complex view contain a HAVING clause?
Yes.
CREATE OR REPLACE VIEW large_departments AS
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;Now:
SELECT *
FROM large_departments;returns only departments with more than 10 employees.
18. Can a complex view use multiple aggregate functions?
Yes.
CREATE OR REPLACE VIEW dept_statistics AS
SELECT department_id,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;This is a common reporting view.
19. Can a complex view contain CASE expressions?
Yes.
CREATE OR REPLACE VIEW employee_salary_category AS
SELECT employee_id,
first_name,
salary,
CASE
WHEN salary >= 100000 THEN 'HIGH'
WHEN salary >= 50000 THEN 'MEDIUM'
ELSE 'LOW'
END AS salary_category
FROM employees;Query:
SELECT *
FROM employee_salary_category;Example:
EMPLOYEE_ID FIRST_NAME SALARY SALARY_CATEGORY
----------- ---------- ------ ---------------
101 JOHN 120000 HIGH
102 SMITH 70000 MEDIUM
103 JANE 40000 LOW20. Can a complex view contain functions?
Yes.
CREATE OR REPLACE VIEW employee_display AS
SELECT employee_id,
UPPER(first_name) AS employee_name,
ROUND(salary, -3) AS rounded_salary
FROM employees;You can use Oracle functions such as:
UPPER()
LOWER()
ROUND()
TRUNC()
NVL()
COALESCE()
CASEand many others.
21. Can a complex view use multiple joins?
Yes.
CREATE OR REPLACE VIEW employee_full_details AS
SELECT e.employee_id,
e.first_name,
d.department_name,
l.city,
l.country
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;Conceptually:
EMPLOYEES
|
+---- DEPARTMENTS
|
+---- LOCATIONS
|
↓
EMPLOYEE_FULL_DETAILSThis is a typical complex reporting view.
22. Can a complex view use outer joins?
Yes.
CREATE OR REPLACE VIEW emp_dept_details AS
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;The LEFT JOIN ensures that employees without a matching department can still appear.
23. Can a complex view use INNER JOIN?
Yes.
CREATE OR REPLACE VIEW emp_dept_details AS
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;Only employees with matching departments appear.
24. Can a complex view use ORDER BY?
Oracle allows ORDER BY in certain view definitions depending on the query and Oracle version/context, but you should not rely on a view to guarantee output ordering.
Prefer:
SELECT *
FROM emp_dept_details
ORDER BY first_name;The outermost query should specify the desired ordering.
25. Can a complex view be read-only?
Yes.
CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;Now users can:
SELECT *
FROM dept_summary;but cannot perform DML through the view.
26. What is WITH READ ONLY?
It explicitly makes the view read-only.
CREATE OR REPLACE VIEW employee_report AS
SELECT e.employee_id,
e.first_name,
d.department_name,
e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WITH READ ONLY;Attempting:
UPDATE employee_report
SET salary = 60000
WHERE employee_id = 101;will fail.
This is useful for reporting views.
27. What is WITH CHECK OPTION?
WITH CHECK OPTION ensures that rows modified through a view continue to satisfy the view's WHERE condition.
For example:
CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
first_name,
salary,
department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;This:
UPDATE dept10_employees
SET department_id = 20
WHERE employee_id = 101;is rejected because the resulting row would no longer belong to department 10.
However, WITH CHECK OPTION doesn't magically make a complex aggregate view updatable.
28. Can we insert into a complex view?
Usually not directly, especially when the view contains:
- GROUP BY
- Aggregate functions
- DISTINCT
- Set operators
- Certain joins
- Other constructs that prevent unambiguous mapping
For example:
CREATE VIEW dept_summary AS
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;This isn't an appropriate target for:
INSERT INTO dept_summary ...29. Can we delete from a complex view?
Again, it depends on the view definition.
A view containing aggregation such as:
SELECT department_id,
COUNT(*)
FROM employees
GROUP BY department_id;is not directly deletable in the normal sense.
There is no single employee row corresponding to:
Department 10 → COUNT = 2530. How can we make a complex view updatable?
One common solution is an INSTEAD OF trigger.
Suppose we have:
CREATE OR REPLACE VIEW emp_dept_view 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;You can use an INSTEAD OF trigger to control what happens when users perform DML against the view.
Example:
CREATE OR REPLACE TRIGGER trg_emp_dept_update
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
UPDATE employees
SET salary = :NEW.salary
WHERE employee_id = :OLD.employee_id;
END;
/Now:
UPDATE emp_dept_view
SET salary = 75000
WHERE employee_id = 101;causes the trigger to update the underlying EMPLOYEES table.
UPDATE VIEW
↓
INSTEAD OF TRIGGER
↓
UPDATE EMPLOYEES31. What is a reporting view?
A reporting view is a common type of complex view designed to simplify reports.
CREATE OR REPLACE VIEW department_report AS
SELECT d.department_name,
COUNT(e.employee_id) AS employee_count,
NVL(SUM(e.salary), 0) AS total_salary,
NVL(AVG(e.salary), 0) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;Users can simply run:
SELECT *
FROM department_report;Instead of repeatedly writing the complicated query.
32. What is a business-rule view?
A complex view can hide business logic.
Example:
CREATE OR REPLACE VIEW employee_status_report AS
SELECT employee_id,
first_name,
salary,
CASE
WHEN salary >= 100000 THEN 'EXECUTIVE'
WHEN salary >= 75000 THEN 'SENIOR'
WHEN salary >= 50000 THEN 'MID_LEVEL'
ELSE 'JUNIOR'
END AS employee_level
FROM employees;Now applications don't need to repeat the salary classification logic.
33. Can a complex view improve security?
Yes.
Suppose users shouldn't see every column in the underlying tables.
You can expose only the required information:
CREATE OR REPLACE VIEW employee_report AS
SELECT e.employee_id,
e.first_name,
d.department_name,
e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;Then:
GRANT SELECT
ON employee_report
TO reporting_user;The user can access the view without necessarily receiving direct access to all underlying tables/columns.
34. Does a complex view store data?
No.
A normal complex view doesn't store the query result as physical rows.
For example:
CREATE VIEW dept_summary AS
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;The view stores the definition. The result is generated when queried.
Normal Complex View
↓
Query definition
↓
Underlying tables
↓
ResultIf you need a physically stored result, consider a materialized view.
35. Complex view vs materialized view
| Feature | Complex View | Materialized View |
|---|---|---|
| Stores query definition | ✅ | ✅ |
| Stores query result | ❌ | ✅ |
| Refresh required | ❌ | ✅ |
| Can use aggregation | ✅ | ✅ |
| Can use joins | ✅ | ✅ |
| Useful for reporting | ✅ | ✅ |
| Result physically stored | ❌ | ✅ |
Example normal view:
CREATE VIEW dept_summary AS
SELECT department_id,
COUNT(*)
FROM employees
GROUP BY department_id;Materialized view:
CREATE MATERIALIZED VIEW dept_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
COUNT(*)
FROM employees
GROUP BY department_id;36. How do I check view definitions?
Use USER_VIEWS:
SELECT view_name,
text
FROM user_views
ORDER BY view_name;For a particular view:
SELECT text
FROM user_views
WHERE view_name = 'EMP_DEPT_VIEW';37. How do I check whether view columns are updatable?
Oracle provides:
USER_UPDATABLE_COLUMNS
Example:
SELECT table_name,
column_name,
insertable,
updatable,
deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_DEPT_VIEW';This is especially useful when investigating a complex join view.
38. What are common complex-view errors?
One important error is:
ORA-01779:
cannot modify a column which maps to a non key-preserved tableThis can occur when attempting to modify a column through a join view where Oracle cannot uniquely map the view row to a base-table row.
Another common situation is trying to perform DML against an aggregate view:
UPDATE dept_summary
SET employee_count = 10;Such a view isn't normally directly updatable.
39. Can a complex view depend on another view?
Yes.
Example:
CREATE OR REPLACE 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;Then:
CREATE OR REPLACE VIEW emp_report AS
SELECT employee_id,
first_name,
department_name
FROM emp_dept_view
WHERE department_name = 'IT';Dependency chain:
EMPLOYEES
↓
EMP_DEPT_VIEW
↓
EMP_REPORT40. What happens if an underlying table changes?
A normal view doesn't contain a separate stored copy of the data.
If:
UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 101;then querying a view based on EMPLOYEES reflects the changed underlying data.
However, if you change the structure of the underlying tables, the view may become invalid or require recompilation, depending on the change.
You can inspect object status with:
SELECT object_name,
status
FROM user_objects
WHERE object_type = 'VIEW';41. How do you create a complex view step by step?
Step 1: Create the base tables
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(100)
);CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(100),
salary NUMBER,
department_id NUMBER
);Step 2: Create the complex view
CREATE OR REPLACE VIEW department_report AS
SELECT d.department_id,
d.department_name,
COUNT(e.employee_id) AS employee_count,
SUM(e.salary) AS total_salary,
AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id,
d.department_name;Step 3: Query it
SELECT *
FROM department_report;Possible result:
DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEE_COUNT TOTAL_SALARY AVERAGE_SALARY
------------- --------------- -------------- ------------ --------------
10 IT 5 350000 70000
20 HR 3 210000 70000
30 SALES 8 640000 80000This is a classic complex reporting view.
42. Simple View vs Complex View — Interview Example
Simple view
CREATE VIEW emp_view AS
SELECT employee_id,
first_name,
salary
FROM employees;Characteristics:
- One table
- No aggregation
- No GROUP BY
- Direct columns
- Often updatable
Complex view
CREATE VIEW emp_dept_summary AS
SELECT d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS average_salary
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;Characteristics:
- Multiple tables
- JOIN
- COUNT()
- AVG()
- GROUP BY
- Generally read-only for direct DML
43. What are the most common complex-view interview traps?
Trap 1
Can a complex view have a JOIN?
✅ Yes.
Trap 2
Can a complex view have GROUP BY?
✅ Yes.
Trap 3
Can a complex view have aggregate functions?
✅ Yes.
Trap 4
Is every complex view read-only?
❌ Not necessarily.
Some join views can support certain DML operations depending on Oracle's updatability rules.
Trap 5
Are aggregate views normally updatable?
❌ No.
Trap 6
Can an INSTEAD OF trigger make DML possible on a complex view?
✅ Yes.
This is a very important technique.
Trap 7
Does a normal complex view store data?
❌ No.
A normal view stores its query definition, not a materialized copy of the result.
Trap 8
Does a complex view require refresh?
❌ No.
That's a materialized-view concept.
44. Complex View Cheat Sheet
COMPLEX VIEW
|
+------------+------------+
| | |
JOIN GROUP BY DISTINCT
| | |
Multiple Aggregate Remove
tables results duplicates
| | |
+------------+------------+
|
↓
Complex Query
|
↓
VIEW RESULTCommon complex-view components:
- JOIN
- GROUP BY
- HAVING
- COUNT()
- SUM()
- AVG()
- MIN()
- MAX()
- DISTINCT
- UNION
- UNION ALL
- Subqueries
- CASE
- Calculated columns
- Multiple tables
Key Interview Statement
A complex view is a view whose query contains more advanced SQL constructs—such as joins, aggregation, GROUP BY, DISTINCT, or set operators—and is commonly used to simplify reporting and business logic. Unlike a simple view, it is not necessarily directly updatable.
And the most important distinction:
Simple View
↓
Usually one table
↓
Direct row mapping
↓
Often updatable
Complex View
↓
JOIN / GROUP BY / aggregate / DISTINCT / UNION
↓
Result may not map to one base row
↓
Usually not directly updatable
↓
INSTEAD OF trigger can sometimes provide DML behavior