Restricting and Sorting Data Tips
1.
10+’20’ = 30 ( Implicit
conversion )
2.
To control NULL values,
=> Use “NULLS FIRST” or “NULLS
LAST”.
=> When we
order by a column desc, NULLS are placed first. To shift them to last we need
to use NULLS LAST.
=> When we
order by a column asc, NULLS are placed last. To shift them to first we need to
use NULLS FIRST.
3.
Operators:
(i) Like:
a.
To search Employee name which starts with ‘S’.
Select
* from emp where ename LIKE ‘S%’;
b.
To search Employee name which ends with ‘S’.
Select * from emp where ename LIKE ‘%S’;
c.
To search Employee name which contains 2 ‘S’.
Select *
from emp where ename LIKE ‘%S%S%’;
d.
To search for a string which has 2nd character as
‘S’.
Select * from emp where ename like ‘_S%’;
(ii) BETWEEN:
a.
Salary Range is 2000 to 4000.
Select *
from emp where sal between 2000 and 4000;
b.
Hiredate from “01-jan-2010” to
“01-jan-2011”.
Select * from emp where hiredate between “01-jan-2010” AND
“01-jan-2011”;
(iii) IN:
a.
Select all the employees
who are working in dept no 20,30;
Select *
from emp where deptno IN (20,30);
b.
Select all the employees who are working as ‘CLERK’ and
‘MANAGER’
Select *
from emp where JOB IN (‘CLERK’, ‘MANAGER’)
(iv) IS:
a.
Select all the employees whose COMM is not null.
Select *
from emp where COMM IS NOT NULL;
b.
Select all the employees whose names are not null.
Select *
from emp where ename IS NOT NULL;
(v) ANY:
a.
Select the employees
whose sal is > any of the CLERK.
Select
* from emp where sal > ANY ( Select sal from emp where JOB = ‘CLERK’);
b.
Select the employees whose sal is > any of the employees
working in dept no 20.
Select * from emp where sal > ANY ( Select sal from emp where
deptno = 20 );
(vi) ALL
a.
Select the employees whose sal is > ALL the CLERK.
Select *
from emp where sal > ALL ( Select sal from emp where JOB = ‘CLERK’);
b.
Select the employees whose sal is > ALL the employees who are
working in deptno =20.
Select *
from emp where sal > ALL ( Select sal from emp where deptno = 20 );
(vii)EXISTS
a.
Select the employee records whose deptno exists in dept table.
Select *
from emp where EXISTS
(
select * from dept where dept.deptno = emp.deptno);
(viii) PIVOT
PIVOT is where multiple rows are
aggregated and transposed into columns, with each
column representing a different range of aggregate data.
4.
Operator Precedence
5.
Accept Inputs at Runtime.
(i) Using
Single ‘&’ :
Using ‘&’, we can accept the values at runtime.
a) Select all the employees who are working in dept 30. Accept
the deptno at runtime.
Select * from emp where deptno = & deptno;
b) Select all the employees who are working as CLERK. Accept the
job from runtime.
Select * from emp
where JOB = ‘&job’;
c) Customize Select clause
Select &select
from emp;
When we execute this, It asks for
Enter value for
select: ename, sal
Check your Output.
Note: When we use ‘&job’, Provide Input as CLERK.
When we use &job, Provide Input as ‘CLERK’.
(ii) Using
Double ‘&&’:
Using ‘&&’, we can provide input at
runtime. This will ask for input only one time and
for
every execution it replaces the value.
a) Insert the following data into Emp.
Empno = 1000,1001,1002, 1003.
Ename = ‘JOHN’, ‘JOHNSON’, ‘JAMES’, ‘JIN’.
JOB = ‘CLERK’ ( everyone)
Sal = 3200, 4200, 3800, 2950
Deptno = 20 ( everyone )
Insert into emp ( empno, ename, job, sal, deptno )
values ( &eno, ‘&ename’,
‘&&job’, &sal, &&deptno );
(iii) Using DEFINE:
Define select
ename, sal;
Select &select
from emp;
Note: Here select is
replaced with ename, sal.