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
- Comparison Operator
- Logical operator
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’);