4.6 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.

1. Right-click the Views node in the schema hierarchy on the left side, and select Create (simple).

2. Enter the following information:

   • View Name: Enter patrons_trans_view.

   • In SQL Query, enter 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

3. Click Apply.