Interview Questions On SQL

Update

1) How update works internally ?

----------------------------

--
-- Oops! SQL column swap or SQL column clobber?
--
create table #temp (PK int identity, c1 int, c2 int);

insert into #temp values (1,2);
insert into #temp values (3,2);
insert into #temp values (2,1);
insert into #temp values (5,2);
insert into #temp values (5,3);

select * from #temp order by PK;
--
-- Swap columns c1 and c2 on a
-- row-by-row basis if c1 > c2
--

update #temp
 set c1 = c2, c2 = c1
 where c1 > c2;

--
-- Will column c2 clobber column c1
-- BEFORE column c1 can replace column c2?
-- Do we need a temporary column
-- or variable to correct this?
--
select * from #temp order by PK;
--

drop table #temp;

2) 

-- #1 Table
CREATE TABLE GenderUpdate
(
ID SMALLINT IDENTITY,
Gender CHAR(1)
);

-- #2 Data
INSERT INTO GenderUpdate (Gender) 
VALUES ('M'), ('M'),('M'),('M'),('M'),('M'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F')

-- #3 To check the data count gender wise
SELECT Gender, COUNT(gender) [COUNT] FROM GenderUpdate GROUP BY gender

-- #4 Final update with case statement
UPDATE GenderUpdate
 SET Gender = CASE WHEN GENDER = 'M' then 'F' ELSE 'M' END

 SELECT Gender, COUNT(gender) [COUNT] FROM GenderUpdate GROUP BY gender