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

-- last modified: 3-10-03

spool 180class5_results.txt

-----
-- NESTED selects/subselects
-----

-- or: a WHERE clause MAY have a select statement INSIDE of it!

-- remember the predicate IN?

select *
from   dept
where  dept_loc in ('Dallas', 'Boston');

select empl_last_name
from   empl
where  dept_num in ('100', '200');

-- we could build IN's list of values with another SELECT!

select empl_last_name
from   empl
where  dept_num IN (select dept_num
                    from dept
                    where dept_loc in ('Dallas', 'Boston'));

-- I could write this using a JOIN, too:

select empl_last_name
from   empl e, dept d
where  e.dept_num = d.dept_num
and    dept_loc in ('Dallas', 'Boston');

-- what departments have Managers in them?

select *
from   dept
where dept_num IN (select dept_num
                   from   empl
                   where  job_title = 'Manager');

select d.*
from   dept d, empl e
where  d.dept_num = e.dept_num
and    job_title = 'Manager';

-- quick point: = does NOT work the same for all of these...
== Why? Because = only works with ONE thing on its RHS....

prompt this does NOT work: = cannot have more than 1 on RHS
select *
from   dept
where dept_num = (select dept_num
                   from   empl
                   where  job_title = 'Manager');

-----
-- ORDER BY: goes in OUTERMOST selects, only!
-----

select *
from   dept
where dept_num IN (select dept_num
                   from   empl
                   where  job_title = 'Manager')
order by dept_loc;

prompt this does NOT work: order by goes in OUTERMOST select only
select *
from   dept
where dept_num IN (select dept_num
                   from   empl
                   where  job_title = 'Manager'
                   order by dept_loc);

-----
-- more examples...
-----

-- what are the names of customers whose employee reps are in
-- Chicago?

select cust_lname
from   customer
where  empl_rep in (select empl_num
                    from   empl
                    where  dept_num in (select dept_num
                                        from dept
                                        where dept_loc = 'Chicago'));

-----
-- ANY and ALL predicates
-----

-- relational + one of these can be used with nested queries, too

-- ex: >ALL is true is att is more than ALL of the values on the RHS
-- salary >ALL (100, 200, 300)
--    will be true if salary > 300

-- ex: >ANY is true if att is more than ANY ONE of the values on the RHS
-- salary >ANY (100, 200, 300)
--    will be true if salary > 100

-- can use these with <, <=, >, >= also!

-- are there any Clerks who make more than any non-Clerk?

select *
from   empl
where  job_title = 'Clerk'
and    salary >ANY (select salary
                    from   empl
                    where  job_title != 'Clerk');

-----
-- QUICK attempt at EXISTS, NOT EXISTS
-----

-- EXISTS, NOT EXISTS:
-- NO LHS
-- need to have a CORRELATION CONDITION
--    (it is a condition involving a table in the OUTER select,
--    but NOT the inner select;)

insert into dept
values
('600', 'Database', 'Arcata');

-- what departments currently have employees?

select *
from   dept d
where  EXISTS (select 'a'
               from   empl e
               where  e.dept_num = d.dept_num);

-- what departments currently do NOT have any employees?

select *
from   dept d
where  NOT EXISTS (select 'a'
                   from   empl e
                   where  e.dept_num = d.dept_num);

-- two other ways to show what departments have employees...

-- note that we NEED the distinct here...
select distinct d.*
from   empl e, dept d
where  d.dept_num = e.dept_num;

select *
from   dept
where  dept_num IN (select dept_num
                    from empl);

-- REALLY, these can ALSO be combined however you want/need...

select empl_last_name, dept_loc
from   empl e, dept d
where  d.dept_num = e.dept_num
and    job_title = 'Clerk'
and    salary >ANY (select salary
                    from   empl
                    where  job_title != 'Clerk');



-- stop writing results to file 180class5_results.txt

spool off