Where Clause in SQL with Excellent Example


Where Clause in SQL with Excellent Example


Table 1:-  XX_EMP  A

select * from XX_EMP A
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7369
Sunil
CLERK
7902
17-Dec-80
800

20
7499
Anil
SALESMAN
7698
20-Feb-81
1600
300
30
7521
Sanjay
SALESMAN
7698
22-Feb-81
1250
500
30
7566
Jai Prakas
MANAGER
7839
02-Apr-81
2975

20
7654
Manoj
SALESMAN
7698
28-Sep-81
1250
1400
30
7698
Bal Kishan
MANAGER
7839
01-May-81
2850

30
7782
Anmol
MANAGER
7839
09-Jun-81
2450

10
7788
Sushil
ANALYST
7566
09-Dec-82
3000

20
7839
Kishan
PRESIDENT

17-Nov-81
5000

10
7844
Tarun
SALESMAN
7698
08-Sep-81
1500
0
30
7876
Amit
CLERK
7788
12-Jan-83
1100

20
7900
Jai
CLERK
7698
03-Dec-81
950

30
7902
Faiz
ANALYST
7566
03-Dec-81
3000

20
7934
Vinod
CLERK
7782
23-Jan-82
1300

10

Table 2:-  XX_DEPT B

select * from XX_DEPT B
DEPTNO
DNAME
LOC
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON

--====================================================
-- WHERE Clause in SQL  1. put any Condition
--====================================================

select * from XX_EMP A
where JOB = 'SALESMAN'


select * from XX_EMP A
where UPPER(JOB) = 'SALESMAN'


select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
and A.DEPTNO = 20


--====================================================
-- How to Use (LIKE)
--====================================================

select *  from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO 
and LOWER(A.ENAME) LIKE '%a%'

.....a.....

select *  from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO 
and LOWER(A.ENAME) LIKE '%a_'

--====================================================
-- How to Use (NOT LIKE)
--====================================================

select *  from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO 
and LOWER(A.ENAME) NOT LIKE '%a%'


select *  from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO 
and LOWER(A.ENAME) NOT LIKE '%a_'

--====================================================
-- How to Use (IN)
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
and ENAME IN ('Vinod', 'Anmol', 'Faiz' )


select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
and A.DEPTNO IN ('30')

--====================================================
-- How to Use (NOT IN)
--====================================================



select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
and ENAME NOT IN ('Vinod', 'Anmol', 'Faiz' )


select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
and A.DEPTNO NOT IN ('30')

--====================================================
-- How to Use (NULL)
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO
and MGR IS NULL


--====================================================
-- How to Use (NOT IN)  ( IS NULL )
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
AND MGR IS NOT NULL


--====================================================
-- How to Use (BETWEEN)
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
AND A.DEPTNO BETWEEN '20' AND '35'

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
AND HIREDATE BETWEEN '20-Feb-81' AND '08-Sep-81'


--====================================================
-- How to Use (Greater Then )  >  ,  <  , =  , <>
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO
AND SAL = 1100 

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO
--AND SAL <> 1100
AND SAL != 1100

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO
AND SAL > 1100

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO
AND SAL < 1100

--====================================================
-- How to Use (OR   / AND )
--====================================================

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
AND JOB = 'SALESMAN'
AND SAL = 1250

select * from XX_EMP A, XX_DEPT B
where A.DEPTNO = B.DEPTNO  
AND (JOB = 'SALESMAN' OR  SAL = 1250 )

Comments

Post a Comment

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

 WHAT IS Oracle Fusion E-Text Report ? An eText template is an RTF-based template that is used to generate text output  for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). How it generate the output for User or Bank? At runtime, BI Publisher applies this template to an input XML data file to create an output text file. that can be transmitted to a bank or other customer.  Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data. Example :- Bank payment file , Bank MT940 File .. etc what is look and feel of this file ? Note:  There are two types of eText templates:  1. fixed-position based (EFT templates)  2. Delimiter-based (EDI templates) What IS EFT File? An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text). Link for download:- EFT :-  https://docs.google....
Read more

Supplier API's

API's for Create Supplier, Create Supplier Site, Create Supplier Site Contacts --======================================================================= -------------Supplier API ------------- 1. First Write a CURSOR for Required Data 2. FND_GLOBAL.APPS_INITIALIZE 3. Pass required Values in Proper way. 4. API :- For Supplier Create (pos_vendor_pub_pkg.create_vendor)    API :- For Supplier Site Create (pos_vendor_pub_pkg.create_vendor_site)    API :- For Supplier Create Contact (ap_vendor_pub_pkg.create_vendor_contact) 5. Run API 6. Checking ------------------------------  Query For APPS_INITIALIZE   --------------------------------------------- select 'USER_ID' REMARKS , user_name NAME, user_id ID from fnd_user where user_name like 'DEVELOPER' union select 'RESPONSIBILITY_ID' NAME, responsibility_name , RESPONSIBILITY_ID from fnd_responsibility_tl where responsibility_name like 'Payables Manager' union s...
Read more

How to pass default Parameter in cursor

--==================================================== --Parameterized Cursors /* Syntax of Parameterized Cursor :- CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement; OPEN cur _ name (Parameter value) */ EMPNO ENAME HIREDATE DOB GENDER SAL DEPTNO COMM 1000 Sachin 11-Sep-85 01-Mar-20 M 25000 20 1002 Rena 01-Aug-82 11-Mar-20 F 125000 20 1008 Sonu 11-Sep-85 01-Mar-20 M 2500 10 100 1003 Chavi 25-Mar-81 15-Feb-20 F 5000 20 1006 Ravi 11-Sep-89 01-Mar-19 M 2500 10 100 1005 Vinod 18-Sep-86 01-Mar-20 ...
Read more