/*========
  CS 325 - Week 8 Lecture 2 - 2016-10-13
========*/

spool 325lect08-2-out.txt

/*========
   odd little SQL feature:

   & - if you put an & in front of a name in
       a query, for example, 
       then, when run, SQL*Plus environment will 
       ask you to enter a value for that
       "name",
       and use that entered value in that query;
========*/

prompt
prompt note that single quotes ARE needed here:
prompt

select salary
from   empl
where   job_title = &job_ttl;

/*========
    ...if you put & inside single quotes,
    you DON'T even have to type the single quotes,
    then!
========*/

prompt DO NOT need quotes here:
prompt

select salary
from   empl
where   job_title = '&qtd_job_ttl';

/*========
   it works in SQL*Plus commands, too?!
   (and there can be a blank between the & and the name,
   evidently...!)
========*/

prompt Moo & Oink

prompt
prompt looks like HAVE to put a name after an '&'
prompt character:
prompt

prompt how about this? & 

/*========
    SQL Injection - a technique to maliciously
        exploit applications that use client-supplied
        data in SQL statements;

	 techniques may differ,
	 but they all exploit a single vulnerability
	 in an application:
	 incorrectly validated or nonvalidated string
	 literals are concatenated into a dynamic
	 SQL statement,
	 and interpreted as code by the DBMS
========*/

prompt
prompt give salaries for those with the desired job_title
prompt (quotes required!):
prompt

select salary
from   empl
where  job_title = &job_ttl;

prompt give last names for those with the desired job_title
prompt (quotes required!):
prompt

select empl_last_name
from   empl
where  job_title = &job_ttl;

/*========
    if you enter:

'Manager' or 1=1

		     ...you get ALL of the rows' info

'Manager' union (select table_name from user_tables)

		     ...you get ALL tables in your corner of
		          the database, plus the manager names!
========*/

/*========   
   MORE about this in CS 328
   BUT SOME ways to combat SQL injection include:
   *   always SANITIZE user input before
       using it in a dynamic SQL statement
   *   use so-called "prepared statements"
       that make it harder to SQL-inject
   *   encrypt certain data (such as passwords)
   *   maybe judicious use of GRANT and REVOKE?
   *   take MULTIPLE preventive measures at
       DIFFERENT levels;
========*/

/*========
   and now for something completely different...

   YES, some questions can be answered
   with a CHOICE of query approaches!

   some neeeed joins,
   some neeeeed nesting,
   some can use either,
   some neeeeed both!
========*/

prompt needs BOTH join and nesting
prompt
prompt I want the manager's name AND the clerk's name
prompt    AND the salary for clerk(s) making the
prompt    highest salary

select e_mgr.empl_last_name manager, 
       e_empl.empl_last_name clerk,
       e_empl.salary
from   empl e_empl, empl e_mgr
where  e_empl.mgr = e_mgr.empl_num
       and e_empl.job_title = 'Clerk'
       and e_empl.salary = (select max(salary)
                            from   empl
			    	  		    where  job_title = 'Clerk');

/*========
   examples writing basically the same
   question in more than one way
========*/

prompt employee last names and salaries of those who work
prompt in Chicago, using subselect:

select empl_last_name, salary
from   empl
where  dept_num in
       (select dept_num
        from dept
	where dept_loc = 'Chicago');

prompt employee last names and salaries of those who work
prompt in Chicago, using join:

select empl_last_name, salary
from   empl e, dept d
where  e.dept_num = d.dept_num
and    dept_loc = 'Chicago';

/*========
    sometimes you do get a difference based on if you use
    a sub-select or join...
========*/

prompt dept names and locations of departments that have
prompt at least one employee hired before June 1, 1991,
prompt using nesting:

/*========
    outer select is projecting from dept,
    projecting dept names and locations that are
    selected by the WHERE clause -- and each dept
    only appears once in the dept table
========*/

select dept_name, dept_loc
from   dept
where  dept_num in
       (select dept_num 
       from    empl
       where   hiredate < '01-JUN-1991');

prompt dept names and locations of departments that have
prompt at least one employee hired before June 1, 1991,
prompt using join:
prompt (make sure you understand WHY more rows are projected here)

/*========
    ...this query projects more rows because there is a row
    for EACH employee in the outer select,
    and you are projecting dept name and location
    for EACH employee hired before the specified date
========*/

select dept_name, dept_loc
from   empl e, dept d
where  e.dept_num = d.dept_num
and    hiredate < '01-JUN-1991';

spool off
 
-- end of 325lect08-2.sql