operator in sql

By | December 29, 2018

Limiting rows using a selection in sql -: We can restrict the row returned the query by using the where clause as like this

Select [Distinct]  {* ! column [alias], ….} from table {where condition(S)];

In this syntax :

Where – The where clause can compare value in column literal value arithmetic expression or function. It restrict the query to rows that meet a condition. In where is 3 type elements these are ..

  • Column name
  • Comparison operator
  • Column name, constant or list of values.

As like we take a example  using select command

Select name,fathername,job,salary,deptno from emp where Job=”clerk”;

Table Name is EMP –

Condition-: Is composed of column names,expression constants and a comparison operator.

Character string and dates.-  Like any other language it is the part of sql .

  • Character string and date value are enclosed in single quotation marks.
  • character sting are case sensitive and date value are format sensitive.
  • The date format is DD-MM-YY.
  • Number value are not close in quotation marks.

Operator In sql-  Like any other language sql has some operator these are —

Arithmetical Operator -:We can use arithmetic operators in any clause of a SQL statement except the FROM clause.Add      (  +), Subtract (-), Multiply (*), division(/). These are operators.

 

Comparison Operator – : These are used in conditions that compare one expression to another expression. They are used in the where clause in the following format. …

example -:  Select name,salary from emp where salary <=8000;

Other Comparison Operator -:   

Between operator- : We can display rows based on a range of values using the between operator . as like

Select name, salary from emp where salary Between 8000 AND 10000;

In Operator -: The test for values in a specific list we can use In operator. as like

Select deptNo, name,salary from emp where deptNo In(10,20,30);

The Like Operator –: We can’t knows the exact value to searching. we can select rows that match a character pattern by using Like operator. Here are 2 symbol is using these are

  • represents any sequence of zero or more character
  • _    represents any single character.

Select name from emp where name Like ‘s%’ ;   answer suresh,shyam,sunil

Select name from emp where name Like ‘_A’ ; answer is Ganesh, Rajkumar

The Escape Option -: When we need to have exact match for the actual ‘%’ and ‘_’ character we use the escape option. as like

Select * from emp where name Like ‘s%\_%’Escape’\’ ;

Using null operator -:  It is used when  the value is null.example

Select name,salary from emp where deptno  Is Null;

LoGical Operator

-: A logical Operator combines the result of two condition to produce. A single result based on then or to invert the result of a single condition. These are the logical operators

  • And –  when the both condition are true. example                                             Select name, job, salary from emp where salary >=10000 and job=’clerk’;
  • Or –– Return true if either component condition is true as like                   Select name, job, salary  from emp where salary >=8000 OR Job =’clerk’;
  • Not – Return true if the following condition is false.                        Select name, job, from emp where job Not (‘clerk’,’manager’);

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *