SQL_Joins_RankingFunctions, Sql Server

SQL_Joins_RankingFunctions

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

create table Department
( Id int primary key, [Name] varchar(8))

insert into Department(Id,[Name]) values
(1, 'IT')
,(2, 'Finance')
,(3, 'HR')
,(4, 'Admin')
,(5, 'Network')
select * from Department

create table Employee(ID int identity(1,1) primary key, FirstName varchar(200),
                      LastName varchar(200), Gender varchar(8), Salary int, DeptId int foreign key references Department(Id))

insert into Employee(FirstName, LastName, Gender, Salary, DeptId)values
('Rakesh', 'sharma', 'Male', 12000, 1),
('Rakesh', 'Goel', 'Male', 24000,3),
('Vinod', 'sharma', 'Male', 35000,2),
('Rajni', 'sharma', 'Male', 52000,1),
('Renuka', 'Goel', 'Male', 24000,2),
('Vishakha', 'sharma', 'Male', 35000,2)

select * from Employee
go
Select CharIndex('r', FirstName) Position_of_R from Employee where FirstName = 'Rakesh';
go

-- Employees and their departments
select e.FirstName+ ' ' +e.LastName as Employee, d.Name as Department from  Employee e 
Inner Join Department d on e.deptId = d.id

--Employees per department
select d.Name as Department, Count(e.Id) as Employees from  Employee e 
right Join Department d on e.deptId = d.id
group by d.Name, e.deptId

--Departments that dont have any employees 
select distinct d.Name as NoEmployeeHere from Department d 
  left outer join  Employee e on e.deptId = d.id
 where e.deptId is NULL;
 
-- Employees and their departments
select e.FirstName+ ' ' +e.LastName as Employee, d.Name as Department from  Employee e 
Inner Join Department d on e.deptId = d.id

--Employees per department
select d.Name as Department, Count(e.Id) as Employees from  Employee e 
right Join Department d on e.deptId = d.id
group by d.Name, e.deptId

--Departments that dont have any employees 
select distinct d.Name as NoEmployeeHere from Department d 
  left outer join  Employee e on e.deptId = d.id
 where e.deptId is NULL
 
 ;
 -- 2nd highest salary using sub-query
 select top(1) salary from Employee where salary<(
 select max(salary) from Employee
     ) order by 1 desc
     
  -- nth highest salary using sub-query let 3rd highest
  select top(1) * from (
  select distinct top(3) salary from Employee order by 1 desc)result
      order by 1
      
   -- nth highest salary(let 3rd highest) using dense-rank as row_number() marks duplicates as unique number whereas dense_rank doesn't
   
   select salary from (
   select dense_rank() over(order by salary) DenseRank, salary from Employee
       )result where DenseRank =3


run \| edit \| history \| help	0