Interview Questions -- SQL Scenarios -- Part-4

Scenario-7:   What is the output of following

                 Question-1: SELECT 1 + NULL

          Ans :-                   :-

          Question-2: SELECT 1- NULL

          Ans :-        

          Question-3: SELECT 1* NULL

          Ans :-        

          Question-4: SELECT 1/ NULL

          Ans :-    

Scenario-8:   What is the output of following

                 Question-1:   SELECT CASE WHEN 1=1

                                   THEN 'True' else 'False' End

          Ans :-      

                 Question-2: SELECT CASE WHEN NULL=NULL

                                    THEN 'True' else 'False' End

          Ans :-      

Scenario-9:   What is the output of following

                 Question-1:   DECLARE @A INT

DECLARE @B INT

SET @A=1

SET @B=1

SELECT @A+@B AS ADDITION

          Ans :-      

                 Question-2:   DECLARE @A VARCHAR(100)

DECLARE @B VARCHAR(100)

SET @A=1

SET @B=1

SELECT @A+@B AS CONCATENATION

          Ans :-     

Guys you can share your Query Through Comments.

Interview Questions -- SQL Scenarios -- Part-3

Scenario-5: How to get the 3^rd Maximum salary details in the given employee Table

Sample Scripts :- 

USE tempdb

GO

-- How to Create New Table

DECLARE @EMP TABLE

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

INSERT INTO @EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO @EMP

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO @EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO @EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO @EMP(EMPID, EMPNAME,SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya',60000,'01-12-2014','Bangalore','f',3,103)

SELECT * FROM @EMP

Expected Output :-  

Scenario-6: How to get Top 3 employee details based on their Salary with out using TOP Keyword.

Expected Output :-  

Guys you can share your Query Through Comments.

Interview Questions -- SQL Scenarios -- Part-2

Scenario-3:   How to Swap 2 Column Values 
(OR) How to Interchange 2 Column Values
(OR) How to Update one column(CurrentFlag_Old) Values with another column(CurrentFlag_New)

Sample Scripts :- 

-- How to go inside into the Database

USE tempdb

GO

-- Create Table Variable

DECLARE @SampleTbl TABLE

(

   EmpID INT

  ,EmpName VARCHAR(50)

  ,CurrentFlag_Old BIT

  ,CurrentFlag_New BIT

)

-- INSERT Sample Data Into the Table Variable.

INSERT INTO @SampleTbl

SELECT 1,'Santhosh',0,1

UNION ALL

SELECT 2,'Kumari',1,0

UNION ALL

SELECT 3,'Kamesh',0,1

UNION ALL

SELECT 4,'Kavitha',1,1

UNION ALL

SELECT 5,'Lakshita',0,1

SELECT * FROM @SampleTbl

Expected Output :-  

Scenario-4:   How to Update 0(zero) with 1(One) and 1(One) with 0(zero) in CurrentFlag_New Column Using single Update Query.  

Expected Output :-  

Guys you can share your Query Through Comments.

Interview Questions -- SQL Scenarios -- Part-1

Scenario-1:   How to Get/See/Display duplicate records from                   the given table

Sample Scripts :- 

-- How to go inside to the Database

USE tempdb

GO

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

)

-- INSERT Sample Data Into the Table.

INSERT INTO EMP

SELECT 1,'Santhosh',10000

UNION ALL

SELECT 2,'Kumari',8000

UNION ALL

SELECT 2,'Kumari',8000

UNION ALL

SELECT 3,'Kamesh',75000

UNION ALL

SELECT 3,'Kamesh',75000

UNION ALL

SELECT 3,'Kamesh',75000

SELECT * FROM EMP

Expected Output :-  

Scenario-2:   How to Delete Duplicate records from                            the given table

Expected Output :-  

Guys you can share your Query Through Comments.