Monday 4 February 2013

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)


2 comments:

Please leave a comment for this post