SQL 10 - Views

*Completely Optional*, but Mandatory if you are going for a data analyst role.

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:

1. What are Views

2. Types of Views

3. Working with Views

4. Why people *actually* use Views

5. 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.

How Views work

2 - Types of Views

There are 3 main types of views:

1. Indexed Views

2. Segregated Views

3. 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.

An Indexed View (Materialized View)

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:

• Create a table

• Insert data to a table

• drop/remove entries from a table

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.

1. Given two tables: my_table, and my_other_table. Create a view that gets all of the data from these two tables

2. Given the above two tables. Create a view that gets the column ABC from my_table, and XYZ from my_other_table

3. 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

1. What’s a view in SQL?

   Ans: A view is basically a virtual table created by pulling several columns/rows from real tables together

2. List some advantages of working with SQL views over tables

   Ans: provides protection from unauthorized access, hides data complexity, and streamlines user input

3. 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.

4. How can you create a view from an index?

   Ans: You can create an index on a materialized view. The steps are:

   1. Create a materialized view

   2. Create an index on the view