select from table with no direct relation or foriegn keys

SELECT
  E.EMPNO,
  E.ENAME,
  E.JOB,
  D.DEPTNO,
  D.LOC,
  E.SAL
FROM
  scott.emp E
LEFT JOIN SCOTT.DEPT D
ON
  E.DEPTNO=D.DEPTNO;

SELECT
  E.EMPNO,
  E.ENAME,
  E.JOB,
  D.DEPTNO,
  D.LOC,
  E.SAL,
  (
    SELECT      grade
    FROM
      SCOTT.SALGRADE S
    WHERE
      E.SAL BETWEEN S.LOSAL AND S.HISAL
  ) AS SALGRADE
FROM
  scott.emp E
LEFT JOIN SCOTT.DEPT D
ON
  E.DEPTNO=D.DEPTNO;

Create a PrimaryKey on existing table without unique values

lets assume that there was a table ABC that was already existing in the database and you want to add an additional column with unique primary key values.


sql to create table ABC :

  CREATE TABLE "ABC"
   (    "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "USER_ID" NUMBER NOT NULL ENABLE,
    "CREATED" DATE NOT NULL ENABLE )
   TABLESPACE "USERS" ;

Now we  can add an additional column ID which will be populated with all unique values for PrimaryKey.

alter table abc add(ID NUMBER);

We will now create a sequence and get the values from the seq and insert them into table ABC new ID column:

CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
MINVALUE 1
NOCYCLE;

Now insert the unique values into the database with below sql:
UPDATE abc SET ID = SEQ_ID.NEXTVAL;


now you can add unique constraint (or) add primary key constraint to table,so that it wont take any more duplicate value into the table.

alter table abc add primarykey (ID);