In my previous article i have given the difference between simple view and complex view in SQL. In this article i would like to throw light on what is mean by complex view in SQL and what are its different examples. If you faced the interviews like SQL interviews you faced this question about what is mean by complex view in SQL.
What will you find in this article?
1.What is mean by view in SQL and its types?
2.What is Complex view in SQL with real example.
What is view in SQL?
The view is nothing but the snapshot of the database. If we want specific data from single table or multiple tables which needs to be used again and again then the views are useful. You can say in simple words view in SQL is nothing but the logical table created from one or more tables. There are multiple usages of views in SQL like reporting purpose or if we want to fetch the logical data from multiple table again and again then we can create the view.
There are two types of views in SQL :
- Simple view : When the view is been created on View in SQL
- Complex view : When the view is been created on multiple tables then it is called as complex view.
- Materialized view : The materialized view is view like a physical table which is used mainly for performance management.
What is Complex view in SQL with real example?
In this section I would like to explain about complex view in SQL with real life example.
- Complex view is view which uses multiple data together and create the snapshot of the data.
- Relation between table : The relation between multiple table is must to create the complex views.
- Complex view is nothing but the view which has been created with multiple joins, group by statements or set operators to fetch the complex data from multiple tables.
- The complex views are used to fetch the complex operations to fetch the complex data from multiple table.
Real life example of Complex view in SQL :
If there are two tables Customer table and Items table and I want to prepare the report where Customer bought the items .
1.Customer :-Customer_name, Customer_num, Customer_code columns
2.Item:-Customer_code,Item_code,Item_name,Item_category columns
We need to create view where we want to show the associated Items to Customer.Here We need to use complex join.
Create view V_Customer
as Select e.Customer_name,d.Item_name
from Customer e,Item d
where e.Customer_code=d.customer_code
Group by item_category;
The above view will give you the details about the Customer_name and associated item with customer group by the item category. So this kind of view is called as complex view.