Oracle SQL Tips and Tricks for IR Analysis and Reporting
MingguangXuand Denise Gardner
Office of Institutional Research
University of Georgia
www.uga.edu/oir


Codes For SAIR 07 Presentation 
 
-- Prblem statement: find out the employee number by department and job_title and change the way of data to be displayed. 
--  You use group by to calculate the employee number 
select department_id,job_title,count(*) from emp 
group by department_id,job_title  
order by department_id 
 
-- But now, you want to show departmant_id horizontally, i.e, to make each department_id as a column. You want to transposing 
-- the result set from the query. That is why data transposition is also called pivot query 
select job_title, 
decode(department_id,10,cnt,null) dept10, -- dept10 is a new variable for Department_id =10 
decode(department_id,20,cnt,null) dept20, 
decode(department_id,30,cnt,null) dept30, 
decode(department_id,40,cnt,null) dept40 
from 
( 
select department_id,job_title,count(*) cnt from emp   
group by department_id,job_title 
order by department_id 
) 
 
-- * steps to solve this problem 
-- Step 1. generate a variable for each department_id) 
-- so first find out how many department_id  
 
select distinct department_id from emp 
 
-- And then, generate new variables using decode function 
select job_title, 
decode(department_id,10,cnt,null) dep1, -- dept10 is a new variable for Department_id =10 
decode(department_id,20,cnt,null) dept20, 
decode(department_id,30,cnt,null) dept30, 
decode(department_id,40,cnt,null) dept40 
 
-- step 2. treat the original query as subquery 
select department_id,job_title,count(*) cnt from emp  
group by department_id,job_title  
order by department_id 
 
-- step 3. put the two parts together 
select job_title, 
decode(department_id,10,cnt,null) dept10, -- dept10 is a new variable for Department_id =10 
decode(department_id,20,cnt,null) dept20, 
decode(department_id,30,cnt,null) dept30, 
decode(department_id,40,cnt,null) dept40 
from 
( 
select department_id,job_title,count(*) cnt from emp   
group by department_id,job_title  
order by department_id 
) 
 
-- The cnt has been pivoted by departt_id accross job_title 
 
-- This approach calculates the total first and then pivot the query, another approach is to pivot query first, and then calculate the total 
 
select job_title, 
decode(department_id,10,1,0)dept10, -- dept10 is a new variable for Department_id =10 
decode(department_id,20,1,0) dept20, 
decode(department_id,30,1,0) dept30, 
decode(department_id,40,1,0) dept40 
from emp 
 
 
-- take the above query as subquery and calculate the total 
select job_title,sum(dept10) d10 
,sum(dept20) d20 
,sum(dept30) d30 
,sum(dept40) d40 
from 
( 
select job_title, 
decode(department_id,10,1,null)dept10, -- dept10 is a new variable for Department_id =10 
decode(department_id,20,1,null) dept20, 
decode(department_id,30,1,null) dept30, 
decode(department_id,40,1,null) dept40 
from emp 
) 
group by job_title 
 
-- summary on decode: 
-- starts by specifiying a column name and then followed by set of matched-pairs of transformation values. 
 
--- Use case statement to pivot query. From Oracle9i, case statement can be used. 
select job_title, 
case department_id when 10 then cnt else null end dept10, 
case department_id when 20 then cnt else null end dept20, 
case department_id when 30 then cnt else null end dept30, 
case department_id when 40 then cnt else null end dept40 
from 
( 
select department_id,job_title,count(*) cnt from emp   
group by department_id,job_title  
order by department_id 
) 
order by job_title 
 
-- You have another way to write the case statement 
select job_title, 
case when department_id = 10 then cnt else null end dept10, 
case when department_id = 20 then cnt else null end dept20, 
case when department_id = 30 then cnt else null end dept30, 
case when department_id = 40 then cnt else null end dept40 
from 
( 
select department_id,job_title,count(*) cnt from emp  
group by department_id,job_title  
order by department_id 
) 
order by job_title 
 
-- difference between 'case variable when' and 'case when variable'' 
-- if the condition only needs one variable, you can use either one. If the condition involves two or more variables, you have to use''case when Variable '' 
-- for example, if you need to calculate the employee number by department and job title for employees making certain salary or above 
select job_title, 
sum(dept10) d10 
,sum(dept20) d20 
,sum(dept30) d30 
,sum(dept40) d40 
from 
( 
select job_title, 
case when department_id = 10 and salary>=10000 then 1 else null end dept10, 
case when department_id = 20 and salary>=5000 then 1 else null end dept20, 
case when department_id = 30 and salary>=7000 then 1 else null end dept30, 
case when department_id = 40 and salary>=9000 then 1 else null end dept40 
from emp 
) 
group by job_title 
 
-- to pivot query to convert column to row 
select decode(rn,1, to_char(department_id),2,job_title,3,cnt) detail 
from 
( 
select department_id,job_title,count(*) cnt from emp  
where department_id >=10 and department_id<50   
group by department_id,job_title  
), 
( 
select rownum rn from emp where rownum<4 
) order by department_id,job_title,rn 
-- Problem statement: how to format date from a query; how to do operations on date; how to use oracle date functions 
 
-- frist create a table containing todays date 
 
drop table dt 
 
create table dt as select sysdate dt from dual 
 
-- date format 
 
-- before go to the date format, first look at how oracle store date data type internally 
-- date is stored in seven parts: cc, yr, mon, day, hour, min, and seconds 
select dump(dt) from dt 
 
-- if you don't do any formatting, the date will be displayed as: 
select * from dt 
 
-- but now you want to change the format of the date 
 
-- 1. two digit day, short month anme, 4 digit year, no time 
select to_char(dt,'dd*MON/yyyy') from dt 
 
-- 2. short day name(in a week), two digit day, short month name, 2 digit year, no time 
select to_char(dt,'DY dd mon yy') from dt 
 
-- 3. full day name, two digit day, full munth, 4 digit year, no time 
select to_char(dt,'day dd month yyyy') from dt 
 
-- 4. two digit day, 2 digit month, 4 digit year, no time 
select to_char(dt,'dd mm yyyy') from dt 
 
-- 5. time in 12 hour mode 
select to_char(dt,'hh12:mi:ss am') from dt 
 
-- 6. time in 24 hour mode 
select to_char(dt,'hh24:mi:ss') from dt 
 
-- 7. day of the week 
select to_char(dt,'d') from dt 
 
select to_char(dt,'dy') from dt 
 
select to_char(dt,'day') from dt 
 
-- 8. day of the year 
select to_char(dt,'ddd') from dt 
 
-- 9. week of the year 
select to_char(dt,'ww') from dt 
 
-- 10. Century 
select to_char(dt,'cc') from dt 
 
--- **** Date arithmetic and date functions *** 
 
-- 1. subtract 5 days 
select dt-5 from dt 
 
--- 2. subtract 3 hours 
select dt-3/24 from dt 
 
-- 3. subtract 10 minutes, convert to day 
select dt-10/1440 from dt 
 
-- 4. 30 seconds 
select dt-30/86400 from dt 
 
-- *** how many days between two dates  
--1. the fractional portion of the result is the elasped seconds since midnight divided by the total seconds per day (86400) 
 
select dt- to_date('09-01-07','mm-dd-yy') from dt 
 
-- 2. you cannot add two days together 
select dt +to_date('09-01-07','mm-dd-yy') from dt 
 
--**** Date Functions 
 
-- 1. find out the last day of the month 
select last_day(to_date('09-01-07','mm-dd-yy')) from dt 
 
-- 1.1 do we need first_day function? 
 
-- 2. subtract 1 month 
select add_months(dt,-1) from dt 
 
-- 3. what is the date next to Tuesday 
select next_day(dt,'Tuesday') from dt 
 
-- 4. months_between return the number of months between two dates. fractional portion is based on a 31 day month. 
select months_between(dt,dt+100) from dt 
 
-- **** how to convert string to date, use to_date function and format mask. 
-- 1. date stored as a string in 4 digits year, Full month name, and 2 digits day 
select to_date('2005July04','yyyymonthdd') from dt 
 
-- 2. other storage format 
select to_date('2005/07/04','yyyy/mm/dd') from dt 
 
select to_date('2005-07-04','yyyy-mm-dd') from dt 
 
select to_date('2005Jul04','yyyymondd') from dt 
-- Problem statement: how to calculate sub-total and cross-tab. 
 
select department_id,job_title,count(1) emplNum, sum(salary) totalSalary from emp  
group by department_id,job_title 
order by department_id,job_title 
 
-- if you want to calculate the subtotal for each department over over job_title 
select department_id,job_title,count(1) emplNum, sum(salary) totalSalary from emp   
group by department_id,rollup(job_title) 
order by department_id,job_title 
 
-- if you want to calculate the subtotal for each department and a grant total -- not give you the subtotal for each job_title over department 
select department_id,job_title,count(1) emplNum, sum(salary) totalSalary from emp   
group by rollup(department_id,job_title) 
order by department_id,job_title 
 
-- cross tabular report - i.e. all possible higher level totals are calculated. 
select department_id,job_title,count(1) emplNum, sum(salary) totalSalary from emp   
group by cube(department_id,job_title) 
order by department_id,job_title 
 
-- ** handling null vaule 
-- first approach, using grouping function 
select department_id,job_title,count(1) emplNum, sum(salary) totalSalary,grouping(department_id),grouping(job_title) from emp  
group by cube(department_id,job_title) 
order by department_id,job_title 
 
-- second approach is to use nvl function 
-- the first approach can identify the null generated by rollup or cube, but will create an additional column 
select nvl(to_char(department_id),'jobSubTotal'),nvl(job_title,'deptSubTotal'),count(1) emplNum, sum(salary) totalSalary from emp   
group by cube(department_id,job_title) 
order by department_id,job_title 
 
select nvl(null,'x') from dual