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

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
union all
select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno, depth+1
from tree t, department d
and d.ADMRDEPT <> d.deptno and depth < 10
)
select cast(repeat(' ',4*depth) || deptno as varchar(20))

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
>     union all
>     select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno, d.MGRNO, depth+1
>     from tree t, department d
>       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
>                                  from tree t2
>                                    and t2.MGRNO is not null)
>           ))

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
>     union all
>     select rtrim(t.ADMRDEPT) || rtrim(d.deptno) || '.', d.deptno
>          , coalesce(d.MGRNO,t.MGRNO), depth+1
>     from tree t, department d
>       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

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
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
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
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
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