April 2015 "Fun with SQL" fourth Challenge

April 2015 “Fun with SQL” fourth Challenge

This month on the Content Committee blog we’ll be doing an online version of the “Fun with SQL” dojo. Each week we’ll present an SQL challenge and request the community to solve it. People can respond to the challenge in the comments section of the blog post, or they can email Kurt directly at kurt.struyf@lone-star.be with a solution or idea and he will post it to the blog. You can post a possible answer, ask a question, and comment on something someone else has posted, or offer alternatives to something that’s been posted. Don’t be shy about posting your answer if someone else has already posted. At the end of the month, the content committee will take the names of the people that have participated and randomly select one to receive a reward of an IPad case, courtesy of IBM! So, don’t be shy, just dive in and share your experience or inexperience and get a free and fun education!

Here is the fourth SQL challenge and is again  based upon the DB2 sample database.

write a query that gives on overview of the hierarchal structure of the departments and the name and job description of the managers

remember to have fun and share those results

4 Comments

Sketch for a solution

April 28, 2015 07:47 AM by Lennart Jonsson

Probably easiest to build a materialized path and use that for traversal of the tree. Since all deptno's have the same "width" I did not bother padding them to the same length before concatenating. If the deptno vary in length thsis is important since it will otherwise destroy the sort order. Anyhow, here's a sketch:

ltjn@ltjn-VirtualBox:~/Documents$ db2 "with tree (ADMRDEPT, deptno, depth) as (
    select cast(rtrim(ADMRDEPT) || '.' as varchar(20)),deptno,0
    from department
    where DEPTNO=ADMRDEPT
    union all
    select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno, depth+1
    from tree t, department d
    where d.ADMRDEPT = t.deptno
      and d.ADMRDEPT <> d.deptno and depth < 10
)
select cast(repeat(' ',4*depth) || deptno as varchar(20))
from tree order by ADMRDEPT"

1                  
--------------------
A00                
    B01            
    C01            
    D01            
        D11        
        D21        
    E01            
        E11        
        E21        
        F22        
        G22        
        H22        
        I22        
        J22        

  14 record(s) selected.

 

missing info

April 28, 2015 08:31 AM by Lennart Jonsson

Occationally it helps to read the whole description before starting ... ;-) This is a bit clumsy, but the manager is the longest path that corresponds with the current path and where the manager is not null:

ltjn@ltjn-VirtualBox:~/Documents$ db2 "with tree (ADMRDEPT, deptno, MGRNO, depth) as (
>     select cast(rtrim(ADMRDEPT) || '.' as varchar(20)),deptno,MGRNO,0
>     from department
>     where DEPTNO=ADMRDEPT
>     union all
>     select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno, d.MGRNO, depth+1
>     from tree t, department d
>     where d.ADMRDEPT = t.deptno
>       and d.ADMRDEPT <> d.deptno and depth < 10
> )
> select cast(repeat(' ',4*t1.depth) || t1.deptno as varchar(20))
>      , e.empno, e.FIRSTNME, e.job
> from tree t1
> join employee e
>     on e.empno = coalesce(t1.MGRNO,
>           ( select t3.MGRNO
>             from tree t3
>             where t3.ADMRDEPT = (select max(t2.ADMRDEPT)
>                                  from tree t2
>                                  where t1.ADMRDEPT like t2.ADMRDEPT || '%'
>                                    and t2.MGRNO is not null)
>           ))
> order by ADMRDEPT"

1                    EMPNO  FIRSTNME     JOB    
-------------------- ------ ------------ --------
A00                  000010 CHRISTINE    PRES   
    B01              000020 MICHAEL      MANAGER
    C01              000030 SALLY        MANAGER
    D01              000010 CHRISTINE    PRES   
        D11          000060 IRVING       MANAGER
        D21          000070 EVA          MANAGER
    E01              000050 JOHN         MANAGER
        E11          000090 EILEEN       MANAGER
        E21          000100 THEODORE     MANAGER
        F22          000050 JOHN         MANAGER
        G22          000050 JOHN         MANAGER
        H22          000050 JOHN         MANAGER
        I22          000050 JOHN         MANAGER
        J22          000050 JOHN         MANAGER

  14 record(s) selected.

 

Simpler one

April 28, 2015 08:43 AM by Lennart Jonsson

If the current manager is null we keep the parents manager:

ltjn@ltjn-VirtualBox:~/Documents$ db2 "with tree (ADMRDEPT, deptno, MGRNO, depth) as (
>     select cast(rtrim(ADMRDEPT) || '.' as varchar(20)),deptno,MGRNO,0
>     from department
>     where DEPTNO=ADMRDEPT
>     union all
>     select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno
>          , coalesce(d.MGRNO,t.MGRNO), depth+1
>     from tree t, department d
>     where d.ADMRDEPT = t.deptno
>       and d.ADMRDEPT <> d.deptno and depth < 10
> )
> select cast(repeat(' ',4*t1.depth) || t1.deptno as varchar(20))
>      , e.empno, e.FIRSTNME, e.job
> from tree t1
> join employee e
>     on t1.MGRNO = e.empno
> order by ADMRDEPT"

1                    EMPNO  FIRSTNME     JOB    
-------------------- ------ ------------ --------
A00                  000010 CHRISTINE    PRES   
    B01              000020 MICHAEL      MANAGER
    C01              000030 SALLY        MANAGER
    D01              000010 CHRISTINE    PRES   
        D11          000060 IRVING       MANAGER
        D21          000070 EVA          MANAGER
    E01              000050 JOHN         MANAGER
        E11          000090 EILEEN       MANAGER
        E21          000100 THEODORE     MANAGER
        F22          000050 JOHN         MANAGER
        G22          000050 JOHN         MANAGER
        H22          000050 JOHN         MANAGER
        I22          000050 JOHN         MANAGER
        J22          000050 JOHN         MANAGER

  14 record(s) selected.

 

End of pollution of the thread on my behalf ;-)

 

connect by for recursion

April 28, 2015 09:46 PM by Steve La

A solution using the connect by clause and its supporting built-in functions

db2 => !db2set;
DB2_COMPATIBILITY_VECTOR=08

db2 => with v as ( select d.deptname, mgrno, cast(sys_connect_by_path(d.deptname,'|') as varchar(80)) dept_chain
            from department d start with d.deptno=d.admrdept connect by nocycle prior d.deptno = d.admrdept )
select v.deptname, e.firstnme, e.lastname, e.job, v.dept_chain
from v left outer join employee e on ( v.mgrno=e.empno);

db2 (cont.) => db2 (cont.) => db2 (cont.) =>
DEPTNAME                             FIRSTNME     LASTNAME        JOB      DEPT_CHAIN                                                                     
------------------------------------ ------------ --------------- -------- --------------------------------------------------------------------------------
SPIFFY COMPUTER SERVICE DIV.         CHRISTINE    HAAS            PRES     |SPIFFY COMPUTER SERVICE DIV.                                                  
PLANNING                             MICHAEL      THOMPSON        MANAGER  |SPIFFY COMPUTER SERVICE DIV.|PLANNING                                         
INFORMATION CENTER                   SALLY        KWAN            MANAGER  |SPIFFY COMPUTER SERVICE DIV.|INFORMATION CENTER                               
DEVELOPMENT CENTER                   -            -               -        |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER                               
MANUFACTURING SYSTEMS                IRVING       STERN           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER|MANUFACTURING SYSTEMS         
ADMINISTRATION SYSTEMS               EVA          PULASKI         MANAGER  |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER|ADMINISTRATION SYSTEMS        
SUPPORT SERVICES                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES                                 
OPERATIONS                           EILEEN       HENDERSON       MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|OPERATIONS                      
SOFTWARE SUPPORT                     THEODORE     SPENSER         MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|SOFTWARE SUPPORT                
BRANCH OFFICE F2                     -            -               -        |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE F2                
BRANCH OFFICE G2                     -            -               -        |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE G2                
BRANCH OFFICE H2                     -            -               -        |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE H2                
BRANCH OFFICE I2                     -            -               -        |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE I2                
BRANCH OFFICE J2                     -            -               -        |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE J2                

  14 record(s) selected.

with v as (
select d.deptname, d.mgrno, cast(sys_connect_by_path(d.deptname,'|') as varchar(80)) dept_chain,
   cast(sys_connect_by_path(coalesce(d.mgrno, ''),'') as varchar(80)) mgr_chain
from department d start with d.deptno=d.admrdept connect by nocycle prior d.deptno = d.admrdept )
select v.deptname, e.firstnme, e.lastname, e.job, v.dept_chain
from v inner join employee e on (  right(v.mgr_chain,6)  = e.empno);
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) =>
DEPTNAME                             FIRSTNME     LASTNAME        JOB      DEPT_CHAIN                                                                     
------------------------------------ ------------ --------------- -------- --------------------------------------------------------------------------------
SPIFFY COMPUTER SERVICE DIV.         CHRISTINE    HAAS            PRES     |SPIFFY COMPUTER SERVICE DIV.                                                  
PLANNING                             MICHAEL      THOMPSON        MANAGER  |SPIFFY COMPUTER SERVICE DIV.|PLANNING                                         
INFORMATION CENTER                   SALLY        KWAN            MANAGER  |SPIFFY COMPUTER SERVICE DIV.|INFORMATION CENTER                               
DEVELOPMENT CENTER                   CHRISTINE    HAAS            PRES     |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER                               
MANUFACTURING SYSTEMS                IRVING       STERN           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER|MANUFACTURING SYSTEMS         
ADMINISTRATION SYSTEMS               EVA          PULASKI         MANAGER  |SPIFFY COMPUTER SERVICE DIV.|DEVELOPMENT CENTER|ADMINISTRATION SYSTEMS        
SUPPORT SERVICES                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES                                 
OPERATIONS                           EILEEN       HENDERSON       MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|OPERATIONS                      
SOFTWARE SUPPORT                     THEODORE     SPENSER         MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|SOFTWARE SUPPORT                
BRANCH OFFICE F2                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE F2                
BRANCH OFFICE G2                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE G2                
BRANCH OFFICE H2                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE H2                
BRANCH OFFICE I2                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE I2                
BRANCH OFFICE J2                     JOHN         GEYER           MANAGER  |SPIFFY COMPUTER SERVICE DIV.|SUPPORT SERVICES|BRANCH OFFICE J2                

  14 record(s) selected.

 

 

Recent Stories
SQL Tuning for Developers – Part 1

The Key To Mastering Db2 Locking On Row-Store Tables

Lessons Learned of Locking and Latches by Adrian Burke