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:
1 2 3 | CREATE VIEW view_name (view_column_list) AS select_statement; |
In this syntax:
- First, specify the name of the view which you want to create after the
CREATE VIEWkeywords. The column names of the view will automatically derive from theselect_statement. However, you can tailor the column names for the view by explicitly declare them in parentheses following the view name. - Second, specify a
SELECTstatement 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.
1 2 3 4 5 6 7 8 9 10 11 | CREATE VIEW new_books AS SELECT title, rating, isbn, published_date FROM books WHERE published_date > '2018-01-01'; |
Here is the data returned via the view:
1 2 | SELECT * FROM new_books ORDER BY title; |
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:
1 2 3 4 5 6 7 8 9 10 11 | 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; |
The following statement returns data from the view:
1 2 3 4 5 6 | SELECT * FROM book_details ORDER BY title; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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; |
The following query returns the data from the book_author_stats view:
1 2 3 4 5 6 7 | SELECT book_title, author_count FROM book_author_stats ORDER BY book_title; |
In this tutorial, you have learned how to use the Db2 CREATE VIEW statement to create new views in the database.