useful db-scripts

Rename database

• EXEC sp_renamedb 'oldDatabase, 'newDatabase'

• ---------------convert column to row------------------------

declare @col_to_rows TABLE(stu_name VARCHAR(30),subject VARCHAR(10),marks int);
INSERT INTO @col_to_rows VALUES('GEORGE','ECO',77);
INSERT INTO @col_to_rows VALUES('GEORGE','HIS',99);
INSERT INTO @col_to_rows VALUES('GEORGE','MAT',64);
INSERT INTO @col_to_rows VALUES('GEORGE','GEO',85);
INSERT INTO @col_to_rows VALUES('GEORGE','SCI',98);
INSERT INTO @col_to_rows VALUES('ROBERT','ECO',71);
INSERT INTO @col_to_rows VALUES('ROBERT','HIS',90);
INSERT INTO @col_to_rows VALUES('ROBERT','MAT',84);
INSERT INTO @col_to_rows VALUES('ROBERT','GEO',95);
INSERT INTO @col_to_rows VALUES('ROBERT','SCI',58);
INSERT INTO @col_to_rows VALUES('TIMOTHY','ECO',56);
INSERT INTO @col_to_rows VALUES('TIMOTHY','HIS',55);
INSERT INTO @col_to_rows VALUES('TIMOTHY','MAT',67);
INSERT INTO @col_to_rows VALUES('TIMOTHY','GEO',54);
INSERT INTO @col_to_rows VALUES('TIMOTHY','SCI',45);


SELECT stu_name,
max(CASE WHEN subject='ECO' THEN marks ELSE 0 END) ECO,
max(CASE WHEN subject='HIS' THEN marks ELSE 0 END) HIS,
max(CASE WHEN subject='MAT' THEN marks ELSE 0 END) MAT,
max(CASE WHEN subject='GEO' THEN marks ELSE 0 END) GEO,
max(CASE WHEN subject='SCI' THEN marks ELSE 0 END) SCI
FROM @col_to_rows
GROUP BY stu_name

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

. A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns

--select *from neg_pos

SELECT

SUM(CASE WHEN num < 0 THEN num ELSE 0 END) neg,

SUM(CASE WHEN num > 0 THEN num ELSE 0 END)pos

FROM neg_pos;

-------------------------------------------------
swap values between two rows for a table without using subquery

--ram   2000
--shyam 3000
--alex  5000
--joy   4000
-------------
--ram   2000
--shyam 5000
--alex  3000
--joy   4000
------------------------------------------------

update Employee_Test set EMP_SAL=a.EMP_SAL
from 
(
SELECT
   e1.emp_id, EMP_SAL=
   (case e1.emp_id 
when 2 then (LEAD(e1.EMP_SAL) OVER (ORDER BY e1.emp_id))
when 3 then LAG(e1.EMP_SAL) OVER (ORDER BY e1.emp_id)
else e1.Emp_Sal end
)
FROM Employee_Test e1  
)a
join Employee_Test on Employee_Test.Emp_ID =a.Emp_ID 

select *from Employee_Test
--------------------------------------------------