Create a View

Create a view that returns information about patrons and their transactions. This view queries the PATRONS and TRANSACTIONS tables, and returns rows that contain a patron's ID, last name, and first name, along with a transaction and the transaction type. The rows are ordered by patron ID, and by transaction type within patron IDs.

To create the patrons_trans_view view, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Views node in the schema hierarchy on the left side, select New View, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it.)

Schema: Specify your current schema as the schema in which to create the view.

Name: patrons_trans_view

SQL Query tab

In the SQL Query box, enter (or copy and paste) the following statement:

SELECT p.patron_id,
       p.last_name,
       p.first_name,
       t.transaction_type,
       t.transaction_date
  FROM patrons p, transactions t
 WHERE p.patron_id = t.patron_id
 ORDER BY p.patron_id, t.transaction_type

Then click Test Syntax, and ensure that you have not made any syntax errors. If you made any errors, correct then and click Test Syntax again.

DDL

Review the SQL statement that SQL Developer will use to create the view. If you want to make any changes, go back to the SQL Query tab and make the changes there.

If you want to save the CREATE VIEW statement to a SQL script file, click Save and specify the location and file name.

When you are finished, click OK.

You have finished creating the view. If you inserted data to the underlying tables, as described in Insert Data into the Tables, you can see the data returned by this view as follows: in the Connections navigator, expand Views, and select PATRONS_TRANS_VIEW, then click the Data tab.

To create a procedure that lists all books with a specified rating, go to Create a PL/SQL Procedure.

Related Topics

SQL Developer Tutorial: Creating Objects for a Small Database

SQL Developer User Interface