Category: Tutorials

MULTI ROW FUNCTIONS   :–

{NULL VALUE ARE NOT CALCULATED}

1. These functions are also calld “Aggregate functions” (Or) Group functions.
2. All these multi row functions will process multiple records.
3. Applied on group of records and returns one value from the entire group.

MAX ( ) Function in SQL:-

• Returns maximum value of a given expression.

Syntax   :

Max(expr)

• Select max(sal) from emp
• Output → 5000

Display max salary of 20^th department  :-

• Select max(sal),from emp Where deptno=20
• Output → 3000

• Select max(sal +isnull(comm,0)) from emp
• Output → 5000

• Select max (ename) from emp
• Output → word   {based on Ascii}

• Select max(hiredate)from emp
• Output → 1983

Min ( ) Function in SQL:-

• Returns minimum value of a given expression
• Syntax :-min(expr).
• Select min(sal) from emp
• Output → 36025

Note  :– We can not apply this functions to varchar & data columns, Apply only on numeric columns.

display total salaries paid to manager

• Select sum(sal) from emp Where job ‘manager’

avg  ( ) Function in SQL:-

• It returns avg value of given expression
• Select avg (sal) from emp
• Output → 2250.725

• Select ceiling (avg(sal)) from emp
• Output → 2251  (named value)

Note  :- This function also we can’t apply on varchar & date column.

Count ( ) Function in SQL:-

• Count no of values present in a column
• Select count(empno) from emp
• Output → 16

count * ( ) Function in SQL:-

• It returns no of records in the table
• Select count (*) from emp
• Output → 16

count_big(*) Function in SQL:-

Count

• the return type of count is integer .

count_big

• The return type of count is big_integer.

Select count_big(*) from emp.

OLAP FUNCTIONS  :-

(I) It is also called as “Analytical function”
(II) This is two types

• Rank
• Dense_rank

(III) These two functions are used to calculate rank of a particular value from the set of values.

Syntax  :-

                Rank( ) over (set of values)

                Dense_rank() over [set of values]

Display enames,salary,with rank ranking should be based on salary

Select ename,sal,rank ( ) over (order by sal desc) as rank from emp.

• Ename         sal           rank
•     B               500             1
•     King         500             1
•     Scott        300             3      
•      Ford        300             3
•     Anum       200             5

rank functions generate “gaps”.

dense_rank functions won’t generate “Gaps”.

Select ename,sal dense rank ( ) over(order by sal desc) as rnk from emp

• Ename        sal          rnk
•   B               503            1
•   King          503          1
•   Scott          300         2
•   Ford           300         2
•   Anum         200         3

Based on salary first then rank should be on hiredate

Select ename,sal dense_rank ( ) over(order by sal desc,hiredate) as rnk from emp.

• Ename            sal         rnk
•   B                   500        1
•   King              500        2
•   Scott              300        3
•   Ford               300        4
•   Anum              200       5

Ford the ranks of the employee with in the department based on their salaries.

Select ename,sal,deptno, dense_rank ( ) over(particular by deptno order by sal desc)as rnk from emp.

• Ename       sal        deptno   rnk
•    B             500         10        1
•   King        500         10        1
•   Scot        300          20        1
•  Ford        200           20        2
• Anum      200            30        1

SPECIAL FUNCTIONS :-

db_name ( ) SQL Function:

• Returns current name of the current database.
• Select db_name ( )
• Output → mydb

user_name( ) SQL Function:

• Returns name of the user
• Select user_name ( )
•  Output → dbo {database owner}

host_name ( ) SQL Function:

• Returns name of the server
• Select host_name()
• Output → IEEE

ident-current ( ) SQL Function:

• This functions returns current value of the identity…………………..table name.
• Select identity current(‘cust’).
• Output →  103.

ident_seed ( ) SQL Function:

• It returns starting value of the identity
• Select ident_seed(‘cust’) 
• Output → 100

ident_incr ( ) SQL Function:

• It returns increment by value.
• Select ident_incr(cust)
• Output → 1

isnumeric ( ) SQL Function:

• Isnumeric is expression,if expression is number this function returns ‘1’ otherwise ‘0’.
• Select isnumeric(10)
• Output → 1

• Select isnumeric (‘abed’)
• Output → 0

Isnull ( ) SQL Function:

• This function is used to convert null values.

Syntax  :-   isnull(exp1,xp2)

• If exp 1 is null ,it returns exp2
• If exp1 is notnull ,it returns exp1 only

Example :-

• Select is null (null,100)
• Output → 100
• Select isnull(200,100)
• Output → 200

 

Example  :–

Select ename ,sal,comm,sal+isnull[comm,0]  Totsal from emp

Ename      sal          comm      total

 A           2000        NULL     2000

 B           5000        NULL     5000

 C           5000        1000        6000

Example  :-Select ename,sal,isnull(cast(comm as varchar),N/A) as comm from emp

A           2000       N/A

B            5000      N/A

C            5000     1000

Example  :-     Select sal,comm from emp  Order by comm.

(Note  :- By default sql server decide null value is low}

Display the commission in ascending order but null value is display to last only.

Select ename,sal,comm from emp Order by isnull(comm,99999999999)

            C         5000        1000

            A         2000        NULL

            B         5000        NULL

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