PostgreSQL

In PostgreSQL, a database view is a virtual table that provides a way to present data from one or more tables. Views do not store the data themselves but provide a way to query data as if it were in a table. They are useful for simplifying complex queries, providing security by restricting access to specific data, and presenting data in a more understandable format. Below, we'll explore how to create and use views with some example code related to banking, customers, accounts, and transactions.

1. Creating a Basic View

   To create a view, use the CREATE VIEW statement followed by the view name and the query that defines the view. Here’s an example of creating a view to display basic customer information:

   CREATE VIEW customer_summary AS
   SELECT customer_id, customer_name, customer_email
   FROM customers;
   

   This view, customer_summary, will show only the customer_id, customer_name, and customer_email from the customers table.

2. Using Views

   After creating a view, you can query it just like a regular table. For example:

   SELECT * FROM customer_summary;
   

   This will retrieve all the rows from the customer_summary view.

3. Joining Tables in a View

   Views can combine data from multiple tables. Here’s how to create a view that joins the accounts and transactions tables:

   CREATE VIEW account_transactions AS
   SELECT a.account_id, a.account_type, t.transaction_date, t.transaction_amount
   FROM accounts a
   JOIN transactions t ON a.account_id = t.account_id;
   

   This view, account_transactions, combines information about accounts with their related transactions.

4. Updating Data Through Views

   Some views are updatable, which means you can use them to insert, update, or delete data in the underlying tables. However, this depends on the view definition and constraints. For example:

   UPDATE customer_summary
   SET customer_email = 'newemail@example.com'
   WHERE customer_id = 1;
   

   This statement updates the email address for a specific customer in the customer_summary view, which will reflect in the customers table.

5. Dropping a View

   To remove a view, use the DROP VIEW statement:

   DROP VIEW customer_summary;
   

   This will delete the customer_summary view from the database.

6. Examples Related to Banking
   • Creating a View for Customer Account Balances:

     CREATE VIEW customer_balances AS
     SELECT c.customer_id, c.customer_name, a.account_balance
     FROM customers c
     JOIN accounts a ON c.customer_id = a.customer_id;
     

   • Creating a View for Recent Transactions:

     CREATE VIEW recent_transactions AS
     SELECT t.transaction_id, t.transaction_date, t.transaction_amount, a.account_id
     FROM transactions t
     JOIN accounts a ON t.account_id = a.account_id
     WHERE t.transaction_date >= CURRENT_DATE - INTERVAL '30 days';
     

Feel free to use or adjust this content according to your needs!

Definition

A view is a result set of a stored query on the data, which the database users can query just like they would in a real table. Unlike a real table, a view does not store data physically; it is a set of queries that dynamically retrieve data from the database tables as needed.

Uses and Advantages

• Security: Views can be used to restrict user access to specific rows and columns of data.
• Simplicity: Complex queries can be encapsulated in views.
• Consistency: Views can present a consistent, unchanging image of the structure of the database.
• Logical Data Independence: Views provide a layer of abstraction.
• Data Integrity: Views can be used to ensure data integrity by exposing only valid data to the user.

Types of Views

• Updatable Views: Views that allow data modification operations.
• Read-Only Views: Views that do not allow data modification operations.
• Materialized Views: A materialized view is a database object that contains the results of a query and is stored on disk, acting like a cache to improve query performance. Unlike standard views that dynamically retrieve data every time they are accessed, materialized views hold the actual query result and can be indexed, thus significantly speeding up access to complex aggregated or joined data. They are particularly useful in data warehousing scenarios where queries are run on large volumes of data and require heavy computation. However, because they store a snapshot of the data, materialized views must be refreshed periodically to remain up-to-date with the underlying data changes. This trade-off between data freshness and query speed is a key consideration when deciding to use materialized views in a database system.

Creating a View

Here is the basic SQL syntax for creating a view:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

Consider you have a table employees with columnsid,name,salary,department. You can create a view to show only thenameanddepartment:

CREATE VIEW department_view AS
SELECT name, department
FROM employees;

Modifying a View

To change a view, you generally have to drop and recreate it. Some SQL dialects support the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW department_view AS
SELECT name, department
FROM employees;

Removing a View

To remove a view from the database, you use theDROP VIEWstatement:

DROP VIEW view_name;

Limitations and Considerations

• Views do not have associated indexes, so queries might be slower than direct table queries.
• Some views are not updatable.
• Overusing views can lead to maintenance challenges and performance issues.

In summary, views are a powerful feature in SQL databases that provide a way to simplify complex operations, enhance security, and offer a level of abstraction for database operations.

DATABASE VIEW EXAMPLE

Let's establish a database view to consolidate data for the order management system. Our objective is to extract the client's name, their order numbers, including the order date, order number, number of products ordered, order status, order amount, paid amount, and the agent who processed the order on their behalf. This requirement is depicted in the data model below, with the necessary columns highlighted with red lines.

CREATE VIEW get_order_details AS
SELECT
    org_client.first_name as client_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    count(act_order_detail.product_id) as product_count,
    sum(act_order.sub_total "+" act_order.tax_amount "+" act_order.shipping_amount) as invoice_amount,
    sum(act_payment.paid_amount) as paid_amount,
    org_employee.first_name as booking_agent_name
FROM act_order
INNER JOIN act_lkp_order_status ON act_lkp_order_status.order_status_id = act_order.order_status_id
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
LEFT JOIN act_payment ON act_payment.order_id = act_order.order_id
INNER JOIN org_client ON org_client.client_id = act_order.client_id
INNER JOIN org_employee ON org_employee.employee_id = act_order.sales_agent_employee_id
GROUP BY
    org_client.first_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    org_employee.first_name
ORDER BY act_order.order_date;