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