

Discover more from Data Science & Machine Learning 101
I have never used Views. Like Ever. Like in all of my internships, college courses, full time work, like ever.
However, for some weird odd reason, Indians who are hiring for a Data Analyst role love asking this stuff. So, here’s some free alpha for you:
If you are interviewing for a Data Analyst role, and you know the person doing the interview is Indian, expect at least 1 question on Views.
Table of Contents:
What are Views
Types of Views
Working with Views
Why people *actually* use Views
Exercises/Interview Questions
Note: this is a common question that gets asked during interviews.
1 - What are Views
When we are working with tables, we are storing data on a disk. But, when we are working with views, we are actually storing a method to query data from many tables.
You can think of storing a view as storing a query (method of pulling data). Then, giving it a name, and then passing it along to whoever else wants it. A good way to think of Views in SQL are to think of them as a virtual table, that pull data from real tables.
2 - Types of Views
There are 3 main types of views:
Indexed Views
Segregated Views
Systematic Views
2.1 Indexed Views
Indexed views (materialized views) store the results of a query as a persistent dataset. Indexed views pre-compute the data, and store it in an efficient manner. Indexed views can lead to huge performance increases because the database can grab the data straight from the indexed view. Instead of re-calculating the data from the tables again.
Indexed views come with the trade-off of increased storage requirements.
2.2 Segregated Views
Segregated views are created by segregating specific subsets of data from a larger dataset. These views allow users to analyze only specific parts of the dataset (not the whole thing). Segregated views enhance data security, privacy, and performance, as they allow users to focus on relevant data.
This reduces the risk of unauthorized access to sensitive information.
2.3 Systematic Views
Systematic views provide a structured representation of the underlying data. These views are designed to offer a more consistent and efficient way to access and analyze data. This is done by presenting it in a well-organized and meaningful format.
Systematic views can be created based on specific business requirements or to optimize query performance.
3 - Working with Views
All of the commands here are basically the same as when you are working with tables:
except we now replace the word TABLE with VIEW
3.1 Creating a VIEW
To create a view, we use the CREATE VIEW command
CREATE VIEW my_view AS
SELECT *
FROM my_table;
If you want to make a materialized view:
CREATE MATERIALIZED VIEW my_view AS
SELECT *
FROM my_table;
we can of course use the WHERE clause for filtering data.
3.2 Inserting rows into a VIEW
We will use the INSERT INTO command to insert rows into a view
INSERT INTO my_view
VALUES (value 1, value 2, ....);
3.3 Delete rows in a VIEW
We can use the DELETE FROM command to delete rows from a view
DELETE FROM my_view
WHERE *some condition here*;
3.4 Drop a VIEW
We can use the DROP VIEW command to remove a view, similar to how we can DROP a table.
DROP VIEW my_view;
4 - Why people *actually* use Views
Like I said at the top, I basically never use Views. But, there are other people who do use them, and here’s the reasons why:
Limiting Access: Views provide a degree of protection for the data stored within a table. They do this by restricting access to only a specific subset of a table’s rows/columns
Hides Data Complexity: All of the SQL schema, and joins can be hidden behind a view
Streamlines User Input: When working with views, a user can basically pull data from multiple tables without doing any joins, or any real work on their part
5 - Exercises/Interview Questions
5.1 Exercises
Here are some coding exercises you can do to get some practice.
Given two tables: my_table, and my_other_table. Create a view that gets all of the data from these two tables
Given the above two tables. Create a view that gets the column ABC from my_table, and XYZ from my_other_table
Create a VIEW that gets the first 100 rows from a table called my_table. Then drop this view.
5.2 Interview Questions
Here are some of the most common interview questions asked about VIEWs
What’s a view in SQL?
Ans: A view is basically a virtual table created by pulling several columns/rows from real tables together
List some advantages of working with SQL views over tables
Ans: provides protection from unauthorized access, hides data complexity, and streamlines user input
Does a View store actual data?
Ans: A view doesn’t store actual data. Actual data is stored within the rows/columns of tables on a disc.
How can you create a view from an index?
Ans: You can create an index on a materialized view. The steps are:
Create a materialized view
Create an index on the view
SQL 10 - Views
Views are great, especially if you're using entity framework or other database frameworks. But agreed that they're not used very often