9 Solutions to 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

Solutions:

Solution #1: Using Subquery with HAVING clause:

SELECT first_name, last_name, department_id, employee_id 
FROM hr.employees 
WHERE last_name IN (SELECT last_name 
                    FROM hr.employees 
		    GROUP BY last_name 
		    HAVING COUNT(*)>1) 
ORDER BY 2,1

Solution #2: Using Multi-Column Subquery with NO HAVING clause

SELECT first_name, last_name, department_id, employee_id 
FROM hr.employees 
WHERE (last_name, 1) IN (SELECT last_name, SIGN(COUNT(*)-1) 
                         FROM hr.employees 
			 GROUP BY last_name) 
ORDER BY 2,1

Solution #3: Using Subquery with IN operator

SELECT first_name, last_name, department_id, employee_id 
FROM hr.employees a 
WHERE last_name IN (SELECT b.last_name 
                    FROM hr.employees b 
		    WHERE a.employee_id!=b.employee_id) 
ORDER BY 2,1

Solution #4: Using Self-Join with duplicate elimination in GROUP BY

SELECT a.first_name, a.last_name, a.department_id, a.employee_id 
FROM hr.employees a JOIN hr.employees b ON a.last_name=b.last_name 
                                       AND a.employee_id!=b.employee_id 
GROUP BY a.first_name, a.last_name, a.department_id, a.employee_id 
ORDER BY 2,1

Solution #5: Using a filter by COUNT analytic function with PARTITION BY

WITH x AS ( 
SELECT first_name, last_name, department_id, employee_id,  
       COUNT(*) OVER(PARTITION BY last_name) cnt 
FROM hr.employees 
)	 
SELECT first_name, last_name, department_id, employee_id 
FROM x 
WHERE cnt>1 
ORDER BY 2,1

Solution #6: Mimicking COUNT analytic function with MODEL clause

(credit to Naoto Katayama)

WITH x AS ( 
SELECT first_name, last_name, department_id, employee_id, cnt 
FROM hr.employees 
MODEL 
RETURN UPDATED ROWS 
DIMENSION BY (last_name, employee_id) 
MEASURES(first_name, department_id, 0 AS cnt) 
RULES (cnt[ANY, ANY]=COUNT(*)[CV(), ANY]) 
) 
SELECT first_name, last_name, department_id, employee_id 
FROM x 
WHERE cnt>1 
ORDER BY 2,1

Solution #7: Filtering by LEAD and LAG analytic functions

WITH x AS ( 
SELECT first_name, last_name, department_id, employee_id,  
       LAG (last_name,1) OVER(ORDER BY last_name) lag_name, 
       LEAD(last_name,1) OVER(ORDER BY last_name) lead_name 
FROM hr.employees 
)	 
SELECT first_name, last_name, department_id, employee_id 
FROM x 
WHERE last_name IN (lag_name, lead_name) 
ORDER BY 2,1

Solution #8: Using MODEL clause with dummy measure for SIGN over analytic function expression

SELECT first_name, last_name, department_id, employee_id
FROM hr.employees 
MODEL 
RETURN UPDATED ROWS 
PARTITION BY (last_name) 
DIMENSION BY (SIGN(COUNT(*) OVER(PARTITION BY last_name)-1) AS n, 
              employee_id) 
MEASURES(first_name, department_id, 0 AS dummy) 
RULES (dummy[1, ANY]=1) 
ORDER BY 2,1

Solution #9: Using UNPIVOT with DISTINCT option over CONNECT BY with PRIOR

WITH x AS (
SELECT first_name curr_first, last_name, department_id curr_dept, employee_id curr_id, 
       PRIOR first_name prior_first, PRIOR department_id prior_dept, PRIOR employee_id prior_id
FROM hr.employees
WHERE level=2
CONNECT BY last_name=PRIOR last_name AND employee_id>PRIOR employee_id
)
SELECT DISTINCT first_name, last_name, department_id, employee_id
FROM x
UNPIVOT( 
    (first_name, department_id, employee_id)  for dummy IN ((curr_first, curr_dept, curr_id),
                                                            (prior_first,prior_dept,prior_id))
)
ORDER BY 2,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.

Advertisements

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.