SQL Views, Updatability, and Programmatic SQL (Embedded & Cursors)

What is a View in SQL? (In Simple Words)

A View is essentially a Virtual Table based on the result-set of an underlying SQL query.

Unlike a physical base table, a View does NOT store data rows on the hard drive!
Instead, the DBMS stores only the SQL definition of the view in the Data Dictionary.
Whenever an application queries the view, the database engine executes the stored query dynamically behind the scenes!

1. Creating and Using Views (`CREATE VIEW`)

Syntax:

CREATE VIEW View_Name AS
SELECT Column1, Column2, ...
FROM Base_Table
WHERE Condition;

Practical Example:

Suppose the college database administrator wants examination staff to view student names and course marks, but wants to hide confidential phone numbers, home addresses, and caste categories:

-- Create a secure view for the Examination Cell
CREATE VIEW Exam_Marks_View AS
SELECT 
    S.Roll_No,
    S.Student_Name,
    C.Course_Name,
    E.Marks
FROM Student S
JOIN Enroll E ON S.Roll_No = E.Roll_No
JOIN Course C ON E.Course_ID = C.Course_ID;

-- Querying the view just like an ordinary physical table!
SELECT * FROM Exam_Marks_View WHERE Marks >= 75;

2. Advantages of Using Views

The Four Major Benefits of Views

1. Security & Access Control

Hides sensitive columns (like salaries, passwords) from unauthorized users.

2. Query Simplification

Hides complex 5-table joins behind a simple SELECT * FROM MyView.

3. Logical Data Independence

If base tables are restructured, the view definition can be modified without breaking client apps.

4. Customized Perspectives

Different departments see only the data relevant to their day-to-day operations.

3. Updatable Views vs. Non-Updatable Views

Can you run INSERT, UPDATE, or DELETE statements on a View?

Yes, BUT ONLY IF THE VIEW IS UPDATABLE!
If a view is updatable, any change made through the view is immediately propagated down into the underlying physical base table.

Strict Conditions for a View to Be Updatable:

A view is NOT updatable if its SELECT definition contains any of the following:

Aggregate Functions: (SUM, AVG, COUNT, MAX, MIN).
The DISTINCT keyword.
The GROUP BY or HAVING clause.
The UNION or UNION ALL operator.
Multiple Base Tables joined together (in most standard RDBMS, though single-table column updates may be permitted).
Target columns in the base table that are defined as NOT NULL without default values and are missing from the view.

4. Programmatic SQL: Using SQL with Programming Languages

SQL is a non-procedural language lacking loops (for, while), if-else branches, and user interface controls. To build complete enterprise applications, developers integrate SQL with host languages (like Java, C++, Python, or C#).

Two Primary Integration Approaches:

Embedded SQL: Standard SQL statements are written directly inside host language source code (prefixed with EXEC SQL). A pre-compiler translates SQL statements into direct database function calls.
Dynamic SQL / APIs (CLI, JDBC, ODBC):
- ODBC (Open Database Connectivity): Standard C/C++ API for database access across different database vendors.
- JDBC (Java Database Connectivity): Standard Java API allowing Java applications to execute SQL queries and process result sets seamlessly.

5. Database Cursors: Bridging SQL and Host Languages

The Fundamental Impedance Mismatch:

SQL operates on SETS: A SELECT query returns a set of 1,000 rows all at once (Set-at-a-time).
Programming Languages operate on VARIABLES: Java or C processes one variable at a time (Record-at-a-time).
A Cursor is a database mechanism that acts as a pointer or iterator over the active query result set, allowing the host application to process records one row at a time.

Cursor Life-Cycle Steps

1. DECLARE
Defines the SQL query for the cursor

➔ 2. OPEN
Executes query & allocates memory buffer

➔ 3. FETCH
Reads row into variables (Loop)

➔ 4. CLOSE
Releases memory resources

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is a View in SQL? List three advantages of using Views.”

Define View as a virtual table stored as an SQL query in the data dictionary.

Give the CREATE VIEW syntax.

Explain Security (hiding columns), Simplicity (encapsulating joins), and Logical Data Independence.

10-Mark Question: “Explain Views in SQL and detail the restrictions that make a view non-updatable. What is the impedance mismatch between SQL and host languages? Explain the lifecycle of a Database Cursor (DECLARE, OPEN, FETCH, CLOSE).”

Give clear SQL examples of creating and querying views.

List the 6 conditions preventing updatability (GROUP BY, DISTINCT, aggregates, UNION).

Define Impedance Mismatch (Set-at-a-time vs Record-at-a-time).

Detail the 4 cursor steps with pseudo-code.

SQL Views, Updatability & Programmatic SQL (Embedded, Cursors)