SQL SERVER Queries

Department wise highest salary

declare @table table
(
    Emp_ID int,
    Emp_Name varchar(10),
    Dept_ID int,
    Salary decimal(10,2)
)

insert into @table values
(101, 'Ram',     10,         10000.00),
(111, 'Ram 2',     10,         10000.00),
(102, 'Mohan',   10,         11000.00),
(103, 'Sohan',   10,         12000.00),
(104, 'John',    20,         10500.00),
(105, 'Peter',   20,         11500.00),
(106, 'Herry',   20,         12500.00),
(107, 'Munna',   30,         12000.00),
(108, 'Helan',   30,         14000.00),
(109, 'Cherry',  30,         15000.00),
(110, 'Mack',    40,         13000.00);

 
 
    --- example 1
  SELECT Dept_ID,  max(salary) FROM @table  WHERE salary NOT IN  (SELECT max(salary) FROM @table GROUP BY Dept_ID) GROUP BY Dept_ID;
  
   --- example 2
  
with rw as(
 select
       Emp_Name,
       Dept_ID,
       Salary,
       Row_number() over (PARTITION BY Dept_ID order by Salary desc) as rownum
    from
       @table
) select * from rw

--Exmaple 3

Select * From (SELECT    A.*,

(SELECT COUNT(DISTINCT(B.salary))
FROM @table B
WHERE B.salary >= A.salary and A.Dept_ID=B.Dept_ID) as Rank FROM  @table A) Emp
Where Emp.Rank <=1

References:http://www.geekinterview.com/question_details/25636

GROUP BY, CUBE, ROLLUP and SQL SERVER 2005

September 9, 2008 by chiragrdarji

       I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below,

   1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)

   2: FROM Orders GROUP BY CustomerName,CustomerName

Fig – (1) Group By clause.
     Which will returns the result as shown below,

Fig – (2) Result of GROUP BY clause
    However what if you want result as display below,

Fig – (3) Desire result
       Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP.  ROLLUP adds new row for each column used in GROUP BY clause. First have a look at the query and then we will discuss more,

   1: SELECT 

   2:     CASE 

   3:     WHEN GROUPING(customername) = 1 THEN 'All Customer' 

   4:     ELSE customername END CustomerName,

   5:     CASE WHEN GROUPING(itemname) = 1 THEN 'All Items' 

   6:     ELSE itemname END ItemName,

   7: SUM(Quantity*PricePerCase) 

   8: FROM orders GROUP BY customername,itemname

   9: WITH ROLLUP

Fig – (4) Query for output shown in fig – 3
       As you can see in query we have used ROLLUP after GROUP BY clause. Here ROLLUP has added new row at line 3,5 and 6 in fig 3. If you have used only one columns in GROUP BY clause then only row will have been added. The new clause in query is GROUPING. GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP or CUBE then the value of GROUPING column is 1 else 0.
       In fig – 3 we have total price by user name, wow lets assume you want the total price by item name also. Here you have to use CUBE as shown below,

   1: SELECT 

   2:     CASE 

   3:     WHEN GROUPING(customername) = 1 THEN 'All Customer' 

   4:     ELSE customername END CustomerName,

   5:     CASE WHEN GROUPING(itemname) = 1 THEN 'All Items' 

   6:     ELSE itemname END ItemName,

   7: SUM(Quantity*PricePerCase) 

   8: FROM orders GROUP BY customername,itemname

   9: WITH CUBE

Fig – (5) CUBE clause


Compute

   declare @Sales  TABLE (EmpId INT, Yr INT, Sales MONEY)
    INSERT @Sales VALUES(1, 2005, 12000)
    INSERT @Sales VALUES(1, 2006, 18000)
    INSERT @Sales VALUES(1, 2007, 25000)
    INSERT @Sales VALUES(2, 2005, 15000)
    INSERT @Sales VALUES(2, 2006, 6000)
    INSERT @Sales VALUES(3, 2006, 20000)
    INSERT @Sales VALUES(3, 2007, 24000)

    SELECT EmpId, Yr, SUM(Sales) AS Sales
    FROM @Sales
    GROUP BY EmpId, Yr WITH CUBE

    SELECT EmpId, Yr, SUM(Sales) AS Sales
    FROM @Sales
    GROUP BY EmpId, Yr WITH ROLLUP
   
     SELECT EmpId, Yr, Sales AS Sales
    FROM @Sales order by empid
     compute sum(sales)   BY empid
    
    

How to display even number employees 

   declare @Sales  TABLE (EmpId INT, Yr INT, Sales MONEY)
    INSERT @Sales VALUES(1, 2005, 12000)
    INSERT @Sales VALUES(1, 2006, 18000)
    INSERT @Sales VALUES(1, 2007, 25000)
    INSERT @Sales VALUES(2, 2005, 15000)
    INSERT @Sales VALUES(2, 2006, 6000)
    INSERT @Sales VALUES(3, 2006, 20000)
    INSERT @Sales VALUES(3, 2007, 24000)
   
   
    select *  from @sales where empid%2 =0

How to change gender male to female and female to male  using single query

   declare @Sales  TABLE (EmpId INT, Yr INT, Sales MONEY, gender varchar(2))
    INSERT @Sales VALUES(1, 2005, 12000,'f')
    INSERT @Sales VALUES(1, 2006, 18000,'f')
    INSERT @Sales VALUES(1, 2007, 25000,'f')
    INSERT @Sales VALUES(2, 2005, 15000,'M')
    INSERT @Sales VALUES(2, 2006, 6000,'M')
    INSERT @Sales VALUES(3, 2006, 20000,'f')
    INSERT @Sales VALUES(3, 2007, 24000,'f')
    select * from @Sales
    update @Sales set gender=case when gender='F' then 'M' else 'F' end
    select * from @Sales
 
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

find duplicate records

 Coalesce Example

declare @table table
(
    Emp_ID int,
    Emp_Name varchar(10),
    Emp_Name2 varchar(10),
    Dept_ID int,
    Salary decimal(10,2)
)

insert into @table values
(101, 'Ram', 'Ram',     10,         10000.00),
(111, 'Ram 2','kk',     10,         10000.00),
(102, 'Mohan','kk',   10,         11000.00),
(103, 'Sohan','kk',   10,         12000.00),
(104, 'John','kk',    20,         10500.00),
(105, 'Peter','kk',   20,         11500.00),
(106, 'Herry','kk',   20,         12500.00),
(107, 'Munna','kk',   30,         12000.00),
(108, 'Helan', 'kk',  30,         14000.00),
(109, 'Cherry','kk',  30,         15000.00),
(110, 'Mack',  'kk',  40,         13000.00),
(111, null,   'kk',30,        15000.00),
(112, null,  null, null,        15000.00);

select Coalesce(emp_name,Emp_Name2,'noname') from @table