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.

Advertisements

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:

    http://forums.oracle.com/forums/thread.jspa?messageID=4311622

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: