Frequently asked SQL Server Queries

Data to test:

CREATE TABLE [dbo].[emp](

      [emp_id] [nchar](10) NULL,

      [emp_name] [nchar](10) NULL,

      [salary] [int] NULL

) ON [PRIMARY]

 insert emp ( emp_id,emp_name,salary )  select '1','ABC',-20000

 insert emp ( emp_id,emp_name,salary )  select '2','XYZ',-5000

 insert emp ( emp_id,emp_name,salary )  select '3','PQR',-7800

 insert emp ( emp_id,emp_name,salary )  select '4','AK ',10000

 insert emp ( emp_id,emp_name,salary )  select '5','MP ',19000

 insert emp ( emp_id,emp_name,salary )  select '6','KHJ',11000

 insert emp ( emp_id,emp_name,salary )  select '7','LTR',13000

 insert emp ( emp_id,emp_name,salary )  select '8','MAN',14000

select *from emp.

emp_id                emp_name        salary

1              ABC                     -20000

2              XYZ                         -5000

3              PQR                       -7800

4              AK                          10000

5              MP                         19000

6              KHJ                         11000

7              LTR                         13000

8              MAN                     14000

·        How to get nth Highest Salary from Emp Table

SELECT * FROM  emp a WHERE 3 = (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

emp_id      emp_name    salary

7           LTR         13000

for nth salary

SELECT * FROM  emp a WHERE nth= (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

·        How to delete duplicate rows from a table

--insert auto increment id if not in table

ALTER TABLE emp ADD autoid INT IDENTITY(1,1)

--then query  returns autoid for unique rows

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary

--delete duplicate rows

DELETE

FROM emp

WHERE autoid NOT IN

(

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary)

·        Select alternate rows from Emp table

--Using CTE

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp

)

SELECT * FROM CTE WHERE ROW%2=0

--Using SubQuery

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW%2=0

Output:

ROW   emp_id      emp_name    salary      autoid

2     2           XYZ         -5000       10

4     4           AK          10000       4

6     6           KHJ         11000       6

8     8           MAN         14000       8

·        Select nth record from Emp table

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW=3

Output:

ROW      emp_id                emp_name        salary         autoid

3              3              PQR                                       -7800                     3

·        Suppose a column has some -Ve values and +Ve  values.Find the sum of -Ve numbers and the sum of the +Ve numbers.

data:

SELECT

SUM(CASE WHEN salary < 0 THEN salary ELSE 0 END) Sum_of_negative_amount,

SUM(CASE WHEN salary > 0 THEN salary ELSE 0 END) Sum_of_positive_amount

FROM emp;

Sum_of_negative_amount  Sum_of_positive_amount

-32800                  6000

• Suppose You have a table

   create table tbltests(id int ,id1 int)

  insert into tbltests(id,id1) values

  (1,2),(2,1),(3,null),(4,5),(5,4)

  
  

  Table Data:

   1  2

   2  1

   3  NULL

   4  5

   5  4

   Fetch data like

  id            id1
  
  1              2
  
  3              NULL
  
  4              5
  
  Query:
  
  
  
  SELECT t1.id,t1.id1
  FROM tbltests t1
  WHERE NOT EXISTS(SELECT * FROM tbltests t2
                   WHERE t2.id = t1.id1
                     AND t2.id1 = t1.id
                     AND t2.id < t2.id1)