DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Views in SQL
Last Updated : 21 May, 2026
A view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base tables whenev
Unit 2249 words9 headingsTables includedExamples included
What is a View?
A view is a virtual table defined by a stored SQL query. It does not physically store data — instead, it dynamically retrieves data from the underlying base tables whenever it is queried.
Base Table: Employee
→View filters columns/rows
→EmployeePublic hides SSN and Salary
Creating Views
sql exampleWoHoTech
-- Basic view
CREATE VIEW CS_Employees AS
SELECT emp_id, name, salary
FROM Employee
WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'Computer Science');
-- View with join
CREATE VIEW EmployeeDetails AS
SELECT e.emp_id, e.name, e.salary, d.dept_name, d.location
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;
-- View with aggregate
CREATE VIEW DeptSalaryStats AS
SELECT d.dept_name,
COUNT(e.emp_id) AS emp_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary
FROM Department d
LEFT JOIN Employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
-- View with alias columns
CREATE VIEW SalarySummary (department, headcount, average_pay) AS
SELECT dept_id, COUNT(*), AVG(salary)
FROM Employee
GROUP BY dept_id;Using Views
Once created, a view is used exactly like a table:
sql exampleWoHoTech
-- Query a view
SELECT * FROM EmployeeDetails WHERE dept_name = 'Mathematics';
-- Filter and sort
SELECT name, salary FROM CS_Employees ORDER BY salary DESC;
-- Join a view with a table
SELECT v.name, v.salary, p.project_title
FROM CS_Employees v
JOIN Works_On w ON v.emp_id = w.emp_id
JOIN Project p ON w.proj_id = p.proj_id;Modifying and Dropping Views
sql exampleWoHoTech
-- Redefine a view (replace existing)
CREATE OR REPLACE VIEW CS_Employees AS
SELECT emp_id, name, salary, hire_date
FROM Employee
WHERE dept_id = 1;
-- Drop a view
DROP VIEW CS_Employees;
DROP VIEW IF EXISTS EmployeeDetails;Updatable Views
Some views can be used to INSERT, UPDATE, or DELETE data, which propagates to the base table.
A view is updatable if:
- Based on a single table (no joins)
- No GROUP BY, HAVING, DISTINCT, aggregate functions
- No subqueries in SELECT list
- Includes the primary key of the base table
sql exampleWoHoTech
-- Updatable view
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1;
-- This UPDATE affects the Employee base table
UPDATE CS_Staff SET salary = 85000 WHERE emp_id = 101;
-- WITH CHECK OPTION ensures updates/inserts satisfy the view's WHERE
CREATE VIEW CS_Staff AS
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1
WITH CHECK OPTION;
-- Now: INSERT INTO CS_Staff ... will fail if dept_id != 1Non-Updatable Views
Views are NOT updatable if they contain:
- JOIN across multiple tables
- GROUP BY or HAVING
- DISTINCT
- Aggregate functions (SUM, COUNT, AVG, etc.)
- UNION
- Subqueries
Advantages of Views
| Advantage | Explanation |
|---|---|
| Security | Hide sensitive columns (salary, SSN) from certain users |
| Simplicity | Encapsulate complex joins/aggregations into a simple name |
| Data independence | Applications use views; underlying table changes don't break apps |
| Reusability | Define complex logic once; reuse in multiple queries |
| Logical data independence | Rename or restructure tables; keep views the same |
Views vs. Materialized Views
| Feature | View (Standard) | Materialized View |
|---|---|---|
| Data storage | Not stored | Physically stored on disk |
| Freshness | Always current (live query) | Stale until refreshed |
| Performance | May be slow (runs query every time) | Fast (pre-computed) |
| Refresh | Automatic (real-time) | Manual or scheduled |
| Storage cost | None | Disk space required |
| Support | All RDBMS | Oracle, PostgreSQL (not standard MySQL) |
sql exampleWoHoTech
-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept_id, COUNT(*), AVG(salary)
FROM Employee
GROUP BY dept_id;
-- Refresh when base data changes
REFRESH MATERIALIZED VIEW dept_stats;View Diagram
Users / Applications
→Query the View
→View translates to base tables
→Result returned
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Views in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, views, views in sql
Related DBMS Topics
Continue learning this concept
Unit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — indepenUnit 2Joins in SQLA join combines rows from two or more tables based on a related column between them. Joins are the primary mechanism for querying data spread across multiple tables in a Unit 2Triggers in SQLA trigger is a stored procedure that automatically executes fires in response to specific events on a table or view. Triggers are event-driven and cannot be called manualUnit 2Unit 2 — Relational Model & SQLThis unit covers the formal foundations of relational databases and the Structured Query Language SQL. Starting from the mathematical basis of relational algebra and calcUnit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on sets of rows, Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.