2018 Oracle SQL Puzzle of the Week #7

Namesake Puzzle

Show groups of employees having the same last name.

  • Use a single SELECT statement only
  • Use hr.employees table
  • 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:

LAST_NAME FIRST_NAME DEPARTMENT_ID EMPLOYEE_ID
Cambrault Gerald 80 148
Cambrault Nanette 80 154
Grant Douglas 50 199
Grant Kimberely 178
King Janette 80 156
King Steven 90 100
Smith Lindsey 80 159
Smith William 80 171
Taylor Jonathon 80 176
Taylor Winston 50 180

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.

Advertisements

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.

 

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
  • 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:

TEAM_NO TEAM_MEMBER
1 ADAMS
1 FORD
1 SMITH
2 ADAMS
2 SCOTT
2 SMITH
3 ADAMS
3 JONES
3 SMITH
4 FORD
4 SCOTT
4 SMITH
5 ADAMS
5 FORD
5 SCOTT
6 FORD
6 JONES
6 SMITH
7 ADAMS
7 FORD
7 JONES
8 FORD
8 JONES
8 SCOTT
9 JONES
9 SCOTT
9 SMITH
10 ADAMS
10 JONES
10 SCOTT

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 the 2018 Oracle SQL Puzzle of the Week #5

Puzzle of the Week #5:

Find the shortest and longest last names of the employees in each department.

 

  • If two or more employees tie for the shortest or longest name, pick the name that comes first in alphabetical order
  • Use hr.employees or scott.emp tables
  • Use a single SELECT statement only
  • Ideally, the solution should NOT rely on any sub-queries, CTEs  (WITH clause), or inline views
  • Exclude unknown (NULL) departments

Solutions:

#1. Using MIN() KEEP Group Function

SELECT department_id, 
 MIN(last_name) KEEP(DENSE_RANK FIRST 
                     ORDER BY LENGTH(last_name)) shortest,
 MIN(last_name) KEEP(DENSE_RANK FIRST 
                     ORDER BY LENGTH(last_name) DESC) longest
FROM hr.employees
WHERE department_id IS NOT NULL
GROUP BY department_id

#2. Using FIRST_VALUE Analytic Function and DISTINCT option

(Credit to Igor Shpungin)

SELECT DISTINCT department_id, 
   FIRST_VALUE(last_name) OVER(PARTITION BY department_id 
                               ORDER BY LENGTH(last_name))      shortest, 
   FIRST_VALUE(last_name) OVER(PARTITION BY department_id 
                               ORDER BY LENGTH(last_name) DESC) longest 
FROM hr.employees 
WHERE department_id IS NOT NULL 
ORDER BY 1

#3. Using MODEL clause

(Credit to Naoto Katayama)

SELECT department_id, shortest, longest
FROM hr.employees
WHERE department_id IS NOT NULL
MODEL
RETURN UPDATED ROWS
PARTITION BY (department_id)
DIMENSION BY (
 ROW_NUMBER()OVER(PARTITION BY department_id 
                  ORDER BY LENGTH(last_name), last_name) rn1,
 ROW_NUMBER()OVER(PARTITION BY department_id 
                  ORDER BY LENGTH(last_name) DESC, last_name) rn2)
MEASURES(last_name, 
         CAST(NULL AS VARCHAR2(25)) AS shortest, 
         CAST(NULL AS VARCHAR2(25)) AS longest)
RULES(
      shortest[0,0]=MAX(last_name)[1,ANY], 
      longest [0,0]=MAX(last_name)[ANY,1]
)
ORDER BY department_id

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

Find the shortest and longest last names of the employees in each department.

  • If two or more employees tie for the shortest or longest name, pick the name that comes first in alphabetical order
  • Use hr.employees or scott.emp tables
  • Use a single SELECT statement only
  • Ideally, the solution should NOT rely on any sub-queries, CTEs  (WITH clause), or inline views
  • Exclude unknown (NULL) departments
  • More information on the challenge can be found here.

Expected Result (for hr.employees table):

DEPARTMENT_ID SHORTEST LONGEST
10 Whalen Whalen
20 Fay Hartstein
30 Khoo Colmenares
40 Mavris Mavris
50 Gee Mikkilineni
60 Ernst Pataballa
70 Baer Baer
80 Fox Livingston
90 King De Haan
100 Chen Greenberg
110 Gietz Higgins

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

2018 Puzzle of the Week #4:

Calculate Mutual Funds’ Performance

For a given table fund_performance (see the CREATE TABLE statement below), calculate each fund’s performance over the 6-month period from Jan-2016 till Jun-2016.

  • Use a single SELECT statement
  • Performance is calculated as a multiplication of all the months’ performance rates for the given time frame
  • The solution should work for any time frame, so treat from-month and to-month as query parameters
  • DDL command:
  • CREATE TABLE fund_performance AS
    SELECT 1 fund_id, '2016-01' perf_month, 1.05 perf_rate
    FROM dual 
    UNION ALL
    SELECT 1, '2016-02', 1.02 FROM dual UNION ALL
    SELECT 1, '2016-03', 0.92 FROM dual UNION ALL
    SELECT 1, '2016-04', 1.01 FROM dual UNION ALL
    SELECT 1, '2016-05', 1.04 FROM dual UNION ALL
    SELECT 1, '2016-06', 0.95 FROM dual UNION ALL
    SELECT 2, '2016-01', 1.04 FROM dual UNION ALL
    SELECT 2, '2016-02', 1.03 FROM dual UNION ALL
    SELECT 2, '2016-03', 0.98 FROM dual UNION ALL
    SELECT 2, '2016-04', 1.04 FROM dual UNION ALL
    SELECT 2, '2016-05', 1.01 FROM dual UNION ALL
    SELECT 2, '2016-06', 0.98 FROM dual;
    

    Expected Result:

    FUND_ID Cumulative Performance
    1 0.98
    2 1.08

Solutions:

Solution #1: Using Math Formula (Sum of Logs = Log of Product)

SELECT fund_id, ROUND(EXP(SUM(LN(perf_rate))),2) "Cumulative Performance" 
FROM fund_performance 
WHERE perf_month BETWEEN '2016-02' AND '2016-05' 
GROUP BY fund_id 
ORDER BY 1

Solution #2: Using Dynamic XML Query with XMLTYPE

(Credit to: Katayama Naoto)

SELECT fund_id,  
       ROUND(TO_NUMBER(EXTRACTVALUE(XMLTYPE(
		dbms_xmlgen.getxml('SELECT '|| LISTAGG(perf_rate,'*') 
                                      WITHIN GROUP(ORDER BY perf_month)||' C 
				    FROM dual')),'/ROWSET/ROW/C')),2) AS "cumulative performance" 
FROM fund_performance 
WHERE perf_month BETWEEN '2016-01' AND '2016-06' 
GROUP BY fund_id 
ORDER BY 1

Solution #3: Using Dynamic XML with XMLQUERY

(Credit to: Boobal Ganesan)

SELECT fund_id, 
       ROUND(TO_NUMBER(XMLQUERY((LISTAGG(perf_rate,'*') 
	             WITHIN GROUP(ORDER BY fund_id)) RETURNING CONTENT)),2) "cumulative performance" 
FROM fund_performance 
WHERE perf_month BETWEEN '2016-01' AND '2016-06' 
GROUP BY fund_id

Solution #4: Using Model Clause with 2 measures

(Credit to: Katayama Naoto)

WITH x AS ( 
SELECT fund_id, cump, flag 
FROM fund_performance 
WHERE perf_month BETWEEN '2016-01' AND '2016-06' 
MODEL 
PARTITION BY (fund_id) 
DIMENSION BY (ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) AS N) 
MEASURES(perf_rate, 
         CAST(0 AS NUMBER) AS cump, 
         CAST(0 AS NUMBER) AS flag) 
RULES( 
      cump[ANY] ORDER BY N = perf_rate[CV(N)] * NVL(cump[CV(N)-1],1),
      flag[ANY] ORDER BY N = NVL2(perf_rate[CV(N)+1],0,1)
     )
)
SELECT fund_id, ROUND(cump,2) "Cumulative Performance" 
FROM x
WHERE flag=1 
ORDER BY fund_id

Solution #5: Using Model clause with 1 measure

WITH d AS (
SELECT fund_id, perf_month, perf_rate, 
       RANK()OVER(PARTITION BY fund_id ORDER BY perf_month DESC) rk
FROM fund_performance 
WHERE perf_month BETWEEN '2016-01' AND '2016-06' 
), x AS ( 
SELECT * 
FROM d
MODEL 
  PARTITION BY (fund_id) 
  DIMENSION BY (ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) AS N) 
  MEASURES     (perf_rate, rk, CAST(0 AS NUMBER) AS cump) 
  RULES        (cump[ANY] ORDER BY N = perf_rate[CV(N)] * NVL(cump[CV(N)-1],1) )
)
SELECT fund_id, ROUND(cump,2) "Cumulative Performance" 
FROM x
WHERE rk=1 
ORDER BY fund_id

Solution #6: Using Recursive CTE

WITH d AS (
SELECT fund_id, perf_rate, 
       ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) rn,
       COUNT(*)OVER(PARTITION BY fund_id) cnt
FROM fund_performance 
WHERE perf_month BETWEEN '2016-01' AND '2016-06' 
), x(fund_id, cum_perf, rn, cnt) AS (
SELECT fund_id, perf_rate, 1, cnt
FROM d
WHERE rn=1
UNION ALL
SELECT x.fund_id, x.cum_perf*d.perf_rate, d.rn, d.cnt
FROM x JOIN d ON x.fund_id=d.fund_id
             AND x.rn+1=d.rn
)
SELECT fund_id, ROUND(cum_perf,2) "Cumulative Performance"
FROM x
WHERE rn=cnt

Solution #7: Using 12c new Function based WITH clause

(Credit to: Katayama Naoto)

WITH
FUNCTION product(list IN sys.odcinumberlist) RETURN NUMBER IS
   v_result NUMBER DEFAULT 1;
BEGIN
  FOR i IN list.FIRST .. list.LAST LOOP
      v_result := v_result * list(i);
  END LOOP;
  RETURN v_result;
END;
SELECT fund_id, product(CAST(COLLECT(perf_rate) AS sys.odcinumberlist)) AS "Cumulative Performance"
FROM fund_performance
GROUP BY fund_id
ORDER BY 1

You can execute first 6 of 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.

2018 Oracle SQL 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

A correct answer (and workarounds!) will be published here in 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 #2

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.

 

2018 Oracle SQL Puzzle of the Week #2

Random Selection per Employee Salary Range

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

A correct answer (and workarounds!) will be published here in 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.