Operators in SQL Server

  1. Conditional Operators:- =, <, >, <=, >=, <>, !=, !>, !<
    • Example:
    • Select * from emp where id=1
    • Select * from emp where salary > 9000
  2. Logical Operators:- and, or, not, between, like, in, all, any, some
    • Example:
    • Select * from emp where salary>999 and city=”Chandigarh”;
    • Note:- Both Condition should be satisfied.

    • Select * from emp where salary > 9000 or city=”Chandigarh”;
    • Note:- Either one condition should be satisfied.

    • Select * from emp where city != “Chandigarh”;
    • Note:- Except Chandigarh employees, all others will display.

    • Select * from emp where salary between 5000 and 10000;
    • Select * from emp where name like ‘a%’;
    • Note:- first char a and anything after that

    • Select * from emp where name like ‘_a%’;
    • Note:- one char before a and anything after that

    • Select * from emp where name like ‘%a%’;
    • //a anywhere

    • Select * from emp where id in(4,45,54);
    • Note:- Select with random ids.

    • select * from emp where EmpDeptId > all (select min(DeptId) from Dept)
    • select name from product where productSubcategoryID = ANY (select productSubcategoryID FROM productSubcategory where name = ‘toys’)
    • select * from item where price > some (select min(price) from item)
  3. Others:- Exists
    • IF EXISTS(SELECT id FROM emp WHERE id=55)
      BEG
      IN
      SELECT name FROm emp WHERE id=55
      END
      ELSE
      BEGIN
      PRINT ‘not exist’
      END

Leave a Reply