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)
(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 :-
Expected Output :-
Guys you can share your Query Through Comments.
Scenario-3:
ReplyDeleteQuery:
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
Update @SampleTbl
Set
CurrentFlag_Old=CurrentFlag_New,
CurrentFlag_New=CurrentFlag_Old
SELECT * FROM @SampleTbl
Scenario-4:
ReplyDeleteQuery:
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
Update @SampleTbl
Set CurrentFlag_New = Case CurrentFlag_New
When 1 Then 0 Else 1
END
SELECT * FROM @SampleTbl
Scenario-3:
ReplyDeleteDECLARE @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
UPDATE @SampleTbl SET CurrentFlag_Old=CurrentFlag_New,CurrentFlag_New=CurrentFlag_Old
SELECT * FROM @SampleTbl
Scenario-4:
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
UPDATE @SampleTbl SET CurrentFlag_Old=~CurrentFlag_Old,CurrentFlag_New=~CurrentFlag_New
SELECT * FROM @SampleTbl
PART-2
ReplyDelete--SWAPPING COLUMN VALUES
UPDATE SAMP
SET CurrentFlag_Old =CurrentFlag_New
, CurrentFlag_New = CurrentFlag_Old
--To Update 0(zero) with 1(One) and 1(One) with 0(zero) in CurrentFlag_New Column Using single Update Query
update samp
SET CurrentFlag_Old = CASE WHEN CurrentFlag_Old = 0 THEN 1
WHEN CurrentFlag_Old =1 THEN 0
END
,CurrentFlag_New = CASE WHEN CurrentFlag_New =1 THEN 0
WHEN CurrentFlag_New =0 THEN 1
END
Scenario 3
ReplyDelete-----------------------------
DECLARE @temp BIT
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
update @SampleTbl set currentflag_old=@temp,@temp=CurrentFlag_New,CurrentFlag_new=CurrentFlag_Old
select * from @SampleTbl