Please send questions to
st10@humboldt.edu .
--
-- 315lab12-projected.sql
--
-- last modified: 11-17-10 (after lab)
-- let's start all of this with a nice, clean set of example tables;
-- (copy and paste from course web page --- under "SQL and Lab-related
-- Examples List" --- if you do not have this script handy.)
@ set-up-ex-tbls
-- SET UP for WEEK 13 LAB EXERCISE:
-- * ssh to nrs-labs
-- * mkdir 315lab13
-- * chmod 700 315lab13
-- * cd 315lab13
-- * nano lab13.sql
-- * ...and start up today's lab script with:
-- * comments containing your name, Week 13 lab, today's date
-- * also spool on and spool off commands:
-- spool lab13-results.txt
-- spool off
-- REMEMBER: put the queries for today's lab exercise
-- IN BETWEEN the spool and spool off commands!
spool lab13-results.txt
--******************************************************---
-- 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
--*****
-- NEW stuff starts HERE
--*****
--**************************************************************---
-- COLUMN examples!
--**************************************************************---
-----
-- the BIG thing to remember: these do NOT change the tables or data
-- in ANY WAY --- they are simply DISPLAY preferences;
-----
-- use a for formatting strings (and dates, too) --- a is for
-- ALPHANUMERIC (thanks to B. Gilden)
--
-- | in a heading is where you want a next line to begin in that
-- heading;
column empl_last_name heading 'Employee|Last Name' format a25
column empl_num format a8
set linesize 95
select *
from empl;
-- look! you don't have to have quotes to use a multi-line
-- column heading!
column empl_last_name heading Employee|Name format a25
/
-- but, you MUST have quotes if a heading has a space!
-- (this fails...)
column empl_last_name heading Employee|Last Name format a25
-- hey look --- double quotes work, too...
column empl_last_name heading "Employee|Last Name" format a25
/
-- INSERT CLICKER QUESTION 1 HERE
-- lets make the employee last name column a trifle narrower
-- (note how too-short a format for a string column causes ugly
-- wrapping to next line;)
column empl_last_name heading 'Employee|Last Name' format a2
/
-- if you put TRUNCATED (or TRU) after a format --- will TRUNCATE (cut off)
-- after that many characters, instead of wrapping:
column empl_last_name heading 'Employee|Last Name' format a2 TRUNCATED
/
-- you can have WRAPPED (the default), to wrap at EXACTLY the column
-- width --- or WOR (for WORD WRAPPED), to break on a blank
insert into painting
values
('Waterlilies', '104');
insert into painting
values
('Yet four more', '104');
column ptg_title format a7 WOR
column ptg_title format a7 WRAPPED
select *
from painting;
column ptg_title format a7 WOR
/
-- you can also left and right and center justify --- the HEADING?!
-- JUS L
-- JUS R
-- JUS C
-- (not the column contents, darn it --- JUST the heading...!
column empl_last_name heading "Employee|Last Name" format a16
/
column empl_last_name heading "Employee|Last Name" format a16 JUS L
/
column empl_last_name heading "Employee|Last Name" format a16 JUS R
/
column empl_last_name heading "Employee|Last Name" format a16 JUS C
/
-- can format so a column is wide enough for its entire heading...
-- (yes, if do not want heading or format, just don't put that clause)
column empl_num format a8
-- use format A (alphanumeric) for dates as well
column hiredate format a15
/
-- LAB EXERCISE problem 1
-- * write a prompt that this is problem 1
-- * give job_title's column a noticeably different heading and format
-- using the COLUMN command
-- * then project the contents of the empl table (at least project
-- the job_title column...!)
--*****
-- some numeric formatting examples
-- let's play with formatting salaries...
column salary heading 'Salary' format 99999
select empl_last_name, dept_num, salary
from empl;
-- make employee name heading back to left-justified:
-- (HAD to put JUS L to get left justified again --- why?)
column empl_last_name heading "Employee|Last Name" format a16 JUS L
-- let's make dept_num look nicer
column dept_num heading 'Dept#' format a5
-- note that dept_num in ANY table will be formatted using this, now
select *
from empl;
select *
from dept;
-- let's make it too narrow:
column salary heading 'Salary' format 99
/
-- ...or if you try to format a numeric column with A
column salary heading 'Salary' format a6
/
-- let's make it better:
column salary heading 'Salary' format 99999
/
-- let's add fractional parts..
-- (more fractional places? it rounds...)
column salary heading 'Salary' format 99999.99
/
-- do you want the comma?
column salary heading 'Salary' format 99,999.99
/
-- and a dollar sign, since this is money
column salary heading 'Salary' format $99,999.99
/
-- make first digit in format a 0 to get leading zeros
column salary heading 'Salary' format $09,999.99
/
-- no 0 for values less than 1, more than or equal to 0, this way:
column commission heading 'Commission' format $9,999.99
select empl_last_name, commission
from empl
where job_title = 'Salesman';
-- this'll get you that 0:
column commission heading 'Commission' format $9,990.99
/
column commission heading 'Commission' format $0,000.00
/
-- INSERT CLICKER QUESTION 2 HERE
-- I can give one column the same format as another using "like"
column salary heading 'Salary' format $99,990.99
column commission like salary heading 'Commission'
select empl_last_name, salary, commission
from empl
where job_title = 'Salesman';
--*************************************************---
-- PLEASE NOTE: a situation where a view can be quite convenient;
-- what if you have a computation whose results you would
-- like to format?
select dept_name, avg(salary)
from empl e, dept d
where e.dept_num = d.dept_num
group by dept_name;
-- Ugh! you'd like to FORMAT that avg(salary) column ---
-- UH OH --- but how?
-- actually, this WILL work:
column avg(salary) heading "Dept Avg" format $99,999.99
/
-- of course, it will do this column command for EVERY instance
-- of avg(salary)...
select job_title, avg(salary)
from empl
group by job_title;
-- BUT --- if you make this into a VIEW, you then GIVE it a
-- column name! A more particular place to "hang" a column
-- command...
drop view dept_avgs;
create view dept_avgs(dept_name, dept_avg) as
select dept_name, avg(salary)
from empl e, dept d
where e.dept_num = d.dept_num
group by dept_name;
-- NOW you have a more appropriate name for that column of
-- *department* averages:
column dept_avg heading "Dept Avg" format $99,999.99
column dept_name heading "Dept Name"
-- check out how much better these look!
select *
from dept_avgs
order by dept_name;
select *
from dept_avgs
order by dept_avg desc;
-- LAB EXERCISE problem 2
-- * write a prompt that this is problem 2
-- * give empl's salary column a noticeably-different heading and
-- numeric format
-- from its default, using the column command
-- * then project the empl table's contents (or at least the
-- salary column)
--************************************************************---
-- BREAK command
-- do NOT confuse with GROUP BY clause!
-- it is used in conjunction with ORDER BY clause to get
-- "prettier" ordered table displays
--************************************************************---
-- note how this looks...
select dept_num, empl_last_name, salary
from empl
order by dept_num;
column dept_num heading 'Dept' format a4
/
-- this BREAK causes only the "first" dept_num in a "row" to
-- display;
break on empl on dept_num
/
-- I can get blank lines between each broken-into section:
break on empl on dept_num skip 1
/
column mgr heading Mgr
select dept_num, mgr, empl_last_name, salary
from empl
order by dept_num, mgr;
-- INSERT CLICKER QUESTION 3 HERE
-- can have the break effect on more than one column at a time ---
-- BUT only 1 break command can be in effect at one time, so
-- put ALL the columns you want to break on in a single break command
break on empl on dept_num on mgr skip 1
/
-- and to NOT get the skip after each manager? (thanks to C. McLain)
break on empl on dept_num skip 1 on mgr
/
-- no, this one ISN'T pretty...
break on empl on dept_num skip 1 on mgr skip 2
/
-- REMEMBER: need to ORDER BY the column(s) you are breaking on,
-- or WON'T LOOK GOOD...
--*****
-- remember: a SQL*Plus command is supposed to be only on ONE line;
--
-- IF a SQL*Plus command like break is getting too long, however,
-- you can CONTINUE to the next line (ask sqlplus to pretend it
-- isn't a new line yet) by using a - at the end of the line:
break on empl on dept_num -
on mgr skip 3
/
-- LAB EXERCISE problem 3
-- * write a prompt that this is problem 3
-- * write a break command to break on column job_title
-- * now write a query to project the rows of the empl table,
-- ordered by job_title
--**************************************************************---
-- COMPUTE will let you make computations on those broken-into sections
-- (this works in CONJUNCTION with a break!!)
--**************************************************************---
break on empl on dept_num skip 1 on mgr
compute avg min max of salary on dept_num
/
-- std for standard deviation
-- number for number of rows even counting those with nulls
-- variance computes the variance of the values in the columns
-- 'compute' will show you your current compute definition
compute
-- and 'break' will show you your current break definition
break
-- (NOTE that each break, CLEARS/replaces the previous
-- break, compute --- can have more than one compute at a
-- time, however (although not on the same column --- if
-- that's the case, newer one REPLACES the old)
compute count of empl_last_name on dept_num
/
-- TWO computes in effect now:
compute
-- does this one replace earlier?
compute count of salary on dept_num
/
-- yes!
compute
--------------------------------------------------------------------
-- to customize how your compute results are labeled:
--
-- label option for compute command: (compliments of Mr. Serrano)
--
column dept_num format a5
break on dept_num skip 1
compute sum label 'total' of salary on dept_num
--
select dept_num, empl_last_name, salary
from empl
order by dept_num;
--------------------------------------------------------------------
--------------------------------------------------------------------
-- to get a "grand" (overall) computation:
-- (compliments of L. Holden)
--
-- "Breaking and computing "on report" provides a grand total for
-- an entire report.... See code below, it computes a total of
-- employees by department and a grand total of all employees:"
break on dept_num skip 1 on REPORT
compute count of empl_num on dept_num
compute count label Total of empl_num on REPORT
column dept_num format a7
column empl_num format a7
set pagesize 53
select dept_num, empl_num
from empl
order by dept_num;
--**********************************************---
-- title-related commands: ttitle, btitle
-----
show ttitle
show btitle
-- want a TITLE aTOP each page? ttitle
ttitle 'Beautiful|Three Line|Top Title'
-- want a BOTTOM title? btitle
btitle 'Gorgeous Two-line|Bottom Title'
/
spool off
--*****************************************************---
-- quick slightly-flaky flat file example:
--*****************************************************---
-- aha! space is # of spaces BETWEEN columns; default is 1
set space 0
-- this is also in try-flat.sql -- it seemed to work, in test-trial
-- 11-17-10
-- (DOESN'T work as well PASTED into sqlldr -- DOES work better run
-- as a SQL SCRIPT!)
-- (pasted, you get the SQL command echoed into your output...
-- run as a script, you don't, in that 11-17-10 trial;)
set newpage 0
set linesize 80
set pagesize 0
set echo off
set feedback off
set heading off
set space 0
spool flat-empl-data.txt
select empl_last_name || ',' || salary
from empl;
-- don't forget to spool off, or results file may be empty or
-- incomplete;
spool off
-- see end of script for what cleanup script does...
@ cleanup
spool lab13-results-part2.txt
-- don't forget a string operation we've already seen: concatenation!
select empl_last_name || '_' || salary
from empl;
select empl_last_name || ', $' || salary "Pay Info"
from empl
order by empl_last_name;
-- in a report, canny concatenation can lead to nicer results...
/* for a phone directory report
select last_name || ', ' || first_name "Name", extension "Extension"
from ...
where ...
order by last_name;
*/
/* maybe for an attendees list report
select first_name || ' ' || last_name "Attendees"
from ...
where ...
order by last_name;
*/
/* MANY combinations/concatenations of city, state, zip are possible!
select city || ', ' || state || ' ' || zip
from ...
where ...
order by ...;
select zip || '-' || city
from ...
where ...
order by zip;
select state ||': ' || city
from ...
where ...
order by state, city;
*/
-- how about some date-related functions?
-- one you know about: sysdate!
select sysdate
from dual;
-- to_char: expects a date and a format, and it returns a character string
-- equivalent to the date based on the format
-- example 1: show JUST the month (format string 'MONTH' shows the entire
-- month name
select to_char(sysdate, 'MONTH')
from dual;
select to_char(hiredate, 'MONTH')
from empl;
-- string function initcap expects a string, and returns a string
-- with an initial uppercase letter
-- 'YYYY': the full 4-digit year
column "Month of Hiring" format a15
select initcap(empl_last_name) "Employee",
initcap( to_char(hiredate, 'MONTH YYYY') ) "Month of Hiring"
from empl
order by hiredate;
-- some more options:
-- 'MM' - month number
-- 'MON' - the first 3 letters of the month name
-- 'MONTH' - the entire month
-- 'DAY' - fully spelled out day of the week
-- 'DY' - 3-letter abbreviation of the day of the week
select initcap( to_char(hiredate, 'DAY') )
from empl;
-- these can be combined carefully with literals:
select initcap( to_char(hiredate, 'DAY, MONTH YY') ) "Hiredate"
from empl;
-- 'D' - number of date's day in the current week (Sunday is 1)
-- 'DD' - number of date's day in the current month
-- 'DDD' - number of date's day in the current year
-- 'HH12' - hours of the day (1-12)
-- 'HH24' - hours of the day (0-23)
-- 'MI' - minutes of the hour
-- 'SS' - seconds of the minute
-- 'AM' - displays AM or PM depending on the time
select to_char( sysdate, 'D DD DDD HH12 HH24 MI SS AM') "UGLY"
from dual;
-- a few more string functions
-- lower - all-lowercase version of your string
-- upper - all-uppercase version of the string
-- lpad, rpad -
-- lpad(string, length, ['char']) -
-- returns a string that is the input <string> padded on the left with
-- the <chars> until the length of the string reaches <length>
--
-- rpad: the same thing, on the right
select lpad(empl_last_name, 12, '.')
from empl;
select rpad(empl_last_name, 12, ' ')
from empl;
select lpad(initcap(to_char(hiredate, 'DAY')), 14, ' ') ||
initcap(to_char(hiredate, ', MONTH YY') ) "Hiredate"
from empl;
-- ltrim, rtrim let you trim characters from the left or right of
-- a string
-- length: returns the length of the string
-- substr - to grab a substring of a string
select initcap('SILLY') looky
from dual;
select lower(empl_last_name), upper(empl_last_name)
from empl
where job_title = 'President';
select lpad(empl_last_name, 12, '.') dots,
rpad(empl_last_name, 15, '?!') huh,
lpad(empl_last_name, 12, ' ') maybe_right_justifd
from empl;
select ltrim(' Hi ') lftchop,
rtrim(' Hi ') rtchop,
rtrim( to_char(sysdate, 'Day')) || ', ' ||
rtrim(to_char(sysdate, 'Month')) ||
' ' || to_char(sysdate, 'DD, YYYY') nicer
from dual;
select ltrim(' Hi ') lftchop,
rtrim(' Hi ') rtchop,
rtrim( to_char(hiredate, 'Day')) || ', ' ||
rtrim(to_char(hiredate, 'Month')) ||
' ' || to_char(hiredate, 'DD, YYYY') nicer
from empl;
select empl_last_name,
length(empl_last_name) length,
substr(empl_last_name, 1, 3) abb,
substr(empl_last_name, 3) rest
from empl;
-- there are some conversion functions, too:
-- to_char can also be used to convert a number to a string
-- to_number takes a character string and IF that character string
-- is reasonable, turns it into a number
-- to_date converts a number or a string into a date
select to_number('0001') + 3
from dual;
drop table playdate;
create table playdate
(play_id int,
play_date_time date,
primary key (play_id)
);
insert into playdate
values
(1, to_date('01-Jan-2009 21:30', 'dd-mon-yyyy hh24:mi'));
insert into playdate
values
(2, to_date('22:29', 'hh24:mi'));
select play_id, to_char(play_date_time, 'hh12:mi')
from playdate;
-- and there are more date-related functions
-- +, - : you can add days to dates
select sysdate + 1
from dual;
select to_date('31-DEC-08') + 1
from dual;
select to_date('28-Feb-08') + 1
from dual;
select to_date('28-Feb-09') + 1
from dual;
-- next_day(d, day) a date d, and a string day rep the day of the
-- week, and returns the date of the next date after d that has
-- that day of the week
select next_day(sysdate, 'TUESDAY')
from dual;
select next_day(sysdate, 'WEDNESDAY')
from dual;
-- least, greatest, add_months (to make a date m months of the given date),
-- months_between (returns the number of months between two dates)
-- and more...
select add_months('30-Jan-09', 1) one_month_later,
months_between('15-Apr-09', '15-Jan-09') diff1,
months_between('15-Apr-09', '01-Jun-09') diff2
from dual;
spool off
--******************************************************---
-- AT THE END OF A REPORT SCRIPT, YOU *SHOULD*****
-- clean up when done (so as to not shock user with their
-- next query)
-- better to put the below lines into another cleanup
-- script you can call frequently! (thanks to T. Koyuncu)
-- @ cleanup
clear breaks
clear columns
clear computes
set space 1
set feedback 6
set pagesize 14
set linesize 80
set newpage 1
set heading on
-- to turn off titles set!
ttitle off
btitle off
-- end of 315lab13-projected.sql