SQL Views - SQL Interview Question

SQL Views is the question that is being asked in many interviews including companies like Paytm, Google, Facebook, etc. 

What are SQL Views?

There are several tables in our database for storing collections of useful data. Relations bind every table to every other table. But a straightforward question should come to mind: Why do we need to generate so many tables, as opposed to simply one? Instead of duplicating the same data in one table, many tables allow for the systematic storage of vast volumes of data. We cannot, however, ignore the requirement to combine all pertinent tables into a single new table in order to query some data from this database.

Therefore, if we carefully consider this matter, we have the benefit of keeping data in several tables on the one hand, but on the other hand, we have the difficulty of accessing such dispersed data.

The solution to this problem brings “SQL Views” into the picture. SQL Views have made managing data and tables relatively easy for users. 

Views are virtual tables; they do not exist in fact in the database and so do not require storage. Virtual Tables, like actual tables in databases, have rows and columns. Such views are easily created by picking data (fields) from one or more tables in the database, with specified rules for selecting table rows.

Uses of Views:

Views are just a SQL Statement, which allows you to do the:

Views hide the complexity of the data in the database as they join and simplify multiple tables into a single virtual table, which is easier for a user to understand.

Views take very little storage since the database contains only a view's statements and not a copy of all the tables the view is creating.

Views provide security to the data, acting as a security mechanism. Let’s take a simple scenario, in an IT company, the engineer, the HR, and the manager might be using the same table for some information. But because of their different departments, some data must be irrelevant to HR but relevant to the manager. If so, shouldn’t there be a security mechanism that would hide irrelevant information of the table from HR? Yes, views allow us to mask/show some data of the table depending on requirements and security. With the help of conditions, we can hide some data for a particular person.

We can easily update the rows in the virtual tables (views) as the DBMS translates our request through the views. 

Views also maintain data integrity as it presents a consistent and accurate image of the data from the database even if the underlying source is restructured, renamed, or split. It can automatically check the data which a user or any other third party is trying to access meets the conditions mentioned in the views to maintain accuracy while displaying data. 

Creating a View in SQL

CREATE VIEW is the syntax for creating Views in SQL. Following is the basic syntax to create a 

CREATE VIEW view_name AS  
SELECT column1, column2...column N 
FROM table1, table2...table N 
WHERE condition;

To see the data in the view, we can query the view using the following SELECT statement:

SELECT  * FROM [view_name];

  • “CREATE VIEW” is used at the start of the code to initiate the view.
  • “SELECT” is used to decide which columns to pick from the tables.
  • With the help of the “FROM” term, we can select the tables from which columns(data) have to be picked.
  • “Table1..table N” denotes the names of the tables. (Here, for example, we have “Scaler Courses” & “Author Details” as tables.)
  • “WHERE” is used to define the condition pertaining to selecting rows.

We will be using different types of View operations with easy and simple examples.

Let's understamd with a example:

We will take two tables named Courses,

 Table Courses:

Id Name Duration Language Cost
1 Javascript 3-4 months English 1500
2 Python 5 months English 1000
3 C++ 4-5 months Hindi 500
4 Interview Preparation 6 months English 1800
5 Node Js 6 months Hindi 2500

Now let’s start by creating a view. Here the tech team will create a view named “CourseView” from the table “ Courses” for querying some specific course details for the students, which are below the cost of Rs 2000, to display on the website.

SELECT Name, Duration
FROM ScalerCourses
WHERE Cost < 2000;

We can see the data by querying the view as follows:

SELECT * FROM CourseView;

Output for the query,

Name Duration
Javascript 3-4 months
Python 5 months
C++ 4-5 months
Interview Preparation 6 months

 Isn't it simple and clear? Let's look at the various types of operations available in views with some examples and code snippets.

Updating a View

Let's say one of the team members thinks of adding details about the language used while teaching the courses to the students, to allow students from all parts of India to choose their education courses. Easily. Teams can easily add these items by updating the view.

A view can be easily updated with the CREATE OR REPLACE VIEW statement, but certain conditions must be considered while updating. For example, the Tech Team wants to update the CourseView and add the Language as a new field to this View.

SELECT Name, Duration, Language
FROM Courses
WHERE Cost < 2000;

Now, Let's look at the data in CourseView,

SELECT * FROM CourseView;

Ouput of the above query will be:

Name Duration Language
Javascript 3-4 months English
Python 5 months English
C++ 4-5 months Hindi
Interview Preparation 6 months English

As we can see from the above output, the column of Language got added to the views.

Here we have updated the view by keeping the condition that “Cost” is below 2000. It is possible to keep the condition (WHERE statement) of a particular view based on a field that is present in the original table in the database but not in the views we created.

Inserting Rows in a SQL View

Now looking into the demand for some courses by the students, they thought of adding a new course to the virtual table. For updating the view with a new row, they can update the view by inserting new rows into the view, by using the INSERT INTO statement.

INSERT INTO CourseView(Name, Duration, Language)
VALUES("Java", "4 months", "English");

Ouput of the above query will be:

Name Duration Language
Javascript 3-4 months English
Python 5 months English
C++ 4-5 months Hindi
Interview Preparation 6 months English
Java 4 months English

Deleting Rows Into a View

At regular intervals of the educational courses, feedback was taken from the students. While taking those feedback into account, the team at Scaler wanted to take down a particular course. To tackle this issue, they used one of the functionalities of the views in SQL. A row can be deleted from the view using the DELETE FROM statement. The following is the syntax to remove a row from the view.

WHERE Name = “Javascript”;

Ouput of the above query will be:

Name Duration Language
Python 5 months English
C++ 4-5 months Hindi
Interview Preparation 6 months English
Java 4 months English

I hope you understood the SQL Views. If you have any doubts, please let us know in the comments.

Post a Comment

Previous Post Next Post