Scenario1

--1.how to get max sal by using Dense_Rank() function?
select * from employee
go

select * from (select E.*,DENSE_RANK() over(order by Sal desc) rank1 from employee e) a
where rank1=2
-------------------------------
--2.purpose of @@rowcount,@@trancount?
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the
-- transaction count by 1.
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
-- The COMMIT statement will decrement the transaction count by 1.
COMMIT
PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET Title = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO
-------------------
--3.how to delete duplicate rows in sql server
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
--The first step is to identify which rows have duplicate primary key values:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
--If there are only a few sets of duplicate PK values,
--the best procedure is to
--delete these manually on an individual basis. For example:
set rowcount 1
delete from t1
where col1=1 and col2=1

select * from t1