Tricky SQL Views Interview Question
With SQL views you can protect base table data, restrict visibility of certain columns. So to create a view you need a Table. Considering deletion of a view, it is still present even after base table got deleted.
3 Types of SQL Views
Generally, views are three types. There is a fourth view called Materialized view. But this is not popular in development.
Let me share the best example on how to create an Aggregated view. Then I will modify the base-table. We will see what may happen.
SQL Query to Create Aggregate View
CREATE VIEW Aggreg_Order_v AS
,COUNT(Order_Total) AS Order_Cnt
,SUM(Order_Total) AS Order_Sum
,AVG(Order_Total) AS Order_Avg
GROUP BY Customer_Number ;
The view name is “Aggreg_Order_v. This is now created from ‘Order_table’, which is called base table.
I am now going to ALTER the base table.
ALTER Table order_table
add mgr_no integer;
Now I added a new column (mgr_no) to Order_table. Even after modifying the base table, the view will still present. Views still present until you drop them.
Subscribe to get access
Read more of this content when you subscribe today.
- Can we delete base-table when view is created on it. – Yes and you will get error while accessing the View.
- Can we modify base table when view already created on it – Yes