Puzzle of the Week #19 – Department Salary Report

Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

  • Use Single SELECT statement only
  • DECODE and CASE functions are not allowed
  • An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
  • The query should work in Oracle 11g.

Expected Result:

ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
ADAMS                       1100
JAMES                                   950
FORD                        3000
MILLER           1300

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

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

5 thoughts on “Puzzle of the Week #19 – Department Salary Report

  1. Taroon Ray August 1, 2016 / 11:46 pm

    WITH A AS(
    SELECT SAL, EMPNO, ENAME, DEPTNO FROM EMP
    )
    SELECT ENAME,
    (SELECT SAL FROM EMP WHERE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 10) AS “10”,
    (SELECT SAL FROM EMP WHERE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 20) AS “20”,
    (SELECT SAL FROM EMP WHETE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 30) AS “30”
    FROM A;

  2. Krishna Jamal August 1, 2016 / 11:53 pm

    SELECT * FROM
    (SELECT Deptno, Ename, Sal FROM Emp)
    PIVOT (SUM(Sal) FOR Deptno IN (10, 20, 30));

  3. Krishna Jamal August 2, 2016 / 4:36 am

    SELECT E.Ename,
    (SELECT E1.Sal FROM Emp E1 WHERE E1.Empno=E.Empno AND E1.Deptno=10) “10”,
    (SELECT E2.Sal FROM Emp E2 WHERE E2.Empno=E.Empno AND E2.Deptno=20) “20”,
    (SELECT E3.Sal FROM Emp E3 WHERE E3.Empno=E.Empno AND E3.Deptno=30) “30”
    FROM Emp E;

  4. praneeth August 5, 2016 / 2:02 am

    select ename, sal “10”, null “20”, null “30” from emp where deptno=10
    union
    select ename, null, sal, null from emp where deptno=20
    union
    select ename, null, null, sal from emp where deptno=30;

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