Please send questions to
st10@humboldt.edu .
-----
-- CIS 180 - Intro to SQL
-- Class 9 - 4-4-03
-----
-- last modified: 4-4-03, post-class
-- clear columns, breaks, and computes before I begin
clear col
clear break
clear compute
spool 180class9_results.txt
-- HW #8, number 7...
(select movie_title, count(*) "# Times Rented"
from movie m, video v, rental r
where m.movie_num = v.movie_num
and v.vid_id = r.vid_id
group by movie_title)
union
(select movie_title, 0
from movie m
where not exists
(select 'a'
from video v, rental r
where v.vid_id = r.vid_id
and v.movie_num = m.movie_num))
order by "# Times Rented" desc, movie_title;
-- setting up a clean set of empl, dept, customer tables
@ set_up_ex_tbls
-----
-- STRING CONCATENATION - ||
-----
-- || makes two strings into one...
select cust_lname || ', ' || cust_fname "Customer",
cust_city || ', ' || cust_state || ' ' || cust_zip "Location"
from customer
order by cust_lname;
--*******************************************
-- REPORT SCRIPT/SQL*PLUS SETTINGS ADVICE
--*******************************************
-- 1. Your script should explicitly make any SQL*Plus settings
-- that you want for that script's execution
-- 2. Your script should explicitly RESET any made back to their
-- DEFAULT values at the END of that script, to avoid disrupting
-- the behavior of other SQL scripts or of whatever the user
-- may choose to do next after running your script!
-----
-- reminder about /
-----
-- remember that / causes the last SQL (not SQL*Plus!) command to be
-- re-run --- convenient when you are tweaking formatting with SQL*Plus!
-----
-- TTITLE, BTITLE
-----
-- commands for setting top (ttitle) and bottom (btitle) titles on a page
-- (these have numerous additional options --- see Oracle documentation!)
-- TO TURN THEM OFF:
-- ttitle off
-- btitle off
-- (and these HAVE been added to END of this script!)
-- give them a SINGLE string, and use | inside that string whenever you
-- want a line break within the title:
ttitle 'My Beautiful|Four Line|Title|of Glory'
btitle ' |My Quaint|Bottom Title'
-- when will you see this? at the top and bottom of each "page";
-- page's size? Can be reset using pagesize (discussed below)
-- and note that each query's result's start on a new "page"
-- let's look at our previous query with these top and bottom titles:
/
-- you can see what the current setting of ttitle and btitle are
-- by simply typing:
ttitle
btitle
-----
-- 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
/
-----
-- COLUMN (COL)
-----
-- (put CLEAR COL) at beginning of script!)
-- 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
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 a10
/
-- but, you MUST have quotes if a heading has a space!
-- (this fails...)
prompt FAILS --- need QUOTES if a column heading contains a space:
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 a13
/
-- 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 empl(empl_num, empl_last_name, job_title, mgr, hiredate, salary,
dept_num)
values
('9999', 'de la Garza', 'Manager', '7839', sysdate, 2800, '500');
clear col
column empl_last_name format a8
select *
from empl
where empl_num = '9999';
column empl_last_name format a8 WOR
/
-- 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
-- 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;
-- let's make dept_num and empl last name look nicer
column dept_num heading 'Dept#' format a5
column empl_last_name heading 'Employee|Last Name' format a10
-- 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
/
-- 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
/
-- 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';
--*****
-- HANDY TIP
--*****
-- it can be useful to make a query with computed columns into
-- a view, to give the computation a name suitable for use in
-- a column command;
-- consider:
select dept_name, avg(salary)
from dept d, empl e
where e.dept_num = d.dept_num
group by dept_name;
-- ?! Oddly enough, this works:
column avg(salary) heading dept_avg format $9999.99
/
-- ...but EVERY avg(salary) then gets the same treatment...
select avg(salary)
from empl;
-- ...which is a trifle odd, if you forget about the column
-- command.
-- here's an alternative:
drop view avg_dept_salary;
create view avg_dept_salary(dept_name, avg_salary) as
select dept_name, avg(salary)
from dept d, empl e
where e.dept_num = d.dept_num
group by dept_name;
column avg_salary heading 'Avg Salary' format $9999.99
column dept_name heading 'Dept Name' format a10
select *
from avg_dept_salary
order by avg_salary desc;
-----
-- 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;
-- 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
/
-- NOTE that a break must be on ONE line, but I can continue
-- a line by ending it with a single -
break on empl on dept_num -
skip 3
-----
-- 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 sum of salary on dept_num
/
compute avg min max of salary on dept_num
/
break on empl on dept_num skip 1
compute count of empl_last_name on dept_num
/
-- see how can have MORE than one compute at a time?
clear compute
compute count of empl_last_name on dept_num
/
-- '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 --- CAN have more than one compute in effect at once, however)
--------------------------------------------------------------------
-- 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;
--------------------------------------------------------------------
-----
-- 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 4 rows were selected.
select *
from empl
where job_title = 'Manager';
-- this will not note that one row was:
select *
from empl
where commission = 0;
-- this will simply turn feedback OFF
set feedback off
-----
-- 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...)
-----
-- REMINDER: prompt: print a message to the screen (or to a file, of course,
-- if spooling
-----
prompt Hello, there, here is a message
-----
-- (you already have echo set off by default, which is what you want
-- in a report!) (BUT to make SURE it is off when a user RUNS your script,
-- SHOULD explicitly include this anyway!)
-----
set echo off
spool off
-----
-- quick flat file example:
-----
-- aha! space is # of spaces BETWEEN columns; default is 1
set space 0
set newpage 0
set linesize 80
set pagesize 0
set echo off
set feedback off
-- you can say no column headings, too!
set heading off
spool flat_empl_data.txt
select empl_last_name || ',' || salary
from empl;
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)
clear col
clear break
clear compute
set pagesize 14
set space 1
set feedback 6
set linesize 80
set newpage 1
set heading on
-- to turn off titles set!
ttitle off
btitle off