Creating a View in DB2: Step-by-Step Guide

Views are virtual tables that you can create based on a physical table. If there are no original tables, then the concept of views does not exist.

SQL: Rules to create a View

• The error occurs when the physical table does not exist in the database.
• You cannot delete records in the view as it is a logical representation.

SQL View Types

SQL views are virtual tables that are derived from the query result of a SELECT statement. They provide a way to represent data from one or more tables, or even other views, in a structured manner. Views in SQL come in different types, including read-only views, updatable views, and force views.

1. SQL Read-only views:
   • Read-only views are views that allow users to retrieve data but not perform any modifications.
   • These views are particularly useful when you want to restrict access to certain columns or rows of a table.
   • By defining read-only views, you can provide a simplified and controlled interface for end-users or applications.
2. SQL Updatable views:
   • Updatable views allow not only retrieving data but also modifying and inserting data into the underlying tables.
   • With updatable views, you can abstract complex join operations or calculations, making it easier to update or insert data using a more straightforward view structure.
3. SQL Force views:
   • Force views, also known as materialized views or indexed views, are special types of views that store the results of the underlying query on disk.
   • These views are precomputed and stored, allowing for faster data retrieval in certain cases where there are complex calculations or aggregations involved.
   • Force views are especially helpful when dealing with large datasets or frequently executed queries, as they can significantly improve query performance.

SQL Views: Purpose

• Control who can access the data in a table.
• Make the data more user-friendly
• Easily grant access to only specific views instead of the entire table
• Display only certain parts of the table data
• Show a summary of a table
• Combine two or more tables in a meaningful manner
• Display only the relevant rows that are important for a particular process

Read: Instant SQL format Tool for Your Projects

Examples to Create a View

This section provides examples to create a view in your application.

Example – 1

CREATE VIEW ma_proj AS
SELECT *
FROM   project
WHERE  substr(projno, 1, 2) = 'MA'

Example -2

CREATE VIEW ma_proj AS selectprojno,
projname, respemp
FROM        project
WHERE       substr (projno,1, 2) = 'MA'

Related Posts