Tricky SQL Views Interview Question

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

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.

Subscribe to get access

Read more of this content when you subscribe today.

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


Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.