===== here is one way to join empl and dept and project empl names and dept names: ===== EMPL_LAST_NAME DEPT_NAME --------------- --------------- King Management Jones Research Blake Sales Raimi Accounting Ford Research Smith Research Michaels Sales Ward Sales Martin Sales Scott Research Turner Sales EMPL_LAST_NAME DEPT_NAME --------------- --------------- Adams Operations James Sales Miller Accounting 14 rows selected. ===== here is another way of writing the same operations, using ANSI join notation: ===== EMPL_LAST_NAME DEPT_NAME --------------- --------------- King Management Jones Research Blake Sales Raimi Accounting Ford Research Smith Research Michaels Sales Ward Sales Martin Sales Scott Research Turner Sales EMPL_LAST_NAME DEPT_NAME --------------- --------------- Adams Operations James Sales Miller Accounting 14 rows selected. ===== OH, the parentheses are NOT required around the join condition for Oracle implementation of ANSI join syntax: ===== EMPL_LAST_NAME DEPT_NAME --------------- --------------- King Management Jones Research Blake Sales Raimi Accounting Ford Research Smith Research Michaels Sales Ward Sales Martin Sales Scott Research Turner Sales EMPL_LAST_NAME DEPT_NAME --------------- --------------- Adams Operations James Sales Miller Accounting 14 rows selected. ===== YES you can have table aliases using ANSI join syntax, also ===== EMPL_LAST_NAME DEPT_NAME --------------- --------------- King Management Jones Research Blake Sales Raimi Accounting Ford Research Smith Research Michaels Sales Ward Sales Martin Sales Scott Research Turner Sales EMPL_LAST_NAME DEPT_NAME --------------- --------------- Adams Operations James Sales Miller Accounting 14 rows selected. ===== what if you want: the name of a customer, the name of the employee who represents them, and the name of the department that employee works for? ===== CUST_LNAME EMPL_LAST_NAME DEPT_NAME -------------------- --------------- --------------- Firstly Michaels Sales Secondly Martin Sales Thirdly Michaels Sales ===== using ANSI join syntax: ===== CUST_LNAME EMPL_LAST_NAME DEPT_NAME -------------------- --------------- --------------- Thirdly Michaels Sales Secondly Martin Sales Firstly Michaels Sales ===== either form can have a where clause, group by, order by, etc. version 1: ===== CUST_LNAME EMPL_LAST_NAME DEPT_NAME -------------------- --------------- --------------- Firstly Michaels Sales Thirdly Michaels Sales ===== version 2: ===== CUST_LNAME EMPL_LAST_NAME DEPT_NAME -------------------- --------------- --------------- Firstly Michaels Sales Thirdly Michaels Sales ===== dropping and creating view short_empl: ===== View dropped. View created. ===== using view short_empl: ===== EMPL_LAST_NAME JOB_TITLE --------------- ---------- King President Jones Manager Blake Manager Raimi Manager Ford Analyst Smith Clerk Michaels Sales Ward Sales Martin Sales Scott Analyst Turner Sales EMPL_LAST_NAME JOB_TITLE --------------- ---------- Adams Clerk James Clerk Miller Clerk 14 rows selected. ===== committing before a temporary change: ===== Commit complete. ===== deleting all clerks: ===== 4 rows deleted. ===== BUT see? view short_empl, re-generated each time used, properly reflects the changed contents: ===== EMPL_LAST_NAME JOB_TITLE --------------- ---------- King President Jones Manager Blake Manager Raimi Manager Ford Analyst Michaels Sales Ward Sales Martin Sales Scott Analyst Turner Sales 10 rows selected. ===== rolling back that delete: ===== Rollback complete. ===== dropping and creating view short_empl2, defining column aliases in the select: ===== View dropped. View created. ===== querying short_empl2: ===== LAST_NAME JOB --------------- ---------- King President Jones Manager Blake Manager Raimi Manager Ford Analyst Smith Clerk Michaels Sales Ward Sales Martin Sales Scott Analyst Turner Sales LAST_NAME JOB --------------- ---------- Adams Clerk James Clerk Miller Clerk 14 rows selected. ===== projecting just job, last_name from short_empl2, ordering results by job: ===== JOB LAST_NAME ---------- --------------- Analyst Ford Analyst Scott Clerk Smith Clerk Miller Clerk Adams Clerk James Manager Blake Manager Raimi Manager Jones President King Sales Turner JOB LAST_NAME ---------- --------------- Sales Martin Sales Ward Sales Michaels 14 rows selected. ===== a case where a column in the view MUST be renamed, when projecting an aggregate function result or a computation: ===== View dropped. View created. ===== now querying view salary_avgs: ===== JOB_TITLE JOB_AVG_SALARY ---------- -------------- Manager 2758.33333 Analyst 3000 Clerk 1037.5 President 5000 Sales 1400 ===== using other syntax for naming columns, as drop and create view salary_avgs2: ===== View dropped. View created. ===== querying salary_avgs2: ===== JOB_TITLE JOB_AVG_SALARY ---------- -------------- Manager 2758.33333 Analyst 3000 Clerk 1037.5 President 5000 Sales 1400