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

Interview Question: Get top and bottom paid employees in each department

This is a typical interview problem: list all bottom and top paid employees in each department. A preferred solution should not be using UNION or UNION ALL operators.

Please watch this short video to learn a couple of non-obvious techniques and to impress your potential employers on your next job interview.

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.

 

SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.job=b.job
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #1.1 – Generic substitution for NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job
           AND a.empno!=b.empno)
ORDER BY deptno, job

Solution #2: Using NOT IN

SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
                           FROM emp b
                           WHERE a.empno!=b.empno)
ORDER BY deptno, job

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Solution #4: Using Analytical function COUNT

WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

WITH x AS (
SELECT ename, deptno, job, sal, 
       MAX(empno) OVER(PARTITION BY deptno, job) max_no,
       MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job

Solution #5: Using In-Line view (WITH)

WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

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

SQL Puzzle: Find top management in each department – with multiple workaround solutions, by Zahar Hilkevich

I was recently asked by a colleague at work to help him solving a problem that can be interpreted in scott’s emp table terms as the following:

In each department, list all managers and a president, but in the department where the president works, all managers (if any) should be filtered out.

If we look at all managers/president(s), we will see the following result:

ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
BLAKE      MANAGER           30
CLARK      MANAGER           10
KING       PRESIDENT         10

Our desired result should exclude CLARK.

Solution/Workaround #1:

SELECT ename, job, deptno
FROM emp a
WHERE job='PRESIDENT'
  OR (job='MANAGER' AND NOT EXISTS(SELECT 1
                                   FROM emp b
                                   WHERE b.deptno=a.deptno
                                     AND job='PRESIDENT')
     )
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

This is the most straight-forward solution and it barely requires any explanation.

Workaround #2:

WITH x AS (
SELECT ename, job, deptno, 
       RANK()OVER(PARTITION BY deptno 
                  ORDER BY DECODE(job,'PRESIDENT',1,'MANAGER',2)) rk
FROM emp a
WHERE job IN ('PRESIDENT', 'MANAGER')
)
SELECT ename, job, deptno
FROM x
WHERE rk=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

Analytical functions make the solution very simple. Here, we use custom sorting (in the ORDER BY) with the RANK function.

Workaround #3:

WITH x AS (
SELECT ename, job, deptno,
       COUNT(DISTINCT job)OVER(PARTITION BY deptno) cnt
FROM emp a
WHERE job IN ('MANAGER', 'PRESIDENT')
)
SELECT ename, job, deptno
FROM x
WHERE job='PRESIDENT' OR cnt=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

This solution shows a use of COUNT(DISTINCT …)OVER() analytical function.

Workaround #4:

SELECT ename, job, deptno 
FROM emp 
WHERE (deptno, DECODE(job,'PRESIDENT',1,'MANAGER',2)) IN 
  (SELECT deptno, MIN(DECODE(job,'PRESIDENT',1,'MANAGER',2))
   FROM emp
   GROUP BY deptno)
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30

Another example of the use of custom order hidden in the multi-column subquery.

There are at least 3-5 other workarounds available for this puzzle.

You will have no problems uncovering them after reading my book “Oracle SQL Tricks and Workarounds”.