-----
-- CIS 180 - Intro to SQL
-- Class 3 - 3-3-03
-----

-- last modified: 3-3-03

-- write script results to file 180class3_results.txt

spool 180class3_results.txt

-----
-- Cartesian product using SQL Select statement
-----

-- select *
-- from  tbl1, tbl2;
--
-- cartesian product of tbl1 and tbl2

select *
from   dept, empl;


-----
-- equi-join using SQL Select statement
-----

-- select *
-- from  tbl1, tbl2
-- where tbl1.common_column = tbl2.common_column;
--
-- equi-join of tbl1 and tbl2 on common column common_column
-- (common_column is often a PRIMARY key in one table, and a
-- FOREIGN KEY in the other...)

-- equi-join on tables dept, empl on their "common" column
-- dept_num

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


-- equi-join on tables empl, customer
-- on common column empl_num in empl, empl_rep in customer

select *
from   customer, empl
where  empl_rep = empl_num; -- column names differ, don't HAVE to
                            -- put the table names first

-----
-- table aliases
-----

-- can RENAME a table temporarily (for a single select statement!)

-- select *
-- from   tbl new_name
-- where  cond;
--
-- tbl must now be referred to as new_name for this entire
-- select statement;

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

-- but --- you MUST use this alias THROUGHOUT the select statement...

-- let's say you JUSTS want to project the dept_num, dept_name,
-- and empl_last_name --- that's additional projection from an
-- equi-join, right?

-- WILL NOT WORK:

select dept_num, dept_name, empl_last_name
from   dept d, empl e
where  d.dept_num = e.dept_num;

-- WILL NOT WORK #2

select dept.dept_num, dept_name, empl_last_name
from   dept d, empl e
where  d.dept_num = e.dept_num;

-- THIS version works:

select d.dept_num, dept_name, empl_last_name
from   dept d, empl e
where  d.dept_num = e.dept_num;

-- THIS works, too:

select e.dept_num, dept_name, empl_last_name
from   dept d, empl e
where  d.dept_num = e.dept_num;

-----
-- 3-table equi-joins, and more...
-----

-----
-- RULE OF THUMB: when you join X tables, you MUST have (X-1)
-- join conditions!!!!!!
-----

-- what if I want to see the customer last name, the name of
-- their empl rep, and the department name and department location
-- of their empl rep?

select cust_lname customer, empl_last_name sales_rep, dept_name, dept_loc
from   customer c, empl e, dept d
where  empl_rep = empl_num
and    e.dept_num = d.dept_num; 

-----
-- when MUST you use table aliases?
-----

-- what if I want to show each employee's last name, AND the
-- last name of their manager?

-- MUST use table aliases for this...

select e1.empl_last_name employee, e2.empl_last_name manager
from   empl e1, empl e2
where  e1.mgr = e2.empl_num;


-----
-- COMBINING selection, projection, and equi-join (and order by!)
-----

-- these can be combined as MUCH as you wish!

-- what I I want to show the employee last names and their
-- managers' names and their salaries for salesmen making
-- less than or equal to 1500, ordered by increasing salary?

select   e1.empl_last_name employee, e2.empl_last_name manager, e1.salary
from     empl e1, empl e2
where    e1.mgr = e2.empl_num
and      e1.job_title = 'Salesman'
and      e1.salary <= 1500
order by e1.salary;


-- stop writing results to file 180class3_results.txt

spool off