SQL VIEW

View

A VIEW in SQL in completely based on the concept of Virtual Table which comes as a result of some SQL query. It behaves like a real table and you can perform all SQL statements and function on a VIEW.

How to create a VIEW:

Syntax:

CREATE VIEW [dbo].[View_Name] as

select A.Name, A.Email, A.Phone

from tbl_name as A

where A.status = 1

Example:

For example let me create a table as tblEmployee

CREATE TABLE [dbo].[tblEmployee]

(

id int identity(1,1),

Emp_Code varchar(20) primary key,

Emp_Name varchar(500),

Emp_Email nvarchar(500),

Emp_Salary decimal,

Emp_Dept int,

Emp_Status int

)

Now I am going to create another table with the name tblDepartment

CREATE TABLE [dbo].[tblDepartment]

(

id int identity(1,1),

Dept_Id int primary key,

Dept_Name varchar(500),

Dept_Status int

)

Now based on these two tables I want to get details of those employees who are currently working for the organization i.e. has Emp_Status as 1, for this I am going to create a VIEW named as View_Emp_Active.

CREATE VIEW [dbo].[View_Emp_Active] as

select A.Emp_Code, A.Emp_Name, A.Emp_Email, A.Emp_Salary, B.Dept_Name

from tblEmployee A inner join tblDepartment B

on A.Emp_Dept = B.Dept_Id

where a.Emp_Status = 1

So, whenever you need to get the list of active employees just call this View rather than writing the full code of the select statement from two tables using join statement.

Some Important things you should know about the SQL VIEW:

1.    A view can only be updated i.e. insert, delete, update statement can only applied on the View if it is formed from s single table, otherwise you cannot perform these actions on a View.

2.    You cannot have a view having more than 1000+ columns.

For more information about website development please visit http://www.jalwaltechnologies.com.

Splitstring in SQL

Introduction:  This function will split the string given by the character given in SQL.

Code: 

create FUNCTION [dbo].[string_split]

(   

      @string NVARCHAR(MAX),

      @split_character CHAR(1)

)

RETURNS @result TABLE (

      Item NVARCHAR(1000)

)

AS

BEGIN

      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1

      IF SUBSTRING(@string, LEN(@string) - 1, LEN(@string)) <> @split_character

      BEGIN

            SET @string = @string + @split_character

      END

      WHILE CHARINDEX(@split_character, @string) > 0

      BEGIN

            SET @EndIndex = CHARINDEX(@split_character, @string)

          

            INSERT INTO @result(Item)

            SELECT rtrim(ltrim(SUBSTRING(@string, @StartIndex, @EndIndex - 1)))

          

            SET @string = SUBSTRING(@string, @EndIndex + 1, LEN(@string))

      END

      RETURN

END

To excute function:

select item from [string_split] ('Ram, Shyam', ',')

How to change SCHEMA name in SQL SERVER

Description:

 A Database schema is a described structure in a language supported by DBMS.

Example:

USE databasename;
GO
ALTER SCHEMA new_schema TRANSFER old_schema.TableName;
GO

Use of HAVING clause in SQL

Overview : 

Having clause in SQL is used to place aggregate functions  to filter data because where condition does not allow the use of aggregate function.

Syntax :

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Demo :

Suppose we have a database of items as shown below. 

Now if we need to find out if there is any duplicate bar-code in these items then we can use having clause.

Query :

select count(item_code), item_name, item_purchase_rate, item_sale_rate from item group by item_code, item_name, item_purchase_rate, item_sale_rate having count(item_code)  > 0

Using PIVOT in SQL

Overview : PIVOT turns the unique values from one column in the expression to multiple columns in result, and applies aggregation on remaining value found in resultant output.

Example: lets create a table for this as follows –

create table demo(id int identity(1,1), item nvarchar(max), category nvarchar(max), qty int)

now lets insert some items in this table –

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat02', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item01', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat03', 5)

insert into demo(item, category, qty) values('item02', 'cat01', 5)

insert into demo(item, category, qty) values('item03', 'cat01', 5)

insert into demo(item, category, qty) values('item02', 'cat02', 5)

Lets apply our PIVOT query on this –

select * from(select item, category, qty from demo

)datatable

 PIVOT

 (

   sum(qty) for category  IN ([cat01], [cat02], [cat03])

 )

 Pivottable

Result :

Using CURSOR in SQL

DECLARE @variable int  -- Declaring variable to use in cursor 
-- You can declare as many variables as per your need
--------------------------------------------------------
DECLARE @cursorName CURSOR   -- Declaring cursor
SET @cursorName = CURSOR FAST_FORWARD
FOR
SELECT column_name FROM   Table_Name   -- query for cursor
OPEN @cursorName
FETCH NEXT FROM @cursorName
INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Name set column_name= something where column_name=something -- write your query here

FETCH NEXT FROM @cursorName
INTO @variable
END
CLOSE @cursorName
DEALLOCATE @cursorName