Use Scalar Subqueries in SQL

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT   ename,
  2          (SELECT sal FROM emp WHERE empno = 1) AS New_Salary
  3  FROM     emp;

ENAME      NEW_SALARY
---------- ----------
SMITH             800
ALLEN             800
WARD              800
JONES             800
MARTIN            800
BLAKE             800
CLARK             800
SCOTT             800
KING              800
TURNER            800
ADAMS             800

11 rows selected.

SQL>

SQL> INSERT INTO emp (empno)
  2  VALUES ((SELECT empno FROM emp WHERE eNAME = 'SMITH') );

1 row created.

SQL>
SQL> select * from emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
         1 SMITH      CLERK            800         20
         2 ALLEN      SALESMAN        1600         30
         3 WARD       SALESMAN        1250         30
         4 JONES      MANAGER         2975         20
         5 MARTIN     SALESMAN        1250         30
         6 BLAKE      MANAGER         2850         30
         7 CLARK      MANAGER         2850         10
         8 SCOTT      ANALYST         3000         20
         9 KING       PRESIDENT       3000         10
        10 TURNER     SALESMAN        1500         30
        11 ADAMS      CLERK           1500         20
         1

12 rows selected.

SQL>