SQL Server : Some common queries on Employee and Department table

Schema of Employee and Department table are as follows :

Create Department :

Create table Department (DepartmentId integer not null primary key identity, DepartmentName varchar(50));


Create Employee :

Create table Employee(
EmployeeId integer not null identity primary key
,FirstName varchar(50)
,LastName varchar(50)
,DepartmentId integer
);

---------------Some common queries on above tables-----------------

--Department wise employees name and other details 
select d.DepartmentName, e.FirstName,e.LastName,e.Salary
from Employee e left join Department d on e.departmentId=d.DepartmentId

-- Find out no. of Employees in Each Deaprtment.
---------------------------------------------------------
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
group by d.departmentID,d.departmentName
 --------------------------------------------------------
-- find out departments where no.of employee more than 3
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
where d.departmentname is not null
group by d.departmentID,d.departmentName having  count(e.employeeid)>3

-----------------------------------------------------
-- Find out name of employee,salary and departmentName, who has max salary in their department .
--i.e. departmentname and name of employee who has max salary in their department.

SELECT DepartmentName, firstName,LastName, Salary, Salaryrank from
(select d.DepartmentName, e.firstName,e.LastName, e.Salary, row_number() over (partition by e.departmentID order by salary desc)  as Salaryrank
from Employee e
left join Department d on e.departmentId=d.DepartmentId) as result
where result.Salaryrank=1
-- Result will contains department name ,name of employee who has highest salary in their department, salary

SQL Server : Some common and useful queries

Find all the employees and their department name

select emp_id,first_name,Last_Name,dept_id,dept_name
from employee e
inner join 
department d on e.department_id = d.DEPT_ID

we can also apply where clause to add filter for emp_id or first_name and find department and other details for specific employee.

Find all the employees who is not part of any department

select emp_id,first_name,Last_Name,dept_id,dept_name from employee e
left join department d on e.department_id = d.DEPT_ID

where e.department_id is null

Find no. of employees in each department and sum of their salary (i.e. total salary distributed for each department)

select count(emp_id) as empCount,sum(salary),department_id from employee

group by department_id

Find the no.of employee and department where salary distributed is greater than 100000

select count(emp_id) as empCount,sum(salary) as totalSalary, department_id from employee

group by department_id having sum(salary) >100000

ROW_NUMBER(), RANK(), DENSE_RANK()


SELECT  
--ROW_NUMBER() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
 ROW_NUMBER() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,
EMP_ID,DEPARTMENT_ID,FIRST_NAME,SALARY 
FROM EMPLOYEE where salary is not null Order by DEPARTMENT_ID

SELECT  
--RANK() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,
EMP_ID,FIRST_NAME,SALARY ,DEPARTMENT_ID
FROM EMPLOYEE  where salary is not null Order by DEPARTMENT_ID

SELECT  --DENSE_RANK() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,EMP_ID,FIRST_NAME,SALARY ,DEPARTMENT_ID
FROM EMPLOYEE where salary is not null and DEPARTMENT_ID IS NOT NULL
Order by DEPARTMENT_ID

Note: 
ROW_NUMBER will generate sequential row number based on given partition and order by columns.

RANK () will generate rank based on given partition and order by columns. for example :
if  you have given department_Id in partition and order by salary and lets consider that there are two employee who has same salary so for first employee rank would be 1 and second and third employee rank would be 2 and for fourth employee rank would be 4 as row count for 4th employee would be 4th.

DENSE_RANK() is same as rank but it will give rank 3rd for fourth employee instead of 4th.
i.e. rank assignment will not be depend on row number of particular result.
run above queries to see result.

Create Table :

CREATE TABLE [dbo].TEST (
    [Id] int IDENTITY(1,1) primary Key,
    [Name] nchar(50)  NOT NULL

);
creating table with primary key.

Primary key creation with clustered index :

ALTER TABLE [dbo].[Countries]
ADD CONSTRAINT [PK_Countries]
    PRIMARY KEY CLUSTERED ([CountryId] ASC);
GO

Renaming TABLE Column:

EXEC dbo.sp_rename @objname=N'[dbo].[Cities].[ref]', @newname=N're', @objtype=N'COLUMN'

Cursor Example :

ALTER PROCEDURE SP_TEST_DATETIME
 AS
 BEGIN

  DECLARE @WorkingStatrt DateTime
  DECLARE @WorkingEnd DateTime
  DECLARE @TempTime DateTime
  DECLARE @TotalTime datetime
  DECLARE @CaseKey int
  DECLARE @createTimeStamp datetime
  DECLARE @Action varchar(50)
  
DECLARE WorkingHorsCalculation CURSOR FOR
SELECT CASE_KEY,CREATE_TIMESTAMP,ACTION
FROM AUDIT_LOG_CASE 

-- Open cursor
OPEN WorkingHorsCalculation
-- Fetch data from cursor in to variable
FETCH NEXT FROM WorkingHorsCalculation
INTO @CaseKey,@createTimeStamp,@Action
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do operation according to row value
if @Action='Working Case'
begin
PRINT @Action
SET @WorkingStatrt=@createTimeStamp;
PRINT @WorkingStatrt

end
-- Fetch the next cursor
FETCH NEXT FROM WorkingHorsCalculation
INTO @CaseKey,@createTimeStamp,@Action
END
-- Finally do not forget to close and deallocate the cursor
CLOSE WorkingHorsCalculation
DEALLOCATE WorkingHorsCalculation


END

--*******************************************************************----