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

## Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

• Use Single SELECT statement only
• DECODE and CASE functions are not allowed
• An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
• The query should work in Oracle 11g.

### Expected Result:

```ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
JAMES                                   950
FORD                        3000
MILLER           1300```

### #1: Using NULLIF, ABS, and SIGN functions

```SELECT ename, NULLIF(sal * (1-ABS(SIGN(deptno-10))),0) "10",
NULLIF(sal * (1-ABS(SIGN(deptno-20))),0) "20",
NULLIF(sal * (1-ABS(SIGN(deptno-30))),0) "30"
FROM emp
```

### #2: Using NULLIF and INSTR functions

```SELECT ename, NULLIF(sal * INSTR(deptno, 10), 0) "10",
NULLIF(sal * INSTR(deptno, 20), 0) "20",
NULLIF(sal * INSTR(deptno, 30), 0) "30"
FROM emp```

### #3: Using NVL2 and NULLIF functions

```SELECT ename, NVL2(NULLIF(deptno, 10), NULL, 1) * sal "10",
NVL2(NULLIF(deptno, 20), NULL, 1) * sal "20",
NVL2(NULLIF(deptno, 30), NULL, 1) * sal "30"
FROM emp```

### #4: Using PIVOT clause

```SELECT *
FROM  (SELECT deptno, ename, sal
FROM emp)
PIVOT (MAX(sal)
FOR deptno IN (10, 20, 30)
);```

### #5: Using Scalar SELECT statements in SELECT clause

```SELECT ename,
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=10) "10",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=20) "20",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=30) "30"
FROM emp e;```

### #6: Using UNION (different sort order)

```SELECT ename, sal "10", NULL "20", NULL "30"
FROM emp
WHERE deptno=10
UNION
SELECT ename, NULL, sal, NULL
FROM emp
WHERE deptno=20
UNION
SELECT ename, NULL, NULL, sal
FROM emp
WHERE deptno=30;

ENAME              10         20         30
---------- ---------- ---------- ----------
ALLEN                                  1600
BLAKE                                  2850
CLARK            2450
FORD                        3000
JAMES                                   950
JONES                       2975
KING             5000
MARTIN                                 1250
MILLER           1300
SCOTT                       3000
SMITH                        800
TURNER                                 1500
WARD                                   125

```

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

## An interview question that checks knowledge of Oracle SQL character functions.

Level: Beginner

Question: Find all employee names beginning with ‘S’ not using LIKE operator.

Method #1: INSTR function

```SELECT ename
FROM emp
WHERE INSTR(ename, 'S')=1
/

--Result:

ENAME
-------
SMITH
SCOTT
```

Workaround/Method #2: Range Test

```SELECT ename
FROM emp
WHERE ename>='S' AND ename<'T'
/

--Result:

ENAME
-------
SMITH
SCOTT
```

Workaround/Method #3: SUBSTR function

```SELECT ename
FROM emp
WHERE SUBSTR(ename, 1, 1) = 'S'
/

--Result:

ENAME
-------
SMITH
SCOTT
```

Workaround/Method #4: ASCII function

```SELECT ename
FROM emp
WHERE ASCII(ename)=ASCII('S')
/

--Result:

ENAME
-------
SMITH
SCOTT
```

Workaround/Method #5: SUBSTR functions

```SELECT ename
FROM emp
WHERE 'S' || SUBSTR(ename, 2) = ename
/

--Result:

ENAME
-------
SMITH
SCOTT
```

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