SQL Corner

Wednesday, June 5, 2019

SQL Indexer

Indexer

Indexer is also same as index in sql server table

Indexers are of two type

Simple
Composite

An indexer created on single column of table is simple indexer where as indexer created on more
than one column is called composite indexer.

Simple Indexer
Syntax:
create index <indexName> on <tableName>(<columnName>);
Example:
create index Emp_Id_idx on Employee_Master(EmpId);

Composite index
Syntax:
create index <indexName> on <tableName> (<columnName1>,<columnName2>);
Example:
create index index11 on Employee_Master(Age,Salary);

Sunday, June 2, 2019

Get all child or sub employee with its Level by using CTE

Get all child employee with its Level by using CTE

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how get all child employee recursively and also get its employment level

Here i have usermaster table with structure Like

Column_name Type

UserID int

UserName nvarchar(100)

Password nvarchar(400)

FirstName nvarchar(40)

LastName nvarchar(40)

Surname nvarchar(100)

Mobile nvarchar(32)

Email nvarchar(100)

ParentUserID int

-------------------------
Example :
-------------------------
;with cte(UserID,FirstName,ParentUserID,[Level])
as(
select UserID,FirstName,ParentUserID,0 as [Level] from usermaster where UserID = 1
union all
select u.UserID,u.FirstName,u.ParentUserID, c.[Level]+1 as [Level] from UserMaster u inner join cte c on c.UserID=u.ParentUserID
)
select * from cte
---------------------------
output
---------------------------

UserID FirstName ParentUserID Level

1 Jayendra 0 0

2 aaa 1 1

3 bbb 1 1

4 ccc 1 1

13 chaa 1 1

14 ccc 1 1

16 jay 3 2

12 chaa 2 2

15 chaa 2 2

print 1 to 100 by CTE

CTE

How to print 1 to 100 in sql by using CTE?

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how to print 1 to 100 in single select statement.

-------------------------
Example :
-------------------------
;with cte (no)
as(
select 1 as no
union all
select no+1 from cte where no < 100
) select no from cte
---------------------------
output
---------------------------
no
1
2
3
4
5
6
7
.
.
.

100

use of Distinct

Distinct

Sometimes from data table, we may get redundant data.
To avoid redundant output, distinct keyword is used.

Example:

select distinct CustomerName from Customers

--get distinct name

select distinct CustomerName,Country from Customers

--get distinct name from every country event its repeat in other country

Pattern Matching by like

Pattern Matching

To compare one string with another ‘like’ is used.
For this, like will use wildcard characters that are

% for matching string of any length
_ for matching of single characters

Example

select * from customers where CustomerName like 'j%'
-- search records startig with j

select * from customers where CustomerName like '%a'
-- search records ending with a

select * from customers where CustomerName like '%ye%'
-- search records which contain ye any where

select * from customers where CustomerName like '_a%'
-- search records which has second character a

Keys in sql server - key Constrains

To avoid duplication, we need to define constrains on data.
For example, all students will have unique roll number.
Here are primary key, foreign key, unique key etc.

Primary Key

Primary key is column inside table that can be used to uniquely identify a record.
For example, we can get record of particular employee based
on his employee number.
We can define primary key at the time of table creation.
Syntax: create table <tableName> (<columnName><datatype>(<size>)Primary key));
Example : CREATE TABLE Employee (Emp_Id int PRIMARY KEY IDENTITY(1,1), LastName varchar(255) NOT NULL, FirstName varchar(255), LastName varchar(255), Add varchar(255), City varchar(255)
Here Emp_Id field is primary key & auto incremented
which has been specified by Identity(1,1)

There are some attributes of primary key.

It will not allow duplicate values.
It will not allow null values.
Only one primary key is allowed per table.
It’s main purpose is record uniqueness.

Foreign Key

When one column is primary key in one table and being referred in second table it is called foreign key in second table.
For example, Emp_Id of employee will be referred in personal information table, leave table, salary table and so on..
Syntax: <columnName><datatypa>(<size>) references <tableName> [(<columnName>)]
Example:

create table Salary_Master(ID int primary key, Emp_ID numeric(5,0) references Employee_Master, basic numeric(5,0) date datetime);
This will refer Emp_Id from Employee_Master table and inside Employee_Master it is primary key.

Attributes of foreign key are…

Parent that is being referred must have primary key.
Parent record can be deleted if there is no record in child.
Child may have duplicate value from parent but parent can not have.
Record can not be inserted in child if corresponding record does not exist in parent table.

Unique Key

When column inside the table is defined as unique key, this key can be used to uniquely identify a record.
It is similar to primary key but difference is it allows null inside column where as primary key will not allow null value.
A table can have more than one unique key but it is not possible with primary key.

Saturday, June 1, 2019

Types of SQL Commands

SQL command are divided in to 4 parts

1. DDL

DDL stands for Data definition language.
It is use to define structure of database and table.
user can create change and delete structure of table.

List of DDL commands are

create

Create command is use to create structure of table.
Syntax: create table <table_name> (<columnName1> <datatype>(<size>));
Example: crate table User_Master(Name varchar(50),Age numeric(18,0), Address1 varchar(500));

alter

after create structure changes or modification is done by alter command
alter command is use to add new column, delete existing column and increase size of column
Add new column
Syntax: alter table <tableName> add(<NewColumnName1> <dataType>(<Size>),<NewColumnName2><datatype>(<size>)..);
Example: alter table User_Master add Address2 varchar(400);
this command will add new column in existing table User_Master
Delete column
Syntax: alter table <tableName> drop column<columnName>;
Example: alter table User_Master drop column Address2;
This will delete column Address2 from table User_Master
Modify column size
Syntax: alter table <tableName> alter (<columnName> <DataType> <newSize>));
Example: alter table User_Master alter Address2 varchar(500);

rename

this command is used to rename the existing table.
Syntax: exec sp_rename <OldtableName>,<newTableName>
Example: exec sp_rename User_Master ,Member_Master
This will change name of User_Master to Member_Master

truncate

Truncate will empty table completely.
It will drop table first and then will recreate structure of table.
Syntax : truncate table <tableName>;
Example: truncate table Member_Master;
This will delete and recreate table again.

drop

This command will discard whole table.
Syntax : drop table <tableName>;
Example : drop table Member_Master;

describe

This command will describe the structure of table
Syntax : exec sp_help tablename
Example : exec sp_help Member_Master;
result is in format of column name, datatype and size

2. DML

DML is data manipulation language.
It is used to manipulate data inside table.

list of DML Commands are:

Insert

This command is used to insert records inside table.
Syntax: insert into <tableName>(columnName1,columnName2,..column Name...n) values (value1,value2,..valuen);
Example: insert into Member_Master(FName,LNane,Age,Address) values (‘Name1’,‘Name2’,28,’Address_here’);

Update

This command is used to modify data inside table.
Syntax: update <tableName> set <columnName1>=value1,<columnName2>=value2..<columnName n=value n;
Example:update Member_Master set Name=‘new name’;
Importnt : Condition is specified by where clause
Example : update Member_Master set Name=‘new name’ where id = 55
it will only update record which has id 55

Delete

This command is used to delete records from table.
Syntax: delete from <tableName>;
Example: delete from Member_Master;
This will delete all records from Member_Master,
for delete particular record we need to specify where clause as like update command.
Example : delete from Member_Master where id = 55
it will only delete rcord which has id 55

3. DQL

DQL stands for Data Query Language.

List of DQL commands are

Select

use to get data from table
Syntax : select <columnName1>,<columnName2>,..<ColumnName n> from <tableName>;
This will select all columns from table.
Example : select Name,Age,Gender,Address from Person_Master;
for get specific record add where clause who have age > 15 then query will be,
select * from Member_Master where Age > 15;

4. TCL

TCL stands for Transaction control language

List of TCL commands are

Commit

Once any transaction has been performed successfully
commit will save changes to the database.
Commit is saving of result of all actions or queries.
Syntax : commit;
Example :

begin tran
delete from User_Master where id =111
Commit

Than after execute rollback command it will not show deleted record

Rollback

Once any transaction has been done, to undo that transaction, rollback can be used.
Rollback can undo only last transaction.
Syntax : rollback;
Example:

begin tran
delete from Member_Masterwhere id =104
select * from Student_Master
rollback tran

select * from Student_Master

When we execute above code it will not show deleted record but after rollback execution record will display
after commit you can not rollback transaction.
every transaction is started with begin tran

Save point and Rollback to save point

Sometimes, during execution of transactions, we need to check whether some conditions are met after transactions or not, if conditions are met, we want to commit changes but If condition doesn’t met we need to rollback to any previous transactions.
For this type of requirement, we can use rollback to save point.
In this, we are defining one save point.
Example :

begin tran

save tran trans1

delete from ....

save tran trans2