SQL interview questions

Table-1

Col1 col2
11     A   
26     B
31     C
43     D

Table-2

Col1 col2
16     E   
46     F
39     G
42     H

And need output is

Col1 col2 Col1 col2
11     A   16   E
26     B   46   F
31     C   39   G
43     D   42   H

Answer

Here’s one option:

SQL> with
  2  -- sample data
  3  a (col1, col2) as
  4    (select 11, 'A' from dual union all
  5     select 26, 'B' from dual union all
  6     select 31, 'C' from dual union all
  7     select 43, 'D' from dual
  8    ),
  9  b (col1, col2) as
 10    (select 16, 'E' from dual union all
 11     select 46, 'F' from dual union all
 12     select 39, 'G' from dual union all
 13     select 42, 'H' from dual
 14    ),
 15  -- find something to join rows on - for example, row number. Your example shows that
 16  -- values are sorted by COL2
 17  a2 as
 18    (select col1, col2, row_number() over (order by col2) rn from a),
 19  b2 as
 20    (select col1, col2, row_number() over (order by col2) rn from b)
 21  -- join a2 and b2 on RN
 22  select a2.col1, a2.col2, b2.col1, b2.col2
 23  from a2 join b2 on a2.rn = b2.rn
 24  order by a2.col1;

 COL1 COL2  COL1 COL2
----- ---- ----- ----
   11 A       16 E
   26 B       46 F
   31 C       39 G
   43 D       42 H

SQL>