/* CS 325 - Week 5 Labs - 3:00 lab - 2016-09-21 */

spool 325lab05-out.txt

/*==========
    NOTE that the SQL SELECT is used to implement
    ALL the relational operations (and more)

    (don't get SQL SELECT and relational selection
    mixed up...!)

    *   SQL SELECT makes it quite reasonable to write
        ad-hoc queries -- answering questions on the fly
	 about your data that you might not even have
	 considered during the design phase --
	 by COMBINING relational operations plus a bit more

    [ ] around OPTIONAL parts (don't type the [ ] !!)
    < > around programmer-chosen parts
                              (don't type the < > !!)

    SELECT [DISTINCT] <one-or-more-comma-sep'd-exprs>
    FROM   <one-or-more-comma-sep'd-exprs-rep'ing-relations>
    [WHERE <boolean_expression>];

    *    here are the informal SEMANTICS of this:
         FIRST: CONCEPTUALLY, take the Cartesian product
	          of the relations in the FROM clause

         SECOND: IF there is a WHERE clause,
	           take a relational selection
		   	 of the rows resulting from the the FIRST
			     step that satisfy the WHERE clause's
			     	   boolean expression

         THIRD: do a PROJECTION, on the rows resulting
	          from the SECOND step, of the expressions
		       in the SELECT clause;
		       	  (it is a TRUE relational projection,
			      getting rid of duplicate rows in the
			      	      result, IF you use DISTINCT after the
				      	      SELECT)

    *   what about SELECT *?
        ...a * in the SELECT clause is a shorthand for
	    ALL the columns in the resulting relation
*/

/*========== 
    true relational projection!
 
    SELECT DISTINCT <comma-sep'd-list-of-columns>
    FROM <tblname>;
*/

prompt ===============================================
prompt true relational projection of last names, salaries, and 
prompt hiredates from the empl relation 

select distinct empl_last_name, hiredate, job_title
from   empl;

prompt ===============================================
prompt true relational projection of job titles
prompt from the empl relation 

select distinct job_title
from   empl;

prompt ===============================================
prompt you can project the columns in the order desired;
prompt here, projecting salary then empl_last_name:

select distinct salary, empl_last_name
from   empl;

/* if your leave off the distinct,
   you may not get a "true" relational projection,
   because you might get duplicate rows,
   which relations don't have...
*/

prompt ===============================================
prompt notice the duplicate rows here...(NO distinct!)

select job_title
from   empl;

/* remember, duplicate TUPLES/rows are the issue,
   not repeated values in some column...
*/

prompt ===============================================
prompt pure relational projection of job titles, dept nums

select distinct job_title, dept_num
from   empl;

prompt ===============================================
prompt not pure projection, has duplicates ROWS (no distinct!)

select job_title, dept_num
from   empl;

/*==========
    true relational SELECTION

    SELECT *
    FROM   <tblname>
    WHERE  <boolean_expression>;

    ...note that SQL has = < > <= >=
                         <> !=
					 and or not
                         ...and more...!
*/

prompt ===============================================
prompt pure relational selection of empl rows with a job title
prompt of 'manager' (to demo that case matters in string *literals*)

select *
from   empl
where  job_title = 'manager';

prompt ===============================================
prompt pure relational selection of empl rows with a job title
prompt of 'Manager' 

select *
from   empl
where  job_title = 'Manager';

prompt ===============================================
prompt pure relational selection of empl rows with a salary
prompt that is more than 4000

select *
from   empl
where  salary > 4000;

prompt ===============================================
prompt pure relational selection of empl rows with a job title
prompt of 'Manager' AND a salary more than 2500

select *
from   empl
where  job_title = 'Manager'
       and salary > 2500;

/*========== 
   course style: in a SQL SELECT statement:

   *   start the FROM clause on its own line!
   *   start the WHERE clause on its own line!
   *   indent at least 3 spaces if a clause is longer
       than one line
   *   (and no blank lines INSIDE!!!! a select!)
   *   (BUT put a blank line BEFORE and AFTER a select...)
*/

/*==========
    relational equi-join and natural join
    (and, OK, Cartesian products along the way...)

    Cartesian product:

    SELECT *
    FROM   tbl1, tbl2;

    ...if you want an equi- or natural join,
    you NEEEEEEEEEEEEEEEED a join condition!

    there are TWO ways to give this condition --
    
    HERE are TWO WAYS to write an EQUI-JOIN!!!!

    1. give the join condition in the where clause

       SELECT *
       FROM   tbl1, tbl2
       WHERE  tbl1.column_name = tbl2.column_name;

    2. ANSI SQL/99 syntax:

       SELECT *
       FROM tbl1 JOIN tbl2 
            ON (tbl1.column_name = tbl2.column_name);
       
*/

prompt ===============================================
prompt TWO true equi-joins of the empl and dept tables with
prompt the join condition (empl.dept_num = dept.dept_num)
prompt (using both "old" and ANSI SQL/99 versions)

select *
from   empl, dept
where  empl.dept_num = dept.dept_num;

select *
from   empl JOIN dept on (empl.dept_num = dept.dept_num);

/*==========   
    WARNING:

     there is a NATURAL JOIN option in ANSI SQL/99
     (that standard) that I've read has AMBIGUITY problems
     in practice --
     course style standard: we will NOT use the
     NATURAL JOIN variant

     how do we get a natural join, then?
     ...we will list, in the SELECT clause,
         the names of all BUT one of the joined-upon
	   columns...!
         *   does not matter WHICH you project --
	       BUT!!!!

         *   ...NOTE!!! if a column with the same name appears in
             MORE THAN ONE relation in the FROM clause,
             MUST precede its name with the table name
             (so NOT ambiguous!!)

*/

prompt ===============================================
prompt TWO true natural joins of the empl and dept tables with
prompt the join condition (empl.dept_num = dept.dept_num)
prompt (using both "old" and ANSI SQL/99 versions)

select empl_num, empl_last_name, job_title, mgr,
       hiredate, salary, commission, empl.dept_num,
       dept_name, dept_loc
from   empl, dept
where  empl.dept_num = dept.dept_num;

select empl_num, empl_last_name, job_title, mgr,
       hiredate, salary, commission, empl.dept_num,
       dept_name, dept_loc
from   empl JOIN dept ON empl.dept_num = dept.dept_num;

prompt ===============================================
prompt here is the error you get if you JUST project dept_num
       in this attempted natural join:

select empl_num, empl_last_name, job_title, mgr,
       hiredate, salary, commission, dept_num,
       dept_name, dept_loc
from   empl, dept
where  empl.dept_num = dept.dept_num;

spool off