🎯 Objective
Students will be able to implement the concept of views and understand their practical applications in database management systems.
What is a Database View?
Definition
A view is a virtual table based on the result set of an SQL statement. It contains rows and columns, just like a real table, but it doesn't store data physically. Instead, it dynamically retrieves data from one or more base tables.
Key Characteristics
Virtual Table
No physical storage of data
Security
Restrict access to sensitive data
Simplicity
Simplify complex queries
Abstraction
Hide database complexity
Step 1: Creating the EMPLOYEES Table
Table Structure
| Column Name | Data Type | Width | Attributes |
|---|---|---|---|
| Employee_id | Character | 10 | Primary Key (PK) |
| First_Name | Character | 30 | Not Null (NN) |
| Last_Name | Character | 30 | Not Null (NN) |
| DOB | Date | - | - |
| Salary | Number | 25 | Not Null (NN) |
| Department_id | Character | 10 | - |
SQL Query to Create Table
CREATE TABLE EMPLOYEES (
Employee_id VARCHAR(10) PRIMARY KEY,
First_Name VARCHAR(30) NOT NULL,
Last_Name VARCHAR(30) NOT NULL,
DOB DATE,
Salary NUMBER(25) NOT NULL,
Department_id VARCHAR(10)
);
Sample Data Insertion (6 Rows)
INSERT INTO EMPLOYEES VALUES
('EMP001', 'Rajesh', 'Kumar', TO_DATE('1985-05-15', 'YYYY-MM-DD'), 75000, '10');
INSERT INTO EMPLOYEES VALUES
('EMP002', 'Priya', 'Sharma', TO_DATE('1990-08-22', 'YYYY-MM-DD'), 82000, '20');
INSERT INTO EMPLOYEES VALUES
('EMP003', 'Amit', 'Verma', TO_DATE('1988-03-10', 'YYYY-MM-DD'), 68000, '20');
INSERT INTO EMPLOYEES VALUES
('EMP004', 'Sneha', 'Patel', TO_DATE('1992-11-05', 'YYYY-MM-DD'), 90000, '30');
INSERT INTO EMPLOYEES VALUES
('EMP005', 'Vikram', 'Singh', TO_DATE('1987-07-18', 'YYYY-MM-DD'), 72000, '20');
INSERT INTO EMPLOYEES VALUES
('EMP006', 'Anjali', 'Gupta', TO_DATE('1991-12-30', 'YYYY-MM-DD'), 85000, '10');
COMMIT;
Step 2: View Operations
1Create View emp_view
Objective: Create a view showing only Employee_id, Last_Name, Salary, and Department_id
CREATE VIEW emp_view AS
SELECT Employee_id, Last_Name, Salary, Department_id
FROM EMPLOYEES;
-- Verify the view
SELECT * FROM emp_view;
Note: This view provides a restricted view of the EMPLOYEES table, hiding sensitive information like First_Name and DOB.
2Insert Values into View
Objective: Insert data through the view after removing NOT NULL constraint
Step 2a: Modify Table to Allow NULLs
-- Remove NOT NULL constraint from First_Name
ALTER TABLE EMPLOYEES
MODIFY First_Name VARCHAR(30) NULL;
-- Remove NOT NULL constraint from Salary if needed
ALTER TABLE EMPLOYEES
MODIFY Salary NUMBER(25) NULL;
Step 2b: Insert into View
-- This will insert NULL for columns not in the view
INSERT INTO emp_view (Employee_id, Last_Name, Salary, Department_id)
VALUES ('EMP007', 'Reddy', 78000, '30');
COMMIT;
-- Verify insertion in both view and base table
SELECT * FROM emp_view;
SELECT * FROM EMPLOYEES WHERE Employee_id = 'EMP007';
Important: Inserting through a view affects the underlying base table. Columns not in the view will be set to NULL (if allowed).
3Modify, Delete, and Drop Operations on View
Modify (UPDATE) Operation
-- Update salary through the view
UPDATE emp_view
SET Salary = 95000
WHERE Employee_id = 'EMP004';
COMMIT;
-- Verify the update
SELECT * FROM emp_view WHERE Employee_id = 'EMP004';
Delete Operation
-- Delete a record through the view
DELETE FROM emp_view
WHERE Employee_id = 'EMP007';
COMMIT;
-- Verify deletion
SELECT * FROM EMPLOYEES WHERE Employee_id = 'EMP007';
Drop View Operation
-- Drop the view (base table remains intact)
DROP VIEW emp_view;
-- Verify view is dropped (this will give an error)
SELECT * FROM emp_view;
-- Base table still exists
SELECT * FROM EMPLOYEES;
Key Point: Dropping a view does NOT delete the underlying base table or its data. Only the view definition is removed.
4Create salary_view for Department 20
Objective: Create a view showing employees in department 20 with their annual salary
CREATE VIEW salary_view AS
SELECT
Employee_id,
First_Name,
Last_Name,
Salary AS Monthly_Salary,
(Salary * 12) AS Annual_Salary,
Department_id
FROM EMPLOYEES
WHERE Department_id = '20';
-- Query the view
SELECT * FROM salary_view;
Expected Output
| Employee_id | First_Name | Last_Name | Monthly_Salary | Annual_Salary | Department_id |
|---|---|---|---|---|---|
| EMP002 | Priya | Sharma | 82000 | 984000 | 20 |
| EMP003 | Amit | Verma | 68000 | 816000 | 20 |
| EMP005 | Vikram | Singh | 72000 | 864000 | 20 |
Advantages of Using Views
1. Security & Access Control
Views can restrict user access to specific columns and rows, ensuring sensitive data remains protected.
2. Simplification
Complex queries involving multiple joins can be encapsulated in a view, making them easier to use.
3. Data Abstraction
Views hide the complexity of the database structure from end users and applications.
4. Logical Data Independence
Applications can continue using views even if the underlying table structure changes.
Key Takeaways
- Views are virtual tables that don't store data physically
- DML operations (INSERT, UPDATE, DELETE) on simple views affect the base table
- Dropping a view doesn't affect the underlying base table
- Views can perform calculations and aggregate data
- Views enhance security by limiting data exposure
- Complex queries can be simplified using views