Question and Answers in Sql Server - Part 8

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


31. Usage of Orderby , Group by , Having , Row_number, Rank, Dense_Rank  in TABLE ?

CREATE TABLE employee

(

id INT IDENTITY(1,1),

NAME VARCHAR(30),

age INT,

salary INT,

department varchar(40)

)

CREATE PROCEDURE insert_record(@name varchar(30),@age int,@salary int,@department varchar(40))

AS

BEGIN

 INSERT INTO employee

 (

       name,

       age,

       salary,

       department

 )

 SELECT     @name,

             @age,

             @salary,

             @department

END

EXEC insert_record 'danie',33,1000000,'Finance'

EXEC insert_record 'Jim',23,2000000,'Technical'

EXEC insert_record 'Alex',27,4000000,'Technical'

EXEC insert_record 'Jhon',43,1700000,'HR'

EXEC insert_record 'Cruz',28,900000,'HR'

EXEC insert_record 'Misa',23,1500000,'Technical'

-- Order the employee based on name

SELECT * FROM employee ORDER BY name

-- Grouping the employee based on department

SELECT COUNT(age) AS 'no of employees',department

FROM employee

GROUP BY department

HAVING department IN ('Technical','HR')

-- Rank the employee based on age and partition based on age and department

SELECT name , RANK() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee

SELECT name , row_number() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee

SELECT name , Dense_Rank() OVER(PARTITION BY age,department ORDER BY age DESC) FROM employee