8 Solutions to Puzzle of the Week #21

Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

  • Use Single SELECT statement only
  • Use mgr column to identify employee’s immediate manager
  • The query should work in Oracle 11g.
  • A preferred solution should use only a single instance of emp table.

Expected Result:

NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
ADAMS      SCOTT      JONES
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING

Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
       SYS_CONNECT_BY_PATH(ename, ',') path,
       CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
             REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;

#2. Using CONNECT BY twice

WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno

#3. Using CONNECT BY and Self Outer Join

WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno

#4. Using 2 Self Outer Joins

SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
           LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno

#5. Using CONNECT BY and PIVOT

SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;

#6. PIVOT Simulation

WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
       MAX(DECODE(lvl, 2, ename)) name2,
       MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL, 
    3, CONNECT_BY_ROOT(ename), 
    4, TRIM(BOTH ' ' FROM 
        REPLACE(
            REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename), 
        CONNECT_BY_ROOT(ename)))
        ) Name3
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;

#8. A composition of Methods 1 and 7:

SELECT ename Name1, PRIOR ename Name2,
       CASE WHEN LEVEL IN (3,4) 
          THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2) 
       END AS Name3
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

3 Solutions to Puzzle of the Week #20

Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

  • Use Single SELECT statement only
  • Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
  • Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
  • If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
  • The query should work in Oracle 11g.

Expected Result:

POW20ER

#1. Using Common Table Expression (CTE) or Recursive WITH clause

WITH y AS (
SELECT ename, job, deptno, hiredate, sal, 
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY hiredate) rn
FROM emp
), x (ename, job, deptno, hiredate, sal, min_sal, max_sal, min_flag, max_flag, rn) AS (
SELECT ename, job, deptno, hiredate, sal, sal, sal, 1, 1, 1
FROM y
WHERE rn=1
UNION ALL
SELECT y.ename, y.job, y.deptno, y.hiredate, y.sal, 
       LEAST(x.min_sal, y.sal), GREATEST(x.max_sal, y.sal),
       CASE WHEN y.sal<x.min_sal THEN 1 END, 
       CASE WHEN y.sal>x.max_sal THEN 1 END, y.rn
FROM y JOIN x ON y.deptno=x.deptno AND y.rn=x.rn+1
)
SELECT ename, job, deptno, hiredate, sal, min_flag, max_flag
FROM x
WHERE 1 IN (min_flag, max_flag)
ORDER BY deptno, hiredate;

#2. Using Cumulative Analytic Functions MIN, MAX, and ROW_NUMBER

WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
       MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate) min_sal,
       MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate) max_sal,
       ROW_NUMBER()OVER(PARTITION BY deptno, sal ORDER BY hiredate) rn
FROM emp
)
SELECT ename, job, deptno, hiredate, sal,
       DECODE(sal, min_sal, 1) min_flag,
       DECODE(sal, max_sal, 1) max_flag
FROM x
WHERE sal IN (min_sal, max_sal)
  AND rn=1;

#3. Using Cumulative Analytic Functions MIN, MAX, and COUNT

WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
       CASE WHEN MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
             AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1 
       END min_flag,
       CASE WHEN MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
             AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1 
       END max_flag
FROM emp
)
SELECT *
FROM x
WHERE 1 IN (min_flag, max_flag);

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

 

 

 

Interview Question: get 2 random employees for each salary range?

Interview Question

Level: Intermediate/Advanced

For each of the following salary ranges select two randomly chosen employees:

0-999
1000-1999
2000-2999
3000+

Expected Result:

ENAME             SAL RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999

Solution:

WITH x AS (
SELECT ename, sal,
       CASE WHEN sal>=3000 THEN '3000+'
            WHEN sal>=2000 THEN '2000-2999'
            WHEN sal>=1000 THEN '1000-1999'
            ELSE                '0-999'
       END as range,
       ROW_NUMBER() OVER(PARTITION BY DECODE(GREATEST(sal, 3000), sal, 0, 1) +
                                      DECODE(GREATEST(sal, 2000), sal, 0, 1) +
                                      DECODE(GREATEST(sal, 1000), sal, 0, 1)
                         ORDER BY DBMS_RANDOM.VALUE) rn
FROM emp
)
SELECT ename, sal, range
FROM x
WHERE rn<=2
ORDER BY sal DESC

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Two ways to build a salary range report without using CASE function

Interview Question: Produce a salary range report with a single SELECT statement. Decode function is allowed, CASE function – is not.

Level: Intermediate

Expected Result:

RANGE                             Employees
-------------------------------- ----------
0-999                                     2
1000-2999                                 9
3000-5999                                 3

Strategy #1:

SELECT COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
                DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
                DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
                ) AS range,
       COUNT(*) "Employees"
FROM emp
GROUP BY COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
                  DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
                  DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
                  )
ORDER BY 1

Explanation:

In Oracle SQL terms, a mathematical condition

a <=x <=b

can be  interpreted as

x BETWEEN a AND b

however, this condition is good only for CASE function, and not for DECODE. The trick is to use another interpretation:

LEAST(b,x)=GREATEST(x,a)

– that can be used in DECODE.

CASE-based Solution:

SELECT CASE WHEN sal<=999 THEN '0-999'
            WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
            WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
       END AS range,
       COUNT(*) "Employees"
FROM emp
GROUP BY CASE WHEN sal<=999 THEN '0-999'
              WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
              WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
         END
ORDER BY 1

Strategy #2:

WITH x AS (
SELECT DECODE(1, (SELECT COUNT(*) FROM dual WHERE emp.sal<=999), '0-999',
                 (SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 1000 AND 2999), '1000-2999',
                 (SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 3000 AND 5999), '3000-5999'
             ) AS range
FROM emp
)
SELECT range, COUNT(*) AS "Employees"
FROM x
GROUP BY range
ORDER BY 1

Explanation:
This query demonstrates how to mimic CASE function using DECODE and in-line scalar subquery from dual.

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

How to split numeric and text values into separate columns

Puzzle: There is a table T with a single column C VARCHAR2(20) that contains random values. Some of the values are numeric. Write a single SELECT statement that outputs 2 columns: NUM and TEXT with numeric and non-numeric values correspondingly.

To mimic the T table, we will create a view:

CREATE OR REPLACE VIEW T
AS
WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
            ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
       END rnd,
       ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) C
FROM x
WHERE rk<=10;

Expected Result:
Due to the random nature of the values in T view, actual results will be different every time you run a query; however, all the results will look somewhat like this:

NUM  TEXT
---- ----
-146 4R9
-362 78R
-762 ICY
236  U3W
     VIK
     Y21

Solution:

WITH x AS (
SELECT c, CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+$') THEN 1 ELSE 0 END is_int,
       RANK()OVER(PARTITION BY CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+$') THEN 1 ELSE 0 END ORDER BY c) rk
FROM t	   
)
SELECT MAX(DECODE(is_int, 1, c)) NUM,
       MAX(DECODE(is_int, 0, c)) TEXT
FROM x
GROUP BY rk
ORDER BY rk

Explanation:

The above solution uses regular expression for identifying numeric integer values (positive and negative):

^-?[[:digit:]]+$

The way to break down the results into two columns was demonstrated in my previous post: Generate a department/employee roll report

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

 

Interview Question: Show Location for every employee without using joins

Interview Question: Show Location for every employee without using joins

Level: Intermediate

Expected Result:

ENAME          DEPTNO LOCATION
---------- ---------- ---------
CLARK              10 NEW YORK
KING               10 NEW YORK
MILLER             10 NEW YORK
ADAMS              20 DALLAS
FORD               20 DALLAS
JONES              20 DALLAS
SCOTT              20 DALLAS
SMITH              20 DALLAS
ALLEN              30 CHICAGO
BLAKE              30 CHICAGO
JAMES              30 CHICAGO
MARTIN             30 CHICAGO
TURNER             30 CHICAGO
WARD               30 CHICAGO

Method/Workaround #1: Use Aggregation over Cartesian Product

SELECT e.ename, e.deptno, MAX(DECODE(e.deptno,d.deptno, d.loc)) location
FROM emp e, dept d
GROUP BY e.ename, e.deptno
ORDER BY 2,1;

Method/Workaround #2: Use In-Line Scalar Subquery

SELECT e.ename, e.deptno, 
       (SELECT loc FROM dept d WHERE deptno=e.deptno) location
FROM emp e
ORDER BY 2,1;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.



Find a multiplication product on a numeric column.

Long ago I was challenged to write a query that would get me a product of all numerical values in a column. I was given a hint to use a logarithmic function which made things fairly simple.

Off course, you can develop a custom group function, though at the time I was challenged with this puzzle, such feature was not yet available.

A solution presented below is based on the well known basic math fact that a sum of logarithms is a logarithm of the product:

Log(a) + Log(b) = Log(a*b)

In Oracle syntax it is:

log(n, a) + log(n, b) = log(n, a*b)

Where n is a basis and has to be positive and not equal to 1. If we want to use decimal logarithms, we will use n=10.

Since we can use SQL sum function, we can get a sum of all logarithm values in the column (the only requirement is that all the arguments must be positive) which will be equal to the logarithm of the product (this is what we want!). Getting the logarithm’s argument is also simple – we just need to take the number 10 (or any other basis n of the logarithm that we use) to the power of the logarithm value:

a*b = Power(10, Log(10, a*b))

For the illustration, let’s get a product on all deptno values from dept table (scott schema).

SELECT POWER(10, SUM(LOG(10,deptno))) product
FROM dept

Result:

 PRODUCT
--------
  240000

If you need to multiply both, negative and positive numbers, you can first multiply absolute values and then figure out which sign the product has based on the number of negative multipliers.

First, let’s simulate such table:

SELECT 2-level AS num_value
FROM dual
WHERE level!=2
CONNECT BY level<6

Result:

NUM_VALUE
---------
        1
       -1
       -2
       -3

Solution:

WITH x AS (
SELECT 2-level AS num_value
FROM dual
WHERE level!=2
CONNECT BY level<6
)
SELECT POWER(-1, COUNT(DECODE(SIGN(num_value),-1,1)) ) AS "Sign", 
       POWER(10.0, SUM(LOG(10,ABS(num_value)))) AS "Product"
FROM x

Result:

      Sign    Product
---------- ----------
        -1          6

Here is a workaround:

WITH x AS (
SELECT 2-level                                  AS num_value,
       COUNT(DECODE(SIGN(2-level),-1,1)) OVER() AS prod_sign
FROM dual
WHERE level!=2
CONNECT BY level<6
)
SELECT POWER(-1, prod_sign) AS "Sign",
       CASE WHEN MIN(ABS(num_value))=0 THEN 0
            ELSE POWER(10, SUM(LOG(10,ABS(num_value))))
       END AS "Product"
FROM x
GROUP BY prod_sign

Result:

      Sign    Product
---------- ----------
        -1          6

If you don’t know if the column values contain zeros, which would automatically mean that the entire product is also zero, check it first using case function.

To implement that check, it would be easier to use the last workaround and modify the with clause:

WITH x AS (
SELECT 2-level                                  AS num_value,
       COUNT(DECODE(SIGN(2-level),-1,1)) OVER() AS prod_sign,
       DECODE(2-level,0,NULL,2-level)           AS no_zero_value
FROM dual
--WHERE level!=2
CONNECT BY level<6
)
SELECT CASE WHEN MIN(ABS(num_value))=0 THEN 0
            ELSE POWER(-1, prod_sign)*POWER(10, SUM(LOG(10,ABS(no_zero_value))))
       END AS product
FROM x
GROUP BY prod_sign

Get the root record when its parent id matches the root’s id, by Zahar Hilkevich

Very often developers decide to set parent id of a hierarchy root record to the record’s id value, meaning that the parent of that record is the record itself. This works fine as long as you don’t need to show a branch (or the entire) hierarchy from a record up to its very top root.

Let’s look at a query against the famous scott schema that comes with default Oracle’s database:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 FROM emp
 CONNECT BY empno=PRIOR mgr
 START WITH empno=7788

Result:

   ID NAME        PARENT_ID      LEVEL
----- ---------- ---------- ----------
 7788 SCOTT            7566          1
 7566 JONES            7839          2
 7839 KING                           3

so far so good – note that King’s manager employee number (parent_id) is NULL.

Let’s update it to 7839 (King’s empno) and re-run the above SQL:

UPDATE emp
SET mgr=empno
WHERE empno=7839

Now, the result is quite different:

SQL> SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 2  FROM emp
 3  CONNECT BY empno=PRIOR mgr
 4  START WITH empno=7788
 5  /
 ERROR:
 ORA-01436: CONNECT BY loop in user data

Of course, you can use NOCYCLE clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY NOCYCLE empno=PRIOR mgr
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2

The KING has gone! Now the challenge is quite apparent – we need to see the entire branch that includes the KING!

The trick is to mimic the original design when the King’s parent id was NULL!

A very first idea is to use WITH clause:

WITH x AS (
 SELECT empno AS ID, ename AS name, DECODE(mgr,empno,NULL,mgr) AS parent_id
 FROM emp
 )
 SELECT ID, name, parent_id, LEVEL
 FROM x
 CONNECT BY id=PRIOR parent_id
 START WITH id=7788

Result:

  ID NAME       PARENT_ID       LEVEL
---- ---------- --------------- ----------
7788 SCOTT      7566            1
7566 JONES      7839            2
7839 KING                       3

Note, that parent_id for KING is shown as NULL even though we updated it to 7839 – this can be fixed by applying NVL on the parent_id if needed.

A closer look allowed us to find a nice workaround that does not rely on the WITH clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY empno=PRIOR DECODE(mgr,empno,NULL,mgr)
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2
7839 KING             7839          3

Here we go!

Don’t forget to rollback the changes to set mgr=NULL for the KING’s record

P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”