## SQL Puzzle

### June 24, 2010

Given the two tables `EMP` and `DEPT`, what query could produce the following output?

```DNAME          SUM(SAL)
-------------- ----------------------
SALES          9400
SALES          12000
RESEARCH       10875
ACCOUNTING     8750
```

Hint: it requires putting couple extra rows into the standard `EMP` and `DEPT` tables.

### 5 Responses to “SQL Puzzle”

1. INSERT INTO dept
VALUES (50, ‘SALES’, ‘CHICAGO’);

INSERT INTO emp
VALUES (8839, ‘KING’, ‘PRESIDENT’, NULL, ’17-NOV-81′, 12000, NULL, 50);

SELECT dname, sal
FROM (SELECT dept.deptno, dname, SUM (sal) sal
FROM dept, emp
WHERE dept.deptno = emp.deptno
GROUP BY dept.deptno, dname)
ORDER BY 1 DESC;

• only one row in EMP

`insert into lsc_emp(empno,deptno,sal) values (-1,30,2600);`

``` ```

```select dname,sum(sal) from dept natural join emp group by grouping sets ((dname,deptno),(dname,deptno,sign(empno))) having (grouping(sign(empno))=0 or dname='SALES') and max(empno)!=-1 order by 1 desc,2; ```

What did you have in mind? Venkiru solution is the first that came up in my mind. Or having ‘SALES ‘ + ‘SALES’

2. The query I had in mind is

SELECT (SELECT dname
FROM dept
WHERE dept.deptno = emp.deptno),
SUM (sal)
from emp
GROUP BY deptno

It was triggered by a bug in SQL Developer automated group by generation:

The other way to write it

SELECT dname, (SELECT SUM(sal)
FROM emp
WHERE dept.deptno = emp.deptno)
from dept

With not bijective DEPTNO and DNAME both queries give a result which is different from ubiqutous join then group by.

3. SALES 9400
SALES 12000

the queries you provided do not provide two groups for SALES.