Select + Where

(  =, !=, >, <, >=, <=, LIKE, NOT LIKE, IN, ALL, ANY, BETWEEN, IS, AND, OR, NOT 

1. Display Employee records who are working in dept no 30.     

    Select * from emp where deptno = 30;

2. Display Employee records who are working in dept no 20 but JOB is not CLERK.

    Select * from emp where deptno = 20 AND JOB != 'CLERK';

3. Display Employee records whose sal is greater than or equal to 2000.

    Select * from emp where sal >= 2000;

4. Display Employee records whose sal is less than 4000.

    Select * from emp where sal < 4000;

5. Display Employee records whose name ends with 'S'.

    Select * from emp where upper(ename) like '%S';

6. Display Employee records whose name contains atleast one 'A'.

    Select * from emp where upper(ename) like '%A%';

7. Display Employee records who are not working as 'CLERK'.

    Select * from emp where upper(job) NOT LIKE 'CLERK';

8. Display Employee records who are working in Deptno 10, 30 as 'CLERK' or 'SALESMAN'.

    Select * from emp where deptno in (10,30) AND upper(JOB) in ('CLERK','SALESMAN');

9. Display Employee records who are earning  sal greater then all the Employee who are working in Deptno in 20.

    Select * from emp where sal > ALL ( select sal from emp where deptno = 20);

10. Display Employee records who are earning sal greater than Any of the Employee who are working as 'CLERK' , 'SALESMAN'.

    Select * from emp where sal > ANY ( Select sal from emp where upper(JOB) in ('CLERK','SALESMAN'));

11. Display Employee records who are hired from '01-JUN-80' to '01-JUN-82'.

    Select * from emp where hiredate BETWEEN '01-JUN-80' AND '01-JUN-82';

12. Display all the Employee's whose COMM is not null.

    Select * from emp where COMM IS NOT NULL;

13. Display all the Employees who are working in Deptno 20 and earning sal > 2000.

    Select * from emp where deptno = 20 AND sal > 2000;

14. Display all the Employees who are either working as 'MANAGER' or earning sal > 2000.

    Select * from emp where JOB like 'MANAGER' OR SAL > 2000;

15. Display all the employees who are working in deptno 30 and whose comm is not  null.
   
    Select * from emp where deptno = 30 and NOT COMM IS NULL;