===== union of department numbers in Chicago, and of employees who are managers ===== DEP --- 100 200 300 ===== hey let us view these in reverse order of dept_num ===== DEP --- 300 200 100 ===== here is an example where union is REALLLLY useful: what if you want to see "total pay" for ALL employees, when only some have non-NULL commissions? union along with projecting a literal can make this quite reasonable! ===== ===== remember, this did NOT work: (will not do computations with null) ===== EMPL_LAST_NAME SALARY+COMMISSION --------------- ----------------- King Jones Blake Raimi Ford Smith Michaels 1900 Ward 1750 Martin 2650 Scott Turner 1500 EMPL_LAST_NAME SALARY+COMMISSION --------------- ----------------- Adams James Miller 14 rows selected. ===== BUT this can work: (note the outside ORDER BY only "sees" the final result! Use the column alias it can "SEE"!) ===== Employee Total Pay --------------- ---------- Smith 800 James 950 Adams 1100 Miller 1300 Turner 1500 Ward 1750 Michaels 1900 Raimi 2450 Martin 2650 Blake 2850 Jones 2975 Employee Total Pay --------------- ---------- Ford 3000 Scott 3000 King 5000 14 rows selected. ===== UNION ALL will let me have a non-truly-relational union including BOTH copies of a tuple from both sub-selects: ===== DEP --- 300 200 300 100 ===== the INTERSECTION of employees hired after 2013-01-01 intersected with employees located in Dallas (just project out the employee last name, dept_num, and hiredate) ===== EMPL_LAST_NAME DEP HIREDATE --------------- --- --------- Ford 200 03-DEC-12 Jones 200 02-APR-12 Smith 200 17-DEC-12 ===== the DIFFERENCE of employees hired after 2013-01-01 "minus" employees located in Dallas (just project out the employee last name, dept_num, and hiredate) ===== EMPL_LAST_NAME DEP HIREDATE --------------- --- --------- King 500 17-NOV-11 Raimi 100 09-JUN-12 ===== order of the operands MATTERS with minus operation! the DIFFERENCE of employees located in Dallas "minus" employees hired after 2013-01-01 (just project out the employee last name, dept_num, and hiredate) ===== EMPL_LAST_NAME DEP HIREDATE --------------- --- --------- Scott 200 09-NOV-18 ===== current employee last names, job titles, salaries, and hiredates: ===== EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- King President 5000 17-NOV-11 Jones Manager 2975 02-APR-12 Blake Manager 2850 01-MAY-13 Raimi Manager 2450 09-JUN-12 Ford Analyst 3000 03-DEC-12 Smith Clerk 800 17-DEC-12 Michaels Sales 1600 20-FEB-18 Ward Sales 1250 22-FEB-19 Martin Sales 1250 28-SEP-18 Scott Analyst 3000 09-NOV-18 Turner Sales 1500 08-SEP-19 EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- Adams Clerk 1100 23-SEP-18 James Clerk 950 03-DEC-17 Miller Clerk 1300 23-JAN-16 14 rows selected. ===== updating empl: for employees whose salary is LESS than the average salary of a Sales person, change their hiredate to the maximum hiredate of any employee: ===== 6 rows updated. ===== afterwards, here are employee last names, job titles, salaries, and hiredates see those who have a changed hiredate to latest hiredate? ===== EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- King President 5000 17-NOV-11 Jones Manager 2975 02-APR-12 Blake Manager 2850 01-MAY-13 Raimi Manager 2450 09-JUN-12 Ford Analyst 3000 03-DEC-12 Smith Clerk 800 08-SEP-19 Michaels Sales 1600 20-FEB-18 Ward Sales 1250 08-SEP-19 Martin Sales 1250 08-SEP-19 Scott Analyst 3000 09-NOV-18 Turner Sales 1500 08-SEP-19 EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- Adams Clerk 1100 08-SEP-19 James Clerk 950 08-SEP-19 Miller Clerk 1300 08-SEP-19 14 rows selected. ===== remember, you can UNDO a change such as an insert, update, or delete IF you have not yet committed that change (either with a commit command or by changing a table's structure or exiting sqlplus) by using the SQL rollback command. Now rolling back the above update: ===== Rollback complete. ===== current employee last names, job titles, salaries, and hiredates: ===== EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- King President 5000 17-NOV-11 Jones Manager 2975 02-APR-12 Blake Manager 2850 01-MAY-13 Raimi Manager 2450 09-JUN-12 Ford Analyst 3000 03-DEC-12 Smith Clerk 800 17-DEC-12 Michaels Sales 1600 20-FEB-18 Ward Sales 1250 22-FEB-19 Martin Sales 1250 28-SEP-18 Scott Analyst 3000 09-NOV-18 Turner Sales 1500 08-SEP-19 EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- Adams Clerk 1100 23-SEP-18 James Clerk 950 03-DEC-17 Miller Clerk 1300 23-JAN-16 14 rows selected. ===== deleting rows of Clerks whose salary is more than the average salary for a Clerk: ===== 2 rows deleted. ===== afterwards, here are employee last names, job titles, salaries, and hiredates see how there are now 12 rows, and two fewer Clerks? (the 2 highest-paid?) ===== EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- King President 5000 17-NOV-11 Jones Manager 2975 02-APR-12 Blake Manager 2850 01-MAY-13 Raimi Manager 2450 09-JUN-12 Ford Analyst 3000 03-DEC-12 Smith Clerk 800 17-DEC-12 Michaels Sales 1600 20-FEB-18 Ward Sales 1250 22-FEB-19 Martin Sales 1250 28-SEP-18 Scott Analyst 3000 09-NOV-18 Turner Sales 1500 08-SEP-19 EMPL_LAST_NAME JOB_TITLE SALARY HIREDATE --------------- ---------- ---------- --------- James Clerk 950 03-DEC-17 12 rows selected. ===== I am now going to rollback that change... 8-) ===== Rollback complete.