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
· Howto 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) ;
· Howto 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)
· Selectalternate 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
· Selectnth 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
· Supposea 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.id1FROM tbltests t1WHERE NOT EXISTS(SELECT * FROM tbltests t2WHERE t2.id = t1.id1
AND t2.id1 = t1.id
AND t2.id < t2.id1)
