SQL Views: Tricky Interview Question Asked in Many Companies

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
SELECT Customer_Number
 ,COUNT(Order_Total) AS Order_Cnt
 ,SUM(Order_Total)   AS Order_Sum
 ,AVG(Order_Total)   AS Order_Avg
FROM    Order_Table
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.

Interview Questions

1. Can we delete base-table when view is created on it. – Yes and you will get error while accessing the View.
2. Can we modify base table when view already created on it – Yes

References

• SQL Views Tutorial

Related Posts