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

## Analyze all objects in current schema

Since Oracle version 10, ANALYZE command has been deprecated for statistics gathering. The command stays in Oracle and is intended to be used to list chained rows and validate structure.

The ANALYZE command was superseded by procedures in dbms_stats package.

Specifically, if you need to collect statistics for a schema, you need to use dbms_stats.gather_schema_stats procedure.

If you want to run it for the current schema with all default parameters use this:

`exec dbms_stats.gather_schema_stats(USER)`

The full signature of this procedure:

```DBMS_STATS.GATHER_SCHEMA_STATS (
ownname          VARCHAR2,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample     BOOLEAN  DEFAULT FALSE,
method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),