# 800 Phone Puzzle

For a given 800 phone number (like 1-800-123-4567) find all number-letter representations.

• Use a single SELECT statement only.
• Only last 4 digits of the phone number have to be replaced with letters.
• Exactly 1 letter (out of 4) must be vowel,  the rest – consonant
• The following table shows all possible mappings:
Digit Maps to
1 1
2 A, B, C
3 D, E, F
4 G, H, I
5 J, K, L
6 M, N, O
7 P, Q, R, S
8 T, U, V
9 W, X, Y, Z
0 0

# Solutions:

Essentially, all solutions below share the same idea of generating the letter based phone numbers. The differences are in a way the mapping CTE is created and a way to limit the number of vowels to 1.

### Solution #1. Compact form of creating the map CTE with recursive check for the vowels:

``````WITH map AS (
SELECT digit, letter, '4357' phone
FROM TABLE(sys.odcivarchar2list('00','11','2ABC','3DEF','4GHI',
'5JKL','6MNO','7PQRS','8TUV','9WXYZ')) t,
LATERAL(SELECT SUBSTR(t.column_value,1,1) digit,
SUBSTR(t.column_value,1+LEVEL,1) letter
FROM dual
CONNECT BY SUBSTR(t.column_value,1+LEVEL,1) IS NOT NULL) x
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1``````

### Solution #2. Using more efficient way of creating the map CTE :

```WITH x AS (
SELECT ROWNUM-1 digit,COLUMN_VALUE letters
FROM TABLE(sys.odcivarchar2list('0','1','ABC','DEF','GHI','JKL',
'MNO','PQRS','TUV','WXYZ'))
), map AS (
SELECT digit, SUBSTR(letters, level, 1) letter, '4357' phone
FROM x
CONNECT BY SUBSTR(letters, level, 1) IS NOT NULL
AND PRIOR digit = digit
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1```

### Solution #3. Much more efficient way of creating the map CTE and using Regular Expression to limit the vowels :

```WITH d AS (
SELECT LEVEL+1 n, CASE WHEN LEVEL+1 IN (7,9) THEN 4 ELSE 3 END cnt,
'4357' phone
FROM dual
CONNECT BY LEVEL<=8
), a AS (
SELECT CHR(ASCII('A')+LEVEL-1) letter, ROWNUM rn
FROM dual
CONNECT BY CHR(ASCII('A')+LEVEL-1)<='Z'
), x AS (
SELECT n,
1+NVL(SUM(cnt) OVER(ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING),0) c1,
SUM(cnt) OVER(ORDER BY n) c2,
phone
FROM d
), map AS (
SELECT n digit, letter, x.phone
FROM x JOIN a ON a.rn BETWEEN x.c1 AND x.c2
UNION
SELECT ROWNUM-1, TO_CHAR(ROWNUM-1), x.phone
FROM x
WHERE ROWNUM<=2
), res(str, lvl) AS (
SELECT letter, 1
FROM map
WHERE SUBSTR(map.phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1
FROM res JOIN map ON SUBSTR(map.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(map.phone)
AND REGEXP_COUNT(res.str || letter,'[AEIOU]')<=1
)
SELECT str phone
FROM res
WHERE lvl=4
AND REGEXP_COUNT(str,'[AEIOU]')=1```

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

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.

# 2018 Puzzle of the Week #2:

For each of the following salary ranges select two randomly chosen employees:
0-999
1000-1999
2000-2999
3000+

Expected Result (in SQL*Plus):

```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```
• Remember to use only a single SELECT statement.
• Use table emp (from Oracle scott schema)

# Solutions:

## Solution #1: Using ROW_NUMBER with random.value functions:

We are applying a random sorting order to each of the salary ranges and take 2 top records from each range:

```WITH x AS (
SELECT CASE WHEN sal<=999  THEN '0-999'
WHEN sal<=1999 THEN '1000-1999'
WHEN sal<=2999 THEN '2000-2999'
ELSE                '3000+'
END range,
ename, sal
FROM emp
), y AS (
SELECT ename, sal, range,
ROW_NUMBER()OVER(PARTITION BY range
ORDER BY dbms_random.value) rn
FROM x
)
SELECT range, ename, sal
FROM y
WHERE rn<=2
ORDER BY range```

Result:

```RANGE     ENAME      SAL
--------- ---------- ----------
0-999     JAMES      950
0-999     SMITH      800
1000-1999 WARD 1250
1000-1999 TURNER 1500
2000-2999 JONES 2975
2000-2999 CLARK 2450
3000+     FORD 3000
3000+     KING 5000```

Result (of subsequent execution):

```RANGE     ENAME             SAL
--------- ---------- ----------
0-999     SMITH             800
0-999     JAMES             950
1000-1999 WARD             1250
1000-1999 MARTIN           1250
2000-2999 BLAKE            2850
2000-2999 JONES            2975
3000+     SCOTT            3000
3000+     KING             5000```

## Solution #2: Using DECODE, MAX() KEEP and UNION ALL:

Instead of taking top 2 records (randomly sorted), we are taking top 1 and bottom 1 and combine them together. DECODE function mimics the CASE from the previous solution.

```WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal, ROWNUM || dbms_random.value rnd
FROM scott.emp
)
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename,
MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal
FROM x
GROUP BY range
UNION ALL
SELECT range, MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename,
MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal
FROM x
GROUP BY range
ORDER BY range
```

Result:

```RANGE     ENAME             SAL
--------- ---------- ----------
0-999     JAMES             950
0-999     SMITH             800
1000-1999 MARTIN           1250
1000-1999 WARD             1250
2000-2999 JONES            2975
2000-2999 BLAKE            2850
3000+     FORD             3000
3000+     KING             5000
```

Note, that we concatenated ROWNUM with dbms_random.value to produce UNIQUE random value. Without ROWNUM (or any other KEY) there is always a chance that dbms_random.value will repeat on different rows and hence top and bottom values could be mixed and the same employee will be repeated twice.

## Solution #3: Using SIN for random value simulation and multi-column UNPIVOT with MAX() KEEP function:

Instead of combining top and bottom records from two statements using UNION ALL, here were calculating top and bottom values as 1 record and UNPIVOT them to produce two rows per salary range:

```WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal,
SIN(ROWNUM*TO_NUMBER(SUBSTR(
extract(second
from current_timestamp),-3))
) rnd
FROM scott.emp
), y AS (
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename1,
MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal1,
MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename2,
MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal2
FROM x
GROUP BY range
)
SELECT range, ename, sal
FROM y
UNPIVOT (
(ename, sal) for (t1, t2) in ((ename1,sal1), (ename2,sal2))
)
ORDER BY range
```

Result:

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

Note the use of multi-column UNPIVOT. Randomization simulation is based on a fairly random selection of the last 3 digits in the current timestamp’s second value. This number is used as a “seed”. When this seed is multiplied by the rownum, the result is used as a SIN function argument which makes the outcome pseudo-random.

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

## Interview Question

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.

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

## How to generate a random sample of numeric and alpha-numeric values

Puzzle: Generate a random sample of numeric and alpha-numeric values

This problem often arises when you need to run a test and you don’t have a table to experiment with. Not a problem any more!

Related Post: Use TRUNC function to generate various date ranges

Step 1: Generate a random list of 10 alpha-numeric values:

```SELECT dbms_random.string('x',3) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-----
3TI
1JB
CIP
9SE
79K
YNZ
VEG
V0B
KPN
ILR
```

Step 2: Generate a random list of 10 integer values:

```SELECT TRUNC(dbms_random.VALUE(-999,999)) rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
----
539
153
979
689
212
267
-5
-832
-160
665
```

Step 3: Mix the above lists together (with equal shares):

```SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd
FROM dual
CONNECT BY ROWNUM<=10

RND
-------
-513
SVA
-475
NRM
-903
G45
-654
2S3
415
0HG

```

Step 4: Let’s have a fairly random number of integers and strings in the output

```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)) rnd
FROM x
WHERE rk<=10

RND
----
-985
0TG
8JZ
-714
500
199
7IJ
249
RNI
F2G
```

Step 5: Final touch – let’s add a column that would flag integers

```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)) rnd, CASE WHEN REGEXP_LIKE(rnd,'^-?[[:digit:]]+\$') THEN 1 ELSE 0 END is_int
FROM x
WHERE rk<=10

RND      IS_INT
---- ----------
WIS           0
-558          1
0QR           0
-433          1
RB0           0
PT8           0
409           1
YOV           0
969           1
FFI           0
```

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.

## Puzzle of the Week Challenge – Solutions to the 2nd Puzzle

Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!

Dish washing schedule puzzle:

 Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random.

Solution #1: Traditional approach for mimicking pivoted report.

```WITH x AS (
SELECT FLOOR((LEVEL-1)/4) id,
LEVEL AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
2          1          4          3
6          5          8          7
11          9         10         12
14         15         16         13
20         19         18         17
24         21         22         23
28         27         26         25
31                    30         29
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
3          4          2          1
7          5          6          8
12          9         11         10
16         14         13         15
20         19         18         17
23         24         21         22
25         27         26         28
31         29         30
```

Solution #2: Using Recursive WITH clause for range generation:

```WITH x(d) AS (
SELECT 1 AS d
FROM dual
UNION ALL
SELECT d+1
FROM x
WHERE d<32
), y AS (
SELECT FLOOR((d-1)/4) id,
CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d,
RANK()OVER(PARTITION BY FLOOR((d-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          2          3
8          7          6          5
11         10          9         12
15         14         13         16
18         19         20         17
24         22         21         23
25         27         28         26
29                    30         31
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          3          2
5          6          7          8
11         12         10          9
13         15         16         14
20         19         17         18
22         23         24         21
28         26         27         25
29         30         31
```

Solution #3: Using PIVOT clause:

```SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"
FROM (
SELECT FLOOR((LEVEL-1)/4) id,
CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT
(
MAX(d)
FOR rk IN (1,2,3,4)
)
ORDER BY id;
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
7          6          8          5
10          9         11         12
16         14         13         15
20         19         17         18
23         22         21         24
26         25         27         28
31                    29         30
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
8          7          5          6
11         10         12          9
16         13         14         15
20         19         17         18
21         22         23         24
28         26         27         25
31         29         30
```

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.

## How to generate an even/odd random number in Oracle SQL?

Question Level: Beginner/Intermediate

Question: Generate a random even (or odd) number in the range 1..100.

Odd/Even random number generation is fairly straightforward process if you understand the math behind a simple SQL expression:

```SELECT 2*(ROUND(dbms_random.value(1,100)/2,0))   even_random,
2*(ROUND(dbms_random.value(1,100)/2,0))+1 odd_random
FROM dual
```

Using the same approach we can generate a random number that is a multiple of 5 (or any other number):

```SELECT 5*(ROUND(dbms_random.value(1,100)/5,0))   multiple5_random
FROM dual
```