Tech Trainings

Oracle Database Course – WHERE and ORDER BY Clause

October 25, 2020 by admin

--WHERE and ORDER BY clause Samples
 select * from emp ; 
 
 --retrieve all the employees who belong to department 30 
 
 select * 
 from emp 
 where deptno = 30 ; 
 
 
 select * 
 from emp 
 where deptno = 10 ; 
 
 
 --Retrieve all the employees whose job is CLERK
 
 select * from emp
 where job ='CLERK' ; 
 
 select * from emp where hiredate='17-DEC-80';
 
 ------------------------------------
 
--retrieve all the employees whose department is not equal to 10 

select * from emp where deptno <> 10  ; 

--retreive all the employees who does not have dept no 

select * from emp where deptno is null ; 

---BETWEEN--empno .....7000 and 7500
--inclusive....
select * from emp 
where empno between 7000 and 7499 ; 

---retrieve all the employees whose dept belongs to either 10  or 20 
select * from emp 
where deptno in (10,20) ; 

select * from emp 
where deptno =10 
or deptno=20 ; 

----------------------------------------------------------

select * from hz_parties ; 

--Retrieve all the parties which are starting with letter A

select * from hz_parties where party_name like 'A%' ; 

--Retrieve all the employees whose party_number starts with 100
select * from hz_parties where party_number like '100%' ; 

--creation date like 97
select * from hz_parties where creation_date like '%FEB%97' ; 

--ends with n

select * from hz_parties where party_name like '%n' ; 

--retreieve all the party_name whose name start with A , followed by 4 characters 

select * from hz_parties where party_name like 'A____' ; 

--retrieve all the employees whose name start with A and third letter a and followed by any other letter
select * from hz_parties where party_name like 'A_a%' ; 

----------

--get all the employees who does not have commission
select * from emp where comm = null ;  ******wrong

select * from emp where comm IS NULL; 

---who are having commission 

select * from emp where comm is not null ; 


---whose name starts with A and job = SALESMAN

select * from emp 
where 
1=1
AND ename like '%'
AND job='SALESMAN' 
;


select * from dual where 1=1  and dummy='X'; 

select * from dual where 1=0 ;   --false condition 

select * from dual where -1 = -1 ; 

select * from dual where null = null ; 

select * from dual where null is null ; 

---------------------------------------------------------------------

select * from hz_parties ; 

--party type = ORGANIZATION and party_name starts with W

select * from hz_parties
where 1=1
and party_name like 'W%'
and party_type='ORGANIZATION'
;



select * from hz_parties
where 1=1
and (party_name like 'W%' OR party_type='ORGANIZATION'  )
;

--NOT

select * from hz_parties
where party_name not like 'A%' ;

-------------------------------------------------------

select * from emp order by comm ;  ---asc

select * from emp order by comm asc; 

select * from emp order by comm desc;


select * from emp order by 1 ;

select * from emp order by empno asc ;

desc emp;

select * from emp order by 1 , 2 ;

select * from emp where sal=1250 order by sal, comm ; 

select * from emp order by sal, comm ; 

select * from emp order by sal, comm desc ; 

Comments |0|