Alter the table to add a new Column

Syntax

GO
ALTER TABLE [InphoDBS].dbo.USER_MASTER ADD [LAST_NAME] [nvarchar](50) NULL;

Note: While altering the table to add a Column the Column does not use NOT NULL property. Only NULL property can be used as we are altering the table.

Using dynamic index number with Select Queries

To include index numbers in select query SQL Server provides “Row_Number()” and “rank()” methods. Following is the example to use the methods.

Example

SELECT Row_Number() OVER (ORDER BY ORD.OrderDesc) as LIST_ORDER ,ORD.OrderDesc
FROM tblOrderDescaster ORD

Using Case Statement in Select Query

Syntax:

(CASE WHEN [COLUMN_NAME] = VALUE THEN VALUE End) COLUMN_NAME
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END


Examples

(CASE WHEN ActiveStatus = 'P' Then 'Premium' When ActiveStatus = 'N' Then 'Non Premium' end)ActiveStatus

Another example

select sno,sname,
(case when total>50
then 'good'
when total>35 and total<50
then 'average'
else
'fail'
end)result

from student_result