Overview of SQL Views and Materialized Views

What is a View?

A View is virtual table and it is built by selecting data from one or more tables.A view can be accessed by using the SELECT statement as though it is like a normal table.Changes applied to the data in a relevant underlying table(s) are reflected in the data shown in subsequent queries of the view.A view can contain all columns of a table or few columns as desired.

You can even add SQL functions, WHERE and JOIN statements to a view and present the data as if the data were coming from one single table.

Note: A view always shows up to date data.Every time you select a view it fetches the data again from the underlying base table(s) which is not the case with Materialized View(we will see shortly what is Materialized View)

Why we use View?

1)We can use Views as security mechanisms in databases.We can restrict some columns or data to the users.

E.g.:Let's say you have a table called Employees, and it has columns like salary and age.You don't want everyone with access to the database to be able to see those columns, so you can create a view called Employee_Info which doesn't select those columns, and deny access to the base table to the users (either by placing them in specific roles/groups and denying access that way, or issuing explicit DENY statements to each user). This way, users don't even have to know that there is a table with more information behind the scenes.

2)Views can join multiple tables into a single virtual table.

3)Views take very little space to store.The database contains only the definition of a view, not a copy of all the data that it presents.

4)Database complexity is hidden


Lets see how to create Views

Database Views can be created using CREATE VIEW statement.

CREATE VIEW view_name AS
SELECT column1, column2, column3.....
FROM table_name
WHERE [condition];


E.g.:

CREATE VIEW EMPLOYEE_INFO AS
SELECT emp_id, emp_name,emp_age
FROM EMPLOYEES
WHERE emp_id=844;

We can query the created view like a normal select statement

SELECT * FROM EMPLOYEE_INFO;


Updating a View:

You can update a view with the following command

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Dropping a View:

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

DROP VIEW view_name;

Let's see what is Materialized Views:

Why we need Materialized Views

Sometimes there would be a need for a huge query with many join operations between more than 10-20 tables containing millions of records and many WHERE conditions. Especially in a data warehousing environment or for reporting purposes. In such conditions, running the query each time whenever required is not a feasible option. So, there should be a technique to store the data such that we should run the query only if there is a change in data in the underlying tables There comes the usage of a materialized views as they can store the data they have queried.

Materialized Views are similar to normal Views except in which the query result is cached as a concrete table.This enables much more efficient access, at the cost of some data being potentially out of date. It is most useful in data warehousing scenarios, where frequent queries of the actual base tables can be extremely expensive.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables.

How to create MV's

CREATE MATERIALIZED VIEW <MV_NAME>
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM <table_name>;

E.g.:

CREATE MATERIALIZED VIEW MV_EMPLOYEES_INFO
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM EMPLOYEES;

Selecting from Materialized View

Since this  MV is similar to a physical table you can use SELECT statements normally like shown below

SELECT emp_id,emp_name from MV_EMPLOYEES_INFO;

Dropping Materialized View

DROP MATERIALIZED VIEW MV_EMPLOYEES_INFO PRESERVE TABLE;


Thanks for visiting my blog!!!!!!!!!

Database Normalization

Normalization is the way in which we organize the data of database to minimize redundancy(duplicate data). Normalization usually involves decomposing a large table into smaller tables and defining relationships between them(primary key and foreign key).

The main objective of normalization is to eliminate redundancy and undesired characteristics like creation,updation and deletion dependencies and ensuring data is logically stored.

First Normal Form(1NF): 
Rules:

1)Table should consist of a primary key(uniquely identifies a row in the table)
2)Table shouldn't consists of duplicate data.
3)The values in a column should be of the same type.
4)The order of the rows should be insignificant.
5)Each column must be unique

Second Normal Form(2NF):
Rules:

1)It should meet all the rules defined for 1NF and there shouldn't be no partial dependencies of any of the columns on the primary key.

To put the above point in another way, relations that have a single attribute for a key are automatically in 2NF.

E.g.: instead of having a primary key (customer_id, orders_id) for customer_orders table, we can divided the table into separate tables so  that each table has a separate primary key (customer_id) for customer table and (orders_id) for orders table.

Third Normal Form(3NF):
Rules:

1)It should meet all the rules defined for 2NF and all the non primary fields should be dependent on primary key.

Thanks for visiting my blog!!!!!!!!!!

Select nth highest salary

This is very popular interview question asked for beginners to experienced programmers.

I will explain how we can find the nth highest salary using Oracle row_number() analytic function.

Let me first explain what is row_number() analytic function.

row_number() assign a unique number to each row to which it applied based on the order by clause(either descending order or ascending order).

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query.

Query:

SELECT *
FROM
  ( SELECT employee.*,
           row_number() over (
                              ORDER BY employee.salary DESC) rownumber
   FROM employee employee)
WHERE rownumber=n;

As you see the above query, first we are using the inner query which assign a number for each row starting from 1. Since we are doing order by desc for the salary, the salaries will be order by highest to smallest.

Since the rows are arranged in descending order, the row with the highest salary will have a 1 for the row number.Since we know the row numbers we just pass the row number(n) and it will compare and return the result.

That's all about selecting the nth highest salary.

Thanks for visiting my blog!!!!!