---
title: "SQL Views: Tricky Interview Question Asked in Many Companies"
description: "In this post, I have answered one tricky SQL-view interview question. Views availability after the base table modified confuses many developers. I provided an answer to this."
url: https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/
date: 2020-05-17
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2020/05/sql-views-1.jpg?fit=1200%2C700&ssl=1
categories: ["SQL"]
tags: ["Use of View in SQL", "What is View in DBMS"]
type: post
lang: en
---

# SQL Views: Tricky Interview Question Asked in Many Companies

Tricky SQL Views Interview Question

With SQL views you can protect base table data, restrict visibility of certain columns. So to create [a view](https://srinimf.com/2023/11/02/how-to-create-a-view-in-db2-an-example-sql/) 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.](https://www.biganalytics.me/2017/04/how-to-create-view-better-way-in-sql.html) There is a fourth view called [Materialized view.](https://en.wikipedia.org/wiki/Materialized_view) But this is not popular in development.

Let me share the best example on [how to](https://srinimf.com/2017/10/27/sql-deadlock-four-precise-ways-to-avoid-it/) 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;
```

Related[Mainframe -OS/390 and ZO/s difference](https://srinimf.com/2015/11/11/mainframe-os390-and-zos-difference/)

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.

[SubscribeSubmitting form](https://subscribe.wordpress.com/memberships/?blog=12352918&plan=19631&lang=en_US&pid=20123&redirect=https%3A%2F%2Fsrinimf.com)

[Log in](https://subscribe.wordpress.com/memberships/jwt/?site_id=12352918&redirect_url=https%3A%2F%2Fsrinimf.com%2Fwp-json%2Fjetpack%2Fv4%2Fsubscribers%2Fauth%3Fredirect_url%3Dhttps%253A%252F%252Fsrinimf.com%252F2020%252F05%252F17%252Fsql-views-present-after-modifying-table%252F)

**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](https://www.techonthenet.com/sql/views.php)

Related Posts

- [8 Top Rules to Write Updatable-view](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/)DateNovember 22, 2015
- [Creating a View in DB2: Step-by-Step Guide](https://srinimf.com/2023/11/02/how-to-create-a-view-in-db2-an-example-sql/)DateNovember 2, 2023
- [Ambiguous Column Error in Subquery – Here’s Resolution](https://srinimf.com/2017/06/26/avoid-ambiguous-column-select/)DateJune 26, 2017

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2020%2F05%2F17%2Fsql-views-present-after-modifying-table%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2020%2F05%2Fsql-views-1.jpg%3Ffit%3D1200%252C700%26ssl%3D1&description=SQL%20Views%3A%20Tricky%20Interview%20Question%20Asked%20in%20Many%20Companies)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2020/05/17/sql-views-present-after-modifying-table/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20Views%3A%20Tricky%20Interview%20Question%20Asked%20in%20Many%20Companies&body=https%3A%2F%2Fsrinimf.com%2F2020%2F05%2F17%2Fsql-views-present-after-modifying-table%2F&share=email)
-

### Like this:

Like Loading…