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

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