Interview Question: Count number of every week day in a year

Interview Question: With a single SELECT statement get the number of each week day in the current year.

Level: Intermediate

Expected Result:

Day                                  Days in Year
------------------------------------ ------------
SUNDAY                                         52
MONDAY                                         52
TUESDAY                                        52
WEDNESDAY                                      52
THURSDAY                                       52
FRIDAY                                         53
SATURDAY                                       53

Solution #1:

WITH x AS (
SELECT LEVEL-1+TRUNC(SYSDATE, 'YYYY') AS dd
FROM dual
CONNECT BY TRUNC(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'YYYY')=TRUNC(SYSDATE, 'YYYY')
)
SELECT TO_CHAR(dd, 'DAY') "Day", COUNT(*) "Days in Year"
FROM x
GROUP BY TO_CHAR(dd, 'DAY'), TO_CHAR(dd, 'D')
ORDER BY TO_CHAR(dd, 'D');

Explanation:

The WITH clause returns all days in the current year, this is a common trick used in majority of sql puzzle related to a calendar. The connect by query used in the WITH generated a date range which starts on TRUNC(SYSDATE, ‘YYYY’) – i.e. the 1st day of the year – and continues as long as the next day falls into the same year (see condition in the CONNECT BY clause). The main query groups by day name – TO_CHAR(dd, ‘DAY’) – and sorts by day number (in a week) – TO_CHAR(dd, ‘D’).

Solution #2:

WITH x AS (
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'), 12)-1, 'DDD') days_in_year
FROM dual
)
SELECT TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'DAY') "Day",
       CASE WHEN MOD(days_in_year,52)>=LEVEL THEN 53
            ELSE 52
       END "Days in Year"
FROM x
CONNECT BY LEVEL<=7
ORDER BY TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'D');

Explanation:

The idea behind this solution is totally different than in the 1st one. A year has 52 weeks and 1 or 2 days depending on whether it is a  leap year or not. So each day of the week happens 52 times a year + first one or two days of the year make corresponding week days have 53 days in that same year. If we know the number of days in a year (365 or 366) we can find out which days of the week will happen 53 times. For that matter we can take MOD(days_in_year, 52) expression that will return either 1 or 2. If the day order number within a year is 1 (or 2 for the leap year) we know that the corresponding week day will occur 53 times, otherwise – 52.

The WITH clause returns number of days in the current year. We get that by taking the 1st day of the current year: TRUNC(SYSDATE,’YYYY’), adding 12 months to it and subtract 1 day to get the last day of the current year. Taking TO_CHAR(…, ‘DDD’) – gives us the order number of that day in the year which is exactly the number of days in the current year.

The main query generates the date range from Jan-1 to Jan-7 in the current year, and assigns 52 or 53 to the 2nd column based on the logic described above.

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

Two ways to build a salary range report without using CASE function

Interview Question: Produce a salary range report with a single SELECT statement. Decode function is allowed, CASE function – is not.

Level: Intermediate

Expected Result:

RANGE                             Employees
-------------------------------- ----------
0-999                                     2
1000-2999                                 9
3000-5999                                 3

Strategy #1:

SELECT COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
                DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
                DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
                ) AS range,
       COUNT(*) "Employees"
FROM emp
GROUP BY COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
                  DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
                  DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
                  )
ORDER BY 1

Explanation:

In Oracle SQL terms, a mathematical condition

a <=x <=b

can be  interpreted as

x BETWEEN a AND b

however, this condition is good only for CASE function, and not for DECODE. The trick is to use another interpretation:

LEAST(b,x)=GREATEST(x,a)

– that can be used in DECODE.

CASE-based Solution:

SELECT CASE WHEN sal<=999 THEN '0-999'
            WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
            WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
       END AS range,
       COUNT(*) "Employees"
FROM emp
GROUP BY CASE WHEN sal<=999 THEN '0-999'
              WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
              WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
         END
ORDER BY 1

Strategy #2:

WITH x AS (
SELECT DECODE(1, (SELECT COUNT(*) FROM dual WHERE emp.sal<=999), '0-999',
                 (SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 1000 AND 2999), '1000-2999',
                 (SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 3000 AND 5999), '3000-5999'
             ) AS range
FROM emp
)
SELECT range, COUNT(*) AS "Employees"
FROM x
GROUP BY range
ORDER BY 1

Explanation:
This query demonstrates how to mimic CASE function using DECODE and in-line scalar subquery from dual.

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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/

How to delete duplicate records without using Joins and aggregate functions

Interview Question: How to delete duplicate records without using Joins and aggregate functions

Level: Intermediate/Advanced

Answer/Solution:

DELETE
FROM emp_dups a
WHERE ROWID<ANY(SELECT ROWID
                FROM emp_dups b
                WHERE b.empno=a.empno)

Explanation:

If we were allowed to use group functions, we could have used a very well known strategy:

DELETE 
FROM emp_dups a
WHERE ROWID<(SELECT MAX(ROWID)
             FROM emp b
             WHERE b.empno=a.empno)

Since group functions are not allowed, we need to find a substitution. In our case, the solution is based on the equivalence of the following 2 conditions:

a<(SELECT MAX(a) ...)
a<ANY(SELECT a ...)

Another example of such equivalent conditions:

a=(SELECT MAX(a) ...)
a>=ALL(SELECT a ...)

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

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

 

Interview Question: Show Odd/Even rows without using any functions and pseudo-columns

Sushil Kumar, Database Developer at JP Morgan Chase & Co, has recently asked me this question on my Facebook group page. My first reaction was: “What a silly question! Of course it is impossible to identify odd and even rows without using functions”. But shortly after that, I realized that this is a great SQL puzzle. It took me about 30 minutes (which is a lot!) to solve it.

Interview Question: Show Odd/Even rows without using any functions and pseudo-columns

Level: Advanced

Sample Expected Result:

ENAME           EMPNO   ODD_EVEN
---------- ---------- ----------
MILLER           7934          1
FORD             7902          0
JAMES            7900          1
ADAMS            7876          0
TURNER           7844          1
KING             7839          0
SCOTT            7788          1
CLARK            7782          0
BLAKE            7698          1
MARTIN           7654          0
JONES            7566          1
WARD             7521          0
ALLEN            7499          1
SMITH            7369          0

--Note: Rows are sorted by empno

The idea behind the following solution is quite simple: substitute functions with operators and predicates. Several similar techniques were described in my book Oracle SQL Tricks and Workarounds.

Solution:

WITH x (ename, empno, odd_even) AS
(
SELECT ename, empno, 1 as odd_even
FROM emp
WHERE empno>=ALL(SELECT empno FROM emp)
UNION ALL
SELECT e.ename, e.empno, 1-odd_even
FROM emp e, x
WHERE e.empno>=ALL(SELECT empno FROM emp WHERE empno<x.empno)
)
SELECT *
FROM x
/

ENAME           EMPNO   ODD_EVEN
---------- ---------- ----------
MILLER           7934          1
FORD             7902          0
JAMES            7900          1
ADAMS            7876          0
TURNER           7844          1
KING             7839          0
SCOTT            7788          1
CLARK            7782          0
BLAKE            7698          1
MARTIN           7654          0
JONES            7566          1
WARD             7521          0
ALLEN            7499          1
SMITH            7369          0


If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

Interview Question: Show Location for every employee without using joins

Interview Question: Show Location for every employee without using joins

Level: Intermediate

Expected Result:

ENAME          DEPTNO LOCATION
---------- ---------- ---------
CLARK              10 NEW YORK
KING               10 NEW YORK
MILLER             10 NEW YORK
ADAMS              20 DALLAS
FORD               20 DALLAS
JONES              20 DALLAS
SCOTT              20 DALLAS
SMITH              20 DALLAS
ALLEN              30 CHICAGO
BLAKE              30 CHICAGO
JAMES              30 CHICAGO
MARTIN             30 CHICAGO
TURNER             30 CHICAGO
WARD               30 CHICAGO

Method/Workaround #1: Use Aggregation over Cartesian Product

SELECT e.ename, e.deptno, MAX(DECODE(e.deptno,d.deptno, d.loc)) location
FROM emp e, dept d
GROUP BY e.ename, e.deptno
ORDER BY 2,1;

Method/Workaround #2: Use In-Line Scalar Subquery

SELECT e.ename, e.deptno, 
       (SELECT loc FROM dept d WHERE deptno=e.deptno) location
FROM emp e
ORDER BY 2,1;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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



Common rule to create or validate the GROUP BY clause

Level: Intermediate

Do you know that the content of the GROUP BY clause can be derived from the rest of the clauses in SELECT statement?

Well, it is true to a great extent. What can be derived is a combination of columns and expressions that must be included in GROUP BY.

We will start a demonstration with a simple puzzle:

Complete GROUP BY Clause in the following query:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno
GROUP BY _______________________________
HAVING e.job=’SALESMAN’
   AND SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

If you are not sure about the answer, learn the following rule.

Common rule to create or validate the GROUP BY clause:

It should at least include all columns and scalar expressions (constants, aggregate functions, and analytical functions are excluded) referenced in SELECT, HAVING, and ORDER BY clauses. Other columns can be specified as well, but they don’t have to.

Here is a solution to the above puzzle:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno
GROUP BY d.deptno, e.job, TO_CHAR(e.hiredate,'YYYY')
HAVING e.job=’SALESMAN’
   AND SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

Note that logically e.job=’SALESMAN’ condition does not belong to HAVING clause. If we push it to WHERE clause (where it belongs to), the group by clause may change:

SELECT 'Salesmen Report' AS "Report Title", 
       d.deptno, COUNT(e.empno) cnt
FROM dept d, emp e 
WHERE d.deptno=e.deptno AND e.job=’SALESMAN’
GROUP BY d.deptno, TO_CHAR(e.hiredate,'YYYY')
HAVING SUM(e.sal)>2000
ORDER BY TO_CHAR(e.hiredate,’YYYY’), SUM(e.sal) DESC;

Since e.job is no longer in the HAVING clause, we don’t have to put it in GROUP BY, but we CAN. The Rule on GROUP BY clause says that at very least, the columns referenced in SELECT, HAVING, and ORDER BY clauses must be referenced in GROUP BY as well, but there might be other columns.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

Interview Question: Can a Select statement have HAVING clause and no GROUP BY?

Question: Can a Select statement have HAVING clause and no GROUP BY?
Level: Intermediate/Advanced.

I was first asked this question about 20 years ago and I have to admit I did not provide the right answer which seemed to be very counter intuitive.

The correct answer: Yes, it can.

Example:

SELECT SUM(sal)
FROM emp
HAVING COUNT(*)>10

Essentially, when we don’t use GROUP BY clause we treat the entire table as a single group. As with any group, we can reference various aggregate functions in SELECT, HAVING, and ORDER BY clauses.

We can even use some analytical functions as follows:

SELECT COUNT(*), SUM(COUNT(*)) OVER() sm
FROM emp
HAVING COUNT(*)<100

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

Create and test a function that returns a cursor.

Interview Question: How to create and test a function that returns a cursor?

Level: Beginner/Intermediate

Step 1: Create a sample function

All you need to know to complete this step is how to use SYS_REFCURSOR type:

SQL> CREATE OR REPLACE FUNCTION get_emp_data RETURN SYS_REFCURSOR
  2  AS
  3     c SYS_REFCURSOR;
  4  BEGIN
  5     OPEN c FOR
  6     SELECT empno,ename,job,deptno
  7     FROM emp;
  8
  9     RETURN c;
 10  END;
 11  /

Function created.

Step 2: Test the new function
I will demonstrate 3 methods/workarounds for testing the function that has just been created.

Method/Workaround #1: Using Anonymous Block
Assumption: We know the structure of the returning cursor, i.e fields and their data types.

set serveroutput on size 2000 format wrapped

DECLARE
  c_emp SYS_REFCURSOR;
  TYPE typ_emp IS RECORD
   (
      empno  emp.empno%TYPE,
      ename  emp.ename%TYPE,
      job    emp.job%TYPE,
      deptno emp.deptno%TYPE
   );
   v typ_emp;
BEGIN
   c_emp:=get_emp_data;
   --Print the header line:
   DBMS_OUTPUT.PUT_LINE(LPAD('empno', 10) || '  ' ||
                        RPAD('ename', 15) ||
                        RPAD('job', 15) ||
                        LPAD('deptno', 6)
                        );
   DBMS_OUTPUT.PUT_LINE(LPAD('------', 10) || '  ' ||
                        RPAD('------', 15) ||
                        RPAD('------', 15) ||
                        LPAD('------', 6)
                        );
   LOOP
       FETCH c_emp INTO v;
       EXIT WHEN c_emp%NOTFOUND;
       --Print the current employee record:
       DBMS_OUTPUT.PUT_LINE(LPAD(v.empno, 10) || '  ' ||
                            RPAD(v.ename, 15) ||
                            RPAD(v.job, 15) ||
                            LPAD(v.deptno, 6)
                           );
   END LOOP;
   CLOSE c_emp;
END;
/
     empno  ename          job            deptno
    ------  ------         ------         ------
      7369  SMITH          CLERK              20
      7499  ALLEN          SALESMAN           30
      7521  WARD           SALESMAN           30
      7566  JONES          MANAGER            20
      7654  MARTIN         SALESMAN           30
      7698  BLAKE          MANAGER            30
      7782  CLARK          MANAGER            10
      7788  SCOTT          ANALYST            20
      7839  KING           PRESIDENT          10
      7844  TURNER         SALESMAN           30
      7876  ADAMS          CLERK              20
      7900  JAMES          CLERK              30
      7902  FORD           ANALYST            20
      7934  MILLER         CLERK              10

PL/SQL procedure successfully completed.

Method/Workaround #2: Using Select Statement
This method does not require us to know the structure of the cursor returned by the function:

SQL> SELECT get_emp_data
  2  FROM dual;

GET_EMP_DATA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

Method/Workaround #3: Using Bind Variable and PRINT command in SQL*PLus
The following method does not require us to know the structure of the cursor returned by the function, but it can only be executed in SQL*Plus:

SQL> var c refcursor
SQL> exec :c:=get_emp_data

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

How to check and change Oracle ArchiveLog Mode

Level: Beginner (DBA)

Question: How to check database ArchiveLog mode?

Answer:

To check the database archive log mode you need to
Step 1: Connect as SYSDBA

ex: connect sys/sysdbapass@server as sysdba

Step 2: Execute one of the following commands:
a) ARCHIVE LOG LIST

--In SQL*PLus
SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

b) Select from v$database

SQL> SELECT log_mode
  2  FROM sys.v$database
  3  /

LOG_MODE
------------
NOARCHIVELOG

Question: How to enable ArchiveLog mode?

You can specify the initial archiving mode in the CREATE DATABASE statement. Normally, there is no need to use this option as you can always change the mode later.

Answer:

Step 1: Connect as sysdba user

Step 2: Assuming that you are using spfile for parameter initialization, we need to configure the archive log destination. We will use a simple single destination option. All available options are described in the Oracle documentation.

SQL> ALTER SYSTEM SET log_archive_dest='F:\OraArchivedLogs' SCOPE=spfile;

System altered.

REM Note that this change will only work if db_recovery_file_dest is set to a blank, so you can execute another command just in case:
SQL> ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=spfile;

System altered.

Step 3: Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Backup the database (Cold backup)
Step 5: Start a new instance and mount the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.

Step 6: Change the database archiving mode

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

Step 7: Open the database.

SQL> ALTER DATABASE OPEN;

Database altered.

Step 8: Shut down the database (see Step 3 above)

Step 9: Backup the database (see Step 4 above)

Step 10: Open the database:

SQL> STARTUP
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Next log sequence to archive   36300
Current log sequence           36300

Question: How to disable ArchiveLog mode?

Answer:

Step 1: Connect as sysdba user
Step 2: Shutdown the database
Step 3: Backup the database (Cold backup)
Step 4: Start a new instance and mount the database.
Step 5: Change the archivelog mode:

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

Step 6: Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

 ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
  1 KING               5000 FORD               3000
  2 SCOTT              3000 JONES              2975
  3 BLAKE              2850 CLARK              2450
  4 ALLEN              1600 TURNER             1500
  5 MILLER             1300 WARD               1250
  6 MARTIN             1250 ADAMS              1100
  7 JAMES               950 SMITH               800

Problem Level: Intermediate/Advanced

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
START WITH rn=1
CONNECT BY rn=1+PRIOR rn

Method/Workaround #2: Using Analytical Function (Level: Advanced)

WITH X AS (
SELECT ename left_name, sal left_sal, 
       LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name, 
       LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
       ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
                       right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
      FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
            FROM emp
            )
      )
PIVOT (MAX(ename) AS name,
       MAX(sal)   AS sal
       FOR (col_no) IN (1 AS left, 2 AS right)
       )
ORDER BY 1

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal, 
                      SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1

Method/Workaround #5: Using Self-Join (Level: Intermediate)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
                     b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn 
WHERE mod(a.rn,2)=1   

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