______________________________________________________________________________________________
EXCEPTION HANDLING:

ERRORS:
1.) Syntax errors: When keyword and grammer is not properly used
2.) Logical errors: When desired is not there
3.) Exception Errors: External errors happen situationally for divide by zero,primary key errors

Using Try-Catch Exceptions are handled
syntax:
Begin try
--Statements
End try
Begin Catch
--Statements
End Catch

e.g.:

declare @n1 int,@n2 int,@result decimal
Begin try select @n1 = 100,@n2 = 0
select @result = @n1/@n2
print @result
end try
begin catch
print 'Error occured due to divide by zero is encountered:'
print Error_number()
print Error_message()
print Error_Line()
print error_severity ()
end catch

select * from sys.messages where message_id = 8134
select * from sys.syslanguages
select * from sys.sysprocesses

______________________________________________________________________________________________
TRANSACTIONS:

Logical grouping of a  set of DML operations which must execute totally or none

transaction execute statements temporarily in buffer and makes it parmanent when it is committed......
Before committin u may revert or rollback the operations

Transactions gets over either when u rollback or commit the transaction

Types of transactions:
1.) Implicit Transaction: These are initiated by server automatically and committed also. Aotu commit is enabled by default in SQL server,           which can be changed using statements;
         SET IMPLICIT_TRANSACTIONS ON/OFF
2.)Explicit Transaction: Starting and finishing both are done exclusively by users using the block:
         BEGIN TRANSACTION AND ROLLBACK TRANSACTION OR COMMIT TRANSACTION
use employeeDB
select * from employees

set implicit_transactions off    --Default: Server will auto commit for this state
insert into employees values (110,'aaa',1111,210,111,'Elec')
rollback tran
delete from employees where ecode = 110

begin try
begin tran
delete from employees where ecode = 103
--statement1
commit
print 'Records deleted'
--statement2
end try begin catch
--statement1
print 'Error deleting the records'
rollback tran
end catch

Transactions can also be nested.........
For multiple ongoing transactions we use the isolations

set transaction isolation level read committed
--Other transaction will get only committed data
set transaction isolation level read uncommitted
--Other transactions will get uncommitted data also i.e. dirty read

use employeeDB
set transaction isolation level read uncommitted
begin tran
select * from employees where ecode =102

______________________________________________________________________________________________
TOTALLING AND SUB-TOTALLING OF DATA

select deptid,sum(salary) from employees
group by deptid with rolldown

Two-Dimensional Sub Totalling using: PIVOT (Also called as cross tab queries)

create table MonthlySales
(
eid int,
salesamt int,
monthid int
)

insert into MonthlySales values (1,100,1)
insert into MonthlySales values (1,200,2)
insert into MonthlySales values (1,300,3)
insert into MonthlySales values (1,400,4)

insert into MonthlySales values (2,1000,1)
insert into MonthlySales values (2,2000,2)
insert into MonthlySales values (2,3000,3)
insert into MonthlySales values (2,4000,4)

insert into MonthlySales values (3,10000,1)
insert into MonthlySales values (3,20000,2)
insert into MonthlySales values (3,30000,3)
insert into MonthlySales values (3,40000,4)

select * from MonthlySales

select * from MonthlySales PIVOT( sum(salesamt) for monthid IN ([1],[2],[3],[4])) as a

CTE (Common Table Expressions)

with totalsales(eid,m1,m2,m3,m4)
as
(
select * from MonthlySales PIVOT( sum(salesamt) for monthid IN ([1],[2],[3],[4])) as a
)
select sum(m1) "Jan",sum(m2) "Feb",sum(m3) "Mar",sum(m4) "Apr"
from totalsales

______________________________________________________________________________________________
RANKING OF RECORDS:

select Row_number() over(order by ecode) as "Row Number",ecode,ename,salary,deptid,bonus from employees
delete employees where ecode = 110
select Row_number() over(order by (select 1)) as "Row Number",ecode,ename,salary,deptid,bonus from employees

select Row_number() over(partition by deptid order by ecode) as "Row Number",ecode,ename,salary,deptid,bonus from employees --To inturn rank the result set by any column like deptid as in the query above