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

sql sort order

SQL Server Order by Statement
SELECT ColumnName(s) FROM TableName Order by ColumnName Asc|Desc

Example 1:
--ascending order
select ('Rudi')FootBallTeam union
select ('Anton') union
select ('Budi') union
select ('Cahya') union
select ('Yahya') Order by FootBallTeam Asc

#Results:
FootBallTeam
Anton
Budi
Cahya
Rudi
Yahya

Example 2:

--descending order

select ('Rudi')FootBallTeam union
select ('Anton') union
select ('Budi') union
select ('Cahya') union
select ('Yahya') Order by FootBallTeam Desc

#Results:
FootBallTeam
Yahya
Rudi
Cahya
Budi
Anton

sql server group by

SQL Server Group by Statement
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator value

Group by Statemet often used in conjunction with a having clause statement.

Example 1:
select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0

#Results:
Catagory
Fish
Bird
Bird
Fish
Bird

Example 2:
Still use query on Example 1, with added group by statement.
 select Catagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory
Bird
Fish

Example 3:
Still use query on Example 2, with added SQL Server COUNT() Function to get Count of Similiar Catagory.
select Catagory, COUNT(Catagory)NumOfCatagory from (
    select ('Stork')AnimalName,('Bird')Catagory union
    select ('Pigeon'),('Bird') union
    select ('Hawk'),('Bird') union
    select ('Shark'),('Fish') union
    select ('Dolphin'),('Fish')
)v0 group by Catagory

#Results:
Catagory    NumOfCatagory
Bird        3
Fish        2

having in sql

SQL Server Having Clause Statement:
SELECT ColumnName, aggregate_function(ColumnName) FROM TableName
WHERE ColumnName operator value GROUP BY ColumnName
HAVING aggregate_function(ColumnName) operator valu

Example 1:
 select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 group by CustName,ProductName having CustName='Citra Makmur'

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

On Example 1, query can modified like show on Example 2 to make same result.

Example 2:
select CustName,ProductName,SUM(Qty)Quantity from (
    select ('Buana Jaya')CustName,('Tubex')ProductName,(10)Qty union
    select ('Citra Makmur'),('Tubex'),(15) union
    select ('Citra Makmur'),('Dengue'),(5) union
    select ('Buana Jaya'),('Tubex'),(5) union
    select ('Citra Makmur'),('Tubex'),(25)
)v0 where CustName='Citra Makmur' group by CustName,ProductName

#Results:
CustomerName    ProductName    Quantity
Citra Makmur    Dengue         5
Citra Makmur    Tubex          40

Notes:
Example 1 use combine group by having clause sql server statement while
Example 2 use combine where group by sql server statement