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

spool 325lab05-out.txt

/*==========
    the SQL SELECT statement is a powerful tool
    for combining relational operations and more
    in writing ad-hoc queries,
    asking questions on-the-fly about your data

    yes, you use the SQL SELECT statement
    to write relational selections,
    relational projections,
    natural joins, equi-joins,
    Cartesian products, 
    combinations of all of the above,
    and more...
*/

/* 
    yes, there is more to SELECT than
    
    SELECT *
    FROM   tblname;
*/

/* 
    there are TWO required clauses in a SELECT:

    [ ] means something is OPTIONAL - you don't type the [ ]!!
    < > means something the programmer chooses - you don't type
        the < > !

   SELECT [DISTINCT] <one-or-more-comma-sep'd-expressions>
   FROM  <one-or-more-comma-sep'd-things-rep'ing-relations>;

    *   STYLE: always start the FROM clause
        on its own line

    *   informal semantics:
        FIRST, (conceptually), the Cartesian product of
            the relations in the FROM clause is done;
	         (you just get the relation if there is only
		     one)

        SECOND: (conceptually) you do a projection
	     from the FIRST step's results, projecting
	         what is in the SELECT clause
 
    *   there is a very common optional 3rd clause,
        the WHERE clause:

   SELECT [DISTINCT] <one-or-more-comma-sep'd-expressions>
   FROM  <one-or-more-comma-sep'd-things-rep'ing-relations>
   [WHERE <boolean-condition>];

        this CHANGES the informal semantics,
	 ADDING a step between the two mentioned
	 above:

    *   informal semantics:
        FIRST, (conceptually), the Cartesian product of
            the relations in the FROM clause is done;
	         (you just get the relation if there is only
		     one)

        SECOND: (conceptually), take the relational selection
	      of the rows from the FIRST step's results
	           for which the WHERE clause's condition is true

        THIRD: (conceptually) you do a projection
	     from the SECOND step's results, projecting
	         what is in the SELECT clause
*/

/* btw: what about SELECT *?
   * just means project ALL the columns in the given
     relation resulting from the FROM and WHERE clauses
*/

/*==========
     true RELATIONAL PROJECTION!

     here's how you do a "pure"/"true" relational
     projection in SQL:

     SELECT DISTINCT <comma-sep'd-columns-to-project>
     FROM   tblname;
*/

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

select distinct empl_last_name, salary, hiredate
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;

/*  (you get a NOT-QUITE-TRUE projection
    without the DISTINCT --
    duplicate rows are left in the result!
*/

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

select job_title
from   empl;

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;

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

    SELECT *
    FROM   <tblname>
    WHERE  <boolean_condition>;
    
    ...this selects tuples from the given relation
    for which the given boolean condition is true

    ...= < > <= >=
       <>  !=
       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