Simple and Trick Question Answer SQL

Q.1 Below query will execute or give error.
     
       SELECT *,YEAR(CreatedOn) AS Yr FROM Customer WHERE YEAR(CreatedOn) > 2017;


Ans :-It will give records which year is greater than 2017.


Q.2 Below query will execute or give error.
 
SELECT CASE WHEN NULL=NULL THEN 'YES' ELSE 'NO' END

Ans :- Execute - NO

Q.3 What will be result?

    a)   SELECT COUNT(*);
    b)   SELECT SUM(NULL);

ANS :-  a) 1
             b) Operand data type NULL is invalid for sum operator.              

Q.4 Single query for update swap record?

       e.g  :- if customer table have one column i.e. Gender and some are Female & some Male then                update Female to Male & Male to Female.

ANS :-

     UPDATE Customer
     SET Gender = (CASE WHEN Gender='Female' THEN 'Male' ELSE 'Female' END);

Q.5 How to get nth salary in sql?

ANS :-
             WITH ResultCTE AS
              (
                 SELECT Salary,DENSE_RANK() OVER (ORDER BY Salary DESC) as DeskRank
                 FROM Employee
               )
              SELECT Salary FROM ResultCTE  WHERE ResultCTE .DeskRank=nth;

Q.6 How to delete duplicate record from table?

ANS :-
             WITH ResultCTE AS
              (
                 SELECT *,Row_Number() OVER (Partition by Id ORDER BY Id) as RowNumber
                 FROM Employee
               ) 
              DELETE From ResultCTE WHERE RowNumber > 1;