👁️ Oracle SQL Views: Create, Drop & Update

👁️ Oracle SQL Views: CREATE, DROP, and Updatable Views Tutorial

#OracleSQL #Views #CreateView #DropView #UpdatableViews #SQLTutorial #SQLLab

Views in Oracle SQL are virtual tables based on the result set of a query. They provide a way to simplify complex queries, improve security by restricting access, and allow updating data through updatable views.

🛠️ 1. CREATE VIEW

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW EMP_SALARY_VIEW AS
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 50000;

Explanation:

This view shows employees whose salary is greater than 50,000. It acts like a table you can query.

🗑️ 2. DROP VIEW

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW EMP_SALARY_VIEW;

Explanation:

This command deletes the view from the database. It does not affect the underlying tables.

✏️ 3. Updatable Views

Some views can be updated, which means you can perform INSERT, UPDATE, or DELETE operations on the view, and those changes affect the base table.

Conditions for Updatable Views:

• The view must be based on a single table
• The view must include all NOT NULL columns without default values
• Cannot use GROUP BY, DISTINCT, aggregates, or joins

Example:

CREATE VIEW EMP_SALARY_UPD AS
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE DEPT_ID = 10;

Update through view:

UPDATE EMP_SALARY_UPD
SET SALARY = SALARY + 5000
WHERE EMP_ID = 101;

This updates the SALARY in the EMPLOYEE table for EMP_ID 101.

Non-Updatable View Example:

CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_NAME
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.DEPT_ID = D.DEPT_ID;

This view involves a join, so it is not updatable by default.

---

📘 Views simplify queries and improve security. Use updatable views to make your applications flexible!