## Examples of alternative SQL "WHERE" clauses


SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION  
WHERE  SUBSTR(ACCOUNT_NAME,1,7) = 'CAPITAL';

SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  ACCOUNT_NAME LIKE 'CAPITAL%';


SELECT ACCOUNT_NAME,TRANS_DATE,AMOUNT  
FROM   TRANSACTION
WHERE  AMOUNT != 0;

SELECT ACCOUNT_NAME,TRANS_DATE,AMOUNT  
FROM   TRANSACTION
WHERE  AMOUNT > 0 ;


SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  TRUNC(TRANS_DATE) = TRUNC(SYSDATE);

SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  TRANS_DATE BETWEEN TRUNC(SYSDATE) 
                      AND TRUNC(SYSDATE) + .99999; 


SELECT TO_DATE('01-JAN-93') + .99999    
FROM   DUAL;

     returns: '01-JAN-93 23:59:59'

SELECT TO_DATE('01-JAN-93') + .999999
FROM   DUAL;

     returns: '02-JAN-93 00:00:00’


SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  ACCOUNT_NAME || ACCOUNT_TYPE = 'AMEXA';

SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT
FROM   TRANSACTION
WHERE  ACCOUNT_NAME ='AMEX'   
AND    ACCOUNT_TYPE = 'A' ;


SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  AMOUNT + 3000 < 5000;

SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT  
FROM   TRANSACTION
WHERE  AMOUNT < 2000;


SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT
FROM  TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME);

SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT
FROM  TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, '%');