Category: SQL Server Tutorial

CONVERSION FUNCTIONS  :-

This functions is used to one type to another type.

Cast ( ) Function in SQL Server with Examples:-

Syntax  :-  cast (expr as type)

Example :- Select ename+earns+ cast(sal as varchar) from emp

Output :-

• Smith earns 2000
• Anu earns     4000

Example :-

Select ename+joined on +cast(hiredate as varchar) from emp.

Select ename+joined on + Date name (dw,hiredate) from emp.

Convert ( ) Function in SQL Server with Examples:-

Syntax :  Convert(targettype,source value)

Example :-

• Select convert (int,10.2345)
•         ↓                 
• Output →10
• Select convert(varchar,getdate())
•           ↓
• Output→ OCT, 24, 2009

MATHEMATICAL FUNCTIONS :-

Abs( ) Mathematical Function in SQL:-

It returns absolute value.

Example :-

Select abs (-10)
          ↓
Output →10

Sign ( ) Mathematical Function in SQL:-

Syntax :- Sign(expr)

If expr is  

• + (plus)         →              1
• – (minus)      →            -1
•    0                      →             0

Example :-

•           Select sign (-10)                   select     sign   (10-10)
•                   ↓                                                   ↓
•           Output →   -1                              Output →    0

Power  ( ) Mathematical Function in SQL:-

• Used to calculate power
• power(m,n)

Example  :-

• Select power (3,2)       (mⁿ)
•             ↓
• Output     →  9

Sqrt ( ) Mathematical Function in SQL:-

• Returns squre root.
• Select sqrt(16)

                         ↓

               Output  → 4

Square ( ) Mathematical Function in SQL:-

• It returns square of a number.
• Select square (5)

                        ↓

                Output  → 25

Round ( ) Mathematical Function in SQL:-

• Rounds a number to specify no of decimal places.

Syntax : round (number,decimal places[1/10])

Example :-

                                Select round (3.456,2)

                                           ↓                           {default+0 →rounded,  default+1 → truncated}

                                      Output   → 3.46                 

                                Select round(3.553,0)

                                          ↓

                                     Output    → 4

                               Select round (385.456,-2)     {means integer part +means decimal part}

                                          ↓

                                    Output    → 400

                               Select round (325,456,-1)

                                                      ↓

                                   Output     →330

                               Select round (325,456,-0)

                                                ↓

                                      Output   → 0

                              Select round (3.456,2,1)

                                                 ↓

                                    Output  →3.45

Ceiling ( ) Mathematical Function in SQL:-

• It returns highest value.
• Select ceiling (3.123)

                      ↓

               Output   → 4

Floor ( ) Mathematical Function in SQL:-

• Returns least integer.
• Select floor(3,921)

            Output  →3

Date Functions in SQL :-

Getdate () Function In Sql Server :-

• This function returns correct date
•   Example  :-select getdate ( )

Year ( ) Function In Sql Server :-

• Returns year part of the date

Syntax  :-year (date)

Example  :- Select year(getdate())

Display employee records joined in last year.

Select * from emp where year (hiredate)%4=0

Month ( )  Function In Sql Server :-

• Returns month part from date.

Select month(getdate())

Display employee records join between january & April.

Day ( ) Function In Sql Server  :-

• Returns day part from the date

Syntax  :- day(date)

Example  :-

Display employe records who are joined in first 15 days of January month inyear 1981.

 

DatePart ( ) Function In Sql Server  :-

• It extracts part of the date

Syntax :- datepart(interval,date)

Example  :-Select date part (yy,getdate())

Output  :- 2009

Different intervals are :- 

• dd
• mm
• yy
• hh—(hours)
• mi—(minutes)
• ss     (seconds)
• dw    (day after week)

DateName ( ) Function In Sql Server  :-

• Syntax    :  Date name(interval,date)

                Interval  :-mm→october

                dw→Friday

• Example :-    Select datename(mm,setdate())

Output → october

• Display ‘smith joined on Sunday like  :-

   Select ename +joined on +datename(dw,hiredate) from emp

DATEADD  ( )  Function In Sql Server  :-

• To add no. of months days years to a particular date.

Syntax: dateadd (interval,number,date)

Example  :-  Select dateadd (dd,10,getdata())

Date Diff ( ) Function In Sql Server  :-

• It returns difference b/w two dates.

Example  :-    date diff (interval, d1, d2)

Example  :-   Select date diff(dd, getdata( ), 23-oct-2010)

                ↓

Output:-   365

STRING FUNCTIONS :  

(A)  UPPER FUNCTION  :

• This function converts a string into upper case.

Syntax   :-

UPPER(STRING)

Example  :-    Select   upper(‘hello’)

Select empno,upper(ename) from emp.

(B)   LOWER FUNCTION  :-

• It is converts a string into lower case.

Syntax  :-

Lower(string)

Example   :-Select lower (‘hello’)

(C)  LEN FUNCTION  :-

• It returns string length.

Syntax  :-     len (string)

Example   :-    Select * from emp where  Len(ename)=5.

(D) LEFT FUNCTION   :-

• It returns specify no of character starting from leftside.

Syntax   :-

Left (string,no of characters)

Example  :-  Select left(“hello”,4)

O/P -> hell

Select * from emp where left(ename,1)=’s’

(E)  RIGHT FUNCTION :-

• To extract specify no of character starting from right side.

Syntax  :-

                     Right(string,no of characters)

Example  :-

             Select right (“hello”,4)

           O/P  :–ello.

                Update emp set

              Ename=upper (left(ename,1))+lower(right(ename,len(ename-1)))

Output : ->                Ename

                                       Arun

                                       Giri

                                       Raju

                                       Hari

(F)    Substring  FUNCTION:-

• Used to extract part of the string starting from specified character.

Syntax  :- Substring (string,start,no of chars)

example  :- select substring (‘hello’, 2, 3) delete from emp Where substring

(G) Charindex  FUNCTION:-

• This function checks occurance of one string in another string.

Syntax :   

    Charindex (str1,str2,[start])

   Str1 exists in str2 -> position

    Not exists -> 0

Example  :-Select charindex(‘0’,’hello welcome’)

O/P  :-> 5  {by default first occurance exists}

Select charindex  (‘0’,’hello welcome’,6)

  O/P   : 11

Ename  :

Sachin       r       tendulkar

Mahendra  s    dhoni

Select left(ename,charindex(‘’,ename)-1) From emp

Output  :-     sachin

                      Mahendra

(H) Replace  Function:

• This function is used to replace one string with another string.

Syntax  :- replace(string,search string,replace with string)

Example :-    Select replace(‘hello’,’ell’,’abc’)

O/P  :-habco

Display employee records whose name with 2A’s

Example    :–adams

Select * from emp Where len(ename) – len (replace(ename,’a’,’’))

                  5                              3

 =2 -> [5-3=2]

(I)    SPACE  Function  :-

• This function is used to generate “spaces”.

Example  :-              

             Select ‘hello’+space(5)  + welcome

             O/P  →   hello welcome

FUNCTIONS   :-

Built in functions in sql server categorized into two categories:

• Single row Functions
• Multi row Functions

(I) Single Row Functions in SQL:

These function are capable of processing are row at a time and returns one value per row.

Single row function are catagorized into following categories  :

1. STRING
2. DATE
3. MATHEMATICAL
4. CONVERSION
5. SPECIAL
6. OLAP

SET OPERATORS :-     

The different Set Operator are

•                 (I) UNION
•                (II) UNION ALL
•               (III) INTERSECT
•                (IV) EXCEPT

Syntax   :-

Select statement1  Union/union all/intersect/except Select statement    2

(I)   UNION Operator in SQL Server  :–

• It combines the result of two select statements.
• Union operator eliminates duplicates.
• The result is sorted.

Example  :-

Select job from emp where deptno=10 Union Select   job from emp where deptno=20

Output :-

                              Job

•                 Analyst
•                 Clerk
•                 Manager
•                 President

ROLE  :-

• To perform union operation, query 1 structure must be equal to query2 structure.
• Structure means -> number of columns & data type

                   Select job sal from emp where dno=10  Union Select job from emp where dno=20 ->error because columns are not same

Join vs Union SQL Server : –

UNION Operator in SQL Server                                            

1. Accumlates the data                        
2. {T1 U T2(▒)}                                        
3. To dissimilar structure can’t be combine with union operation.

JOIN Operator in SQL Server

1. Relates the data
2. {T1 ∞ T2(▒) (▒)}
3.  *To dissimilar structure can be joined

           Sales1                                       Sales2

Subsid salamt cusid         salesidd salesamt custid

                         Cust

   Cusid          cname     caddr

(sales1   v   sales 2) ∞ cust.

select job, sal from emp where depno=10 union Select job sal from emp where deptno=20 order by sal.

(II) union all in sql server :-

• It is similar to union.
• Here duplicates are included and result is not sorted.
• Select job from  emp where dno=10 Union all select job from emp where dno=20.

(III) intersect in sql server  :-

• It Returns common values from the result of two selection statements.
• Select job from emp where dno=10 intersect select job from emp where dno=20

(IV) except in sql server :-

• It returns values presenting in result of 1^st select statement  and not in 2^nd select statement.
• select job from emp where dno=10 except select job from emp where dno=20

OUTER JOIN   :-

Equi joins returns only matching records but not un-matching records.

To get the un-matching record also,we need to perform,”out-join”.

Outer joins are three types

• Left outer join
• Right outer join
• Full outer join

(I) Left outer join:-   

Left outer joins returns all records from left side table and matching records from right side table.

Display enames & deptnames

Select e.ename,d.dname From emp e,dept  d Where e.deptno=*.d.deptno

Error because SQL server 2005 doesn’t allow the Non-ANSI  std in outerjoin

ANSI STYLE of left outer join

Select e.ename,d.dname From emp e left outer  join dept d Or e.deptno= d.deptno

          Emp                                                            Dept

Eno       Name       dno                             Dno            dname

1                 A             10                                10               Alcs

2                 B              20                               20               Research       

3                 C              30                               30                 Sales

4                 D              10                               40                operation    

5                 E               —

O/P  :—        ename                        dname

                        A                                   ALCS

                        B                                    Research -> Left Outer

                        C                                     Sales

                        D                                     ALCS

                        E                                     —-

(II) Right outer join  :–

It returns all records from right side table and matching records from left side.

Display enames & dnames

Select e.ename,d.dname  From emp e  right outr join dept d Or  e.deptno=d.deptno.

O/P :–             ename                 dname

                                 A                      ALCS

                                 B                      Research

                                 C                      Sales -> Right outer

                                 D                     ALCS

                                 O                     Operations 

(III) FULL OUTER JOIN  :-

• Returns all records from both table. 

O/P  :–

              A                   ALCS

              B                   Research

              C                   Sales

              D                   ALCS

              E                    ——

              —                  Operation

Select e.ename,d.dname From emp e full outer join dept d On e.deptno=d.deptno.

Non-Ansi std of full outer join {i:e  2000 SQL server}

Select e.ename, d.dname From emp e,dept d Where e.deptno= *d.deptno Union Select e.ename, d.dname From emp e, dept d Where e.deptno *= d.deptno

CROSS JOIN  :-

• It returns cross product of two tables.
• SQL server performs cross join when we submit the select statement with  alt join condition.

Example  :–            Orders               Discount

                                     Ordamt                     dis

                                    1,00,000                    5%

                                                                        20%

                                                                        40%

select ordamt, dis (ordamt- (ordamt*dis/100))

        Netamt          from         orders       ,discount.

O/P:–   Ordamt             dis                netamt

               1,00,000           7                   93,000

               1,00,000            10                90,000

                1,00,000            12                88,000

                1,00,000             13               87,000

SELF JOIN  :-

• A self join is a joining table to itself.
• To perform self join the same table must be listed twice in different Alias.
• Self join is performed in table having self referential integrity.

Display employee names and manager names:

Select x.ename, y.ename manager From  emp x,emp y Where x.mgr= y.empno

Display names of the employee working under manager  blake

Select x.ename employee From emp x, emp y  Where x.mgr=y.empno and y.ename=’BLAKE’

Display names of the employees whos earning morethan his manager.

Select x.ename employee  From  emp x ,emp y x.sal > y.sal

List of employee whose are joined before the manager.

Select x.ename    employee From   emp x,emp y Where   x.mgr=y.empno And x.hiredate<y.hiredate

Write a query to produce the following output

             Ename         Dname        Grade           Mgrname

                 Select  x.ename,d.dname,g.grade,y.ename  mname

                      From emp  x  ,emp y ,dept d,salgrade g

                         Where   x.deptno= d.deptno

                              And

                             x.sal between    g.lowsal and  g.highsal

                           and

                             x.mgr=y.empno

{Note : here,we are used to equi, non equi & self}

ANSI STYLE OF SELF JOIN   :-

Select x.ename,y.ename  mname From emp x inner join emp y Or(x.mgr=y.empno)

NON- EQUI  JOIN  :-

• Joining two tables not based on common column it’s called Non-equi join.

Syntax :-  Select <collist> from <tablelist> Where <join cond> [and<join cond> and<cond>]

Here join condition is based on other than equal operator,so it is Called non-equi join

•                 Emp                                                                           salary grade
• Empno          ename             sal                    Grade      lowsal       highsal
• 1                      A                       300                      1             100           200
• 2                       B                       200                     2               201          300
• 3                       C                        100                     3               301          400
• 4                        D                        400                    4               401           501

Display empnames and their grades:

Select e.ename,g.grade From    emp   e,  salgrade g Where e.sal between g.lowsal and g.highsal

Display the names of employee whos grade=3

Select e.ename From emp   e,salgrade  g Where    e.sal Between    g.lowsal    and   g.highsal And g.grade=3 .

Display ename,dname,grade

Select   e.ename,.dname,g.grade From  emp  e,dept  d,salgrade  g Where e.deptno=d.dno And   e.sal between   g.lowsal and g.highsal

Note: {here we are used both equi & non-equi joins}

Student 

Sno                  sname             s1        s2           s3

result

Result                   Lavg                   Havg

 Dist                        70                        100

 First                        60                        69.99

Second                     50                        59.99

Third                        35                        49.99

Display  sname  & result :

Select s.sname ,r.result From    student    s   ,result r Where   (s.s1+s.s2+s.s3)/3 between r.Lavg  and r.Havg.

ANSI  :-  

                    Select e.ename, g.grade From emp e  inner join g.lowsal and g.highsal.

ANSI JOINS OR SQL/92 JOINS   :-       

Example :- Select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno

Joining 3 tables :-

Example  :-   Select e.ename , d.dname, x.expr from emp e  inner join dept d on e.deptno=d.deptno Inner join Emp_expr  x On x.empno=e.empno.