select + join

( EQUI OR INNER, NON EQUI JOIN, SELF , OUTER - [LEFT, RIGHT, FULL], NATURAL, CROSS )

    1. Display employee names who are working in NEW YORK.

        Select e.ename from emp e JOIN dept d ON e.deptno = d.deptno where UPPER(loc) LIKE 'NEW YORK' ;

    2. Display employee names, dept names and grade who are working in NEW YORK.

        Select e.ename, d.dname, s.grade from emp e
            JOIN dept d ON e.deptno = d.deptno
            JOIN salgrade s ON e.sal between s.losal AND s.hisal;

    3. Display employee details who are working under MANAGER Blake.

        Select e1.ename, e1.sal, e1.deptno from emp e
        JOIN emp e1 ON e.empno = e1.mgr
        Where e.ename = 'BLAKE';

    4. Display employee names and their respective dept names. Include all Employee records.

        Select e.ename, d.dname from emp e
        LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

    5. Display employee names and their respective dept Locations. Include all Dept records.

        Select e.ename, d.loc from emp e
        RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

    6. Display employee names, salary and their respective dept names and Locations. Include all records from both the tables.

        Select e.ename, e.sal, d.dname, d.loc from emp e
        FULL OUTER JOIN dept d ON e.deptno = d.deptno;

    7. Display employee names who are working as MANAGER in Sales Department.

        Select e.ename from emp e
        JOIN dept d USING (deptno)
        Where e.job like 'MANAGER' AND d.dname like 'SALES';

    8. Display employee names thier departments using CROSS JOIN.

        Select e.ename, d.dname from emp e, dept d;  

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;

Retrieving Data Using the SQL SELECT Statement

Select Operations

1. Select + WHERE..................................View Examples                        
2. Select + JOIN     .................................View Examples
3. Select + GROUP BY       
4. Select + HAVING
5. Select + (AND, OR, NOT)
6. Select + SUB QUERY
7. Select + ORDER BY
8. Select + Views
9. Select + Materialized Views
10. Select + Metadata tables.