Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 19 July 2017

SQL Between Operator


Between Operator use to pick the values with the specified range.
It will Supports with numbers and data values.
Between includes range limits in output.
Not between excludes range limits from output.


SQL Between Syntax


SELECT column1,column2...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;


Example

The following statement returns employee salary who having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal BETWEEN 10000 AND 20000;


Example


The following statement update employee salary 35% who joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .35
WHERE hiredate BETWEEN '1 -JAN-10' and '31-DEC-10';



SQL Not Between



SQL NOT Between Syntax




SELECT column1,column2...
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;



Example

The following statement returns employee salary who not having 10000 and 20000

SELECT *
  FROM emp
 WHERE sal NOT BETWEEN 10000 AND 20000;



Example


The following statement update employee salary 15% who not joined between
'1 -JAN-10' and '31-DEC-10'

UPDATE emp
SET Sal = sal + sal * .15
WHERE hiredate NOT BETWEEN '1 -JAN-10' and '31-DEC-10';



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator                         
                            SQL is NULL AND is NOT NULL



Share this article :

2 comments:

  1. Hi Dayakar,

    Smokin hot stuff! You’ve trimmed my dim. I feel as bright and fresh as your prolific website and blogs!

    Module Module1

    Sub Main()
    Dim City As String 'City to look up in array.'
    Dim CityNames() As String 'Initialized array of Michigan cities.'
    Dim FoundIt As Boolean 'Flag variable.'
    Dim X As Long 'Loop control variable.'

    CityNames = Class System.Array("Acme", "Albion", "Detroit", "Watervliet", "Coloma", "Saginaw", "Richland", "Glenn", "Midland", "Brooklyn")

    City = InputBox$("Enter city name: ") 'Get user input.'

    For X = LBound(CityNames) To UBound(CityNames)
    If LCase(City) = LCase(CityNames(X)) Then
    FoundIt = True
    Exit For
    End If
    Next X

    If FoundIt Then
    System.Console.WriteLine("Is a city in Michigan." & City)
    Else
    System.Console.WriteLine("Is NOT a city in Michigan." & City)
    End If
    Console.ReadKey()
    End Sub ' End of Main() procedure

    End Module

    Error I receive is BC30201 Error (local variable) City As String I am unsure what to do to fix this
    This error is on my Class System. Array
    A data block size is specified for each Oracle database when the database is created. A database user and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.
    I am so grateful for your blog. Really looking forward to read more.

    Ciao,
    Kevin

    ReplyDelete
  2. Olà,


    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Between Operator. I agree with you on lot of points!


    how to get the first non space character from a string in Oracle. Could you help me on this?





    Anyways great write up, your efforts are much appreciated.


    Many Thanks,
    Radhey

    ReplyDelete