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)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

-- 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.Name) 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
 
 ;


run \| edit \| history \| help	0