sql case when

SQL Server Case Statement:
CASE [expression]  
   WHEN [value | Boolean expression] THEN [return value] 
   ELSE [return value]
END

Example 1:
declare @Gender nvarchar(6)
set @Gender='M' select case when @Gender='M' then 'Male' else 'Female' End 'Gender'

#Results:  
Gender
Male

 Example 2:  
declare @Gender nvarchar(6)
set @Gender='F' select case when @Gender='M' then 'Male' else 'Female' End 'Gender'

#Results:
Gender
Female

Example 3:
select Num, case Num  
                 when 100 then 'A'  
                 when 90 then 'B'  
                 when 80 then 'C'  
                 when 70 then 'D'
                 when 60 then 'E'  
             end 'Grade' from (
     select (100)Num union
     select (90) union
     select (80) union  
     select (70) union
     select (60)
)v1

#Results:
Num Grade
60 E
70 D
80 C
90 B
100 A

insert into select from

Another method to insert data record into a table in addition on previous post,

We still use table ProductList.


select * from ProductList

If results:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductList'.

Create table ProductList first, use query below.

CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

insert into ProductList select 'CPU001','CPU','300',15

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
CPU001       CPU            300          15

sql insert into

On SQL Server, to add data record into a table use INSERT INTO Statement.

INSERT INTO (FieldName1,FieldName2,FieldName3, ...)TableName VALUES (value1, value2, value3, ...)

Example 1:
We will create a table first, its name ProductList.

--Create Table ProductList
CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

After ProductList created, remark above query, cause table name is unique on a database.

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity

Empty, cause we not yet inserted  anyhing into ProductList, now insert with statement below

insert into ProductList (ProductId,ProductName,UnitPrice,Quantity) values ('LCD001','LCD Monitor','150',5)

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5

Example 2:
We will insert 1 record againt into ProductList, in some fields only.

insert into ProductList (ProductId,ProductName) values ('LCD002','LCD Monitor') 

select * from ProductList
#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5
LCD002       LCD Monitor    NULL         NULL

sql create table

On SQL Server, to create table use statement as follows:

CREATE TABLE TableName
(
    FieldName1 DataType,
    FieldName2 DataType,
    FieldName3 DataType,
    ...
)

Example 1:
Create table with name EmployeeList.

CREATE TABLE EmployeeList
(
    EmployeeId int,
    EmployeeName nvarchar(50),
    EmployeeAddr nvarchar(50),
    EmployeeDept nvarchar(30)
)

After run on query analyzer, here are
#Results:
Command(s) completed successfully

Hereafter, copy and paste on query analyzer script below:

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr    EmployeeDept

This Table is empty.

We can be fill EmployeeList table with data,  use INSERT INTO SQL SERVER Statement.

insert into EmployeeList (EmployeeId,EmployeeName,EmployeeAddr,EmployeeDept) values ('11111','John Nuxer','Jl Raya Bekasi Km 28','MIS Dept')

Yes, now 1 data inserted to EmployeeList table.
See with run this query

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr            EmployeeDept
11111         John Nuxer      Jl Raya Bekasi Km 28    MIS Dept

How to create table with another way?  or Is there any way auto create table on SQL Server?
Yes, here.

sql round

SQL Server ROUND() Function used to get round a numeric field to number of decimals specified.

Statement:
SELECT ROUND(ColumnName,decimals) FROM TableName
where decimals is a number of decimals to be setup.

Example 1:
select ROUND(ItemPrice,0)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.000
21.000
37.000
44.000
59.000

Example 2:
like Example 1 with decimal change to 1

select ROUND(ItemPrice,1)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.400
20.700
37.300
43.600
59.300

Example 3:
like Example 2 with decimal change to 2

select ROUND(ItemPrice,2)ItemPrice from (
    select (15.424)ItemPrice union
    select (20.685) union
    select (37.273) union
    select (43.556) union
    select (59.317)
)v0

#Results:
ItemPrice
15.420
20.690
37.270
43.560
59.320

sql between

BETWEEN SQL Server operator use to get a range of data between two values.

SELECT ColumnName(s) FROM TableName WHERE ColumnName BETWEEN value1 AND value2.

Example 1:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003

In fact there are 3 invoice for that period, but on above query result only 2. Modif above query like below (Example 2) to get actual results.

 Example 2:
select InvoiceNo from (
    select ('YAN/08/12/001')InvoiceNo,('2012/08/20 09:43:29.993')InvoiceDate union
    select ('YAN/08/12/002'),('2012/08/21 09:44:29.993') union
    select ('YAN/08/12/003'),('2012/08/22 09:45:29.993') union
    select ('YAN/08/12/004'),('2012/08/23 13:46:29.993') union
    select ('YAN/08/12/005'),('2012/08/24 09:47:29.993')
)v0 where CONVERT(DATE,InvoiceDate BETWEEN ('2012/08/21') AND ('2012/08/23')

#Results:
InvoiceNo
YAN/08/12/002
YAN/08/12/003
YAN/08/12/004

sql like

Operator LIKE on SQL Server used in a WHERE clause to search for a specified pattern in a column.

SELECT ColumnName(s) FROM TableName WHERE ColumnName LIKE pattern

Example 1:
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like 'J%'

#Results:
StudentName
Juliana Eka Putri
Joko Susanto

Example 2:
Still use Example 1 with small modification.
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like 'i%'

#Results:
StudentName
Juliana Eka Putri
Raihana Pratiwi

Example 3:
Still use Example 1 with small modification.
select StudentName from (
    select ('Wardana Putra')StudentName union
    select ('Raihana Pratiwi') union
    select ('Juliana Eka Putri') union
    select ('Joko Susanto') union
    select ('Sugeng Joko Utomo')
)v0 where StudentName like '%ana%'

#Results:
StudentName
Juliana Eka Putri
Raihana Pratiwi
Wardana Putra