--
-- 315lab12-projected.sql
--
-- last modified: 11-10-10 (during lab)

-- uncomment if needed -- should already have these tables in your
--    database, though
--
-- @ set-up-ex-tbls

-- SET UP for WEEK 12 LAB EXERCISE:
-- *   ssh to nrs-labs
-- *   mkdir 315lab12
-- *   chmod 700 315lab12
-- *   cd 315lab12
-- *   nano lab12.sql
-- *   ...and start up today's lab script with:
--     *   comments containing your name, Week 12 lab, today's date
--     *   also spool on and spool off commands:
--         spool lab12-results.txt
--         spool off
-- REMEMBER: put the queries for today's lab exercise
--    IN BETWEEN the spool and spool off commands!

spool lab12-results.txt

--***********************************
-- SQL*LOADER
--***********************************

-- SQL*Loader is a program that comes with the Oracle DBMS
--    to import data from files into database tables;

-- three SIMPLE examples, look at Google/Oracle doc for more!

-- basically: you set up a .ctl (control) file for SQL*Loader,
--    that says WHERE the data is and HOW to handle it
--    and WHERE to put it,
-- and then run SQL*Loader program with that control file
--    and (possibly) data files

-- consider: the empltemp example (the 4 empltemp files)

-- also consider: the painter example: using a sequence and SQL*Loader
--    in conjunction;

-----
-- QUICK sqlldr additional tidbits: 
-- *   using sequences and sqlldr together
-- *   host and sqlldr
-----

-- want to use sequences AND sqlldr? It can be done!

-- make a new copy of the painter table:

drop table painter cascade constraints;

create table painter
(ptr_num        char(3),
 ptr_lname      varchar2(30) not null,
 ptr_fname      varchar2(15),
 primary key    (ptr_num)
);

drop table painting cascade constraints;

create table painting
(ptg_title      varchar2(30),
 ptr_num        char(3),
 primary key    (ptg_title),
 foreign key    (ptr_num) references painter
);

-- here's the painter sequence, in case you have deleted it or something:

drop sequence painter_seq;

create sequence painter_seq
increment by    2
start with      100;

-- and here are the initial rows:

insert into painter
values
(painter_seq.nextval, 'Van Gogh', 'Vincent');

insert into painter
values
(painter_seq.nextval, 'Monet', 'Claude');

insert into painter
values
(painter_seq.nextval, 'Da Vinci', 'Leonardo');

select  *
from    painter;

-- see painter.ctl and painter.dat to now see how you can now use sqlldr and 
--    sequences together;

-- note that the host SQL*Plus command opens up a new OS shell, and puts you 
--    in it; (when you exit the shell, you are back in your SQL*Plus session)
-- host with an OS command after it just executes that one OS command in the
--    new shell, but you then you come right back to your SQL*Plus session;

-- why mention this now?
-- ...because you type sqlldr at the OS prompt, NOT within SQL*Plus;
-- ...but you can have the sqlldr command in a script IF you precede it with
--    host:

-- when  run, you WILL need to enter your Oracle username and password
--    when prompted

host sqlldr control=painter.ctl log=painter.log

select *
from   painter;

--***********************************************************---
-- VIEW examples
--***********************************************************---

---------
-- A view is a *derived* table --- data is not stored, just
-- how to GENERATE the desired information whenever the view
-- is used. (Can give someone a specific "picture", or view,
-- of certain data --- BUT no update hassles!)
--------

-- create a view short_empl that JUST includes the employee's
-- name, their job_title, and their manager's employee number
-- (as for tables, it is fairly common, note, to precede these 
-- with a "drop view" statement when they are in scripts that 
-- might be re-run...)

drop view short_empl;

create view short_empl as
select	empl_num, empl_last_name, job_title, mgr
from	empl;

-- once a view is created, you can use it just like a
-- table in queries --- BUT remember, it is not REALLY a
-- table, it is "built" whenever it is referred to...

-- let's look at view short_empl:

select	*
from	short_empl;

-- I can now use this like ANY table in queries, note!

select	empl_last_name, cust_lname
from	short_empl, customer
where	short_empl.empl_num = customer.empl_rep;

-- ...and can even use it to build another view...!

drop view cust_rep_display;

create view cust_rep_display as
select  empl_last_name, cust_lname
from    short_empl se, customer c
where   se.empl_num = c.empl_rep;

select	*
from	cust_rep_display;

-- if you change the underlying tables, the view simply
-- reflects those changes next time it is generated:

insert into customer
values
('456789', 'Fourthly', 'Fourth', '7499', '4444 Fourth Street', 
 'Four Corners', 'CA', '94444', 444.44);

select  *
from    cust_rep_display;

--********
-- IMPORTANT SECURITY CONSIDERATION:
-- remember the grant and revoke commands? e.g.,
--
--grant select 
--on painter
--to abc999, cde888, fgh777;
--
--revoke select
--on painter
--from abc999, cde888, fgh777;
--
-- Oracle won't let you protect PART of a table from access; grant
--    isn't that "fine-tuned";
-- BUT can you see that, if a user should only be able to access SOME
--    columns of a table, you COULD use a view to accomplish this:
-- 1. create a view consisting of just what it is OK for that user to see;
-- 2. grant select access to THAT user to that view, NOT the whole table
--    the view is "built" from;
--
-- See how, carefully used, this could enhance database security?
--********

-- LAB EX #1
-- drop and create a view empl_salaries that includes JUST
--    employee last names and salaries
-- THEN write a query projecting all of the "contents" of
--    that view (really computing them right then)
-- THEN write a query that projects JUST the highest
--    salary using ONLY that view

-- you can also give the columns in the view different names
-- than the columns/expressions being projected to create
-- that view

-- we'll do that in short_empl2:

drop view short_empl2;

create view short_empl2(name, "job category", manager) as
select	empl_last_name, job_title, mgr
from	empl;

-- see what column names appear when you look at short_empl2:

select	*
from	short_empl2;

-- the queries using views can be as complex as you'd like, note
-- (but you should use the column names you set up for the view)
--
-- (note the need for double quotes for the column name containing
-- a blank...! Don't use double quotes for view column names, thus,
-- unless you REALLY NEED them --- they unnecessarily complicate
-- subsequent queries!!! 
--    (besides, once you have created a view, you can set up column
--    commands for that view's columns to make their headings,
--    formats, etc. as sophisticated as you'd like...)

select	 "job category", name
from	 short_empl2
order by "job category";

-- (what if you use the original table's column names, instead?)
-- THIS WILL FAIL, complaining about the invalid column name;

select	 "job category", empl_last_name
from	 short_empl2
order by "job category";

-- you can also give the columns names using column aliases in the 
-- sub-select

drop view short_empl3;

create view short_empl3 as
drop view short_empl3;

create view short_empl3 as
select	empl_last_name last_name, job_title position
from	empl;

select	 position, last_name
from	 short_empl3
order by last_name;

-- DO CLICKER QUESTION 1 NOW;

-- another view example --- with functions, 
-- you HAVE to give the column another name...

-- let's say you want a view that gives the average salary
-- per job category: let's call it salary_avgs

-- this WON'T work --- it'll complain that you need a column
-- alias for avg(salary)

drop view salary_avgs;

create view salary_avgs as
select	 job_title, avg(salary)
from	 empl
group by job_title;

-- this WILL work...

create view salary_avgs(job, salary_avg) as
select   job_title, avg(salary)
from     empl
group by job_title; 

-- (this would work, too...:
--
--create view salary_avgs as
--select   job_title, avg(salary) salary_avg
--from     empl
--group by job_title; 

select	*
from	salary_avgs;

--*****
-- now, it USED to be an error to use an "order by" clause in
-- view CREATION --- after all, it certainly doesn't make a
-- lot of sense to include it. You can always order the view
-- however you want when you display it.

-- how about this semester? is it a
-- "SQL command not properly ended" error or not?!

-- THIS IS STILL CONSIDERED POOR STYLE, HOWEVER.

drop view empl_roster;

create view empl_roster as
select	 empl_last_name, dept_name, dept_loc
from	 empl, dept
where	 empl.dept_num = dept.dept_num
order by dept_name;

select	*
from	empl_roster;

select	 *
from	 empl_roster
order by dept_loc;

-- NOT having the order-by clause is
-- better STYLE, though:

drop view empl_roster;

create view empl_roster as
select	empl_last_name, dept_name, dept_loc
from	empl, dept
where	empl.dept_num = dept.dept_num;

-- makes more sense, anyway --- you can always display the
-- view in any order you like!

-- by dept_name...

select	 dept_name, empl_last_name, dept_loc
from	 empl_roster
order by dept_name;

-- by dept_loc...

select	 dept_loc, empl_last_name, dept_name
from	 empl_roster
order by dept_loc;

-- by empl_last_name!

select	 *
from	 empl_roster
order by empl_last_name;

-- LAB EXERCISE #2
-- drop and create a view earliest_hires that will contain 2 columns:
--    *   a job title
--    *   the earliest (minimum) hiredate for someone with that job title
-- THEN write a  query that projects all of the "contents" of that view
-- THEN write a query that project JUST the latest (maximum) hiredate
--    of the hiredates from the earliest_hiredate view (the latest
--    of the earliests...!) (use ONLY the earliest_hiredate view)

--****************************
-- SQL*PLUS REPORTS
--****************************

-----
-- moving on to simple ASCII reports
-----

--*****
-- note: / on a line by itself means to re-run the previous 
-- SQL command (not SQL*PLus command, note!)

-- so, this'll redo previous query:

/

--******************************************************---
-- first thing: it is good form to make sure that no previous
-- breaks, columns, computes are going to mess up your coming
-- report --- so clear 'em in your report script, first:
-----

clear	breaks
clear	columns
clear	computes

-- compliments of S. Griffin: yes, this works, too!!!

clear breaks columns computes

--***********************************---
-- feedback: that little line saying how many rows were selected
-- by a query
-----

-- want to know its current value? Here's how:

show feedback

-- here's how to set the feedback to a different number:

set feedback 3
show feedback

-- this'll note that 3 rows were selected.

select	*
from	painter;

-- this will not note that one row was:

select	*
from	painter
where	ptr_lname = 'Monet';

-- this will simply turn feedback OFF

set feedback off

-- when you are ready to have results written to a file, spool!

-- (commented out for lab purposes...)
-- spool report_output.txt

--***************************************************---
-- pagesize: how many lines in a "page"
-----

show pagesize
set pagesize 30

-- you can set pagesize to 0 to mean, NEVER want page breaks (when
-- you want to generate a flat file of data for another program,
-- for example

--*********************************************************---
-- linesize: how many characters are in a line
-----

show linesize
set linesize 50

--**********************************************************---
-- newpage: the number of blank lines to put before the top title
-- of a page (if any)
-----
-- (appears that each SQL select result starts on a new "page", pagesize-
-- and newpage-wise)

show newpage
set newpage 5

-----
-- can set newpage to 0 as well --- nice for creating a flat file
-- of input
-----

--*****
-- (oddly enough, the number of lines in a page is
-- pagesize + newpage...)

-- INSERT CLICKER QUESTION 2 HERE

--------
-- more on SQL*Plus/ASCII reports in Week 13 lab!
--------

-- COURSE STYLE STANDARD:
-- because this script DOES mess with some default SQL*Plus
--    settings, put 'em "back" before your script ends,
--    to avoid confusing/annoying a user who does something else
--    after running your script;

set feedback 	6
set pagesize 	14
set linesize 	80
set newpage  	1

spool off

-- end of 315lab12-projected.sql