Working with Views in SQL: Everything You Should Know

SQL is an international standard. It is recognized by ISO and ANSI standard bodies. For those looking to conduct data analysis, SQL is a must-have skill. In this article, we talk about what are views in SQL, the different types of views in SQL, the advantages of views in SQL, and how to add and drop a view.

What are views in SQL?

SQL is a very important skill, especially in data science analytics. It’s fairly simple to understand what are views in SQL. Views in SQL refer to nothing more than an SQL statement which is stored in the database under a particular name. It is a virtual table in the form of a predefined SQL query.

An SQL view can contain either all the rows from a table or just select rows. It can also be created from one or many tables depending upon the query that has been written to create the view. You can also use SQL with Python.

Types of Views in SQL

SQL views have a number of different uses. The types of views in SQL and their uses include:

(i) Restricting access to data so that a user can see and modify only what they need to and nothing else.

(ii) Summarize the data from a number of different tables to generate a report.

(iii) Sort unorganized data and structure it in a way that users find easy to understand.

Creating an SQL View

We will be using two sample tables, StudentDetails and StudentMarks to demonstrate how to work with SQL views. Here are the tables:

Student Details

Student Marks

You can create an SQL view using the CREATE VIEW statement. A view can be created either from a single table or by using multiple tables.

Here’s what the syntax looks like:

CREATE VIEW view_name AS

SELECT column1, column2…..

FROM table_name

WHERE condition;

view_name: Name for the View

table_name: Name of the table

condition: Condition to select rows

Let’s see how to create SQL views by using a couple of examples.

Creating an SQL View using just One Table

In this example, we are using a single table StudentDetails, to create an SQL view. Here’s what the query looks like:

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS

FROM StudentDetails

WHERE S_ID < 5;

The view is queried in exactly the same way as we query a table. Here’s how you can do it:
SELECT * FROM DetailsView;

Output:

We now take a second example where we create an SQL view named StudentNames from the table StudentDetails.

Here’s what the query looks like:

CREATE VIEW StudentNames AS

SELECT S_ID, NAME

FROM StudentDetails

ORDER BY NAME;

We can now query the view to get the output:
SELECT * FROM StudentNames;

Creating an SQL view from Multiple Tables

Now we use both the tables, Student Details and StudentMarks to create an SQL view. All we need to do is include both the tables in the SELECT statement. Here’s the query:

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS

FROM StudentDetails, StudentMarks

WHERE StudentDetails.NAME = StudentMarks.NAME;

To display the data:

SELECT * FROM MarksView;

Here’s the output.

Deleting an SQL View

Many SQL views are created for a specific purpose and once that purpose no longer exists, there’s no point in burdening the system. In other words, it’s wise to delete an existing SQL view if it’s no longer needed. This can be done using the DROP statement. Here’s the syntax:

DROP VIEW view_name;

view_name: Name of the View which we want to delete.

For instance, if you want to delete the MarksView SQL view, here’s what you need to do:

DROP VIEW MarksView;

Updating SQL Views

If you want to modify and update an SQL view, you will have to meet certain conditions. Even if one of the conditions is not fulfilled, the view will not be updated.

(i) The SELECT statement cannot contain the DISTINCT keyword

(ii) SQL view cannot have all NOT NULL values

(iii) If an SQL view has been created using nested or complex queries, it cannot be updated.

(iv) An SQL view that’s created from multiple tables cannot be updated. Only the SQL views created from a single table can be updated.

(v) If the SELECT statement used to create the SQL view includes the GROUP BY or ORDER BY clause, you won’t be able to update it.

(vi) To add or remove fields from an SQL view, we just need to use the CREATE OR REPLACE VIEW statement. Here’s the syntax:

Syntax:
CREATE OR REPLACE VIEW view_name AS

SELECT column1,coulmn2,..

FROM table_name

WHERE condition;

Say we want to update the view MarksView to include the AGE field that exists in the StudentMarks Table. Here’s how we can do that:
CREATE OR REPLACE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE

FROM StudentDetails, StudentMarks

WHERE StudentDetails.NAME = StudentMarks.NAME;

You can see the output once you fetch the data from the MarksView table.
SELECT * FROM MarksView;

Here’s the output:

Adding a row to an SQL View

You can use the INSERT INTO statement of SQL to insert a row in an SQL. The entire process is very similar to adding a row to a table. Here’s the syntax:

INSERT view_name(column1, column2 , column3,..)

VALUES(value1, value2, value3..);

view_name: Name of the View

Let’s take an example in which we will add a new row to the DetailsView SQL view. Here’s how it is done.

INSERT INTO DetailsView(NAME, ADDRESS)

VALUES(“Suresh”,”Gurgaon”);

Then we fetch all the data from the DetailsView table:
SELECT * FROM DetailsView;

Here’s what the output looks like:

Deleting rows from an SQL View:

Deleting a row from an SQL view is similar to deleting rows from a table. You can use the DELETE statement to delete the rows from the view. Of course, you can always delete the row from the table which in turn will get reflected in the SQL view.

Here’s the syntax:
DELETE FROM view_name

WHERE condition;

view_name: Name of view from where we want to delete rows

condition: Condition to select rows

Example:
In this example, we will delete the row we just added to the DetailsView SQL view in the previous example.
DELETE FROM DetailsView

WHERE NAME=”Suresh”;

We use the following to get all the data from the SQL view

SELECT * FROM DetailsView;

Here’s the output:

Using the WITH CHECK OPTION clause

The WITH CHECK OPTION CLAUSE in SQL is very useful when it comes to views in SQL. It only applies to views that can be updated, which means the conditions described above need to be fulfilled. You use this clause in the CREATE VIEW statement to prevent insertion of rows in those views where the condition in the WHERE clause of the CREATE VIEW statement is not met. If the SQL view is not updatable and you still use the WITH CHECK OPTION clause in the CREATE VIEW statement, you will get an error.

Here’s an example where we will be creating an SQL view called SampleView using the table StudentDetails. We will use the WITH CHECK OPTION clause to create this view. Here’s what the syntax will look like:

CREATE VIEW SampleView AS

SELECT S_ID, NAME

FROM  StudentDetails

WHERE NAME IS NOT NULL

WITH CHECK OPTION;

If you now try to insert a new row with a null value in the Name column, the system will return an error. This is because you have created the SQL view with the condition that the NAME column should not be null.

Say for instance you use the query given below for this SQL view. It will still return an error even though the view itself is updatable.

INSERT INTO SampleView(S_ID)

VALUES(6);

Here’s a video that will give you more insights into SQL views and how they can be used.

Advantages of Views in SQL

There are a number of advantages of views in SQL. Here’s a list of the most important ones:

(i) Security: This is because each user is now able to access and modify only a pre-determined amount of data.

(ii) Simplicity: A view can take data from a number of tables and represent it in a simple format that is easy to understand and analyze.

(iii) Stabilization and Consistency: Even if the underlying source tables change, views show a consistent image of the database structure.

Conclusion

Creating, removing and updating views in SQL is a very important skill when it comes to data analysis, data science, and analytics.

Have a look at these interview questions on SQL to rock your next interview.

If you’re looking for a career in data science, a thorough course in data science will give you all the SQL skills you need. Make sure the course offers features like instructor-led learning and placement assistance so that you’re in a better position going forward.