SPOOL OUTPUT
SET ECHO ON
SELECT * FROM Employee;
/* lets try the same query with using some useful SQLPLUS features */
COLUMN EMPNO HEADING Employee#
COLUMN ENAME HEADING Name
COLUMN JOB FORMAT A4
COLUMN JOB HEADING Job
COLUMN MGR HEADING Mngr#
COLUMN HIREDATE HEADING "Date Hired"
COLUMN SAL FORMAT $99,990
COLUMN SAL HEADING Salary
COLUMN COMM HEADING Commission
COLUMN DEPTNO FORMAT 9999
COLUMN DEPTNO HEADING Dep#
SELECT * FROM Employee;
/* a more substantial query involving 2 tables*/
SELECT * FROM Employee
WHERE deptno IN (SELECT deptno FROM Department WHERE dname='CS');
/* a query requiring grouping */
SELECT DEPTNO, COUNT(*) "# of Employees",SUM(SAL) "Sum of Salaries" 
FROM Employee
GROUP BY DEPTNO;
/* a query to draw data from both tables */
COLUMN DNAME HEADING "Dept Name"
SELECT DNAME,ENAME,HIREDATE
FROM Employee, Department
where Employee.DEPTNO = Department.DEPTNO;
/* Find name of departments that have no employees */
SELECT DNAME FROM DEPARTMENT 
 WHERE DEPARTMENT.DEPTNO
        NOT IN 
            (SELECT EMPLOYEE.DEPTNO FROM EMPLOYEE);
/* Example of a NULL Value test: Display the name of all employees who have
   no Manager */
SELECT ENAME
  FROM EMPLOYEE
 WHERE MGR IS NULL;
/* Example of an Outer Join: Select department name, and emp info for
   all departments; departments should be in the result set even if they
   have no employees */
SELECT DNAME,ENAME,HIREDATE
FROM Employee, Department
where Employee.DEPTNO (+) = Department.DEPTNO;
/* Example of using EXISTS: Find department names of those who have employees
 */
SELECT DNAME FROM DEPARTMENT
 WHERE EXISTS (SELECT * 
                 FROM EMPLOYEE
                WHERE EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO);
/* Example of using NOT EXISTS: Find department names of those who don't
   have employees.
 */
SELECT DNAME FROM DEPARTMENT
 WHERE NOT EXISTS (SELECT * 
                 FROM EMPLOYEE
                WHERE EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO);
/* Example of Aliasing tables: display pairs of employees who work in the 
   same department */
SELECT A.ENAME "First Emp Name", B.ENAME "Second Emp Name"
  FROM Employee A,Employee B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.EMPNO <B.EMPNO;
/* Display each manager's name along with all his/her employees */
BREAK ON MGR-NAME
SELECT A.ENAME "MGR-NAME", B.ENAME "EMP-NAME"
  FROM Employee A,Employee B
 WHERE A.EMPNO = B.MGR;
SPOOL OFF
