SQL Larning

JOIN

Given Two Tables:

Orders Table:

Customers Table :

1. INNER JOIN:

Only get common Value means Returns all rows when there is at least one match in BOTH tables.

SELECT Orders.OrderID, Customres.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customres

ON Orders.CustomerID=Customres.CustomerID;

2. Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural Join Syntax is,

SELECT *

from table-name1

NATURAL JOIN

table-name2;

Example of Natural JOIN

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The result table will look like,

ID	NAME	Address
1	abhi	DELHI
2	adam	MUMBAI
3	alex	CHENNAI

In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.

LEFT JOIN(LEFT OUTER JOIN)

The left outer join returns a result table with the matched data of two tables then remaining rows of the lefttable and null for the right table's column. (Return All matched data and left table all data id in right table not present data then retun null).

SELECT Orders.OrderID, Customres.CustomerName, Orders.OrderDate

FROM Orders

LEFT OUTER JOIN Customres

ON Orders.CustomerID=Customres.CustomerID;

Output: left join(left outer join)

Example 2:

Example of Left Outer Join

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id=class_info.id);

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null

Right JOIN (Right Outer JOIN)

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table's columns.

SELECT Orders.OrderID, Customres.CustomerName, Orders.OrderDate

FROM Orders

RIGHT JOIN Customres

ON Orders.CustomerID=Customres.CustomerID;

FULL JOIN(FULL OUTER JOIN)

The full outer join returns a result table with the matched data of two table then remaining rows of both lefttable and then the right table.

SELECT Orders.OrderID, Customres.CustomerName, Orders.OrderDate

FROM Orders

FULL JOIN Customres

ON Orders.CustomerID=Customres.CustomerID;

SELECT *

FROM Orders

FULL JOIN Customres

ON Orders.CustomerID=Customres.CustomerID;

SQL PROCEDURE AND FUNCTIONS

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .

3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

5. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

6. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

7. We can go for Transaction Management in Procedure whereas we can't go in Function.

Create:

Create Procedure InserOrderdata(@ID varchar(20), @CID varchar(20))

begin

insert into dbo.Orders values(@ID,@CID,'1996-09-18')

end

Call:

exec InserOredrsData 10310,21

Create:

2. Create Procedure ShowData

Begin

Select * from dbo.Customres

End

Call:

Exec ShowData

User Define Function (SQL)

1. Scalar Function : User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function. In scalar function use begin and end.

Create :

create function Gets_Amonut(@id varchar)

returns decimal

begin

declare @amount decimal

set @amount=(select Pamount from SellProduct where ID=@id)

return @amount

end

Ececute:

select dbo.Gets_Amonut(2) as Amount

2. Inline Table-Valued Function : User defined inline table-valued function returns a single table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement. Not use begin and end.

Create :

create function get_Table()

returns Table

return (select * from SellProduct)

Execute :

Select * from get_Table()

3. Multi-Statement Table-Valued Function : User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

--Create function for EmpID,FirstName and Salary of Employee

Create function fnGetMulEmployee()

returns @Emp Table(EmpID int, FirstName varchar(50),Salary int)

begin

Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;

--Now update salary of first employee

update @Emp set Salary=25000 where EmpID=1;

--It will update only in @Emp table not in Original Employee table

return

end

Create Function :

create function fnGetMulEmploy()

returns @sellP Table(id varchar(50),Pcode varchar(50), Cname varchar(50),Pamount decimal(18,4))

Begin

Insert into @sellP Select e.ID,e.Cname,e.Pcode,e.Pamount from SellProduct e;

update @sellP set Pamount=25000 where id=2;

return

end

--Now call function

Select * from fnGetMulEmploy()

Trigger

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

· A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

· A database definition (DDL) statement (CREATE, ALTER, or DROP).

· A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes:

· Generating some derived column values automatically

· Enforcing referential integrity

· Event logging and storing information on table access

· Auditing

· Synchronous replication of tables

· Imposing security authorizations

· Preventing invalid transactions

//Only store date time

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER UpdateStatus

ON Admin

AFTER INSERT,DELETE,UPDATE

BEGIN

insert into tblUserstatus(LastUpdateTime)

values(getdate());

END

//store date time old password and new password

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER UpdateStats

ON Admin

AFTER INSERT,DELETE,UPDATE

BEGIN

declare @olv varchar(30)

declare @newv varchar(30)

--fetch old value

select @olv=Password from deleted

--fetch new value

select @newv=Password from inserted

insert into tblUserstatus(LastUpdateTime,oldPassword,newPassword)

values(getdate(),@olv,@newv);

END

Update password:

After update password fire tigger and store date time, old password and new password.

//With username

USE [UseTrigger]

/****** Object: Trigger [dbo].[UpdateStats] Script Date: 09/01/2016 16:55:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER TRIGGER [dbo].[UpdateStats]

ON [dbo].[Admin]

AFTER INSERT,DELETE,UPDATE

BEGIN

declare @olv varchar(30)

declare @newv varchar(30)

declare @nm varchar(30)

--fetch old value

select @nm=UserName from deleted

select @olv=Password from deleted

--fetch new value

select @newv=Password from inserted

insert into tblUserstatus(LastUpdateTime,oldPassword,newPassword,username)

values(getdate(),@olv,@newv,@nm);

END

INDEXES

1. Clustered Index

2. Non Clustered Index

1. Clustered Index: A clustered index determines the physical order of data in a table.

1. nonClustered Index: A nonClustered index is analogous to an index in a textbook. The data is stored in one place, the index in another place. The index will have pointers to the storage location of data.

Since, the nonClustered index is stored separately from the actual data, a table can have more than one non clustered index, just like how a book can have an index by chapters’ at the beginning and another index

By common terms at the end.

In the index itself, the data is stored in an ascending or descending order of the index key, which doesn't in any way influence the storage of data in the table.

Select * from Employee

--Cluster Index

Create Clustered Index TBemplyee_Gender_Salary

ON Employee(salary ASC)

--nonclustered index

Create nonClustered Index tbEmployee_name

ON Employee(Name)

C# tutorial.

Search This Blog

SQL Larning

2. Natural JOIN

Example of Natural JOIN

Example of Left Outer Join

Advance Difference

2. Inline Table-Valued Function : User defined inline table-valued function returns a single table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement. Not use begin and end.

Create :

3. Multi-Statement Table-Valued Function : User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

Benefits of Triggers

Comments

Post a Comment