Tricky SQL Views Interview Question
SQL views protect base table data. You can restrict certain columns from base table with Views. The point is will the View still present even after the base table deleted or modified? The answer is ‘Yes’. Let us see how.
3 Types of SQL Views
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