Jumat, 31 Agustus 2012

select top sql

SQL Server Select Top Statement:
SELECT TOP number | number PERCENT ColumnName(s) FROM TableName

Example 1:

select TOP 1 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v0 Order by PersonName Asc

#Results:
PersonName
Ali

Example 2:
select TOP 1 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v1 Order by PersonName Desc

#Results:
PersonName
Zubair

Example 3:
select TOP 3 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Asc


#Results:
PersonName
Ali
Bilal
Muhammad

Example 4:
select TOP 3 * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Desc


PersonName 
Zubair
Umar
Muhammad

Example 5:
select TOP 30 PERCENT * from (
    select ('Umar')PersonName union
    select ('Zubair') union
    select ('Ali') union
    select ('Muhammad') union
    select ('Bilal')
)v2 Order by PersonName Desc

#Results:
PersonName 
Zubair
Umar

Kamis, 30 Agustus 2012

sql select count

SQL Server COUNT() function use to get number of rows that matches a specified criteria.

Example 1:
select COUNT(ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v0

#Results:
NumOfItem
6

Example 2:
select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35)
)v1

#Results:
NumOfItem
2

Example 3:
See following examples and compare with previous query:


select COUNT(DISTINCT ItemName)NumOfItem from (
    select ('pencil')ItemName,('P0001')ItemCode,(10)Qty union
    select ('pencil'),('P0001'),(20) union
    select ('pencil'),('P0001'),(30) union
    select ('pen'),('P0002'),(15) union
    select ('pen'),('P0002'),(25) union
    select ('pen'),('P0002'),(35) union

    select (null),('P0003'),1
)v2

#Results:
NumOfItem
2


Notes:
SQL Server DISTINCT sintax added to get number of unique item name
Results Example 2 and 3  same couse NULL values will not be counted

convert month name to number sql

Use Combine DATEPART() and CAST() SQL Server Function.

For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth

#Results:
NumOfThisMonth
2

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
    select (1)OrdNum,'January'MonthNames union
    select (2),'February' union
    select (3),'March' union
    select (4),'April' union
    select (5),'May' union
    select (6),'June' union
    select (7),'July' union
    select (8),'August' union
    select (9),'September' union
    select (10),'October' union
    select (11),'November' union
    select (12),'December'
)v0

Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber

#Results:
monthNumber
10

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
    select (1)OrdNum,'JAN'MonthNames union
    select (2),'FEB' union
    select (3),'MAR' union
    select (4),'APR' union
    select (5),'MAY' union
    select (6),'JUN' union
    select (7),'JUL' union
    select (8),'AUG' union
    select (9),'SEP' union
    select (10),'OCT' union
    select (11),'NOV' union
    select (12),'DEC'
)v1

OrdNum    NumOfMonth    monthNumber
1         1             1
2         2             2
3         3             3
4         4             4
5         5             5
6         6             6
7         7             7
8         8             8
9         9             9
10        10            10
11        11            11
12        12            12

Rabu, 29 Agustus 2012

average in sql

AVG() on SQL Server Function use to get average value of a numeric column.

select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
select (2),('Product Two'),(3500000) Union
select (3),('Product Three'),(4500000) Union
select (4),('Product Four'),(6500000) Union
select (5),('Product Five'),(7500000)

#Results:
Num  ItemName       ItemPrice
1    Product One    2500000
2    Product Two    3500000
3    Product Three  4500000
4    Product Four   6500000
5    Product Five   7500000

select AVG(ItemPrice)AvgItemPrice from (
   select (1)Num,('Product One')ItemName,(2500000)ItemPrice Union
   select (2),('Product Two'),(3500000) Union
   select (3),('Product Three'),(4500000) Union
   select (4),('Product Four'),(6500000) Union
   select (5),('Product Five'),(7500000)
)v0

#Results:
AvgItemPrice
4900000

sql datepart

DATEPART() SQL Server function Statement:

DATEPART(datepart,date)

declare @ThisDate datetime set @ThisDate='2012-08-30 08:45:08.717'  
select ('yy')DATEPARTInit,('year')UseForShow,DATEPART(yy,@ThisDate)ShowData Union
select ('qq'),('quarter'),DATEPART(qq,@ThisDate) Union
select ('mm'),('month'),DATEPART(mm,@ThisDate) Union
select ('dy'),('dayofyear'),DATEPART(dy,@ThisDate) Union
select ('dd'),('day'),DATEPART(dd,@ThisDate) Union
select ('wk'),('week'),DATEPART(wk,@ThisDate) Union
select ('dw'),('weekday'),DATEPART(dw,@ThisDate) Union
select ('hh'),('hour'),DATEPART(hh,@ThisDate) Union
select ('mi'),('minute'),DATEPART(mi,@ThisDate) Union
select ('ss'),('second'),DATEPART(ss,@ThisDate) Union
select ('ms'),('second'),DATEPART(ms,@ThisDate) --union
--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union
--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)

#Results:
DatePartInit    UseForShow ShowData
dd              day        30
dw              weekday    5
dy              dayofyear  243
hh              hour       8
mi              minute     45
mm              month      8
ms              second     717
qq              quarter    3
ss              second     8
wk              week       35
yy              year       2012

sql date convert

SQL Server Convert Date Time Function Statement:
Convert(data_type(length),expression,style)

declare @CurrDate datetime
set @CurrDate='2012-08-30 07:54:52.293'

select (100)ShowStyle,Convert(nvarchar,@CurrDate,100)ShowOutput Union
select (101),Convert(nvarchar,@CurrDate,101) Union
select (102),Convert(nvarchar,@CurrDate,102) Union
select (103),Convert(nvarchar,@CurrDate,103) Union
select (104),Convert(nvarchar,@CurrDate,104) Union
select (105),Convert(nvarchar,@CurrDate,105) Union
select (106),Convert(nvarchar,@CurrDate,106) Union
select (107),Convert(nvarchar,@CurrDate,107) Union
select (108),Convert(nvarchar,@CurrDate,108) Union
select (109),Convert(nvarchar,@CurrDate,109) Union
select (110),Convert(nvarchar,@CurrDate,110) Union
select (111),Convert(nvarchar,@CurrDate,111) Union
select (112),Convert(nvarchar,@CurrDate,112) Union
select (113),Convert(nvarchar,@CurrDate,113) Union
select (114),Convert(nvarchar,@CurrDate,114) Union
select (120),Convert(nvarchar,@CurrDate,120) Union
select (121),Convert(nvarchar,@CurrDate,121) Union
select (126),Convert(nvarchar,@CurrDate,126) Union
select (130),Convert(nvarchar,@CurrDate,130) Union
select (131),Convert(nvarchar,@CurrDate,131)

#Results:
ShowStyle    ShowOutput
100          Aug 30 2012  7:54AM
101          08/30/2012
102          2012.08.30
103          30/08/2012
104          30.08.2012
105          30-08-2012
106          30 Aug 2012
107          Aug 30, 2012
108          07:54:52
109          Aug 30 2012  7:54:52:293AM
110          08-30-2012
111          2012/08/30
112          20120830
113          30 Aug 2012 07:54:52:293
114          07:54:52:293
120          2012-08-30 07:54:52
121          2012-08-30 07:54:52.293
126          2012-08-30T07:54:52.293
130          13 شوال 1433  7:54:52:293AM
131          13/10/1433  7:54:52:293AM

right in sql

--right sql server function for static position
select RIGHT(MyAnimals,10)AnimalName,LEFT(MyAnimals, CHARINDEX(' ', MyAnimals)-1)qty from (
    select ('5 chickens A')MyAnimals union
    select ('15 chickens B') union
    select ('25 chickens C') union
    select ('35 chickens D') union
    select ('45 chickens E')
)v0

#Results:
AnimalName    qty
chickens B     15
chickens C     25
chickens D     35
chickens E     45
chickens A      5

--right sql server function for dinamic position
select MyAnimals,quantity,RIGHT(MyAnimals,LEN(MyAnimals)-LEN(quantity))AnimalName from (
    select MyAnimals,LEFT(MyAnimals, CHARINDEX(' ',MyAnimals)-1)quantity from (
        select ('5 chickens')MyAnimals union
        select ('14 goats') union
        select ('7 chickens') union
        select ('10 cows') union
        select ('500 ducks')
    )v2
)v3

#Results:
MyAnimals    quantity    AnimalName
10 cows            10    cows
14 goats           14    goats
5 chickens          5    chickens
500 ducks         500    ducks
7 chickens          7    chickens

sql substring

Substring function on sql server is a function like mid on asp, to get one or more characters in the middle of a set of characters.

--substring sql server function for static position
select notes,substring(notes,11,3)qty from (
    select ('bicycle A 285 unit')notes union
    select ('bicycle B 153 unit') union
    select ('bicycle C 120 unit') union
    select ('bicycle D 569 unit') union
    select ('bicycle E 587 unit')
)v1


#Results:
notes                 qty
bicycle A 285 unit    285
bicycle B 153 unit    153
bicycle C 120 unit    120
bicycle D 569 unit    569
bicycle E 587 unit    587


--substring sql server function for dinamic position
select notes,substring(notes, CHARINDEX(' ',notes)+1,3)qty from (
    select ('bicycle 285 unit')notes union
    select ('motorcycle 153 unit') union
    select ('cabriolet 120 unit') union
    select ('motor 406 unit') union
    select ('pedicab 587 unit')
)v1 


notes               qty
bicycle 285 unit    285
cabriolet 120 unit  120
motor 406 unit      406
motorcycle 153 unit 153
pedicab 587 unit    587

Selasa, 28 Agustus 2012

left sql function

-- left sql server function for static position
select CatNo,LEFT(CatNo,8)NewCatNo from (
    select ('MA 30012~100')CatNo union
    select ('MB 30542~20012') union
    select ('MB 30031~321') union
    select ('CA 30035~356487') union
    select ('CB 30041~310205') union
    select ('CC 31112~333')
)v0

#Results:
CatNo              NewCatNo
CA 30035~356487    CA 30035
CB 30041~310205    CB 30041
CC 31112~333       CC 31112
MA 30012~100       MA 30012
MB 30031~321       MB 30031
MB 30542~20012     MB 30542

-- sql server left function for dinamic position
select CatNo,LEFT(CatNo,CHARINDEX('~',CatNo)-1)NewCatNo from (
    select ('MA 30012567~100')CatNo union
    select ('MB 3054231~20012') union
    select ('MB 30031~321') union
    select ('CA 300352345~356487') union
    select ('CB 30041056879~310205') union
    select ('CC 311~333')
)v0

CatNo                    NewCatNo
CA 300352345~356487      CA 300352345
CB 30041056879~310205    CB 30041056879
CC 311~333               CC 311
MA 30012567~100          MA 30012567
MB 30031~321             MB 30031
MB 3054231~20012         MB 3054231

break on sql

declare @loop int
declare @spell nvarchar(15)
declare @query nvarchar(300)
set @loop=10
set @spell=''
set @query=''

WHILE not @loop =20
    Begin
        if @loop=11
            set @spell='''Eleven'''
        else if @loop=12
            set @spell='''Twelve'''
        else if @loop=13
            set @spell='''Thirteen'''
        else if @loop=14
            set @spell='''Fourteen'''
        else if @loop=15
            set @spell='''Fifteen'''
        else if @loop=16
            set @spell='''Sixteen'''
        else if @loop=17
            BEGIN -- Starting from this script down will never executed
                set @spell='''Seventeen'''
                BREAK
            END

        else if @loop=18
                set @spell='''Eighteen'''
        else if @loop=19
            set @spell='''Nineteen'''
        else
            set @spell='''Twenty'''
   
        set @loop=@loop+1
      
        if @query=''
            set @query='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
        else
            set @query=@query+' union '+'select '+convert(nvarchar,@loop)+','+@spell
    End
    exec (@query)

#Results:
num    spell
11     Twenty
12     Eleven
13     Twelve
14     Thirteen
15     Fourteen
16     Fifteen
17     Sixteen

Senin, 27 Agustus 2012

sql if else

declare @loop int
declare @spell nvarchar(15)
declare @qry nvarchar(300)
set @loop=0
set @spell=''
set @qry=''

WHILE not @loop =10
    Begin
        set @loop=@loop+1
        if @loop=1
            set @spell='''One'''
        else if @loop=2
            set @spell='''Two'''
        else if @loop=3
            set @spell='''Three'''
        else if @loop=4
            set @spell='''Four'''
        else if @loop=5
            set @spell='''Five'''
        else if @loop=6
            set @spell='''Six'''
        else if @loop=7
            set @spell='''Seven'''
        else if @loop=8
            set @spell='''Eight'''
        else if @loop=9
            set @spell='''Nine'''
        else
            set @spell='''Ten'''
       
        if @qry=''
            set @qry='select '+convert(nvarchar,@loop)+'num,'+@spell+'spell'
        else
            set @qry=@qry+' union '+'select '+convert(nvarchar,@loop)+','+@spell
    End
    exec (@qry)

#Results:
num    spell
1      One
2      Two
3      Three
4      Four
5      Five
6      Six
7      Seven
8      Eight
9      Nine
10     Ten

sql exec

Exec sintax on sql server usually use to execute parameter

see example below:
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status

#Results:
usr_name       access_sttus    active_status
soemaryanto    admin           active

declare @qry nvarchar(100)
set @qry=('select (''soemaryanto'')usr_name,(''admin'')access_sttus,(''active'')active_status')
select (@qry)text_qry


exec (@qry)

#Results:
text_qry
select ('soemaryanto')usr_name,('admin')access_sttus,('active')active_status

usr_name       access_sttus    active_status
soemaryanto    admin           active

another example exec sql server statement
declare @count int
declare @qry nvarchar(100)
declare @qryBuffer nvarchar(200)
set @count=0
set @qry=''
set @qryBuffer=''

WHILE not @count =10
BEGIN
    set @count=@count+1
    if @qryBuffer=''
        set @qryBuffer=('select ('+convert(nvarchar,@count)+')AutoNum')
    else
        set @qryBuffer=@qryBuffer+' union '+('select ('+convert(nvarchar,@count)+')')
END
exec (@qryBuffer)

#Results:
AutoNum
1
2
3
4
5
6
7
8
9
10

Minggu, 26 Agustus 2012

day name in sql

SELECT DATENAME(dw, 1)NameofThisDay

#Results:
NameofThisDay
Tuesday

SELECT DATENAME(dw, '2012-08-27 11:12:38.803')NameofThisDay

#Results:
NameofThisDay
Monday

Another example with complex case to get days a week with while loop sql server statement

declare @start int,@end int
declare @qry nvarchar(100)
declare @daysAWeek nvarchar(1000)
set @start=1
set @end=8
set @daysAWeek=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName union ')qry)

        set @daysAWeek=@daysAWeek+@qry       
        set @start=@start+1
    end
    exec (@daysAWeek)

#Results:
dayNumber    dayName
1            Tuesday
2            Wednesday
3            Thursday
4            Friday
5            Saturday
6            Sunday
7            Monday

sql month name

declare @start int,@end int
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName 
            union ')qry)

        set @summary=@summary+@qry       
        set @start=@start+1
    end
    exec (@summary)

#Results:
monthNumber    monthName
1              January
2              February
3              March
4              April
5              May
6              June
7              July
8              August
9              September
10             October
11             November
12             December

select DateName(mm,'2012-08-27 10:10:17.327')currMonthName

#Results:
currMonthName
August

sql loop

DECLARE @count int
SELECT @count =11

--Loop sql statement start here
WHILE @count > 0
    BEGIN
        SELECT @count=@count-1
        IF @count=0
            --BEGIN
            --    BREAK
            --END

            IF @count=11
                BEGIN
                CONTINUE
            END
        --Show @count on Query Screen
        PRINT @count
    END 
--End of Loop

#Results:
10
9
8
7
6
5
4
3
2
1
0

Sabtu, 25 Agustus 2012

group by

Group by statement on sql server often use to show some field with one or more of them contain number which will be summed.

We still use example previous post, with small modification.

select product_name,SUM(qty)qty from (
    select 'book notes'product_name,(4)qty union
    select 'pencil',(15) union
    select 'book notes',(3) union
    select 'pencil',(10)
)view1 group by product_name order by product_name 


#Results:
product_name    qty
book notes        7
pencil           25 

view sql

Views that i mean is we can use views method without creat views.

For example:
select product_name,qty from (
    select 'book notes'product_name,(4)qty union
    select 'pencil',(15) union
    select 'book notes',(3) union
    select 'pencil',(10)
)view1 where product_name='pencil' order by product_name

#Results:
product_name    qty
pencil          15
pencil          10

sql comment

When developing, we can always use remark code, a code will be ignored when execute. There are two script comment on sql server, comment line and block comment.

See example below:

this is
sql server
comment line
select 'computer'product_name,(5)qty

#Results:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'is'. 

--this is
--sql server
--comment line
select 'computer'product_name,(5)qty


#Results:
product_name    qty
computer          5



/*this is
sql server
block comment*/
select 'computer'product_name,(5)qty



#Results:
product_name    qty
computer          5






Kamis, 23 Agustus 2012

sql dateadd

declare @thisDate datetime
set @thisDate='2012-08-24 10:37:48.293'

select (5)num,('add day 5')'init',dateadd(d,5,@thisDate)'after_add' union 
select (7),('add month 5'),dateadd(mm,5,@thisDate) union 
select (8),('add quarter 5'),dateadd(qq,5,@thisDate) union 
select (9),('add year 5'),dateadd(yy,5,@thisDate) union 
select (6),('add week 5'),dateadd(wk,5,@thisDate) union 
select (4),('add hour 5'),dateadd(hh,5,@thisDate) union 
select (3),('add minute 5'),dateadd(mi,5,@thisDate) union 
select (2),('add second 5'),dateadd(ss,5,@thisDate) union 
select (1),('add mili second 5'),dateadd(ms,5,@thisDate) order by num asc

#Results:
num    init                 after_add
1      add mili second 5    2012-08-24 10:37:48.300
2      add second 5         2012-08-24 10:37:53.293
3      add minute 5         2012-08-24 10:42:48.293
4      add hour 5           2012-08-24 15:37:48.293
5      add day 5            2012-08-29 10:37:48.293
6      add week 5           2012-09-28 10:37:48.293
7      add month 5          2013-01-24 10:37:48.293
8      add quarter 5        2013-11-24 10:37:48.293
9      add year 5           2017-08-24 10:37:48.293 

datediff in sql

declare @currDate datetime
declare @beforeDate datetime

set @currDate='2012-08-24 09:29:21.377'
set @beforeDate='2005-12-08 03:25:21.377'

select datediff(d,@beforeDate,@currDate)'days', 

datediff(mm,@beforeDate,@currDate)'months', datediff(yy,@beforeDate,@currDate)'years'

#Results:
days    months    years
2451    80        7


or can suggest like it

select (datediff(d,@beforeDate,@currDate))'difference',('Days')'unit' union
select datediff(mm,@beforeDate,@currDate),('Months') union
select datediff(yy,@beforeDate,@currDate),('Years')

#Results:
difference    unit
7             Years
80            Months
2451          Days

Notes:
datediff(d,@beforeDate,@currDate): to get difference of day
datediff(mm,@beforeDate,@currDate): to get difference of month
datediff(yy,@beforeDate,@currDate): to get difference of year

declare in sql

declare @currDate1 datetime
declare @currDate2 datetime
declare @Schedule1 nvarchar(20)
declare @Schedule2 nvarchar(20)

set @currDate1='2012-08-24 06:28:41.420'
set @currDate2='2012-08-24 14:28:41.420'
set @Schedule1='I go to school'  
set @Schedule2='I go to zoo'

select (@currDate1)currDate1,(@Schedule1)Schedule1,(@currDate2)currDate2,(@Schedule2)Schedule2

#Results:
currDate1                  Schedule1         currDate2                  Schedule2
2012-08-24 06:28:41.420    I go to school    2012-08-24 14:28:41.420    I go to zoo

Query can be modified as below
select (@currDate1)schedule_date,(@Schedule1)schedule_activity union  
select (@currDate2)schedule_date,(@Schedule2)schedule_activity

#Results:
schedule_date              schedule_activity
2012-08-24 06:28:41.420    I go to school
2012-08-24 14:28:41.420    I go to zoo

sql autonumber

Use table_accessories which created lesson before

select * from table_accessories order by product_name asc

#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer

select Row_Number() over (order by product_name asc)autoNum,* from table_accessories

#Results:
autoNum   product_name
1         Keyboard
2         Keypad
3         Monitor
4         Mouse
5         Printer

Notes:
order by product_name asc: ascending ordering
order by product_name desc: descending ordering
Row_Number() over (order by product_name asc):
autonumber base on product_name ascending ordering


create table sql auto

select 'Keyboard'product_name into table_accessories union
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name

select * from table_accessories
drop table table_accessories

Please copy code above and paste on query analyzer to see results


Notes:
into table_accessories: auto create table and insert with all query results to table name table_accessories
select * from table_accessories: select all field from table_accessories
drop table table_accessories: remove table_accessories from database 

sql union

select 'Keyboard'product_name union
select 'Mouse' union
select 'Keypad' union
select 'Monitor' union
select 'Printer' order by product_name

#Results:
product_name
Keyboard
Keypad
Monitor
Mouse
Printer