SQLthursday5.sql

Select * from sys.dm_exec_sessions

Select is_user_process, Login_name 
from sys.dm_exec_sessions 
order by login_time desc

create trigger trlogontrigger
on all server
for logon

as
begin

declare @Loginname varchar(100)

set @Loginname = original_login()

IF (select count(*) from sys.dm_exec_sessions
where is_user_process = 1
and original_login_name = @loginname
) > 3
BEGIN
ROLLBACK;
END

DROP TRIGGER TRLOGONTRIGGER ON ALL SERVER

SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Id) AS RunningTotal
FROM tblEmployee

SELECT Name, Salary,
SUM(Salary) OVER(
ORDER BY Id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM tblEmployee

SELECT Name, Salary,
SUM(Salary) OVER(
ORDER BY Id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS SumLastTwoRows
FROM tblEmployee

SELECT Name, DepartmentId, Salary,
SUM(Salary) OVER(
PARTITION BY DepartmentId
ORDER BY Id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS DeptRunningTotal
FROM tblEmployee

SELECT EID,ENAME,ESALARY,
SUM(ESALARY) OVER (ORDER BY ESALARY) AS TOTAL ,
AVG(ESALARY) OVER (ORDER BY ESALARY) AS AVERAGE , 
COUNT(ESALARY) OVER (ORDER BY ESALARY) AS COUNT 
FROM EMPLOYEE

SELECT EID,ENAME,ESALARY,
SUM(ESALARY) OVER (ORDER BY ESALARY ) AS TOTAL ,
AVG(ESALARY) OVER (ORDER BY ESALARY ROWS BETWEEN UNBOUNDED PRECIDING AND CURRENT ROWS ) AS AVERAGE , 
COUNT(ESALARY) OVER (ORDER BY ESALARY) AS COUNT 
FROM EMPLOYEE

--IN PARTITION ,WHEREIN IT WILL BE DOING USING INDIVIDUAL ROWS AND IT WILL SHOW ALPHABETICAL ORDER 


SELECT EID,ENAME,ESALARY 
RANK() OVER (ORDER BY ESALARY) AS RANKVALUE 
FROM EMPLOYEE 

---OVER IS USED FOR GROUPING INDIVIDUAL ROWS 
--- RANKFUNCTON WILL SKIP IF A PERSON IS HAVING SAME SALARY 

SELECT EID,ENAME,ESALARY 
DENSE_RANK() OVER (ORDER BY ESALARY) AS RANKDENSEVALUE 
FROM EMPLOYEE 

---RANKDENSE WILL NOT SKIP THE VALUE 
--- DIFFERNECE BETWEEN RANK AND RANKDENSE 

SELECT EID,ENAME,ESALARY 
DENSE_RANK() OVER (ORDER BY ESALARY DESC) AS RANKDENSEVALUE 
FROM EMPLOYEE 

SELECT EID,ENAME,ESALARY ,
ROW_NUMBER() OVER (ORDER BY ESALARY DESC) AS ROWNUM
FROM EMPLOYEE

DELETE TRIGGER ON DATABASE 
DELETE TRIGGER ON ALL SERVER 

DROP TRIGGER trigger_name ON DATABASE

SELECT * FROM EMPBACKUP