Aviator

Posted on May 22, 2023 • Originally published at Medium

Getting started with SQL View

#database #postgres #datascience #programming

A SQL view returns a table containing only the information you want the end user to see.

Databases are designed to store millions of rows of data, and it can be difficult to query or filter a vast amount of data repeatedly using the same query or filter commands. With SQL views, it becomes much easier.

The type of table a view creates is called a virtual table. A virtual table doesn’t store data, rather it rebuilds itself every time the table is queried.

End users aren’t querying directly the original table created in the database using create table command, but rather a copy of the original table.
This copy of the original table can have more columns returned from it or it can have fewer columns returned. More columns when two or more tables are joined together, and fewer columns when only specific columns are selected.

For example, suppose you are a data analyst working in a bank. The bank manager has authorized you to run an analysis of employee data.

Part of the analysis requires that you use the employee’s table. This employee’s table contains fields/column names such as first and last name, age, contact details, email address, and NIN (National Identification Number).

You do not want to show the employee’s contact details, email address, and NIN. In this scenario, a view can be created that returns the employee’s general information (first name, last name, age), while sensitive information such as the NIN, email address, and contact details, if not needed for the analysis can be hidden.

Another example requires you to combine data from two or more tables. Instead of running the same join query command repeatedly when you want to have access to data, you can create a view that hides all the complex SQL join queries. With this, you can query your underlying data with a simple select statement without having to type repeatedly the complex SQL queries for joining tables.

Benefits of using views

Simplify Complex Queries:
Views can be used to simplify complex queries by breaking them down into smaller, more manageable pieces

Limited Access:
Views can be used to enforce security by limiting access to sensitive data. For example, you can create a view that only shows the first name and last name of employees, but not their salary or other confidential information.

Consistency:
Views can be used to enforce consistency ensuring that all queries use a common data definition.

Creating SQL view.

For this, we will be using the PostgreSQL database.

To create a view, you use the CREATE VIEW statement

CREATE VIEW view_name
AS
SELECT column1 [,column2 ] from original_table_name

CREATE VIEW: Begin the creation of the views

view_name: The name of the view to be created

AS: Specifies the table(s) from which we are populating data from

Setting up a Database using PostgreSQL
For this tutorial, we will be creating our database. Doing so I believe will cement the knowledge and help you comprehend the concept better.

Create Database

CREATE DATABASE explore_view;

With our database created, let’s now create tables and populate data into it.

CREATE TABLE employee_data(
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  salary INT NOT NULL,
  department VARCHAR(50) NOT NULL,
  gender CHAR(10),
  employee_id VARCHAR(50) NOT NULL
)

Insert records

INSERT INTO employee_data (id, first_name, last_name, salary, department, gender, employee_id)
VALUES (1, 'Mark', 'Morisson', 2000, 'Tech', 'female', 1),
       (2, 'Jennifer', 'Michael', 5000, 'Tech', 'female', 3),
       (3, 'Jemmy', 'Ken', 2500, 'Finance', 'male', 5),
       (4, 'Ruth', 'Stones', 7000, 'Customer Care', 'female', 6),
       (5, 'Ernest', 'Luke', 2000, 'Maintenance', 'male', 2),
       (6, 'Madison', 'George', 2000, 'Maintenance', 'male', 4);

Creating the view
To create this view, we would want to exclude the employees’ salary and employee_id from the result of the SQL query. This information is considered sensitive and only important to internal management. External management or auditors aren’t expected to see it.

CREATE VIEW Vemployee_data AS 
  SELECT id, first_name, last_name, department, gender 
 FROM employee_data

I usually prefix the name of the view with a V, to signify that the table created is a views table

Query a view
Once a view is created, it can be queried like a regular table.

SELECT * FROM Vemployee_data

When creating the view with the create view statement, we selected specific columns from the Vemployee_data table, which excluded the salary column. Here the salary column is no longer returned from the result of the query.

View with conditions

SELECT * FROM Vemployee_data WHERE gender = 'female'

Using the where clause, we filter the views table for employees who are female.

We can also define the filter condition when creating the view.

CREATE VIEW Vemployee_data_male AS SELECT id, first_name, last_name, department, gender FROM employee_info WHERE gender = 'male'

Now when you query the newly created view, it returns all rows where the employee’s gender is male.

SELECT * FROM Vemployee_data_male

I usually filter my data after the underlying views have been created, and I would encourage you to do the same. Unless your analysis requires a particular use case where not all rows from the original table should be returned.
E.g: You are trying to run your analysis only for male employees, so returning all rows for both genders would be a waste of time and computing resources.

Insert data into a view
In general, it isn’t recommended to directly insert data into a views table as views are virtual tables and don’t store data on their own. However, there are advanced cases where you can perform data insertion through the view. This is beyond the scope of this article.

It is recommended, to insert the data directly through the original table.

Insert data through the original table

If you add data to one of the tables from which the view is derived, the data will automatically be added and updated in the view.

INSERT INTO employee_data(id, first_name, last_name, salary, department, gender, employee_id) VALUES ('7', 'Eke', 'Mercy', '3500', 'Retail', 'female', '10')

Execute the view

SELECT * FROM Vemployee_data

Updating a View
In most cases, you can’t update a view directly. However, you can update the original table and the changes will be reflected in the view.

Modify a View
In PostgreSQL, you can modify a view by using the ALTER VIEW statement. This statement allows you to change the definition of the view without affecting the underlying data in the tables. To alter a view, you can use the following syntax:

ALTER VIEW Vemployee_data RENAME TO Vemployee_info

To replace a particular column from the view, you first have to drop the existing view and then recreate it.

Here, the create view query is named Vemployee_info because it was renamed above with the ALTER VIEW command.

DROP VIEW IF EXISTS Vemployee_info;
CREATE VIEW Vemployee_info AS SELECT id, first_name, department, gender 
FROM employee_data;

SELECT * FROM Vemployee_info