plsql

Q. What will be output of below query:
             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.

A. Select col1, count(*) as occurrenceCount From tablename
         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 

WHERE row_number = 7

 

 

 

 

reference  - >  http://sqlserverqanda.blogspot.in/2011/11/tricky-select-queries.html