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.

Advertisements

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.

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.

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

 

Three Solutions to Puzzle of the Week #14

Puzzle of the Week #14:

For each department, find its share in the company’s total payroll. The puzzle should be solved with a single SELECT statement that does not utilize sub-queries, WITH clause, in-line views, temporary tables or PL/SQL functions

Expected Result:

DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

Solutions

#1: Using Analytic Functions (SUM)

SELECT DISTINCT deptno, 
                ROUND(100*SUM(sal)OVER(PARTITION BY deptno)/SUM(sal)OVER(),2) AS "Share, %"
FROM emp
ORDER BY 1
/

DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

#2: Using Cartesian Product

SELECT a.deptno, 
       ROUND(100*SUM(a.sal)*COUNT(DISTINCT a.ROWID)/(SUM(b.sal)*COUNT(DISTINCT b.ROWID)), 2) AS "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1
/
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

#3: Using SUM(DISTINCT ..) on a Cartesian Product

SELECT a.deptno,
       ROUND(100*TRUNC(SUM(DISTINCT a.sal+a.empno/1000000)) /
                 TRUNC(SUM(DISTINCT b.sal+b.empno/1000000)), 2) "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1;
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

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.

Solutions to Puzzle of the Week #8

Puzzle of the Week #8:

Find job titles represented in every department. Write a single SELECT statement only.

Expected Result: (Only clerks and managers work in all 3 departments: 10,20, and 30)

JOB
--------
CLERK
MANAGER

Solutions:

#1: Two COUNT(DISTINCT ..) in HAVING

SELECT job
FROM emp
GROUP BY job
HAVING COUNT(DISTINCT deptno)=(SELECT COUNT(DISTINCT deptno) FROM emp)

#2: Analytic COUNT(DISTINCT ..) with CONNECT BY

SELECT DISTINCT job
FROM (
SELECT job, deptno, LEVEL level#, COUNT(DISTINCT deptno) OVER() cnt
FROM emp
CONNECT BY job=PRIOR job
AND deptno>PRIOR deptno
)
WHERE level#=cnt

#3: Two Analytic COUNT(DISTINCT..)

WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT DISTINCT job
FROM x
WHERE cnt=cnt2

OR

WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT job
FROM x
WHERE cnt=cnt2
GROUP BY job

#4: Cartesian Product and Two COUNT(DISTINCT ..)

SELECT a.job
FROM emp a, emp b
GROUP BY a.job
HAVING COUNT(DISTINCT a.deptno)=COUNT(DISTINCT b.deptno)

#5: ROLLUP with RANK OVER COUNT(DISTINCT..)

WITH x AS (
SELECT job, COUNT(DISTINCT deptno) cnt, 
       RANK()OVER(ORDER BY COUNT(DISTINCT deptno)  DESC) rk
FROM emp
GROUP BY ROLLUP(job)
)
SELECT job
FROM x
WHERE rk=1
  AND job IS NOT NULL

#6: Analytic COUNT(DITSINCT..) comparison with MINUS

WITH x AS (
SELECT job, 
       CASE WHEN COUNT(DISTINCT deptno)OVER()=COUNT(DISTINCT deptno)OVER(PARTITION BY job) THEN 1 END
FROM emp
MINUS
SELECT job, NULL
FROM emp
)
SELECT job
FROM x

#7: No COUNT(DISTINCT ..) solution:

WITH x AS (
SELECT a.deptno, b.job, NVL(COUNT(c.empno),0) idx
FROM (SELECT DISTINCT deptno FROM emp) a CROSS JOIN (SELECT DISTINCT job FROM emp) b
    LEFT JOIN emp c ON a.deptno=c.deptno AND b.job=c.job
GROUP BY a.deptno, b.job
)
SELECT job
FROM x
GROUP BY job
HAVING MIN(idx)>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.

Solutions to Puzzle of the Week #7

Puzzle of the Week #7

For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

Expected Result:

EMPNO ENAME       SUM_ASCII
----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299
 7369 SMITH             389
 7844 TURNER            480
 7698 BLAKE             351
 7782 CLARK             365
 7654 MARTIN            459
 7839 KING              297
 7900 JAMES             368

Solutions:

Solution/Workaround #1: Oracle 12c and up Only (submitted by Zohar Elkayam)

WITH
    FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER 
    IS 
       x NUMBER:= 0;
    BEGIN
      FOR i IN 1..LENGTH(p_str) LOOP
          x := x + ASCII(SUBSTR(p_str, i, 1)) ;
      END LOOP;
      RETURN x;
    END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Variation of Solution #1 (Recursive function):

WITH
    FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER 
    IS 
    BEGIN
      IF p_str IS NULL THEN 
	RETURN 0;
      END IF;
      RETURN ASCII(p_str) + sumascii(SUBSTR(p_str,2));      
    END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Solution/Workaround #2: Cartesian Product with Generated Numeric Range (by Zohar Elkayam)

SELECT empno, ename, SUM(ASCII(ename_char)) sum_ascii
FROM (SELECT empno, ename, SUBSTR(ename, i, 1) ename_char
      FROM emp, (SELECT LEVEL i
                 FROM dual
                 CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                      FROM emp)                  )       WHERE LENGTH(ename)>=i
      )
GROUP BY empno, ename
/

Simplified variation of Workaround #2:

SELECT empno, ename, 
       SUM(ASCII(SUBSTR(ename, i, 1))) sum_ascii      
FROM emp, (SELECT LEVEL i
           FROM dual
           CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                FROM emp)            )  WHERE LENGTH(ename)>=i
GROUP BY empno, ename 
/

Solution/Workaround #3: In-Line Scalar Subquery

SELECT empno, ename, 
      (SELECT SUM(ASCII(SUBSTR(a.ename, LEVEL, 1)))
       FROM dual
       CONNECT BY LEVEL<=LENGTH(a.ename)) AS sum_ascii
FROM emp a
/

Solution #4/Workaround : Recursive WITH clause

WITH x(n, empno, ename, letter) AS (
SELECT 1 AS n, empno, ename, SUBSTR(ename, 1, 1)
FROM emp
UNION ALL
SELECT x.n+1, empno, ename, SUBSTR(ename, n+1, 1)
FROM x
WHERE LENGTH(ename)>=n+1
)
SELECT empno, ename, SUM(ASCII(letter)) sum_ascii
FROM x
GROUP BY empno, ename
/

Solution/Workaround #5: Use DUMP function and Regular Expressions (submitted by Sunitha)

SELECT empno, ename, SUM(REGEXP_SUBSTR(nm, '\d+', 1, occ)) AS sum_ascii
FROM (SELECT empno, ename, REGEXP_REPLACE(DUMP(ename), '.*: (\d.*)$', '\1') nm
      FROM emp), 
     (SELECT LEVEL occ FROM dual CONNECT BY LEVEL <=ANY(SELECT LENGTH(ename) FROM emp))
GROUP BY empno, ename
/

Solution/Workaround #6: Use LATERAL View (Oracle 12c and up)

SELECT empno, ename, sum_ascii
FROM emp e, LATERAL (SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
                     FROM dual
                     CONNECT BY LEVEL<=LENGTH(e.ename) ) x

Solution/Workaround #7: Use TABLE/CAST/MULTISET function composition

SELECT empno, ename, x.column_value AS sum_ascii
FROM emp e, 
     TABLE(CAST(MULTISET(SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
                         FROM dual
                         CONNECT BY LEVEL<=LENGTH(e.ename) 
                         ) AS sys.odcinumberlist
                )
          ) 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”.

 

Solutions to Puzzle of the Week #6

Puzzle of the Week #6

Find all employees who is paid one of top 4 salaries in the entire company without using sub-queries and in-line views/WITH clause.

Expected Result:

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

 

Method/Workaround #1: Use Aggregation over Cartesian Product

This method works in all RDBMS systems, such as Oracle, SQL Server, Sybase, Teradata, etc.

SELECT a.ename, a.sal
FROM emp a, emp b
WHERE a.sal<=b.sal
GROUP BY a.ename, a.sal
HAVING COUNT(DISTINCT b.sal)<=4
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This method is described in details in my book Oracle SQL Tricks and Workarounds.

Method/Workaround #2: Use DENSE_RANK and MINUS

This method is more Oracle specific, but it is as good as the 1st one. It was suggested by one of the contest participants:

SELECT ename, 
       CASE WHEN DENSE_RANK()OVER(ORDER BY sal DESC)<=4 THEN sal END sal
FROM emp
MINUS
SELECT ename, NULL
FROM emp
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This is a very elegant variation of the following query with MINUS substituting the filter for analitic function result:

SELECT ename, sal
FROM (SELECT ename, sal, DENSE_RANK()OVER(ORDER BY sal DESC) rk
      FROM emp)
WHERE rk<=4;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

Interestingly enough, Teradata SQL has a special clause QUALIFY for that matter. It is very elegant and maybe one day it will become an ANSII standard:

SELECT ename, sal
FROM emp
QUALIFY DENSE_RANK()OVER(ORDER BY sal DESC)<=4

Finally, I would like to share a couple of more traditional approaches that DO USE subqueries:

SELECT ename, sal
FROM emp a
WHERE 4>=(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>=a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
SELECT ename, sal
FROM emp a
WHERE 4>(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

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.

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.



Interview Question: Get the 2nd highest salary in the company

Question: Get the 2nd highest salary in the company.

Question level: Intermediate

We picked just 6 workarounds for this fairly simple problem. Overall, there are at least 15 different approaches available.

Method/Workaround #1 (Level: Beginner)

SELECT MAX(SAL)
FROM (SELECT SAL
      FROM EMP
      WHERE SAL<(SELECT MAX(SAL) 
                 FROM EMP)
     )

Method/Workaround #2 (Level: Intermediate)

SELECT sal
FROM (SELECT sal, ROWNUM rn
      FROM (SELECT SAL
            FROM EMP
            GROUP BY SAL
            ORDER BY 1 DESC)
      WHERE ROWNUM<=2
      )
WHERE RN=2 

Method/Workaround #3 (Level: Intermediate)

SELECT DISTINCT sal
FROM (SELECT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2      

Method/Workaround #4 (Level: Intermediate)

SELECT SAL
FROM (SELECT DISTINCT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2     

Method/Workaround #5 (Level: Advanced)

SELECT A.SAL
FROM EMP A JOIN EMP B ON A.SAL<=b.SAL
GROUP BY a.sal
HAVING COUNT(DISTINCT b.sal)=2

Method/Workaround #6 (Level: Intermediate)

SELECT DISTINCT SAL
FROM emp a
WHERE 2=(SELECT COUNT(DISTINCT sal)
         FROM emp b
         WHERE b.sal>=a.sal) 

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