3 Solutions to 2018 Oracle SQL Puzzle of the Week #15

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

Further Reading:

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

2018 Oracle SQL Puzzle of the Week #15

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
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points. This is the LAST PUZZLE of this contest.
  • The scoring rules can be found here.
  • Solutions must be submitted as comments to this blog post.
  • Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result (for a phone number 1-800-123-4357):

PHONE
1-800-123-GEJP
1-800-123-GEJQ
1-800-123-GEJR
1-800-123-GEJS
1-800-123-GEKP
1-800-123-GEKQ
1-800-123-GEKR
1-800-123-GEKS
1-800-123-GELP
1-800-123-GELQ
1-800-123-GELR
1-800-123-GELS
1-800-123-HEJP
1-800-123-HEJQ
1-800-123-HEJR
1-800-123-HEJS
1-800-123-HEKP
1-800-123-HEKQ
1-800-123-HEKR
1-800-123-HEKS
1-800-123-HELP
1-800-123-HELQ
1-800-123-HELR
1-800-123-HELS
1-800-123-IDJP
1-800-123-IDJQ
1-800-123-IDJR
1-800-123-IDJS
1-800-123-IDKP
1-800-123-IDKQ
1-800-123-IDKR
1-800-123-IDKS
1-800-123-IDLP
1-800-123-IDLQ
1-800-123-IDLR
1-800-123-IDLS
1-800-123-IFJP
1-800-123-IFJQ
1-800-123-IFJR
1-800-123-IFJS
1-800-123-IFKP
1-800-123-IFKQ
1-800-123-IFKR
1-800-123-IFKS
1-800-123-IFLP
1-800-123-IFLQ
1-800-123-IFLR
1-800-123-IFLS

Apparently, the purpose of this exercise is to pick a string that sounds the best, so in this particular case we would pick: 1-800-123-HELP.

A correct answer (and workarounds!) will be published here in about a week.

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

Yet Another Top Employee Puzzle

Find the employee who remained the top paid employee (excluding the president) the longest period of time between 1980 and 1981

  • Use a single SELECT statement only.
  • President should be excluded from the analysis.
  • Show the number of days the employee remained the top paid person as well as Start Date (hiredate) and End Date (the date when another top employee started)
  • The End Date for the last top paid employee in the interval should be 31-DEC-1981.

Expected Result:

EMPNO ENAME JOB SAL Start Date End Date Days on Top
7566 JONES MANAGER 2975 02-APR-81 03-DEC-81 245

Solutions:

Solution #1. Using RANK to filter the top employee:

WITH x AS ( 
SELECT empno, ename, job, sal, hiredate, 
       MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp  
WHERE job!='PRESIDENT' 
), y AS ( 
SELECT empno, ename, job, sal, hiredate start_date, max_sal,  
       LEAD(hiredate) OVER(ORDER BY hiredate) end_date 
FROM x 
WHERE sal=max_sal 
), z AS ( 
SELECT y.*, LEAST(end_date, date'1981-12-31')-start_date days_on_top, 
RANK()OVER(ORDER BY LEAST(end_date, date'1981-12-31')-start_date DESC) rk 
FROM y 
WHERE EXTRACT(YEAR FROM start_date) IN (1980, 1981)  
) 
SELECT empno,ename,job,sal, start_date "Start Date", 
       end_date "End Date", days_on_top	"Days on Top" 
FROM z 
WHERE rk=1

Solution #2. Using Subquery to filter the top employee:

WITH x AS ( 
SELECT empno, ename, job, sal, hiredate, 
       MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp  
WHERE job!='PRESIDENT' 
  AND hiredate>=date'1980-01-01'  
), y AS ( 
SELECT empno, ename, job, sal, hiredate start_date,  
       LEAST(date'1981-12-31', 
             LEAD(hiredate) OVER(ORDER BY hiredate)) end_date 
FROM x 
WHERE sal=max_sal 
) 
SELECT empno,ename,job,sal, start_date "Start Date", 
       end_date "End Date", end_date-start_date "Days on Top" 
FROM y 
WHERE end_date-start_date=(SELECT MAX(end_date-start_date) FROM y)

Solution #3. Using MODEL with RETURN UPDATED ROWS to filter the top employee:

WITH e AS ( 
SELECT empno, ename, sal, job, LEAST(hiredate, date'1981-12-31') hiredate,  
       MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp 
WHERE hiredate>=date'1980-01-01'  
  AND job!='PRESIDENT' 
), x AS ( 
SELECT empno, ename, job, sal, hiredate,  
       NVL(LEAD(hiredate)OVER(ORDER BY hiredate),
           date'1981-12-31')-hiredate diff, 
       NVL(LEAD(hiredate)OVER(ORDER BY hiredate),
           date'1981-12-31') end_date 
FROM e 
WHERE sal=max_sal 
) 
SELECT empno, ename, job, sal, hiredate "Start Date", 
       end_date "End Date", diff "Days on Top" 
FROM x 
MODEL RETURN UPDATED ROWS 
DIMENSION BY (empno, RANK()OVER(ORDER BY diff DESC) rk) 
MEASURES(ename,job,sal, hiredate, end_date, diff, 0 dummy) 
RULES(dummy[ANY, 1]=1)

The following query will only work as long as there is only 1 top paid employee who stayed on top the longest. In case if we had more than 1 it would only list one of those:

WITH x AS (
SELECT empno, ename, job, sal, hiredate, 
       MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp 
WHERE job!='PRESIDENT'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date, 
 LEAST(date'1981-12-31', 
       LEAD(hiredate) OVER(ORDER BY hiredate)) end_date,
 LEAST(date'1981-12-31', 
       LEAD(hiredate) OVER(ORDER BY hiredate))-hiredate days_top
FROM x
WHERE sal=max_sal
ORDER BY days_top DESC NULLS LAST, hiredate
)
SELECT *
FROM y
WHERE ROWNUM=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/

Further Reading:

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.

 

Tip of the day: use LNNVL function

Recently, I came across LNNVL Oracle function and decided to assess its usability.

According to Oracle documentation, it “provides a concise way to evaluate a condition when one or both operands of the condition may be null.

Let’s see a couple of problems this function can be applied to:

For each department count the number of employees who get no commission.

There are over 10 possible solutions that I am aware of, I will focus only on a couple where we can leverage LNNVL function.

Solution #1. Filter with LNNVL in WHERE clause:

SELECT deptno, COUNT(*) cnt 
FROM scott.emp 
WHERE LNNVL(comm>0)
GROUP BY deptno
ORDER BY 1;

Result:

DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          3

In the above query, LNNVL(comm>0) filter is equivalent to: (comm<=0 OR comm IS NULL) and since comm cannot be negative, we can say that it is the same as NVL(comm,0)=0.

According to that same Oracle documentation, LNNVL “can be used only in the WHERE clause of a query.” This does not seem to be true, at least in 12g+ releases:

Solution #2. Using LNNVL in conditional aggregation:

SELECT deptno, SUM(CASE WHEN LNNVL(comm>0) THEN 1 ELSE 0 END) cnt
FROM scott.emp 
GROUP BY deptno
ORDER BY 1;

LNNVL can also be used with IN operator. We will illustrate it with a solution (one of the many) to the following problem:

List all employees who is not a manager of somebody else.

SELECT empno, ename, deptno, job, mgr
FROM scott.emp
WHERE LNNVL(empno IN (SELECT mgr FROM scott.emp))
ORDER BY 1;

The following boolean expression defines a condition to see managers only:

empno IN (SELECT mgr FROM scott.emp)

We need the opposite one that handles NULLs – as mgr is a nullable column. And this is where LNNVL helps us.

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

Further Reading:

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.

2018 Oracle SQL Puzzle of the Week #14

Yet Another Top Employee Puzzle

Find the employee who remained the top paid employee (excluding the president) the longest period of time between 1980 and 1981

  • Use a single SELECT statement only.
  • President should be excluded from the analysis.
  • Show the number of days the employee remained the top paid person as well as Start Date (hiredate) and End Date (the date when another top employee started)
  • The End Date for the last top paid employee in the interval should be 31-DEC-1981.
  • Check the previous’ week puzzle for some clues.
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
  • The scoring rules can be found here.
  • Solutions must be submitted as comments to this blog post.
  • Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result:

EMPNO ENAME JOB SAL Start Date End Date Days on Top
7566 JONES MANAGER 2975 02-APR-81 03-DEC-81 245

A correct answer (and workarounds!) will be published here in about a week.

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.

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

Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

  • Use a single SELECT statement only.
  • At the time of employment start the rank of the employee by salary should be 2.
  • Show the top salary at the time when the employee started with the company.
  • We assume that no employees have ever been terminated since day 1.

Expected Result:

ENAME JOB SAL HIREDATE MAX_SAL
WARD SALESMAN 1250 22-FEB-81 1600
BLAKE MANAGER 2850 01-MAY-81 2975
FORD ANALYST 3000 03-DEC-81 5000
SCOTT ANALYST 3000 19-APR-87 5000

Solutions:

Solution #1. Using LATERAL view, RANK and cumulative MAX analytic functions (Oracle 12g+):

SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal 
FROM scott.emp e, LATERAL(SELECT a.empno,  
                                 RANK() OVER(ORDER BY a.sal DESC) rk, 
                                 MAX(a.sal) OVER() max_sal 
                          FROM scott.emp a 
                          WHERE a.hiredate<=e.hiredate) r 
WHERE e.empno=r.empno  
  AND rk=2 
ORDER BY e.hiredate

Solution #2. Using CTE, cumulative MAX analytic function and a correlated subquery with COUNT to mimic the filter by RANK:

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp a 
) 
SELECT * 
FROM x 
WHERE 1=(SELECT COUNT(*) 
         FROM scott.emp 
         WHERE hiredate<=x.hiredate 
           AND sal>x.sal) 
ORDER BY hiredate

Solution #3. Using CTE, cumulative MAX analytic function and an in-line scalar subquery in SELECT to mimic the RANK:

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal, 
       (SELECT COUNT(*)+1 
        FROM scott.emp 
        WHERE sal>e.sal  
          AND hiredate<=e.hiredate) rk 
FROM scott.emp e 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM x 
WHERE rk=2 
ORDER BY hiredate

Solution #4. Using self-join and Cartesian Product with aggregation:

SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal 
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate 
                                 AND b.sal>a.sal 
GROUP BY a.ename, a.job, a.hiredate, a.sal 
HAVING COUNT(DISTINCT b.empno)=1 
ORDER BY a.hiredate

Solution #5. Using CTE and cumulative MAX analytic function (twice):

WITH x AS ( 
SELECT ename, job, hiredate, sal, 
       MAX(sal) OVER(ORDER BY hiredate) max_sal 
FROM scott.emp  
), y  AS ( 
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2 
FROM x 
WHERE sal<max_sal 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM y 
WHERE sal=max_sal2 
ORDER BY hiredate

Solution #6. Using regular and recursive CTEs, ROWNUM, GREATEST, and CASE functions (no Analytic functions!):

WITH e AS ( 
SELECT ename, job, sal, hiredate 
FROM scott.emp 
ORDER BY hiredate 
), x AS ( 
SELECT ename, job, sal, hiredate, ROWNUM rn 
FROM e 
), y(max_sal, sal2, rn) AS ( 
SELECT sal, 0, 1 
FROM x 
WHERE rn=1 
UNION ALL 
SELECT GREATEST(x.sal, y.max_sal) AS max_sal, 
       CASE WHEN x.sal>y.max_sal THEN y.max_sal 
            WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal  
            ELSE y.sal2  
       END AS sal2, 
       x.rn 
FROM x JOIN y ON x.rn=y.rn+1 
) 
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal 
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal

Solution #7. Using CTE and MODEL clause to mimic Solution #6:

WITH x AS ( 
SELECT * 
FROM scott.emp 
MODEL 
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn) 
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2) 
RULES( 
    max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]), 
    sal2[rn>1] = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1] 
                      WHEN sal[CV()]> sal2[CV()-1]   
		       AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()]  
                      ELSE sal2[CV()-1] 
                 END 
     ) 
) 
SELECT ename, job, sal, hiredate, max_sal 
FROM x 
WHERE sal=sal2

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/

Further Reading:

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.

2018 Oracle SQL Puzzle of the Week #12

Top and Bottom Paid Employees

List top and bottom paid employees in each department without using UNION [ALL] operator

  • Use a single SELECT statement only.
  • SET Operators are not allowed
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
  • The scoring rules can be found here.
  • Solutions must be submitted as comments to this blog post.
  • Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result:

ENAME DEPTNO SAL
KING 10 5000
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
SMITH 20 800
BLAKE 30 2850
JAMES 30 950

A correct answer (and workarounds!) will be published here in about a week.

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

Mimic ROW_NUMBER function

Write a single SELECT statement that produces the same result as the following one:

SELECT e.*, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM scott.emp e
  • Use a single SELECT statement only.
  • Analytic functions are NOT allowed
  • Any SQL clauses that use PARTITION BY keywords are NOT allowed

Expected Result:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 1
7839 KING PRESIDENT 17-NOV-81 5000 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 2
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 2
7900 JAMES CLERK 7698 03-DEC-81 950 30 3
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 5
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 6

Solutions:

Solution #1. Using MATCH_RECOGNIZE clause

Credit to: Naoto Katayama

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,rn  
FROM scott.emp 
MATCH_RECOGNIZE ( 
ORDER BY deptno,ename,empno 
MEASURES RUNNING COUNT(*) AS rn 
ALL ROWS PER MATCH 
PATTERN (FIRSTROW NEXTROWS*) 
DEFINE 
   FIRSTROW AS PREV(FIRSTROW.deptno) IS NULL  
OR PREV(FIRSTROW.deptno) != FIRSTROW.deptno, 
   NEXTROWS AS PREV(NEXTROWS.deptno) = NEXTROWS.deptno 
)

Solution #2. Using Self-Join with Cartesian Product and GROUP BY

Partial Credit to: Boobal Ganesan

SELECT e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno,  
       COUNT(*) rn 
FROM scott.emp e1 LEFT OUTER JOIN scott.emp e2  
  ON e1.deptno = e2.deptno 
 AND e2.ename || ROWIDTOCHAR(e2.ROWID) <= e1.ename || ROWIDTOCHAR(e1.ROWID) 
GROUP BY e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno 
ORDER BY e1.deptno, COUNT(*)

Solution #3. Using CTE, ROWNUM, and arithmetic formula

WITH x AS ( 
SELECT * 
FROM scott.emp 
ORDER BY deptno, ename 
), y AS ( 
SELECT deptno, MIN(ROWNUM) min_rn 
FROM x 
GROUP BY deptno 
) 
SELECT x.*, ROWNUM-y.min_rn+1 AS rn 
FROM x JOIN y ON x.deptno=y.deptno 
ORDER BY x.deptno, x.ename

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.

2018 Oracle SQL Puzzle of the Week #11

Mimic ROW_NUMBER function

Write a single SELECT statement that produces the same result as the following one:

SELECT e.*, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM scott.emp e
  • Use a single SELECT statement only.
  • Analytic functions are NOT allowed
  • Any SQL clauses that use PARTITION BY keywords are NOT allowed
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
  • The scoring rules can be found here.
  • Solutions must be submitted as comments to this blog post.
  • Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 1
7839 KING PRESIDENT 17-NOV-81 5000 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 2
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 2
7900 JAMES CLERK 7698 03-DEC-81 950 30 3
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 5
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 6

A correct answer (and workarounds!) will be published here in about a week.

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.

12 Solutions to 2018 Oracle SQL Puzzle of the Week #10

Top Salary Puzzle

Find highest salary in each department without using MAX function

  • Use a single SELECT statement only.
  • For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar)

Expected Result:

DEPTNO MAX_SAL
10 5000
20 3000
30 2850

Solutions:

We will begin with a simpler problem that does allow us using functions.

Solution #1. Using MIN function

Credit to: Boobal Ganesan

MIN function can be seen as an opposite to the MAX, so it is trivial to employ it here:

SELECT deptno, -MIN(-sal) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

This is an “order” based approach that sorts the values within a concatenated string and then uses regular expression to cut the first token.

SELECT deptno,
       REGEXP_SUBSTR(LISTAGG(sal,',') 
                     WITHIN GROUP(ORDER BY sal DESC),'[^,]+',1,1) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #3. Using AVG(…) KEEP() group function

This is another “order” based strategy whete AVG function can be replaced with MIN or any other aggregate function that returns a single value out of a set of identical ones.

SELECT deptno, AVG(sal) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #4. Using Analytic function and CTE

ROW_NUMBER is chosen in this approach, though other analytic functions, such as RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, etc can be used here (with some changes) as well. ROW_NUMBER is convenient to use as it allows to avoid DISTINCT option.

WITH x AS (
SELECT deptno, sal, 
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rn
FROM scott.emp
)
SELECT deptno, sal max_sal
FROM x
WHERE rn=1
ORDER BY 1;

Solution #5. Using MATCH_RECOGNIZE clause

Credit to: KATAYAMA NAOTO

This approach is similar to the previous one if we used LAG analytic function: which would return NULL for the top record.

SELECT deptno, sal max_sal 
FROM scott.emp
MATCH_RECOGNIZE (
PARTITION BY deptno
ORDER BY sal DESC
ALL ROWS PER MATCH
PATTERN (ISNULL)
DEFINE ISNULL AS PREV(ISNULL.sal) IS NULL
);

Solution #6. CONNECT BY and CONNECT_BY_ISLEAF while avoiding Analytic functions

This approach is a bit artificial. We could have used DISTINCT and avoid START WITH clause completely.  CTEs x and y are used to simulate ROW_NUMBER analytic function.

WITH x AS (
SELECT deptno, sal
FROM scott.emp
ORDER BY 1,2
), y AS (
SELECT x.*, ROWNUM rn
FROM x
)
SELECT deptno, sal
FROM y
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY deptno=PRIOR deptno
       AND rn=PRIOR rn+1
START WITH (deptno, rn) IN (SELECT deptno, MIN(rn)
                            FROM y
                            GROUP BY deptno);

Solution #7. Using MODEL clause with ROW_NUMBER function

This method is pretty much the same as in the Solution #4 above. The RETURN UPDATED ROWS and dummy measures are used to only return rows with rn=1.

SELECT deptno, max_sal
FROM scott.emp
MODEL
RETURN UPDATED ROWS
PARTITION BY (deptno)
DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rn)
MEASURES(sal max_sal, 0 dummy)
RULES(
 dummy[1]=1
)
ORDER BY 1;

The following 5 solutions (##8-12) satisfy the “added complexity” term and do NOT use any functions at all.

Solution #8. Using ALL predicate

Generally speaking, >=ALL filter is identical to =(SELECT MAX() …). See my book for more detailed explanations.

SELECT deptno, sal max_sal
FROM scott.emp a
WHERE sal>=ALL(SELECT sal
               FROM scott.emp
               WHERE deptno=a.deptno)
GROUP BY deptno, sal
ORDER BY 1;

Solution #9. Using NOT EXISTS predicate

See Chapter 10 of my book for details.

SELECT deptno, sal max_sal
FROM scott.emp a
WHERE NOT EXISTS(SELECT 1
                 FROM scott.emp
                 WHERE deptno=a.deptno
                   AND sal>a.sal)
GROUP BY deptno, sal
ORDER BY 1;

Solution #10. Using Outer-Join with IS NULL filter

This approach is also covered very deeply in my book, Chapter 10.

SELECT a.deptno, a.sal max_sal
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno
                                      AND b.sal>a.sal 
WHERE b.empno IS NULL
GROUP BY a.deptno, a.sal
ORDER BY 1;

Solution #11. Using MINUS and ANY predicate

MINUS serves 2 purposes: it removes non-top rows and eliminates duplicates, so no DISTINCT option (or GROUP BY) is required.

SELECT deptno, sal max_sal 
FROM scott.emp
MINUS
SELECT deptno, sal
FROM scott.emp a
WHERE sal<ANY(SELECT sal 
              FROM scott.emp
              WHERE deptno=a.deptno);

Solution #12. Using MINUS and EXISTS predicate

Last two approaches covered in the drill from the Chapter 10 of my book.

SELECT deptno, sal max_sal 
FROM scott.emp
MINUS
SELECT deptno, sal
FROM scott.emp a
WHERE EXISTS(SELECT 1 
             FROM scott.emp
             WHERE deptno=a.deptno
               AND sal>a.sal);

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.