Db2 CREATE VIEW

Summary: in this tutorial, you will learn how to use the Db2 CREATE VIEW statement to create new views in the database.

Introduction to Db2 CREATE VIEW statement

To create a new view in the database, you use the CREATE VIEW statement. The basic syntax for creating a view is the following:

CREATE VIEW view_name (view_column_list)
AS 
select_statement;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view which you want to create after the CREATE VIEW keywords. The column names of the view will automatically derive from the select_statement. However, you can tailor the column names for the view by explicitly declare them in parentheses following the view name.
• Second, specify a SELECT statement that retrieves data from columns of one or more tables.

Db2 CREATE VIEW statement examples

Let’s take some examples of creating new views. We’ll use the books, book_authors, and publishers tables from the sample database for the demonstration.

1) Creating a view based on partial data of a table

See this books table:

The following statement uses the CREATE VIEW statement to create a new view based on the books table that returns all books published since January 2018.

CREATE VIEW new_books 
AS 
SELECT 
    title, 
    rating, 
    isbn, 
    published_date
FROM 
    books
WHERE 
    published_date > '2018-01-01';
Code language: SQL (Structured Query Language) (sql)

Here is the data returned via the view:

SELECT * FROM new_books
ORDER BY title;
Code language: SQL (Structured Query Language) (sql)

2) Creating a view based on multiple tables example

This example uses the CREATE TABLE statement to create a view based on the books and publishers tables:

CREATE VIEW book_details
AS
    SELECT
        b.title,
        b.rating,
        b.isbn,
        p.name publisher,
        b.published_date
    FROM books b
        INNER JOIN publishers p 
            ON p.publisher_id = b.publisher_id;
Code language: SQL (Structured Query Language) (sql)

The following statement returns data from the view:

SELECT 
  * 
FROM 
    book_details
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

3) Creating a view based on summary data from tables

This statement creates a new view that returns the book title and the number of authors of each book:

CREATE VIEW book_author_stats (
        book_title, 
        author_count
) AS 
SELECT 
    title,
    COUNT(A.author_id)
FROM 
    books b
    INNER JOIN book_authors a 
        ON a.book_id = b.book_id
GROUP BY 
    title;
Code language: SQL (Structured Query Language) (sql)

The following query returns the data from the book_author_stats view:

SELECT 
    book_title, 
    author_count
FROM 
    book_author_stats 
ORDER BY 
    book_title;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 CREATE VIEW statement to create new views in the database.