How to create a procedure by joining two tables that doesn't have a common column in oracle sql?

Question

Table 1: EMP

Columns: EMPNO,ENAME,SAL,JOB,DEPTNO

Table 2 : SALGRADE

Columns : GRADE,HISAL,LOSAL

Given the EMPNO of an employee as input to the procedure, I'll have to get the corresponding Grade and SAL as output.

Answer 1

Join on the sal being between the losal and hisal. Something like:

SELECT g.grade,
       e.sal
       FROM emp e
            LEFT JOIN salgrade g
                      ON g.losal <= e.sal
                         AND g.hisal >= e.sal
       WHERE e.empno = ?;

Answer 2

Or you can think of DB design like below:

create table EMP
(
    EMPNO   NUMBER(5),
    ENAME   VARCHAR2(50),
    SAL     NUMBER(4),
    JOB     VARCHAR2(20),
    DEPTNO  NUMBER(2),
    GRADE   AS (CASE WHEN SAL >= 1000 AND SAL < 2000 THEN 'LOW'
                                 WHEN SAL >= 2000 AND SAL < 3000 THEN 'MEDIUM'
                                 WHEN SAL >= 3000 AND SAL < 4000 THEN 'HIGH'
                                 ELSE
                                    'UNKNOWN'
                            END),
    CONSTRAINT emp_pk PRIMARY KEY (EMPNO)
);

insert into emp (EMPNO,ENAME,SAL,JOB,DEPTNO) values (10001, 'Ram', 1000, 'King', 10 );
insert into emp (EMPNO,ENAME,SAL,JOB,DEPTNO) values (10002, 'Laxman', 2000, 'Minister', 20 );
insert into emp (EMPNO,ENAME,SAL,JOB,DEPTNO) values (10003, 'Sita', 3000, 'Queen', 30 );
insert into emp (EMPNO,ENAME,SAL,JOB,DEPTNO) values (10004, 'Ravan', 5000, 'Rakshas', 40 );

SELECT * FROM EMP;