CONDITIONAL SELECTIONS AND OPERATORS

We have two clauses used in this

1. Where

2. Order by

These are not case sensitive.

WHERE: - It is used to specify the condition while retrieving or manipulating data.

Syntax: - select *from  where ;  
                                                Or
                 Select ….  from where ;  
Column1, column2….column n - column names of the corresponding table
Table        -    database object
Where     -    to specify the condition
 
Types of Operators in SQL and usage of these operators in where clause:-
·         Arithmetic operators   
·         Relational operators
·         Logical operators
·         Special Operators
Arithmetic Operators: - Used to perform any Arithmetic Operations like Addition, Subtraction, Multiplication and Divided by.
These have highest precedence.
                +, -, *, /   -- These are the Arithmetic Operators
Relational Operators: - =, <, >, <=,>=, ! =
Example:-
Display whose salary is equal to 2000
SQL>select *from EMP where sal=2000;

 
 
 
 
Display whose salary is less than 3000
SQL>select *from EMP where sal<3000;
 
 
Display whose salary is greater than 3000
SQL>select *from EMP where sal>3000;
 
 
 
 
Display whose salary is less than or equal to 3000
SQL>select *from EMP where sal<=3000;
 
 
 
 
 
Display whose salary is greater than or equal to 3000
 
SQL>select *from EMP where sal>=3000
 
 
 
 
Display whose employee commission is not null
SQL>select *from EMP where comm. is not null;
 
 
 
 
Display whose employee commission is null
SQL>select *from EMP where comm is null;
 
 
 
 
 
 
 
 
 
Logical Operators: - AND, OR, NOT
Syntax for AND:-
It will give the output when all conditions become true
Select * from  where  and  and ..;
                                                   Or
      Select  ,… from where  and  and ..;  
 
Example:-
SQL>Select *from EMP where sal<=2000 and job=’SALESMAN’ and deptno=30;
 
   
 
 
                                                   Or
 SQL>Select empno, ename, job from EMP where sal<=2000 and job=’SALESMAN’ and deptno=30;
 
   
 
 
Syntax for OR:-
 It will give the output either of the conditions become true.
Select * from  where  or  or... 
                                                                               ;
                                               Or
Select , … from where  or  or...;  
 
 
 
 
 
 
 
Example:-
SQL>Select *from emp where where sal<=2000 or job=’SALESMAN’ or deptno=20;
 
                                              
                                                             Or
 
SQL>Select empno, ename, job from EMP where where sal<=2000 or job=’SALESMAN’ or deptno=20;
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Special Operators: - IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, NULL, NOT NULL
A. IN/NOT IN
Use      to pick one by one value from given list of values
Supports      with all types of data (datatypes)
These      are not case sensitive
 
Syntax:-
Select *from  where  in (, , …..);
 
Update  set = where  in (, …. );
 
Delete from where  in (, …);  
 
Let’s see example for each one
SQL>Select *from EMP where empno in (7125, 7369, 7782);
 
 
 
SQL>Update EMP set sal=sal+200 where ename in (‘SMITH’,’ALLEN’,’WARD’);
 
-- 3 rows updated
 
 
SQL>Delete from EMP where hiredate in (‘22-DEC-82’,’ 17-NOV-81’);
 
 
-- 3 rows deleted
 
 
 
 
 
 
SQL>Select *from EMP where empno not in (7125, 7369, 7782);
 
 
 
SQL>Update EMP set sal=sal+200 where ename not in (‘SMITH’,’ALLEN’,’WARD’);
 
-- 12 rows updated
 
 
SQL>Delete from EMP where hiredate not in (‘22-DEC-82’,’ 17-NOV-81’);
 
  --13 rows deleted
 
B. Between/Not between
use      to pick the values with in specified range
supports      with numbers and date values
between is an inclusive operator which      includes range limits in output
Not between is an exclusive operator which      eliminates range limits from
               Output.
-These are not case sensitive.
 Syntax for BETWEEN:-       
  Select * from  where between  and ;  
 
Table_name   - name of the database object
Col                   - column name to which we have to get range.
Lower bound - ‘value ‘must be lower when compare to ‘upper bound ‘value
Upper bound- ‘value’ must be higher when compare to ‘lower bound ‘value
 
Example:-
SQL>select *from EMP where sal between 2000 and 3000;
 
 
When ever lower bound value is larger than upper bound then it shows ‘no rows selected’
Example:- 
SQL>select *from EMP where sal between 3000 and 2000;
 
-- No rows selected
 
Syntax for NOT BETWEEN:-       
  Select * from  where not between  and ;  
 
Table_name   - name of the database object
Col                   - column name to which we have to get range.
Lower bound - ‘value ‘must be lower when compare to ‘upper bound ‘value
Upper bound- ‘value’ must be higher when compare to ‘lower bound ‘value
Example:-
SQL>select *from EMP where sal not between 2000 and 3000;
 
 
 
C. LIKE/NOT LIKE:-
use      to search for pattern in a given input
It      is supported with character data only
It      uses two Meta characters
% (percentage) and _ (underscore) are two Meta characters.
% (percentage) represents “zero or more characters “in the given input
_ (underscore) represents “one character” in given input
 
Syntax for LIKE:-
SQL>Select *from  where  like ;
 
Example:-
Display the employees whose name is starting with ‘S’ in EMP table.
SQL>Select *from EMP where ename like ‘S%’;
 
 
Display the employees whose name is ends with ‘S’ in EMP table
SQL>Select *from EMP where ename like ‘%S’;
 
 
Display the employees whose names are having second letter as ‘L’ in EMP table
SQL>Select *from EMP where ename like ‘_L%’;
 
 
 
 
 
Syntax for NOT LIKE:-
SQL>Select *from  where  like ;
 
Display the employees whose name is not ends with ‘S’ in EMP table
SQL>Select *from EMP where ename not like ‘%S’;
 
 
Display the employees whose names are not having second letter as ‘L’ in EMP table
SQL>Select *from EMP where ename not like ‘_L%’;
 
 
  
 
 
 
 
 
 
 
Display the employees whose names are not start with ‘S’ in EMP table.
SQL>Select *from EMP where ename not like ‘S%’;
 
 
 
Display the employees whose names are second letter start with ‘R’ from ending.
SQL>select *from EMP where ename like ‘%R_’;
 
 
Display the names in EMP table whose names having ‘LL’
SQL>select *from where ename like ‘%LL%’;
 
 
 
 
 
 
 
 
D.NULL/NOT NULL:-
NULL:-
Use      to search for NULL values In the given input
Supports      with all types of data
 
Syntax:-
Select * from where  is null;  
 
Example:-
SQL>select *from EMP where sal is null;
 
-- No rows selected;
Because there is ‘SAL ‘for every employee.
 
SQL>select *from EMP where comm is null;
 
 
Assign deptno as 10 to those employees whom don’t have any deptno
SQL>update EMP set deptno=10 where deptno is null;
 
--0 rows updated
 
Because there ‘DEPTNO’ for every employee
 
 
 
 
 
 
NOT NULL:-
-Use to search for NULL values in the given input
-Supports with all types of data
Syntax:-
SQL>Select * from where  is not null;  
 
 
SQL>select *from EMP where comm is not null;
 
 
SQL>select *from EMP where sal is not null;
 
 
 
FROM clause:-
If we have multiple data sources we define them in from clause. When multiple data sources are there we use aliasing
SQL>Select e.*, d.* from EMP e, dept d;
 
-- Here we get above 50 records. It leads to cross join.
 
e,d  -- alias names
ALIASING: - It is used to provide temporary headings for columns or expressions in SELECT statement.
It is only for displaying purpose.
It is valid in that statement only.
 
Providing alias names to columns shown below
Example:-
SQL>Select dname as “deptname”,
 Loc as “location”,
Deptno as “department no”;