Create View Example
Customers table
| CUSTOMER_ID | CUSTOMER_NAME | CUSTOMER_TYPE |
|---|
| 1 | CUSTOMER_1 | CC |
| 2 | CUSTOMER_2 | I |
| 3 | CUSTOMER_3 | SM |
| 4 | CUSTOMER_4 | CC |
Contracts table
| CONTRACT_ID | CUSTOMER_ID | AMOUNT |
|---|
| 1 | 1 | 400 |
| 2 | 2 | 500 |
| 3 | 3 | 700 |
| 4 | 1 | 1000 |
| 5 | 2 | 1200 |
| 6 | 4 | 900 |
| 7 | 3 | 2000 |
| 8 | 2 | 1500 |
CREATE VIEW CustomersList
AS
SELECT c.customer_id, c.customer_name, ctr.contract_id, ctr.amount
FROM customers c, contracts ctr
WHERE c.customer_id = ctr.customer_id
AND c.customer_type='CC' ;
Result:
| Customer_Id | Customer_Name | Contract_Id | Amount |
|---|
| 1 | CUSTOMER_1 | 1 | 400 |
| 1 | CUSTOMER_1 | 4 | 1000 |
| 4 | CUSTOMER_4 | 6 | 900 |