SQL Views
Learn to create and manage virtual tables to simplify complex queries, restrict data access, and maintain data consistency.
👓 Topic 1: SQL Views (Virtual Tables)
A **View** is a virtual table whose content is defined by a stored **SQL query**. Unlike regular tables, a view does not hold its own data; it executes the underlying query whenever the view is accessed.
Views are essential for three reasons: **Security** (hiding sensitive columns like salary), **Simplification** (hiding complex joins), and **Consistency** (providing a standardized interface for complex calculations).
[Image of sql view base table relationship]🛠️ Topic 2: Creating and Managing Views
Views are created using DDL commands and require the AS keyword to embed the defining SELECT statement.
📝 CREATE VIEW Syntax:
CREATE VIEW ViewName AS SELECT column1, column2 FROM BaseTable WHERE condition;
💻 Example: Security View (Hiding Salary)
-- Creates a view that joins three tables but only exposes names and projectsCREATE VIEW Public_Info_V ASSELECT E.FirstName, D.Location, P.ProjectNameFROM Employees EJOIN Departments D ON E.DeptID = D.IDLEFT JOIN Projects P ON E.ProjectID = P.ID;
-- To update the view logic: Use CREATE OR REPLACE VIEW-- To delete the view: DROP VIEW Public_Info_V;
🔄 Topic 3: The Danger: Updatable Views
A view is generally **NON-UPDATABLE** if it hides data complexity. If a view contains any of the following, DML commands (`INSERT`, `UPDATE`, `DELETE`) will fail when executed against the view:
🚫 Non-Updatable Conditions:
- Aggregations: Views using `SUM()`, `AVG()`, `GROUP BY`, or `HAVING`.
- Multiple Tables: Views based on `JOIN` operations (especially for
INSERT). - Calculated Fields: Columns derived from functions (e.g., `Age * 12`).
- Distinct: Views using the `DISTINCT` keyword.
Rule of Thumb: A view is only reliably updatable if it selects directly from a **single base table** and includes all the base table's NOT NULL and PRIMARY KEY columns.
🛡️ Topic 4: The WITH CHECK OPTION
The **WITH CHECK OPTION** is a crucial security feature that ensures any data modification made through the view **must adhere to the view's WHERE clause**.
Without this clause, a user querying a view that filters for `Salary < 60000` could successfully update their salary to `70000`. The record would then **vanish** from the view, violating the view's intent.
💻 Example: Enforcing View Integrity
-- Create a View that only shows Junior Employees (Salary < 60000)CREATE VIEW Junior_Employees_V ASSELECT EmployeeID, Salary FROM EmployeesWHERE Salary < 60000WITH CHECK OPTION;
-- If user tries to run: UPDATE Junior_Employees_V SET Salary = 70000...-- The database will REJECT the update.
📚 Module Summary
- View: A virtual table used for security, showing only filtered or simplified data.
- CREATE OR REPLACE: Standard way to update the view definition.
- Updatability: Views with Joins or Aggregations are typically read-only.
- WITH CHECK OPTION: Enforces the view's
WHEREclause on DML operations, preventing records from disappearing.
🤔 Interview Q&A
Tap on the questions below to reveal the answers.