Select top 2 1 '3'
A. 3 will be column name and 1 as data.
3
1
Q. In table_user.gender column change 'male to female' and 'female to male' in one SQL statement.
A. UPDATE table_user
SET gender =
CASE gender
WHEN 'male' THEN 'female'
WHEN 'female' THEN 'male'
ELSE gender
END
Q. Delete Duplicate Records from tbl_emp where employee name and city should not be repeated. i.e if emp_name and city are same are same, it should be concidered duplicate
A. DELETE FROM MyTable
WHERE ID NOT IN
(SELECT MAX(ID) FROM MyTable
GROUP BY emp_name, city)
Q. How will you find the duplicate records in a table? Give Query, also show number of occurrence.
SET gender =
CASE gender
WHEN 'male' THEN 'female'
WHEN 'female' THEN 'male'
ELSE gender
END
Q. Delete Duplicate Records from tbl_emp where employee name and city should not be repeated. i.e if emp_name and city are same are same, it should be concidered duplicate
A. DELETE FROM MyTable
WHERE ID NOT IN
(SELECT MAX(ID) FROM MyTable
GROUP BY emp_name, city)
Q. How will you find the duplicate records in a table? Give Query, also show number of occurrence.
A. Select col1, count(*) as occurrenceCount From tablename
group by col1
having count(col1)>1
group by col1
having count(col1)>1
Q. How will you find out 7th highest salary in a table?
A. SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY sal_amount DESC) row_number, emp_id,
sal_amount
FROM Salary ) a
(SELECT ROW_NUMBER() OVER(ORDER BY sal_amount DESC) row_number, emp_id,
sal_amount
FROM Salary ) a
WHERE row_number = 7
reference - > http://sqlserverqanda.blogspot.in/2011/11/tricky-select-queries.html
