Saturday, 11 June 2016

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 






No comments:

Post a Comment