7 Solutions to 2018 Oracle SQL Puzzle of the Week #6

Conference Team Puzzle

Research department from Dallas (#20) needs to delegate a team of 3 to a annual conference. Create a list of all possible teams of 3 employees from that department.

  • Use a single SELECT statement only
  • Use scott.emp table
  • Exactly 3 employees (no repetitions) must be presented for each team

Solutions:

Essentially, all 6 solutions represent 6 different ways how you can UNPIVOT a result set. Some of those are fairly standard and well known (#3, #4, and #7) while the others are quite tricky (#2, #5, and #6).

Solution #1: Using UNPIVOT clause:

WITH t AS (  --#1: Using UNPIVOT 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, team_member 
FROM x 
UNPIVOT (team_member FOR dummy IN (e1,e2,e3) ) 
ORDER BY 1,2

Solution #2: Mimicking UNPIVOT with IN operator

WITH t AS (  --#2: Mimicking UNPIVOT with IN operator 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT x.team_no, t.ename team_member 
FROM t JOIN x ON t.ename IN (x.e1, x.e2, x.e3) 
ORDER BY 1,2

Solution #3: Mimicking UNPIVOT with MODEL clause

WITH t AS ( --#3: Mimicking UNPIVOT with MODEL clause 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, team_member 
FROM x 
MODEL 
PARTITION BY (team_no) 
DIMENSION BY (1 AS dummy) 
MEASURES (e1 AS team_member, e2, e3) 
RULES( 
    team_member[2]=e2[1], 
    team_member[3]=e3[1] 
) 
ORDER BY 1,2

Solution #4: Mimicking UNPIVOT with UNION operators

WITH t AS ( --#4: Mimicking UNPIVOT with UNIONs 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, e1 team_member 
FROM x 
UNION  
SELECT team_no, e2 team_member 
FROM x 
UNION  
SELECT team_no, e3 team_member 
FROM x

Solution #5: Mimicking UNPIVOT with DECODE and Cartesian Product

WITH t AS ( --#5: Mimicking UNPIVOT with DECODE and Cartesian Product 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, DECODE(y.rn, 1, e1, 2, e2, 3, e3) team_member 
FROM x, (SELECT ROWNUM rn FROM dual CONNECT BY LEVEL<=3) y 
ORDER BY 1,2

Solution #6: Mimicking UNPIVOT with COALESCE and GROUPING SETS

WITH t AS ( --#6: Mimicking UNPIVOT with COALESCE and GROUPING SETS 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, COALESCE(e1, e2, e3) team_member 
FROM x 
GROUP BY team_no, GROUPING SETS(e1,e2,e3) 
ORDER BY 1,2

Solution #7: Mimicking UNPIVOT with Recursive CTE

WITH t AS ( --#7: Mimicking UNPIVOT with Recursive CTE
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
 JOIN t t3 ON t2.empno>t3.empno 
), y(team_no, team_member, e2, e3, lvl) AS (
SELECT team_no, e1, e2, e3, 1
FROM x
UNION ALL
SELECT team_no, DECODE(lvl+1, 2, e2, e3), e2, e3, lvl+1
FROM y
WHERE lvl+1<=3
)
SELECT team_no, team_member
FROM y
ORDER BY 1,2

You can execute the above SQL statements in Oracle Live SQL environment.
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.

 

Advertisements

Term Replication Sequence SQL Puzzle

SQL Puzzle:

Generate a term replication sequence: 1, 2, 2, 3, 3, 3, 4, 4, 4, 4, etc in a single SELECT statement.

Level: Advanced

Expected Result (for N=4):

RN
1
2
2
3
3
3
4
4
4
4

Solutions:

#1: Using CONNECT BY (for both, the range and the sequence generation)

WITH x AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL<=4
)
SELECT rn--, LEVEL
FROM x
CONNECT BY LEVEL<=rn
       AND rn>PRIOR rn
GROUP BY rn, LEVEL
ORDER BY 1;

#2: Using Recursive CTE

WITH x(rn, lvl) AS (
SELECT ROWNUM rn, 1
FROM dual
CONNECT BY LEVEL<=4
UNION ALL
SELECT rn, lvl+1
FROM x
WHERE rn>=lvl+1
)
SELECT rn
FROM x
ORDER BY 1;

#3: Using Self-Join

WITH x AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL<=4
)
SELECT a.rn
FROM x a JOIN x b ON a.rn>=b.rn
ORDER BY 1;

Naoto Katayama submitted one more elegant solution using MODEL clause:

#4: Using MODEL clause

SELECT RN
FROM (SELECT LEVEL rn
      FROM DUAL 
      CONNECT BY LEVEL<=4)
MODEL
PARTITION BY(ROWNUM AS par)
DIMENSION BY(0 AS dummy)
MEASURES(rn)
RULES ITERATE(100) UNTIL ITERATION_NUMBER+1>=rn[0]
(rn[ITERATION_NUMBER]=rn[0])
ORDER BY 1;

My Oracle Group on Facebook:

If you like this post, you may want to join my 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 2018 Oracle SQL Puzzle of the Week #3

2018 Puzzle of the Week #3:

Exact Coin Change Puzzle.

Suppose that you are a sales person at a cash register and you have one purchase to serve before you close. A buyer has to pay X dollars and N cents with bills only (no coins). You have lots of bills of various nomination and limited number of coins: 3 quarters, 9 dimes, 19 nickels, and 4 pennies left in the register. You are required to give the exact change (between 1 and 99 cents) using smallest number of (available) coins.

  • Use a single SELECT statement
  • The result should return 1 row and 4 columns indicating how many coins of each type to use
  • 1 Quarter = 25 cents; 1 Dime = 10 cents; 1 Nickel = 5 cents

Sample result for a change of 63 cents:

 
  Quarters      Dimes    Nickels    Pennies
---------- ---------- ---------- ----------
         2          1          0          3

Solutions:

Solution #1: Using Math formula and MODEL clause:

For American coins one can rely on a mathematical formula to get the smallest number of coins for exact change:

Quarters: FLOOR of [Change Amount]/25
Dimes: FLOOR(([Change Amount] – 25*[Quarters])/10)
Nickels: FLOOR(([Change Amount] – 25*[Quarters]-10*[Dimes])/5)
Pennies: [Change Amount] – 25*[Quarters]-10*[Dimes] – 5*[Nickels]

One of the easiest ways to implement this strategy is to employ the MODEL clause:

WITH m AS (
SELECT 63 AS cents
FROM dual 
)
SELECT cents "Change", 
       Q "Quarters", 
       D "Dimes", 
       N "Nickels", 
       P "Pennies"
FROM m
MODEL
DIMENSION BY(0 AS dummy)
MEASURES(
 cents,
 CAST(0 AS NUMBER(3)) AS Q,
 CAST(0 AS NUMBER(3)) AS D,
 CAST(0 AS NUMBER(3)) AS N,
 CAST(0 AS NUMBER(3)) AS P
)
RULES (
 Q[0]=FLOOR(CENTS[0]/25),
 D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
 N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
 P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)

Result:

Change Quarters Dimes Nickels Pennies
63 2 1 0 3

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH m AS (
SELECT LEVEL cents
FROM dual 
CONNECT BY LEVEL<=99
)
SELECT cents "Change", 
       Q "Quarters", 
       D "Dimes", 
       N "Nickels", 
       P "Pennies"
FROM m
MODEL
PARTITION BY(ROWNUM AS rn)
DIMENSION BY(0 AS dummy)
MEASURES(
 cents,
 CAST(0 AS NUMBER(3)) AS Q,
 CAST(0 AS NUMBER(3)) AS D,
 CAST(0 AS NUMBER(3)) AS N,
 CAST(0 AS NUMBER(3)) AS P
)
RULES (
 Q[0]=FLOOR(CENTS[0]/25),
 D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
 N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
 P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
ORDER BY 1

Result:

Change Quarters Dimes Nickels Pennies
1 0 0 0 1
2 0 0 0 2
3 0 0 0 3
4 0 0 0 4
5 0 0 1 0
6 0 0 1 1
7 0 0 1 2
8 0 0 1 3
9 0 0 1 4
10 0 1 0 0
95 3 2 0 0
96 3 2 0 1
97 3 2 0 2
98 3 2 0 3
99 3 2 0 4

Solution #2: Using Enhanced Math formula:

It’s easy to see that the MOD function is very handy in determining the number of coins other than quarters (the largest):

WITH a AS (
SELECT 63 cents
FROM dual
)
SELECT a.cents "Change",
       FLOOR(a.cents/25) "Quarters", 
       FLOOR(MOD(a.cents,25)/10) "Dimes",
       FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
       MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a

Alternatively, we can see coin combinations for all change amounts from 1 to 99 cents:

WITH a AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<100
)
SELECT a.cents "Change",
       FLOOR(a.cents/25) "Quarters", 
       FLOOR(MOD(a.cents,25)/10) "Dimes",
       FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
       MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
ORDER BY a.cents

Solution #3: Using Cartesian Product and Top Record pattern approach:

If we did not know the exact math formula, we could still count on the brute force approach – go over all possible coin permutations (Cartesian product) that sum up to the required total amount and then chose the combination with the fewest number of coins (top record pattern):

WITH r AS (
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=20
), x AS (
SELECT q.n "Quarters", d.n "Dimes", n.n "Nickels", p.n "Pennies",
 RANK() OVER(ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p
WHERE q.n<=3
 AND d.n<=9
 AND n.n<=19 --not needed
 AND p.n<=4
 AND q.n*25 + d.n*10 + n.n*5 + p.n = 63 --amount of change
)
SELECT "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

WITH r AS ( -- this range is to be reused 5 times in this query
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=100
), x AS (
SELECT c.n "Change", q.n "Quarters", d.n "Dimes", 
       n.n "Nickels", p.n "Pennies",
       RANK() OVER(PARTITION BY c.n ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p, r c
WHERE q.n<=3
 AND d.n<=9
 AND n.n<=19 --now it is needed
 AND p.n<=4  AND q.n*25 + d.n*10 + n.n*5 + p.n = c.n --amount of change  
 AND c.n>0
)
SELECT "Change", "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
ORDER BY 1

You can execute the above SQL statements in Oracle Live SQL environment.

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.

5 Solutions to 2018 Oracle SQL Puzzle of the Week #1

2018 Puzzle of the Week #1:

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

Solutions:

Solution #1: Using CONNECT BY clause (for range generation), REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
), x AS ( 
SELECT SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1) substr, 
       RANK() OVER(ORDER BY r2.rn - r1.rn DESC, r1.rn) rk 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND REGEXP_COUNT(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1') = 0 
) 
SELECT substr 
FROM x 
WHERE rk=1

Result of execution in Oracle Live SQL client:

SUBSTR
rkans

Solution #2: Using CONNECT BY clause (for range generation), REGEXP_LIKE, and MAX() KEEP functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1)) 
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND NOT REGEXP_LIKE(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1')

Solution #3: Using CONNECT BY clause (twice), LATERAL view, REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), s AS ( 
SELECT SUBSTR(word, LEVEL) word, LEVEL rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(x.substr) 
       KEEP(DENSE_RANK FIRST ORDER BY LENGTH(x.substr) DESC, s.rn) substr 
FROM s, LATERAL(SELECT SUBSTR(s.word, 1, LEVEL) substr 
                FROM dual 
                CONNECT BY LEVEL<=LENGTH(s.word)) x 
WHERE REGEXP_COUNT(x.substr, '(.).*\1') = 0

Solution #4: Using XMLTable function (for range generation), Correlated subquery with COUNT(DISTINCT), and MAX() KEEP function:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn, word
FROM w, XMLTABLE('for $i in 1 to $N cast as xs:integer return $i' 
                 PASSING LENGTH(w.word) AS N) x
) 
SELECT MAX(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1))
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2
WHERE r1.rn<=r2.rn 
 AND r2.rn - r1.rn + 1 = 
 (SELECT COUNT(DISTINCT SUBSTR(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1), 
                               LEVEL, 1)) 
 FROM dual 
 CONNECT BY LEVEL<=r2.rn - r1.rn + 1 
 )

Solution #5: Using CONNECT BY, Recursive CTE, INSTR, SUBSTR, and MAX() KEEP functions:

WITH w AS (
 SELECT 'arkansas' word
 FROM dual
), s(sub, word, lvl, rn) AS (
SELECT SUBSTR(word, LEVEL, 1), SUBSTR(word, LEVEL) word, 1, ROWNUM
FROM w
CONNECT BY SUBSTR(word, LEVEL) IS NOT NULL
UNION ALL
SELECT SUBSTR(word, 1, lvl+1), word, lvl+1, ROWNUM
FROM s
WHERE LENGTH(SUBSTR(word, 1, lvl+1))=lvl+1
 AND INSTR(sub, SUBSTR(word, lvl+1, 1))=0
)
SELECT MAX(sub) KEEP (DENSE_RANK FIRST ORDER BY lvl DESC, rn) substr
FROM s

You can execute the above SQL statements in Oracle Live SQL environment.

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.

 

 

 

 

 

 

How to generate a list of first N binary numbers in Oracle SQL?

In my recent post I showed how to convert a decimal number (i.e. an integer) into a binary string. We can build upon that technique to answer the question:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.bin
FROM x, LATERAL (SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                        WITHIN GROUP(ORDER BY LEVEL DESC) bin
                 FROM dual
                 CONNECT BY POWER(2, LEVEL-1)<=x.N) y

Note the LATERAL keyword (Oracle 12c new feature) that enables us to reference “x” in the inline view “y”. In pre-12c world, we would have to use TABLE/CAST/MULTISET function composition to achieve the same result:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.column_value bin
FROM x, TABLE(CAST(MULTISET(
          SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                 WITHIN GROUP(ORDER BY LEVEL DESC) bin
          FROM dual
          CONNECT BY POWER(2, LEVEL-1)<=x.N) AS sys.odcivarchar2list)) y

The idea used in the following query is based on a totally different approach. It builds a string of “0”s and “1”s in a loop until its length reaches a desired value:

WITH x(v, n) AS (
SELECT column_value, 1
FROM TABLE(sys.odcivarchar2list('0','1'))
UNION ALL
SELECT x.v || t.column_value, x.n+1
FROM TABLE(sys.odcivarchar2list('0','1')) t JOIN x on LENGTH(x.v)=n
WHERE n<=CEIL(LOG(2,50))
), y AS (
SELECT NVL(LTRIM(x.v,'0'),'0') bin, ROWNUM-1 dec
FROM x
WHERE n=(SELECT MAX(n) FROM x)
)
SELECT *
FROM y
WHERE dec<=50

To better understand the above query, try the following one:

SELECT *                            
FROM TABLE(sys.odcivarchar2list('0','1')), 
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1'))

If we put enough tables in the Cartesian product and concatenate all column_value columns in a single character string expression, we will achieve our goal. The challenge with this approach is to dynamically change the number of the tables in the FROM clause. This can be simulated in the recursive WITH clause by repeatedly adding more and more collections of bits (0 and 1).

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

 

Integer to Binary Conversion in Oracle SQL

Interestingly enough, Oracle does not have a built-in function to convert Decimal numbers (i.e. integers) into Binary. This post offers an elegant way of doing so.

The following script is intended to be executed in SQL*Plus, so it uses some SQL*Plus commands:

column bin format a40
undefine N
SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=&&N;

Result (for N=400):

BIN
-------------
110010000

Result (for N=1401):

BIN
------------
10101111001

Explanation:

How many digits may the resulting binary string have? The answer comes from Math: not more than LOG(2, N) + 1. Let’s first generate a numeric range from 1 to LOG(2,N)+1:

SELECT LEVEL
FROM dual
CONNECT BY LEVEL<=LOG(2,&N)+1

Result (for N=1401):

 LEVEL
------
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11

Alternatively, we can use mathematically equivalent condition in the CONNECT BY clause using POWER instead of LOG function:

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL)<=&N*2

or

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&N

Now, we will check every bit of the desired result (i.e. binary representation of N) by using BITAND function:

SELECT LEVEL, BITAND(&&N, POWER(2,LEVEL-1)) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          4
    4          8

Positive values in the bit column refer to a bit 1 in the corresponding position (in reverse order) of the binary value. It’s easy to turn those values to 1 by using SIGN function:

SELECT LEVEL, SIGN(BITAND(&&N, POWER(2,LEVEL-1))) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          1
    4          1

Here, we can see that we need to concatenate the values in the bit column in reverse order. This is very easy to do using LISTAGG function:

SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

BIN
----------
1100

Note that we sorted all the rows in descending order of the LEVEL to obtain the correct order of bits.

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.

How to Simulate SIGN Function

Puzzle of the day:

How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.

Solution:

SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))

In SQL, we can demonstrate it as follows:

WITH r AS (
SELECT dbms_random.VALUE(-999,999) rnd
FROM dual
CONNECT BY LEVEL<=10
UNION ALL
SELECT 0
FROM dual
)
SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign"
FROM r

Result:

       RND  SIGN(RND)     MySign
---------- ---------- ----------
  -519.606         -1         -1
-657.62692         -1         -1
414.625079          1          1
736.175183          1          1
268.689074          1          1
-647.12649         -1         -1
338.192233          1          1
784.780876          1          1
-529.69184         -1         -1
-596.56803         -1         -1
         0          0          0

As you can see, “MySign” column perfectly matches SIGN column.

Comment:

WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.

 

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.

9 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 empno;

#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 empno;

#9. Using NTH_VALUE Analytic function (Oracle 11.2 and up):

WITH x AS (
SELECT CONNECT_BY_ROOT(ename) n1, CONNECT_BY_ROOT(empno) empno,
 NTH_VALUE(ename, 2) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n2,
 NTH_VALUE(ename, 3) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n3
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT n1 name1, MAX(n2) name2, MAX(n3) name3
FROM x
GROUP BY n1, empno
ORDER BY empno

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: Count number of every week day in a year

Interview Question: With a single SELECT statement get the number of each week day in the current year.

Level: Intermediate

Expected Result:

Day                                  Days in Year
------------------------------------ ------------
SUNDAY                                         52
MONDAY                                         52
TUESDAY                                        52
WEDNESDAY                                      52
THURSDAY                                       52
FRIDAY                                         53
SATURDAY                                       53

Solution #1:

WITH x AS (
SELECT LEVEL-1+TRUNC(SYSDATE, 'YYYY') AS dd
FROM dual
CONNECT BY TRUNC(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'YYYY')=TRUNC(SYSDATE, 'YYYY')
)
SELECT TO_CHAR(dd, 'DAY') "Day", COUNT(*) "Days in Year"
FROM x
GROUP BY TO_CHAR(dd, 'DAY'), TO_CHAR(dd, 'D')
ORDER BY TO_CHAR(dd, 'D');

Explanation:

The WITH clause returns all days in the current year, this is a common trick used in majority of sql puzzle related to a calendar. The connect by query used in the WITH generated a date range which starts on TRUNC(SYSDATE, ‘YYYY’) – i.e. the 1st day of the year – and continues as long as the next day falls into the same year (see condition in the CONNECT BY clause). The main query groups by day name – TO_CHAR(dd, ‘DAY’) – and sorts by day number (in a week) – TO_CHAR(dd, ‘D’).

Solution #2:

WITH x AS (
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'), 12)-1, 'DDD') days_in_year
FROM dual
)
SELECT TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'DAY') "Day",
       CASE WHEN MOD(days_in_year,52)>=LEVEL THEN 53
            ELSE 52
       END "Days in Year"
FROM x
CONNECT BY LEVEL<=7
ORDER BY TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'D');

Explanation:

The idea behind this solution is totally different than in the 1st one. A year has 52 weeks and 1 or 2 days depending on whether it is a  leap year or not. So each day of the week happens 52 times a year + first one or two days of the year make corresponding week days have 53 days in that same year. If we know the number of days in a year (365 or 366) we can find out which days of the week will happen 53 times. For that matter we can take MOD(days_in_year, 52) expression that will return either 1 or 2. If the day order number within a year is 1 (or 2 for the leap year) we know that the corresponding week day will occur 53 times, otherwise – 52.

The WITH clause returns number of days in the current year. We get that by taking the 1st day of the current year: TRUNC(SYSDATE,’YYYY’), adding 12 months to it and subtract 1 day to get the last day of the current year. Taking TO_CHAR(…, ‘DDD’) – gives us the order number of that day in the year which is exactly the number of days in the current year.

The main query generates the date range from Jan-1 to Jan-7 in the current year, and assigns 52 or 53 to the 2nd column based on the logic described above.

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.

 

 

Three Solutions to Puzzle of the Week #16

Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

Expected Result:

--For N=100:

Biggest Prime Factor
--------------------
                  5

--For N=52:

Biggest Prime Factor
--------------------
                 13

--For N=21:

Biggest Prime Factor
--------------------
                   7

Solutions

#1: Using CTE (recursive WITH)

WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
                   5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
                  13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
                   7


#2: Using CONNECT BY clause , version 1

WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

#3: Using CONNECT BY clause, version 2

WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;

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.