What are the different types of ISOLATION levels in SQL Server ?

Different types of ISOLATION levels in SQL Server?

1.  Read Uncommitted
2.  Read Committed
3. Repeatable Read
4. Serilalizable Read
5. Snapshot 
6. Read Committed Snapshot 

AdventureWorks Employee details using JOINS

/* Find the Employee details like Name,department,shift....... from AdventureWorks database */


SELECT
H.EmployeeID,
C.FirstName+' '+C.LastName AS FULL_NAME,
D.Name AS DEPARTMENT_NAME,
S.Name AS SHIFT_TYPE,
CONVERT(VARCHAR(15),S.StartTime,108)AS StartTime,
CONVERT(VARCHAR(10),S.EndTime,108) AS EndTime,
P.PayFrequency,
P.Rate
FROM HumanResources.EmployeeDepartmentHistory H
JOIN
HumanResources.Employee E
ON H.EmployeeID = E.EmployeeID
JOIN
Person.Contact C
ON E.ContactID = C.ContactID
JOIN
HumanResources.Department D
ON D.DepartmentID = H.DepartmentID
JOIN
HumanResources.Shift S
ON S.ShiftID = H.ShiftID
JOIN
HumanResources.EmployeePayHistory P
ON P.EmployeeID = E.EmployeeID

WHICH AGE GROUP PEOPLE IS GETTING HIGHEST SALARY

/* WHICH AGE GROUP PEOPLE IS GETTING HIGHEST SALARY */

-- MAXIMUM SALARY
-- MINIMUM SALARY

/* REPLACE MIN() FUNCTION WITH MAX() YOU WILL GET MAXIMUM SALARY OR ELSE YOU WILL GET MINIMUM SALARY */


CREATE DATABASE MY_PRACTICE

GO

USE MY_PRACTICE

GO

CREATE TABLE [dbo].[persons](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [Name] [char](20) NULL,
 [age] [int] NULL,
 [salary] [float] NULL)

 GO

 INSERT INTO persons VALUES ('sagar',28,5000),('shirish',25,2000),('raj',32,4000),('AMBI',27,8000)
,('SURESH',30,6000),('vidya',22,1500)

GO

SELECT * FROM persons


GO

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 20 AND 25)
GROUP BY id,Name,age

UNION ALL

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 26 AND 30)
GROUP BY id,Name,age

UNION ALL

SELECT ID,NAME,AGE,MIN(salary)as MAX_SALARY FROM persons
WHERE salary = (SELECT MIN(salary) FROM persons WHERE age BETWEEN 31 AND 35)
GROUP BY id,Name,age

Find the Highest,2nd Highest,Nth Highest Salary in SQL SERVER

Hello,

Below is the complete SQL QUERY to find the Highest Salary and 2nd Highest salary & Nth highest salary.




CREATE DATABASE MY_PRACTICE

GO

USE MY_PRACTICE

GO
CREATE TABLE [dbo].[persons](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [char](20) NULL,
[age] [int] NULL,
[salary] [float] NULL)

GO

INSERT INTO persons VALUES ('sagar',28,5000),('shirish',25,2000),('raj',32,4000),('AMBI',27,8000)
,('SURESH',30,6000)

go
--Employee details
select * from dbo.persons
go

-- Find the highest salary of the employee
select name,MAX(salary) from persons
group by Name

go
-- Find the 2nd highest salary of the employee
select id,name,max(salary) as second_high_salary from persons
where salary <> (select MAX(salary)from persons)
group by id,Name order by second_high_salary desc

go
-- Find the Nth highest salary for the Employee
select * from persons P1
WHERE (1) = (SELECT COUNT(DISTINCT(P2.salary)) FROM persons P2 WHERE P2.salary > P1.salary )

-- USING TOP KEYWORD FIND THE NTH HIGHEST SALARY

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 1 SALARY FROM persons
ORDER BY salary DESC)
AS EMP
ORDER BY salary