/* Some INSERT for Employee and Department tables */
insert into Department  values (19,'PHL');
insert into Department  values (24,'AST');
insert into Department  values (31,'PHY');
insert into Employee    values
 (10,'JONNSON','TEC',Null,'09/01/2005',95000.0,1000,19);
insert into Employee    values
 (12,'SEARS','Prof',10,'11/01/2004',85000.0,Null,19);
insert into Employee    values
 (13,'MONEY','Tec',10,'09/01/2002',65000.0,10000,19);
insert into Employee    values
 (14,'SORTS','Prof',NULL,'09/01/2004',150000.0,NULL,24);
insert into Employee    values
 (17,'SAMS','Prof',24,'09/01/2000',250000.0,NULL,24);
/* commit all changes */
commit;
/* An Updates--Give all 'Prof's a 10% raise */
UPDATE EMPLOYEE SET sal=sal*1.1 WHERE job='Prof'; 
/* Delete Departments with no employees */
DELETE FROM DEPARTMENT 
 WHERE NOT EXISTS (SELECT * FROM EMPLOYEE 
                    WHERE DEPARTMENT.deptno = EMPLOYEE.deptno);
/*undo all changes */
rollback;
/* left for class exercise */

/* List employee# and Names ordered by employee# */
SELECT empno,ename FROM EMPLOYEE ORDER BY empno;

/* List employees' Name, their department# and name, as well as their salary
   ordered by department# as primary and employee name as secondary */ 
SELECT ename,DEPARTMENT.deptno,dname 
  FROM EMPLOYEE,DEPARTMENT 
 WHERE DEPARTMENT.deptno = EMPLOYEE.deptno 
 ORDER BY DEPARTMENT.deptno,ENAME;
/* create a view of only employees that have managers */

/* Display all managers names along with each of thier employees using 
   the above view */

/* List Manager names of those who manage more than one employee */

